ADO.NET CONCEPTS

ADO .NET

Most applications need data access at one point of time making it a crucial component when working with applications. Data access is making the application interact with a database, where all the data is stored. Different applications have different requirements for database access. ASP.NET uses ADO .NET (Active X Data Object) as it's data access and manipulation protocol which also enables us to work with data on the Internet. 

ADO.NET Data Architecture

Data Access in ADO.NET relies on two components: DataSet and Data Provider.

DataSet

The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like Microsoft SQL server database, an Oracle database or from a MicrosoftAccess database.

Data Provider

The Data Provider is responsible for providing and maintaining the connection to the database. A DataProvider is a set of related components that work together to provide data in an efficient and performance driven manner. The .NET Framework currently comes with two DataProviders: the SQL Data Provider which is designed only to work with Microsoft's SQL Server 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle. Each DataProvider consists of the following component classes:

The Connection object which provides a connection to the database
The Command object which is used to execute a command
The DataReader object which provides a forward-only, read only, connected recordset
The DataAdapter object which populates a disconnected DataSet with data and performs update

Data access with ADO.NET can be summarized as follows:

A connection object establishes the connection for the application with the database. The command object provides direct execution of the command to the database. If the command returns more than a single value, the command object returns a DataReader to provide the data. Alternatively, the DataAdapter can be used to fill the Dataset object. The database can be updated using the command object or the DataAdapter.

Component classes that make up the Data Providers

The Connection Object

The Connection object creates the connection to the database. Microsoft Visual Studio .NET provides two types of Connection classes: the SqlConnection object, which is designed specifically to connect to Microsoft SQL Server 7.0 or later, and the OleDbConnection object, which can provide connections to a wide range of database types likeMicrosoft Access and Oracle. The Connection object contains all of the information required to open a connection to the database.

The Command Object

The Command object is represented by two corresponding classes: SqlCommand and OleDbCommand. Command objects are used to execute commands to a database across a data connection. The Command objects can be used to execute stored procedures on the database, SQL commands, or return complete tables directly. Command objects provide three methods that are used to execute commands on the database:

ExecuteNonQuery: Executes commands that have no return values such as INSERT, UPDATE or DELETE
ExecuteScalar: Returns a single value from a database query
ExecuteReader: Returns a result set by way of a DataReader object

The DataReader Object

The DataReader object provides a forward-only, read-only, connected stream recordset from a database. Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated. Rather, the DataReader is returned as the result of the Command object's ExecuteReader method. The SqlCommand.ExecuteReader method returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method returns an OleDbDataReader object. The DataReader can provide rows of data directly toapplication logic when you do not need to keep the data cached in memory. Because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the DataReader.

The DataAdapter Object

The DataAdapter is the class at the core of ADO .NET's disconnected data access. It is essentially the middleman facilitating all communication between the database and a DataSet. The DataAdapter is used either to fill a DataTable or DataSet with data from the database with it's Fill method. After the memory-resident data has been manipulated, the DataAdapter can commit the changes to the database by calling the Update method. The DataAdapter provides four properties that represent database commands:

SelectCommand
InsertCommand
DeleteCommand
UpdateCommand

When the Update method is called, changes in the DataSet are copied back to the database and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed. 

System.Data.OleDb
The System.Data.OleDb namespace provides classes that are required to connect to OLE DB data sources. Let's take a look at the classes provided by System.Data.OleDb.
OleDbConnection class
The System.Data.OleDb.OleDbConnection class represents a connection to OleDb data source. Applications that need to connect to a OleDb data source should use this class.
OleDbCommand class
The System.Data.OleDb.OleDbCommand class represents a SQL statement or stored procedure that is executed in a database by an OLE DB provider. The OleDbCommand class can be used to create the Select, Insert, Update and Delete commands that need to be sent to the data source.
OleDbDataReader
The System.Data.OleDb.OleDbDataReader class creates a data reader. It is used to read a row of data from the database. The data is read as forward-only, read-only stream which means that data is read sequentially, one row after another. The DataReader is independent of the OleDb data source from which the data is retrieved.
OleDbDataAdapter
The System.Data.OleDb.OleDbDataAdapter acts as a middleman between the application and OleDb data source. We use the Select, Insert, Delete and Update command properties of this class for loading and updating the data.
DataSet
The System.Data namespace contains a DataSet class which is a disconnected, in-memory representation of data. It can be considered as a local copy of all the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database.
Code Samples
The following code samples will put the System.Data.OleDb namespace and the classes within it to work. The following code samples assume that you have an Access database named Books (Books.mdb) with three columns (BookName, Publisher, ISBN) on the C: drive of your machine.
Select Command
Open a new Web Forms page, add a Button control to it an paste the following code.

Imports System.Data.OleDb 'namespace to be imported Public Class WebForm5 Inherits System.Web.UI.Page #Region " Web Form Designer Generated Code " #End Region Dim myConn As OleDbConnection Dim myComm As OleDbCommand Dim dr As OleDbDataReader Private Sub Select_Click(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles Select.Click Try myConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;_ Data Source=C:\sandeep\books.mdb;") myConn.Open() myComm = New OleDbCommand("Select* from Table1", myConn) dr = myComm.ExecuteReader Do While dr.Read 'reading from the datareader Response.Write(dr(0) & " ") Response.Write(dr(1) & " ") Response.Write(dr(2) & "<br>") 'displaying data from the table 'html break is used to display data in a tabular format Loop Catch End Try End Sub End Class System.Data.SqlClient The System.Data.SqlClient namespace provides classes that are required to connect to SQL Server. Let's take a look at the classes provided by System.Data.SqlClient. SqlConnection Class The SqlConnection class represents a connection to SQL Server data source. SqlCommand Class The SqlCommand class represents a SQL statement or stored procedure for use in a database with SQL Server. SqlDataReader The SqlDataReader class creates a data reader to be used with SQL Server. SqlDataAdapter The SqlDataAdapter class represents a bridge between the dataset and the SQL Server database. It includes the Select, Insert, Delete and Update commands for loading and updating the data. Code Samples The following code samples will put the System.Data.SqlClient namespace and the classes within it to work. Select Command Open a new Web Forms page, add a Button to it and paste the following code. The following code will display data from Discounts table in Pubs sample database. Imports System.Data.SqlClient Dim myConnection As SqlConnection Dim myCommand As SqlCommand Private Sub Page_Load(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles MyBase.Load End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles Button1.Click myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") 'establishing connection. you need to provide password for sql server Try myConnection.Open() 'opening the connection myCommand = New SqlCommand("Select * from discounts", myConnection) Dim dr As SqlDataReader = myCommand.ExecuteReader() While dr.Read() 'reading from the datareader Response.Write(dr(0).ToString()) Response.Write(dr(1).ToString()) Response.Write(dr(2).ToString()) Response.Write(dr(3).ToString()) Response.Write(dr(4).ToString()) 'displaying data from the table End While dr.Close() myConnection.Close() Catch End Try End Sub Insert Command Add a Button control to the Web Forms page and paste the following code. The following code will insert a record into the Jobs table in Pubs sample database. Private Sub Button2_Click(ByVal sender As System.Object, ByVal e_ As System.EventArgs) Handles Button2.Click Dim ra As Integer myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") 'you need to provide password for sql server myConnection.Open() myCommand = New SqlCommand("Insert into Jobs values 12,'IT Manager',100,300 ", myConnection) ra= myCommand.ExecuteNonQuery() 'Since no value is returned we use ExecuteNonQuery Response.Write("Records Inserted" & ra) myConnection.Close() End Sub Delete Command Add a Button to the Web Forms page and paste the following code. The following code will delete a record from the Authors table in Pubs sample database. Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles Button3.Click Dim ss As Integer myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") myConnection.Open() myCommand = New SqlCommand("Delete from Authors where city='Oakland' ", myConnection) ss = myCommand.ExecuteNonQuery() Response.Write("Records affected" & ss) myConnection.Close() End Sub Update Command Add a Button to the Web Forms page and paste the following code. The following code will update a record in Authors table. Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles Button4.Click Dim sss As Integer myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") myConnection.Open() myCommand = New SqlCommand("Update Authors Set city='Oakland' where city='San Jose' ",_ myConnection) sss = myCommand.ExecuteNonQuery() Response.Write("Records affected" & sss) myConnection.Close() End Sub Data Adapter Configuration Wizard The DataAdapter Configuration wizard let's you customize your data adapter as you want, like displaying the whole table or displaying selected columns from the table and so on. To start, open a new database in Access, name it as books,  create a table, Table1 with some fields in it and save the database in the C: drive of your machine. To start creating your own DataAdapter, open a blank form and add a button (Button1) and a DataGrid control to it from the toolbox. Our intention here is to display the table or some columns in the table which we created in Access in the DataGrid control when Button1 is clicked. To do that, click on the Data tab in the toolbox and double-click OleDbDataAdapter object. We are using OleDbDataAdapter here as we are working with an OleDb data source. After you select OleDbDataAdapter from the data tab in the toolbox it gets added to the component tray beneath the Web Forms designer and opens the Data Adapter Configuration wizard dialog box which looks like the imagebelow. Click the Next> button in the Data Adapter Configuration wizard to select the data connection you want to use. The dialog box that opens up look like the image below. Since we are working with our own table, select the New Connection button in that dialog box which opens up the Data Link properties dialog. The Data Link Properties dialog looks like the image below. In the Data Link properties dialog click the Provider tab and select "Microsoft Jet 4.0 OLE DB Provider" from the list of available providers. After selecting the provider from the Provider tab click Next> button to take you to the Connection tab. Click on the ellipse where it says "Select or enter a database name" and browse for the database on the local drive. Since we are working with our own database (Books.mdb) located on the C: drive, select that. Click on the "Test Connection" button to test the connection and if the connection succeeds, click OK. Clicking OK displays a dialog box like the image below. It's here where we will generate the SQL Statement we use with this data adapter. Click next on this dialog box which takes you to another dialog like the image below. It's here where we build our SQL Queries. To build your query click the Query Builder button on this dialog. Once you click that button, the Query Builder dialog opens with a list that displays all the tables in the database with which we are working. In this case it displays only one table as we created only one table in the books database. The Query Builder dialog looks like the image below. Select Table1 in this dialog and click Add to add Table1 to the Query Builder dialog. You can select entire table to be displayed in the DataGrid or just some columns. To display entire table in the DataGrid select the checkbox named "All Columns" in the small dialog named "Table1" which automatically builds the SQL statement for us. If you want to display specific columns from the table in the DataGrid, check on the columns you want to display. Once you finish with that, click next.  The dialog that opens when you click next looks like the image below. This dialog lists the configuration of the data adapter and lists the results. Click finish to close the Data Adapter Configuration wizard. Data Form Wizard The Data Form Wizard is the easiest and fastest way to develop database applications without writing a single line of code. We will see the Data Form Wizard in action and work with a table from the sample Books database. Before you start, create a new database in Access and name it as Books. Create a table, Table1 in Books, add some fields to the table, enter some values in Table1 and close the Access database. To start working with the Data Form Wizard select Project->Add New Item->Data Form Wizardfrom the main menu. The dialogue box for that looks like the image below. Select Data Form Wizard, type a name for it and click Open. Once you click open, a new dialog opens up which is the Data Form wizard and it looks like the image below. Click Next on this dialog box. Clicking next takes you to a new dialog box which looks like the image below. Here you need to specify the name for your DataSet. Select the radio button which display "create new dataset named", type a name for the DataSet, and click next. Clicking next opens a dialog box like the image below. Here we need to establish a connection to the database. Click on the "New Connection" button which opens up the "Data Link Properties" dialog. Set a connection to the database in the Data Link properties dialog. Here, I am using an Access Books database, you can use any database you wish to work with. Once you finish with the Connection click next. Clicking next takes you to a new dialog box like the image below. This dialog box displays all the tables available in your Books database. Select the table you want to work with and add it using the forward arrow button. I am selecting Table1 from my sample books database. Click next once you are finished. The next dialog box looks like the image below. This dialog box allows us to display columns from more than one table by establishing a master-detail relationship and providing a name for that relation. A master-detail relationship can only be established if you have a common column in both the tables. Since we are working with one table, click next on this dialog box. Clicking next takes to a dialog like the image below. This dialog allows you to select the columns you want to display. Select the columns you wish to display and click finish. That finishes configuring the Data Form Wizard. You will notice a Load button and the columns you selected being added to the Web Forms designer. Run the DataWebForm and click the load button. The data will be dsiplayedon the Web page DataBinding Data Binding is binding controls to data from databases. With data binding we can bind a control to a particular column in a table from the database or we can bind the whole table to the data grid. Data binding provides simple, convenient, and powerful way to create a read/write link between the controls on a form and the data in their application. Working with Data Binding in ASP.NET is slightly different to working with it in VB .NET. Generally, Datasets don't maintain a current record that is displayed in bound controls. In VB .NET, the BindingContext object handles that and it lets us set the record bound control display. In ASP.NET, there is no BindingContext object to handle that. In ASP.NET, we use a DataView to let the user select which record should be displayed in bound controls. We bind the controls using data view and use theRowFilter property of the data view to select the record we want the bound control to display. Simply said, in VB .NET we use the Dataset to bind records to the bound control and in ASP.NET we use a DataView. Sample Application Let's understand data binding in ASP.NET with a sample. To start, create a database in Access, name it as Books.mdb. Create a table, Table1 with three columns, BookName, Publisher and ISBN. Enter some values in Table1, save and close it. On a new Web Forms page add three TextBoxes and four Buttons. The user interface should look like the image below. From the Data tab of the toolbox (as shown in the image below) drag a OledbDataAdapter object and configure it to Table1 of the Books database. Click here to read how toconfigure a Data Adapter. Once you are finished configuring the data adapter, generate the dataset by selecting Data->Generate DataSet from the main menu. Since we will use a DataView instead of a Dataset to navigate through the record, drag a DataView object on to the Web Forms page and set the Table property of the data view to Dataset11.Table1. Select TextBox1, click on the ellipse button for it's DataBindings property to open the DataBinding's dialog box. The data binding's dialog looks like the image below. In the Data Binding's dialog we need to bind TextBox1 to the BookName field, TextBox2 to Publisher field and TextBox3 to the ISBN field using the DataView (DataView1). You should not use DataSet as you would in a Windows Application. Open the Code designer window and paste the following code for each button. Private Sub Page_Load(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles MyBase.Load DataSet41.Clear() OleDbDataAdapter1.Fill(DataSet41) TextBox1.DataBind() TextBox2.DataBind() TextBox3.DataBind() 'filling the dataadapter and displaying the first record from the table when the 'page loads End Sub Private Sub First_Click(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles First.Click Dim abc As String Dim index As Integer = 0 Me.ViewState("index") = index 'creating a variable named index to keep track of the record that is currently being 'displayed and saving it's state across server round trips using the ViewState property abc = DataSet41.Tables(0).Rows(index).Item("BookName") 'selecting a record in the dataview using it's RowFilter property and specifying 'the text value of a field DataView1.RowFilter = "Bookname='" & abc & " '" TextBox1.DataBind() TextBox2.DataBind() TextBox3.DataBind() 'using the abc value with the RowFilter property and binding the text boxes to the newly selected record End Sub Private Sub Previous_Click(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles Previous.Click Dim abc As String Dim index As Integer index = Me.ViewState("index") index -= 1 'checking the index value and setting it to a value less than the current one so that 'it displays the previous record each time the previous button is clicked < 0 Then If index index = 0 End If 'if first record is reached it will display the first record each time the previous 'button is clicked Me.ViewState("index") = index abc = DataSet41.Tables(0).Rows(index).Item("BookName") DataView1.RowFilter = "Bookname='" & abc & "'" TextBox1.DataBind() TextBox2.DataBind() TextBox3.DataBind() End Sub Private Sub Next_Click(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles Next.Click Dim abc As String Dim index As Integer index = Me.ViewState("index") index += 1 'checking the index value and setting it to a value one more than the current one 'so that it displays next record each time the next button is clicked If index > DataSet41.Tables(0).Rows.Count - 1 Then index = DataSet41.Tables(0).Rows.Count - 1 index = 0 End If 'if last record is reached it displays the last record each time the next button is 'clicked. the code counts for the number of rows in the table and sets the last row to the 'index variable once the last row is reached Me.ViewState("index") = index abc = DataSet41.Tables(0).Rows(index).Item("BookName") DataView1.RowFilter = "Bookname='" & abc & "'" TextBox1.DataBind() TextBox2.DataBind() TextBox3.DataBind() End Sub Private Sub Last_Click(ByVal sender As System.Object, ByVal e As_ System.EventArgs) Handles Last.Click Dim abc As String Dim index As Integer index = DataSet41.Tables(0).Rows.Count - 1 'the code counts for the number of rows in the table and sets the last row to the 'index variable so that last record is displayed each time the last button is clicked Me.ViewState("index") = index abc = DataSet41.Tables(0).Rows(index).Item("BookName") DataView1.RowFilter = "Bookname='" & abc & "'" TextBox1.DataBind() TextBox2.DataBind() TextBox3.DataBind() End Sub Once you are finished with the code, run the page. The first record from the table is displayed when the page loads. You can click the button to navigate through the records.