WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Introduction to ADO.NET
Microsoft has improved significantly in data access functionalities over the old ADO model; that new model is called ADO.NET. ADO.NET gives new a definition to the data access layer. ADO.NET model replaces the RecordSet we used in the olden ADO model with new object called the DataSet object. We can perform operations over databases using the DataSet object without maintaining a constant connection to the database; this is advantageous for Web-based applications. We need to design the application with DataSet; it should query and disconnect from the database and perform operations on data and reconnect with the database for further actions.
Figure 1 shows the basic ADO.NET architecture block diagram. It has two main components, Data Provider and DataSet.
- Data Provider: The software that interacts with the database
- DataSet: The container holds the data from result of the operation.
- Connection object: Provides the software that connects the database or accesses the database.
- Command object: Provides software that lets your application perform queries against the database.
- DataReader object: Is a stream-based, read-only data source that provides better performance for applications that do not update data.
- DataAdaptor object: Provides the interface between the data source and the data set.
The ADO.NET environment supports the SQL Server .NET Data Provider and the OLE DB .NET data Provider. In .NET, it's usually referred to as an ADO managed provider.
Figure 1—ADO.NET Architecture
The System.Data namespace consists mostly of the classes that constitute the ADO.NET architecture. The ADO.NET architecture enables you to build components that efficiently manage data from multiple data sources. In a disconnected scenario (such as the Internet), ADO.NET provides the tools to request, update, and reconcile data in multiple tier systems.
By using the System.Data.SqlClient namespace (the SQL Server .NET Data Provider) or the System.Data.OleDb namespace (the OLE DB .NET Data Provider), you can access a data source to use in conjunction with a DataSet. Each .NET data provider has a corresponding DataAdapter that you use as a bridge between a data source and a DataSet. In this article, we will see about SqlClient classes.
Querying the Database (Read-Only)
Listing 1 shows the code to query data from the SQL server. Don't forget to include the following line to the top of the file, Imports System.Data.SqlClient, to use the classes in this namespace. Refer this in the ADOTest1 project in the sample solution.
Dim connect As New SqlConnection("Initial Catalog = Northwind; Data Source=(local); User ID=sa; password = ;Connect _ Timeout=20") connect.Open() Console.WriteLine("Database: " & connect.Database) Console.WriteLine("Database: " & connect.ServerVersion) Console.WriteLine("Database: " & connect.DataSource) Dim command As New SqlCommand("SELECT * from employees", connect) Dim reader As SqlDataReader = _ command.ExecuteReader( _ CommandBehavior.CloseConnection) While reader.Read() Console.WriteLine(reader.GetSqlValue(1)) End While
As shown in Listing 1, to establish a connection with the newly created SqlConnection object, in this example I established a connection with the Northwind database. (I am using SQL Server 7.0; this database is provided as example database.) Here we pass a connection string to construct the connection object. A connection string is like a telephone number; to talk with one person with the telephone, you should use his number to identify that person. Similarly, connection defines an identification to the system such as database, source address, user name, password, and timeout. To check this example database, click Start-> Programs-> Microsoft SQL Server 7.0-> Enterprise Manager. In the SQL Server Enterprise Manager window, you can see the Northwind database, login name, and access permissions. The data source value I specified here as local because I am using a local system for my database; this means I am using the same system as both server and client. If you use a separate server for your database, you need to mention the host name or IP address for this parameter. We can also give the Connect Timeout command with the connection string to the SqlConnection object during construction. Here we use 20 seconds as the connection timeout. Call the Open method of the SqlConnection class to open the database with specification given as string. To check whether we connected with the database, print the server name, version, and source. Okay, now the database is ready.
Once we connected successfully with the database, we are ready to issue a query against the data source, either a DataSet or DataReader object. If your application needs read-only access to a query result, your application will not make any changes to queried data and uses those changes to update the database. In this case, you can use the DataReader object to improve the application's performance. Because the DataReader object buffers one row of data (one record) at a time, but the DataSet object buffers all the data a query returns, the DataReader object does not place any overhead on the server. The disadvantage of using the DataReader object is we need to have a constant connection with the database. So, you need to decide at this point during your database design time your application behavior and requirement.
To issue the query against the database, we use the Command object; here we use the SqlCommand object. You can specify your SQL query command when you create SqlCommand. Here, in the ADOTest1 case, it queries the employees table. We also need to specify a Connection object to correspond to query the database. To hold the query result data, we can use either the DataSet object or DataReader object. Here we use the DataReader object because we only read the database (we don't change the database content). Call the SqlCommand.ExecuteReader function to get the query result; this method returns the SqlDataReader object. We will discuss how to modify the query result and how to update the database in next example, ADOTest2. Get the data and print it in the console. Note how we close the server connection; we don't need to specify explicitly something like close because we specified this in SqlCommand.ExecuteReader as CommandBehavior.CloseConnection. This option automatically closes the connection after we have finished with our work. Any exceptions thrown will be caught and displayed in the error message.
Querying and Modifying the Database
In this example, we will see how to query and modify the result and change the database. The code for this example is shown in Listing 2. Refer the project ADOTest2 in the solution. Up to establishing the connection, it's the same as the first example. Because we are going to experiment with updating the database, it's efficient to use the DataSet object instead of the DataReader object. Here, we read data from the employee table and modify that and update the database with modified data, to communicate between the data set (source data) and the database; we should use the DataAdaptor object as an interface between the client and server. Behind the scenes, the DataAdaptor object performs all necessary actions to update the modified DataSet data to the correct database locations. This is performed by the SqlCommandBuilder object that takes the SqlDataAdaptor object as a parameter and does the insert, delete, and update operations. Call SqlDataAdaptor.Fill to fill or refresh the Dataset with source data. Now, we modify the dataset, changing employee names to uppercase. To update the modified dataset, call SqlCommandBuilder.GetUpdateCommand to get the automatically generated sqlcommand object to call an update and assign this to DataAdaptor.UpdateCommand. Now, we are ready to update the database. Call DataAdaptor.Update to update the database.
Dim connect As New SqlConnection("Initial Catalog = Northwind; Data Source =(local);User ID=sa;password=;Connect _ Timeout = 20") connect.Open() Console.WriteLine("Database: " & connect.Database) Console.WriteLine("Database: " & connect.ServerVersion) Console.WriteLine("Database: " & connect.DataSource) Dim command As String = "SELECT * from employees" Dim DS As New DataSet() Dim adaptor As SqlDataAdapter = New SqlDataAdapter(command, _ connect) Dim cmdBuilder As SqlCommandBuilder = _ New SqlCommandBuilder(adaptor) adaptor.Fill(DS) Dim I As Integer For I = 0 To DS.Tables(0).Rows.Count - 1 DS.Tables(0).Rows(I).Item(1) = UCase(DS.Tables(0).Rows(I). _ Item(1)) ' DS.Tables(0).Rows(I).Item(1) = LCase(DS.Tables(0).Rows(I). _ Item(1)) Next ' Update database with modified data adaptor.UpdateCommand = cmdBuilder.GetUpdateCommand() adaptor.Update(DS.Tables(0))
ADO.NET Model Exploitation with XML
In the ADO.NET environment, XML plays a major role behind the scenes to organize data. When a data set receives data from a database, it actually receives XML-based content. Likewise, if a data set updates its contents, it returns the new contents back to the database using XML. ADO.NET and XML go hand in hand; XML is used to represent structured data. ADO.NET uses XML to pass data between the server and client. We will see how to create a small database in XML, how to query it, and how to store back the modified database. For our testing purposes, we create a small XML file called employee.xml file. Copy and paste the following Listing 3 to it. Refer to the ADOTest3 project for this example.
<NewDataSet> <Table> <Name>Gerhard Shroeder</Name> <Department>Programming</Department> <Salary>6500.65</Salary> </Table> <Table> <Name>Edmund Stoiber</Name> <Department>Analysis</Department> <Salary>6000.45</Salary> </Table> <Table> <Name>Guido Westerwelle</Name> <Department>Antivirus</Department> <Salary>7500.75</Salary> </Table> </NewDataSet>
Listing 4 shows the code to manipulate this with XML data.
Dim DS As New DataSet() ' don't forget to change the path DS.ReadXml("C:\Balaji\ADO Test\employee.xml") Dim I As Integer ' change names to caps For I = 0 To DS.Tables(0).Rows.Count - 1 DS.Tables(0).Rows(I).Item("Name") = _ UCase(DS.Tables(0).Rows(I).Item("Name")) Next ' Display the result For I = 0 To DS.Tables(0).Rows.Count - 1 Console.WriteLine(DS.Tables(0).Rows(I).Item("Name")) Console.WriteLine(" " + DS.Tables(0).Rows(I).Item("Department")) Console.WriteLine(" " + DS.Tables(0).Rows(I).Item("Salary")) Next ' write the modified data to the XML file DS.WriteXml("C:\Balaji\ADO Test\employee.xml")
Call the DataSet.ReadXml method to fill the XML content (database) to the dataset object by giving the full path of the XML file as a parameter. Convert the employee's names into uppercase. To update the XML file, call DataSet.WriteXml of the DataSet object by giving the file name. Give the same file name if you want to change the XML file; otherwise, give another name to store the modified data in a different file.
For comments and criticism, send me mail at email@example.com.