Working with ADO.NET in ASP.NET

MinalS's Avatar author of Working with ADO.NET in ASP.NET
ADO.NET is a mechanism used by the applications to communicate with a database for manipulating data..
In business applications and corporate organizations there is a huge data stored in the database. The data is stored in a relational database in the form of related tables. User needs to access data from the database. There is a requirement of the knowledge of the database commands. Every user is not familiar with the commands.

To overcome this limitation, business applications allows users to retrieve data stored in a database and represent them in a user friendly interface without the use of database commands. All database manipulation can be performed directly from the applications.

ADO.NET is a part of .NET framework architecture. It is a model used by the applications to communicate with a database for manipulating data as shown in the following figure.



In the above figure, client application can be Windows application, Web application or any other office applications, mobile applications. These applications need to retrieve and access from various data sources. The sources can be SQL Server, Oracle or OLEDB. There can be other sources as XML document, a text file or a Web service. These applications can be connected to the data sources using ADO.NET.

The ADO.NET Object Model



It enables the applications to connect to the data sources and manipulate the data. The model is designed in a way that developer can access and write to the variety of sources such as SQL server and XML.

The following figure shows the ADO.NET Object model.



In the ADO.NET object model, the data residing in the database is retrieved through a data provider. The provider is a set of components including command, connection, datareader and dataadapter objects. It provides data to all the applications performs the database updates. The application can access data either through the dataset or through a datareader object.

It also enables to create an XML representation of a dataset. In an XML representation of a dataset, data is written in XML format and the database schema is written in XML Schema Definition language (XSD).

The two key components of the ADO.NET object model are as follows:
  1. Data provider
  2. Dataset
1. Data Provider: A Data provider is used for connecting to a database, retrieving data, storing data in a dataset. The type of data provider is used depending on the data source. There are four main types of data providers. They are as listed below:
  • .NET Framework data provider for SQL Server: The .NET Framework data provider for SQL server is used to work with Microsoft SQL Server. The System.Data.dll assembly implements SQL Server data provider in the System.Data.SqlClient namespace.
  • .NET Framework data provider for OLE DB: The providers use the native OLE DB through Component Object Model (COM) interoperability. The System.Data.dll assembly implements SQL Server data provider in the System.Data.OleDb namespace.
  • .NET Framework data provider for ODBC: The provider uses the native ODBC Driver Manager through COM interoperability to enable data access. The System.Data.ODBC.dll assembly implements ODBC .NET framework data provider.
The main components of a data provider are as follows:
  1. Connection: This component is used to establish a connection with a data source.
  2. Command: This component is used to retrieve data from a data source in a read only and forward only mode.
  3. DataAdapter: This component is used to transfer data to and from a database. It retrieves data from a database into a dataset.
2. DataSet: A dataset is a memory based relational representation of data. It is a part of the disconnected environment. It is a disconnected, cached set of records that are retrieved from the database. A dataset is a collection of one or more DataTable objects made up of rows and columns of data a well as constraint and relation information about data in the datatable objects.

A dataset is present in the DataSet class in the System.Data namespace. The components of a dataset are:
  • DataTableCollection: It contains all tables retrieved from the datasource.
  • DataRelationColection: It contains the relationship and links between tables in a dataset
  • DataTable: It represents a table in the datatable collection of a dataset
  • DataRowCollection: It contains all the rows in the datatable
  • DataColoumnCollection: It contains all the columns in a datatable

Features of ADO.NET



The key features of ADO.NET are as follows:
  1. Disconnected data architecture: It supports the disconnected data architecture. The application connects to the database and keeps the connection open till the application is running. The application interacts with the database only when it has to retrieve or update data.
  2. Data cached in datasets: A dataset is common method for accessing data in ADO.NET as it implements the disconnected architecture. It is possible for the user to interact with the database while processing the records.
  3. Scalability: It supports scalability by working with datasets. The operations are performed on the dataset. The resources are saved and the database can meet the demands of the users.
  4. Data transfer in XML format: The data is transferred from a database into a dataset and from the dataset to another component using XML. It is the fundamental format for data transfer in ADO.NET.
Accessing Data from a Database using ADO.NET

1. Connection objects

The connection component of a data provider is used for establishing connection with the data source. User must create a connection object and then provide the connection string. To connect to the SQL server database, use the SqlConnection class to open the connection.

Properties and methods of the connection object are as shown below:
  1. Attributes: It sets or returns the long value to the user.
  2. CommandTimeout: It sets or returns the value in seconds for the waiting of command to execute
  3. ConnectionString: It provides information such as data source and database name used to establish connection with a database
  4. Cancel: It is used for terminating the asynchronous method call
  5. Close: It is used to close the connection to the database
  6. Execute: It is used to execute the specified query, SQL statement, text, or stored procedure
The Connection string property is a series of unique pieces of information known as connection string properties. While writing the code, the string properties are separated by semicolons. The various parameters used to define the connection string are as listed below:
  1. Data Source: It is used to specify the name of the server to be used when the connection is open.
  2. Initial Catalog: It is used to specify the name of the database
  3. Provider: It is used to return the name of the provider for the connection
  4. User ID: It is used to specify the server login account
  5. Password: It is used to specify the login password for the server login account
  6. Integrated Security: It is used to determine whether the connection is secure or not
The example of creating the connection object is shown below:
Code:
SqlConnection con=new SqlConnection();
con.ConnectionString=”Data Source=SQLSERVER1; 
Initial Catalog=HR; User ID=sa; Password=password@123”;
In the above code, the Provider and the Integrated Security parameters of the ConnectionString property are not used. The .NET Framework data provider for SQL Server would be used as the default Provider. The value of Integrated Security is not used as the value for User ID and Password parameters is assigned.

2. Command Objects

After a connection object is created and connection is established, user can execute commands and return results from the data source by using the command object.

To execute the SQL statements, create an instance of SqlCommand class in the System.Data.SqlClient namespace. The command object specifies the SQL statements that are to be executed and the connection needs to be used to execute these statements. User can execute SQL statements or stored procedures through the command objects.

The following code snippet shows how to create the command object:
Code:
SqlConnection con=new SqlConnection();
Con.ConnectionString=”Data Source=SQLSERVER1; Initial Catalog=HR; User ID=sa; Password=password@123”;
SqlCommand cmd=new SqlCommand(“select * from salary”, con);
In the above code, the SqlCommand object cmd is initialized to the SQL statements that need to be executed.

The properties and methods of the Command object are as listed below:
  1. CommandText: It is used for representing the statement or name of the stored procedure
  2. CommandType: It is used to indicate the CommandText property should be used in the code
  3. CommandTimeout: It is used to indicate the time to wait during the execution of a particular command
  4. ExecuteNonQuery: The method executes the command specified and returns the number of rows affected
  5. ExecuterReader: It executes the method and returns an instance of the SqlDataReader class
  6. ExecuteScalar: The method executes command and returns the first column of first row of the result set
  7. ExecuteXMLReader: The method executes the command specified and returns the instance of XmlReader class.
3. DataReader Objects

After defining the command object, user needs to execute and retrieve data from the data source. The data returned needs to be stored in another object. The DataReader object can be used to store the data returned after executing the command object. It supports the forward – only read – only access to the results.

To create the DataReader object, use the ExecuteReader() method of the command object as shown in the following code snippet:
Code:
SqlConnection con=new SqlConnection();
Con.ConnectionString=”Data Source=SQLSERVER1; Initial Catalog=HR; User ID=sa; Password=password@123”;
SqlCommand cmd=new SqlCommand(“select * from salary”, con);
SqlDataReader dr=new SqlDataReader();
dr=cmd.ExecuteReader();
dr.Read();
In the above code snippet, after SqlCommand object cmd is initialized, SqlDataReader object is created. It is initialized by calling the ExecuteReader() method of the SqlCommand class. The SqlDataReader object provides the means of reading stream of rows from a SQL Server database.

Once the data is retrieved in the DataReader object, user can view a single row at a time by using the Read() method.

The properties and methods of the Command object are as shown below:
  1. FieldCount: It specifies the number of columns of the current row or record
  2. HasRows: It specifies whether the current row has at least one row
  3. IsClosed: It specifies the DbDataReader is closed
  4. GetString: It gets the column ordinal with the column name
  5. NextResult: It proceeds to the next result while reading the batch of statements
  6. Read: It advances the record to the next record.
  7. GetFieldType: It gets the field type of the specified column
4. Dataset Objects

A dataset is a memory based relational representation of data. A dataset is a disconnected cached data set of records that are retrieved from a database. The dataset is a virtual database containing tables, rows, and columns. They are widely used as they do not require the connection to be opened every time. The data is cached from the database and then the connection is closed. The performance of the application is improved.

The dataset object requires the data adapter to retrieve data from the data source. The code for retrieving data is as shown below:
Code:
SqlConnection con1=new SqlConnection(“Data Source=SQLSERVER1; Initial Catalog=Students; User ID=sa; Password=Password@123”);
DataSet ds=new DataSet();
Con1.Open();
SqlDataAdapter da=new SqlDataAdapter (“Select * from course”, con1);
da.Fill (ds, “course”);
GridView1.DataSource=ds;
GridView1.DataBind ();
In the above code, the data from the data source is retrieved by the data adapter and filled into the dataset. The data is displayed by using GridView control.

The list of properties and methods for the Dataset are as listed below:
  1. Container: It gets the container for the component
  2. DataSetName: It gets or sets the name of the DataSet
  3. IsInitialized: It gets a value indicating whether the data is initialized
  4. Relations: It gets the collection of relations that links the tables and allows the navigation from the parent to child tables
  5. AcceptChanges: It commits all the changes since the last time the method was called
  6. CreateDataReader: It returns the DataTableReader with one result per DataTable from the Tables collection
  7. GetChanges (): It gets a copy of the DataSet that contains the changes performed since it was loaded
  8. RejectChanges: It rolls back all the changes made to the DataSet since it was created

Accessing Data in Data Bound Web Server controls



The Data Bound controls are used to bind the data source control to display and modify data in an application. They are composite controls that can be combined with other Web controls into a single unit. There are several data bound controls in ASP.NET. Some of them are demonstrated below.

1. GridView Control

To access data from the database and display the value in the GridView control, follow the steps mentioned below:
  1. Open Visual Studio application in your system
  2. Create a new ASP.NET Web application
  3. Add the GridView control on the design page of the web application. The Toolbox contains the control.


  4. After dragging and dropping, the design view contains the GridView control. The view of the control is as shown below:


  5. Navigate to the .cs page of the added GridView controls page
  6. Add the following code in the code behind file of the application
    Code:
    protected void Page_Load (object sender, EventArgs e)
    {
         SqlConnection con=new SqlConnection ();
         Con.ConnectionString=”Data Source=SQLSERVER1; 
         Initial Catalog=      Students; User ID= sa; Password=”Pass@123”;
         SqlDataSource data=new SqlDataSource();
         data.ConnectionString=con;
         data.SelectCommand=”SELECT studid, studname, course FROM Students”;
         GridView1.DataSource=data;
         GridView1.DataBind ();
    }
  7. After executing the application, the GridView is populated and the output is as shown below
2. DataList Control

The DataList control allows you to display the rows of the database information in a customized format. The steps for adding a list view control and binding them to the database is as shown below:
  1. Drag and drop the DataList control from the Toolbox to the design view
  2. Navigate to the source code window of the application
  3. Add the following code in the source code window
Code:
<asp:datalist id=”DataList1” runat=”server” datakeyfield=”studid” datasourceid=”SqlDataSource1” height=”250px” repeatcolumns=”3” repeatdirection=”Horizontal” width=”130px”>
  <itemtemplate>
    studid: <asp:label id=”IdLabel” runat=”server” text=’<%# eval(“studid”)%>’ /> <br/>
    studname: <asp:label id=”namelabel” runat=”server” text=’<%#Eval (“studname”)%>’ /> <br/>
    course: <asp:label id=”courselabel” runat=”server” text=’<%#Eval (“course”)%>’ /> <br/>
</br>
  </itemtemplate>
</asp:datalist>
<asp:sqldatasource id=”SqlDataSource1” runat=”server” connectionstring=”<%$ connectionstrings:connectionstring %>” SelectCommand=”SELECT * FROM [Students]”> </asp:sqldatasource>
The output is as shown below: