Unit 4 Working with ADO.Net PDF

Unit 4 Working with ADO.Net   ADO.Net Architecture  Characteristics of ADO.Net  Data Namespaces  ADO.Net Object Model  DataSet  DataTable  DataRelation Contents  Connection object  Command Obj...

Unit 4 Working with ADO.Net   ADO.Net Architecture  Characteristics of ADO.Net  Data Namespaces  ADO.Net Object Model  DataSet  DataTable  DataRelation Contents  Connection object  Command Object  Data Reader Object  DataAdapter Object  Data Controls (Repeater, DataList, DataGrid)  Binding data with Crystal Report  Performing CRUD operations   ADO.NET is a set of classes (a framework) to interact with data sources such as databases and XML files. ADO is the acronym for ActiveX Data Object. It is created by Microsoft as part of its.NET framework.  It allows us to connect to underlying data or databases. It has classes and methods to retrieve and manipulate data. What is  The following are a few of the.NET applications that use ADO.NET to connect to a database, execute commands and ADO.NET? retrieve data from the database.  ASP.NET Web Applications.  Console Applications.  Windows Applications.  All the ADO.NET classes are located into System.Data.dll and integrated with XML classes located into System.Xml.dll.   These are the components that are designed for data manipulation and fast access to data. It provides various objects such as Connection, Command, DataReader and DataAdapter that are used to perform database operations. ADO.NET Architecture / Object Model  Class Description SqlConnection It is used to create SQL Server connection. This class cannot be inherited. SqlCommand It is used to execute database queries. This class cannot be inherited. (ExecuteNonQuery to execute Insert, Update, Delete) SqlDataAdapter It represents a set of data commands and a database connection that are used to fill the DataSet. This class cannot be inherited. Classes SqlDataReader It is used to read rows from a SQL Server database. This class cannot be inherited. It is read only, forward only. It is faster than dataset in fetching data. Ex: Select query DataSet The ADO.NET DataReader object is one of the two techniques provided by ADO.NET. This object is utilized to obtain data from the data store.  The 4 core objects that make up a.NET Framework data provider. 1. Connection - Establishes a connection to a specific data source. The base class for all Connection objects is the DbConnection class. 2. Command - Executes a command against a data source. Exposes Parameters and can execute in the scope of a Transaction from a Connection. The base class for all Command objects is the DbCommand class. Classes 3. DataReader - Reads a forward-only, read-only stream of data from a data source. The base class for all DataReader objects is the DbDataReader class. 4. DataAdapter - Populates a DataSet and resolves updates with the data source. The base class for all DataAdapter objects is the DbDataAdapter class.  The ADO.NET DataSet is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. DataSet  The DataSet contains a collection of one or more DataTable objects. DataTable objects consisting of rows and columns of data, and also primary key, foreign key, constraint, and relation information about the data in the DataTable object.   Maintainability  The multilayered architecture enables building application logic in a separate layer – it mitigates the risk and simplifies adding new features.  Interoperability  Data is transferred to and from data sources and internal data representations in an XML format.  Programmability ADO.NET  Strongly typed data and IntelliSense support in Microsoft Visual Studio simplifies writing statements. Features  Scalability  High application scalability is possible thanks to a disconnected mode that enables users to work with the data without retaining an open connection. It conserves resources and allows multiple users to access the data simultaneously.  Performance  Both connected and disconnected classes are optimized for performance, and the proper use of those classes enables maintaining application performance at a high level.   Data Provider is used to connect with the database and then retrieve data as per command execution. It's like a lightweight component with multiple roles. ADO.NET  Some of the popular data providers and frameworks are listed below: Data  ADO.NET Data Providers: Namespaces  OleDb (System.Data.OleDb) - Object Linking and Embedding, Database  SqlClient (System.Data.SqlClient)  Odbc (System.Data.Odbc)  OracleClient (System.Data.OracleClient)   There are the following two types of connection architectures: Various  Connected architecture: the application remains Connection connected with the database throughout the processing. (DataReader) Architectures  Disconnected architecture: the application automatically connects/disconnects during the processing. The application uses temporary data on the application side called a DataSet and DataTable.   It is used to establish an open connection to the SQL Server database.  It is a sealed class so that cannot be inherited. ADO.NET SqlConnection  SqlConnection class uses SqlDataAdapter and SqlCommand classes together to increase performance when connecting Class to a Microsoft SQL Server database.  Connection does not close explicitly even it goes out of scope. Therefore, you must explicitly close the connection by calling Close() method.  Method Description BeginTransaction() It is used to start a database transaction. ChangeDatabase(Stri It is used to change the current database for ng) an open SqlConnection. ChangePassword(Stri It changes the SQL Server password for the ADO.NET ng,String) user indicated in the connection string. SqlConnection Close() It is used to close the connection to the database. Class Methods CreateCommand() It enlists in the specified transaction as a distributed transaction. GetSchema() It returns schema information for the data source of this SqlConnection. Open() It is used to open a database connection. ResetStatistics() It resets all values if statistics gathering is enabled.  SqlCommand object is one that will be running the query against the database, thus while creating SqlCommand Object, we provide the Query and ConnectionOject Query - the SQL statement that need to be run against database ConnectionObject – instance of SqlConnection that we created to connect to the database Example: Sql string cs=@”Data Source = (localdb)\MSSQLLOCALDB; Initial Catalog = Emp; Integrated Security = true”; Command string query = @”select empid,empname from empData;”; using (SqlConnection connection = new SqlConnection(cs)) { connection.Open(); SqlCommand cmd=new SqlCommand(query,conn); }  Once we have SqlCommand Instance ready, we can run the given query (such as CREATE, UPDATE, INSERT, or DELETE)against the database, by using the methods of SqlCommand Class using its instance. Methods: 1. ExecuteNonQuery() - Executes a Transact-SQL statement against the connection and returns the number of rows affected. 2. ExecuteReader() - Sends the CommandText to the Connection and Sql builds a SqlDataReader. Command 3. ExecuteScalar() - Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored. Console app Demo  Console App  using System; using System.Data.SqlClient; namespace AdoNetConsoleApplication{ class Program{ static void Main(string[] args) { Console new Program().Connecting(); app } Demo public void Connecting(){ using ( using // Creating Connection Block SqlConnection con = new SqlConnection("data source=LENOVO\\SQLEXPRESS; database=Demo; integrated security=SSPI") ) { con.Open(); Console.WriteLine("Connection Established Successfully");}}}} Console app Demo  using System; using System.Data.SqlClient; namespace AdoNetConsoleApplication { class Program { static void Main(string[] args) { new Program().Connecting(); Console } App public void Connecting() { using SqlConnection con = null; try try { // Creating Connection catch con = new SqlConnection("data source=.; database=student; integrated security=SSPI"); con.Open(); Console.WriteLine("Connection Established Successfully"); } catch (Exception e) { Console.WriteLine("OOPs, something went wrong.\n"+e); } finally { con.Close(); } } }}   This class is used to store and execute SQL statement for SQL Server database. It is a sealed class so that cannot be inherited. Constructor Description SqlCommand() It is used to initialize a new instance of the SqlCommand class. SqlCommand(Strin I t i s u s e d t o i n i t i a l i z e a n e w i n s t a n c e o f t h e ADO.NET g) SqlCommand class with a string parameter. SqlCommand SqlCommand(String, I t i s u s e d t o i n i t i a l i z e a n e w i n s t a n c e o f t h e SqlConnection) SqlCommand class. It takes two parameters, first is Class query string and second is connection string. SqlCommand(String, I t i s u s e d t o i n i t i a l i z e a n e w i n s t a n c e o f t h e S q l C o n n e c t i o n , SqlCommand class. It takes three parameters query, SqlTransaction) connection and transaction string respectively. SqlCommand(String, It Initializes a new instance of the SqlCommand class S q l C o n n e c t i o n , with specified command text, connection, S q l T r a n s a c t i o n , transaction, and encryption setting. SqlCommandColum nEncryptionSetting)  using System; using System.Data.SqlClient; namespace AdoNetConsoleApplication{ class Program{ static void Main(string[] args){ new Program().CreateTable(); } public void CreateTable(){ ADO.NET SqlConnection con = null; try{ SqlCommand con = new SqlConnection("data source=LENOVO\\SQLEXPRESS; Class database=Demo; integrated security=SSPI"); and SqlCommand cm = new SqlCommand("select * from tbldemo", con); con.Open(); SqlDataReader SqlDataReader sdr = cm.ExecuteReader(); while (sdr.Read()) Class { Console.WriteLine(sdr["sno"] + " " + sdr["name"]); } } catch (Exception e){ Console.WriteLine("OOPs, something went wrong." + e); } finally{ con.Close(); } } } } ADO.NET SqlCommand Class and SqlDataReader Class   DataSet is a collection of data tables that contain the data.  It is used to fetch data without interacting with a Data Source that's why, it also known as disconnected data access method.  It is an in-memory data store that can hold more than one table at the same time.  We can use DataRelation object to relate these tables. The DataSet DataSet and can also be used to read and write data as XML document. DataAdapter  The DataAdapter works as a bridge between a DataSet and a data source to retrieve data.  DataAdapter is a class that represents a set of SQL commands and a database connection. It can be used to fill the DataSet and update the data source. DataSet and DataAdapter  using System; using System.Data.SqlClient; using System.Data; namespace DataAdapterDataSetDemo { public partial class WebForm1 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { DataSet and using (SqlConnection con = new SqlConnection("data DataAdapter source=RAVIKUMAR\\SQLEXPRESS; database=Demo; integrated security=SSPI")) { SqlDataAdapter sde = new SqlDataAdapter("Select * from tbldemo", con); DataSet ds = new DataSet(); sde.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); }}}} DataSet and DataAdapter   The main use of Repeater Control is for displaying a repeated list of items bound to the control.  The Repeater control may be bound to a database table, an XML file, or another list of items.  A Repeater Control is faster and lightweight for displaying data compared to a GridView or DataGrid. Repeater  With the Repeater control we can display data in a custom format.  It is an unformatted control. It has no built-in layout or styles, so you must explicitly declare all layout, formatting, and style tags within the control's templates.  The main drawback of a Repeater Control is that it doesn't support paging and sorting.   The Repeater Control has the following types of template fields:  Item Template – (describing how each item will be rendered from the data source collection.) Repeater  Header Template – (display header element)  Footer Template – (display footer)  Alternating Item Template – (color and style)  Separator Template – (separate each item)   HeaderTemplate: This template is used for elements that you want to render once before your ItemTemplate section.  FooterTemplate: - This template is used for elements that you want to render once after your ItemTemplate section.  ItemTemplate: This template is used for elements that are Repeater rendered once per row of data. It is used to display records  AlternatingItemTemplate: This template is used for elements that are rendered every second row of data. This allows you to alternate background colors. It works on even number of records only.  SeperatorTemplate: It is used for elements to render between each row, such as line breaks. Repeater  //WebForm1.aspx SNO: Student Repeater Name:  //WebForm1.aspx.cs protected void Page_Load(object sender, EventArgs e) { SqlConnection con = new SqlConnection("Data Source=RAVIKUMAR\\SQLEXPRESS;Database=Demo;Integrated Security=True"); Repeater SqlDataAdapter sda = new SqlDataAdapter("select * from tbldemo", con); DataTable dt = new DataTable(); sda.Fill(dt); Repeater1.DataSource = dt; Repeater1.DataBind(); } Repeater   The ASP.NET DataList control is a light weight server side control that works as a container for data items. It is used to display data into a list format to the web pages.  It displays the data from the data source and rows in various layouts, like arranging them in either rows or columns (arrange data items DataList horizontally or vertically using property RepeatDirection).  Rendered as Table.  Performance is slow as compared to Repeater and faster when compared to GridView.   The DataList appearance is controlled by its template fields. The following template fields are supported by the DataList control:  Itemtemplate: It specifies the Items present in the Datasource, it renders itself in the browser as many rows present in the data source collection.  EditItemTemplate: Used to provide edit permissions to the user. DataList  HeaderTemplate: Used to display header text to the data source collection.  FooterTemplate: Used to display footer text to the data source collection.  ItemStyle: Used to apply styles to an ItemTemplate.  EditStyle: Used to apply styles to an EditItemTemplate  HeaderStyle: Used to apply styles to a HeaderTemplate  FooterStyle: Used to apply styles to a FooterTemplate.  DataList ID: Name: Email:  protected void Page_Load(object sender, EventArgs e) { DataTable table = new DataTable(); table.Columns.Add("ID"); table.Columns.Add("Name"); table.Columns.Add("Email"); table.Rows.Add("101", "Sachin Kumar", "[email protected]"); DataList table.Rows.Add("102", "Peter", "[email protected]"); table.Rows.Add("103", "Ravi Kumar", "[email protected]"); table.Rows.Add("104", "Irfan", "[email protected]"); DataList1.DataSource = table; DataList1.DataBind(); } } DataList  .NET Framework provides DataGrid control to display data on the web page. It was introduced in.NET 1.0 and now has been deprecated.  DataGrid is used to display data in scrollable grid. It DataGrid requires data source to populate data in the grid.  It is a server side control and can be dragged from the toolbox to the web form.  Data Source for the DataGrid can be either a DataTable or a database.  .NET Framework provides DataGrid control to display data on the web page. It was introduced in.NET 1.0 and now has been deprecated.  DataGrid is used to display data in scrollable grid. It requires data source to populate data in the grid. DataGrid  It is a server side control and can be dragged from the toolbox to the web form.  Data Source for the DataGrid can be either a DataTable or a database.  Already covered in slide number 24.  Repeater DataList GridView Template driven. Rendered as Table. Introduced with Asp.Net 2.0. This feature is not supported Automatically generates Built-in Paging and Sorting is (automatic column columns from the data provided. generation). source. Selection of row is not Built-in supports for Update Selection of row is supported. supported. and Delete operations. Repeater vs Editing of contents is not Editing of contents is Supports auto format or DataList vs supported. supported. style features. GridView This feature is not supported You can arrange data items horizontally or vertically in RepeatDirection property is (arrange data items with DataList by using property not supported. RepeatDirection). RepeatDirection. Doesn’t support This is very lightweight and Performance is slow as customizable row separator. fast data control among all compared to Repeater Performance is slow as the data control. compared to DataList. S.No. ADO ADO.NET It is based on COM (Component Object 1. It is a CLR (Common Language Runtime) based library. Modelling). It does not needs active connection to access data 2. It works only when data store is connected. from data store. 3. It has feature of locking. It does not have feature of locking. It access and store data from data source by It access and store data from data source by dataset 4. recordset object. object. 5. XML integration is not feasible in ADO. XML integration is feasible in ADO.NET. 6. In ADO, data is stored in binary form. While in this, data is stored in XML. It give us the choice of using weather client side and 7. It allow us to create client side cursors only. server side cursors. It uses DataRelational objects, for combining data It requires SQL JOINs and UNIONs to combine 8. from multiple tables without requiring JOINs and data from multiple tables in a single result table. UNIONs. It supports sequential access of rows in a It allows completely non-sequential data access in 9. RecordSet. DataSet through collection based hierarchy. Crystal Report using Microsoft  https://www.youtube.com/watch?v=1tEuKH64eB4 Reporting Services Projects 2022 CRUD Operation with ADO.NET CRUD Operation with ADO.NET  using System; using System.Web.UI; using System.Data; using System.Data.SqlClient; namespace CRUD_ASP_ADO_DEMO{ public partial class WebForm1 : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e){ if (!IsPostBack) { LoadRecord(); CRUD } } Operation SqlConnection con = new SqlConnection("Data Source = ravikumar\\sqlexpress; Initial Catalog = Demo; User ID = sa; Password=123;Integrated Security=True"); with protected void Button1_Click(object sender, EventArgs e) ADO.NET { con.Open(); SqlCommand comm = new SqlCommand("Insert into crudtbl values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "')", con); comm.ExecuteNonQuery(); con.Close(); ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Successfully Inserted');", true); LoadRecord(); ClearData(); }  void LoadRecord() { SqlCommand comm = new SqlCommand("select * from crudtbl", con); SqlDataAdapter d = new SqlDataAdapter(comm); DataTable dt = new DataTable(); d.Fill(dt); CRUD GridView1.DataSource = dt; Operation GridView1.DataBind(); with } ADO.NET void ClearData() { TextBox1.Text = ""; TextBox2.Text = ""; TextBox3.Text = ""; TextBox4.Text = ""; TextBox5.Text = ""; }  protected void Button2_Click(object sender, EventArgs e) { con.Open(); CRUD SqlCommand comm = new SqlCommand("update crudtbl set Name= '" + TextBox2.Text + "', Address= '"+TextBox3.Text+"', Age='"+TextBox4.Text+"', Contact='"+TextBox5.Text+"' where StuID= '"+TextBox1.Text+"' ", con); Operation comm.ExecuteNonQuery(); with con.Close(); ADO.NET ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Successfully Updated');", true); LoadRecord(); ClearData(); }  protected void Button3_Click(object sender, EventArgs e) { con.Open(); SqlCommand comm = new SqlCommand("delete crudtbl where StuID = '" + TextBox1.Text + "' ", con); CRUD comm.ExecuteNonQuery(); con.Close(); Operation with ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "alert('Successfully Deleted');", true); ADO.NET LoadRecord(); ClearData(); } } }   ADO.Net Architecture  Characteristics of ADO.Net  Data Namespaces  ADO.Net Object Model  DataSet  DataTable  DataRelation Summary  Connection object  Command Object  Data Reader Object  DataAdapter Object  Data Controls (Repeater, DataList, DataGrid)  Binding data with Crystal Report  Performing CRUD operations End of unit 4