Introducing ADO.NET and the Typed DataSet
Introduction
Several weeks ago, I met with the CIO and development staff of a local customer. We discussed their current development strategies and project load. In the last several years they had developed several Visual Basic 6 and ASP based applications that had been extremely successful and represented the core application infrastructure for the business. These applications had become so successful that the business users had recently requested several new applications and pretty substantial enhancements to their existing applications. In reviewing these new requirements the CIO had made the decision that it was quicker and easier to rebuild the applications using .NET and design them using a Service Oriented Application approach.
In reviewing the applications it seemed everything they were doing involved database reads, writes, and manipulation. The development staff had been reviewing the articles and code samples for ADO.NET. They had spent many years refining their existing ADO based infrastructure and perfecting their use of ADO. The problem was when they starting looking at the .NET Framework, they found this new object called the DataSet. It seemed after all their research they continued to remain confused about this object when compared to the ADO recordset object they had used and loved for many years. In this article I will explain the benefits of ADO.NET and how the ADO.NET object model fits into the .NET Framework. Additionally, how the features of the DataSet object can be combined with XML to create a new type of object called the typed DataSet, which simplifies the task of data navigation.
ADO Revisited 
Figure 1: The object structure for recordsets The ADO recordset object represents a collection or records returned when a request is made to the database. This object represents the entire set of records returned from a base table or the complete results of an executed SQL command. At any time during the life of this object, it refers to a single record within the entire set of records. The object structure of the recordset collection is shown in Figure 1.
ADO contains the recordset object, which enables it to manipulate SQL data. One of the primary advantages of recordsets is the ability to loop through records stored in a recordset, accessing each of the individual records and changing, modifying, or deleting specific fields within each.
ADO also makes extensive use of cursors as the way of providing data views. Cursors enable the navigation between records in a recordset. The cursor actually sits on the current record specified in the recordset. The supported ADO cursor types are shown in Table 1.
Table 1: ADO cursor types
| Cursor Type | Description |
| Dynamic Cursor | Enables the viewing of additions, changes, and deletions made by other users and allows forward and backward movement through a recordset object. |
| Keyset cursors | Acts the same as the dynamic cursor except that is prevents users from seeing records that others have added and prevents access to deleted records. |
| Static Cursor | Provides a static copy of a set of records that allows the location of data. Enables forward and backward movement through the recordset. Additions, changes, or deletions by other users will not be visible. |
| Forward-only cursor | Acts the same as the dynamic cursor except that is allows only forward scrolling. This improves performance and is the default type of recordset. |
Note:
The recordset object automatically positions the current record as the first record and sets the Bottom of File (BOF) and End of File (EOF) properties to false. If there are no records, both the BOF and EOF properties are automatically set to true.
Defining a Recordset
Defining a recordset was a pretty trivial task that could be accomplished in less than 10 lines of code. For example, the following code shows how this can be done in an ASP page.
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))set rs = Server.CreateObject("ADODB.recordset")rs.Open "Select * from Customers", conn
do until rs.EOF
for each x in rs.Fields
Response.Write(x.name)
Response.Write(" = ") Response.Write(x.value & "<br />")
next
Response.Write("<br />") rs.MoveNext
loop
rs.close
conn.close
%>
</body>
</html>
Enter ADO.NET
ADO.NET is not an upgrade to ADO but a complete redesign. At the core of ADO.NET is a set of libraries that are designed to communicate with a variety of back-end data stores. The ADO.NET libraries are part of the System.Data namespace and encompass a variety of additional libraries that enable data source connection, query submission, and processing results. One of the goals of the redesign of ADO.NET was to provide a hierarchical, disconnected data cache that works offline and online. The central disconnected object, called the DataSet, allows the search, filtering, navigation and storage of pending data changes. The design of ADO.NET is based on XML and enables ADO.NET to bridge the gap between the traditional database access and XML development. Developers are able to work with both XML data and database data using a common set of data interfaces as shown in Figure 2. 
Figure2: The ADO.NET Object Model
ADO.NET is designed to provide consistent access to common data sources through a managed data provider. This provides a common interface for data sources like SQL Server as well as those exposed through OLE-DB and XML interfaces. Each of the managed providers is designed specifically for the type of data access it will provide. These results can either be processed directly or placed in an ADO.NET DataSet object that can then be exposed to the user, combined together, or even remoted between application tiers.
One of the strongest advantages of the DataSet object is that it can be used independently within the .NET Framework to manage locally stored data or XML files. ADO has really been the mainstay of database access for years, but there were several inherent design issues that ADO.NET was created to overcome. One of the main problems that ADO.NET is designed to solve is the lack of features needed to easily build applications that were part of .NET and the Service Oriented Architecture. For example, XML was added as an afterthought to ADO as the standard started to emerge within the industry. ADO.NET on the other hand was designed based on XML. Additionally, the ADO cursor engine makes it possible to pass disconnected ADO recordset objects between tiers in applications, but it doesn't provide a way to combine multiple recordset objects together. ADO also provides a way to submit cached changes to the database but doesn't provide programmatic control over the logic used to submit the updates. One of the major limitations for developing scalable SOA based applications that is lacking within the ADO cursor engine is the ability to submit pending changes to the database via stored procedures. Many database administrators allow users to only modify the contents of their databases through stored procedures, which is not possible through the binary object of the ADO recordset object.
.NET Data Providers
Within the .NET Framework a data provider is a collection of classes designed to allow you to communicate with a specific type of data store. The .NET Framework comes pre-built with providers for SQL Server, OLE-DB sources, Oracle, and ODBC. In addition there are several additional providers that have been released independently including DB2 and MYSQL. The OLE DB .NET Data Provider is designed to communicate with various data stores through the OLE DB providers. The SQL Client .NET Data provider is designed to solely communicate with SQL Server databases. Each of these managed providers implements the same base classes - Connection, Command, Data Reader, Parameter and Transaction. The names and specific namespaces may change depending on the specific provider and the underlying database requirements. For example, the SQL Managed Provider uses a SQLConnection object, while the OLE DB provider uses an OLEDbConnection object. Each provider is designed to take advantage of the specific performance and limitations of the specific data source.
Regardless of which .NET data provider you use, the provider's Connection object implements the same basic set of interfaces. When opening a connection to the data store, developers create an instance of the providers connection object, set the ConnectionString and then call its Open method.
Connecting to a Data Source
The connection object represents a connection to your data source. For SQL Server you use the namespace System.Data.SQLClient.SQLConnection, and for OleDB you use System.Data.OleDb.OleDbConnection. When specifying the datasource you can provide a variety of amplifying information that describes the data source that includes its location and connection type. The connection object is similar to the ADO Connection object in that it is used to connect and disconnect from a data source. The connection object acts as the glue through which other objects like the DataAdaptor and the Command objects communicate with the database and submit queries and retrieve results. The following code example shows how to connect to a local SQL Server database using the managed provider.
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim strConstring As String
Dim intUserID As Integer
strConstring = ConfigurationSettings.AppSettings("constring")
strConstring = "Data Source=(local);Initial Catalog= _
Northwind;Integrated Security=SSPI"
sqlConn = New SqlConnection(strConstring)
sqlConn.Open()
Note:
The connection object also provides a BeginTransaction method that is used to create a Transaction object. This object is used to either commit or cancel the changes made to the database during the lifetime of the Transaction object.
DataAdapter Object
The DataAdapter is a new concept introduced to data access with the ADO.NET model. It acts as the bridge between the database and the disconnected objects in the ADO.NET object model. The DataAdaptor object's Fill method provides the mechanism to fetch the results of a query into a DataSet or a DataTable so that the data is easily accessed. The DataAdapter object is also used to submit pending changes stored in the DataSet back to the database. The DataAdapter object is part of the Command object and exposes a variety of properties and methods. For example, the SelectCommand property contains a Command object that represents the query that is used to populate the DataSet object. Additionally, this object exposes the UpdateCommand, InsertCommand and DeleteCommand properties that correspond to the Command objects used when you submit modified, new or deleted data rows back to the database. With each of these properties you are able to call stored procedures or a SQL statement that performs the type of operation. Once completed you can call the Update method on the DataAdapter object, and ADO.NET will use the Command object created earlier to update the database as shown in the following code example.
Public Shared Function CreateCandyAdapter(ByVal conn As SqlConnection)_
As SqlDataAdapter
Dim da As SqlDataAdapter = New SqlDataAdapter
Dim cmd As SqlCommand
Dim parm As SqlParameter
' Create the SelectCommand.
cmd = New SqlCommand("sp_select", conn)
cmd.CommandType = CommandType.StoredProcedure
da.SelectCommand = cmd
' Create the UpdateCommand
cmd = New SqlCommand("sp_update", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@CandyName", SqlDbType.VarChar, 5, "CandyName")
cmd.Parameters.Add("@CandyDescription", SqlDbType.VarChar, 40, _
"CandyPrice")
parm.SourceVersion = DataRowVersion.Original
da.UpdateCommand = cmd
Return da
End Function
In order to maintain what is happening to the database and the data, the DataAdapter object provides a variety of supporting properties that can track which tables in the database correspond to the tables in your DataSet object. The TableMappings collection is the property used to track which table in the database corresponds to which table in your DataSet object. Each table mapping has a similar property for mapping columns called a ColumnMappings collection.
Enter the DataSet
The DataSet object is central to supporting disconnected, distributed data scenarios using ADO.NET. This object is actually a memory resident representation of data that provides a consistent relational programming model regardless of the data source. The DataSet object represents a complete set of data including the related tables, constraints and relationships. Figure 3 shows the DataSet object model. 
Figure 3: The Dataset object model.
The DataSet object as its name implies contains data. It provides a container for a number of DataTable objects that are stored in the DataSet's object tables collection. One of the biggest advantages of the DataSet is that you can package an entire structure into it. Instead of having to call multiple times into a middle tier object to retrieve data you can simply package the entire instance, including table references, directly into a single object that can be returned.
The data stored in the DataSet object is disconnected from the database. Any changes that are made are simply cached in a DataRow. When it's time to send the changes back the database, you can pass the entire object to the middle tier server. The DataSet provides a GetChanges method that can be used to extract only the modified rows from a DataSet. This enables specific changed rows to be extracted and passed and reduces the overhead of data across a network.
The DataSet also exposes a Merge method, which acts in conjunction with the GetChanges method. The middle tier server can submit changes to the database using the smaller DataSet returned by the Merge method and return a DataSet that contains the new data. Then using the Merge method this returned DataSet can then be merged back to the contents of the existing DataSet object as shown in the following example.
CandyDS.Merge(otherDS)
The DataSet also provides the ability to read and write data to it from a file or an area of memory. This means you can just save the contents of the DataSet object or the structure. ADO.NET stores the data as an XML document. It is important to remember that ADO.NET and XML are tightly coupled, which means that moving data between the two is an easy task. For example, the following code shows how to create a DataSet and then display the XML contents to the console.
Private Shared Sub DemonstrateGetXml()
' Create a DataSet with 1 table two columns and 10 rows.
Dim ds As DataSet = New DataSet("myDataSet")
Dim t As DataTable = ds.Tables.Add("Items")
t.Columns.Add("id", Type.GetType("System.Int32"))
t.Columns.Add("Item", Type.GetType("System.String"))
' Add ten rows.
Dim r As DataRow
Dim i As Integer
For i = 0 To 9
r = t.NewRow()
r("id") = i
r("Item")= "Item" & i
t.Rows.Add(r)
Next
' Display the DataSet contents as XML.
Console.WriteLine( ds.GetXml() )
End Sub
DataSet Web Services
Within the world of the Services Oriented Architecture, the Web Service is the basic unit of work. This means that often a Web Service is constructed to only return a set of data based on a query that the front-end application sends in. This provides a perfect use for the DataSet as a way of returning data. For example, the following code section shows how data can be returned directly from a Web Service using a DataSet.
Public Function GetRequests(ByVal RequestedStatus As
Boolean) As DataSet
'db connection
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim strConstring As String
Dim intUserID As Integer
strConstring = ConfigurationSettings.AppSettings("constring")
sqlConn = New SqlConnection(strConstring)
sqlConn.Open()
sqlCmd = New SqlCommand
With sqlCmd
.Connection = sqlConn
.CommandTimeout = 30
.CommandType = CommandType.StoredProcedure
If RequestedStatus = True Then
.CommandText = "spGetOpenRequests"
Else
.CommandText = "spGetClosedRequests"
End If
End With
Dim RequestDA As SqlDataAdapter = New SqlDataAdapter
RequestDA.SelectCommand = sqlCmd
Dim RequestDS As DataSet = New DataSet
RequestDA.Fill(RequestDS, "RequestType")
Return RequestDS
sqlConn.Close()
End Function
When a user accesses the data from a front-end application, he receives a stream of XML as shown in Figure 4. 
Figure 4: XML returned from a DataSet object
The transportable nature of the DataSet and XML enables the client the ability to reconstitute the data on the receiving end as shown in the following example.
Dim ws As New localhost.wsSubmit
Dim xmlStatus As DataSet
xmlStatus = ws.GetRequests(RequestedStatus:=False)
One of the difficult parts of using the DataSet this way is that table, column, and row navigation can be difficult. The navigation is actually done through the combination of tables, columns, and rows that make up the DataSet.
Dim s As String
s = CType(dsCustomersOrders1.Tables("Customers")._
Rows(0).Item("CustomerID"), String)
Typed Vs. Untyped DataSets
The nature of a DataSet requires that it is either typed or untyped. By definition a typed DataSet is any DataSet that is derived from the base DataSet class that applies the information contained in the XSD to generate a typed class. Information from the schema that contains the tables, columns, and rows is generated and compiled into a new DataSet derived from the XSD and this promotes the DataSet to a first class object in the .NET Framework. The process of inheriting the typed DataSet from the base DataSet class means that the typed class assumes all functionality of the DataSet class and can be used with methods that take an instance of the DataSet class as a parameter. This is the opposite of the untyped DataSet that has no corresponding schema and is exposed only as a collection.
Defining the Schema
The first step in defining a typed DataSet is to create the XSD that is used to control and provide structure for the DataSet. This can be done by adding a new XML schema to your project as shown in Figure 5. 
Figure 5: Adding a new schema
This adds a new XSD item to your project. When defining the XSD structure, always make sure that you point to the existing Web Service namespace and provide a fully qualified name of the exposed DataSet, as shown in the following XSD.
<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema id="candyTypeDataset"
targetNamespace="http://tempuri.org/CandyType.xsd"
elementFormDefault="qualified" xmlns="http://tempuri.org/CandyType.xsd"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="candyTypeDataset">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="candyType" minOccurs="0"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The schema provides a structure that is used to define a set of classes. The schema shown above defines a single field as a string with the name "candyType". Once the schema is created, it can be viewed using the XML view of Visual Studio. This view shows the completed XML schema as shown in Figure 6.
Figure 6: Completed XSD view.
Once completed the XSD schema can be compiled into a typed DataSet using the right click menu located on the graphical XSD display as shown in Figure 7.
Figure 7: Generating a typed DataSet.
The compilation of the typed DataSet creates two additional files as shown in Figure 8.
Figure 8: Wrapper classes created in Visual Studio.
The first file which contains the .vb extension is used to define the proxy class that is generated when the DataSet is compiled. This class contains the methods and properties that are needed to access, manipulate, and serialize the DataSet. The second file with the .xsx extension contains information about the layout of the XSD on the design surface as shown in Figure 6.
Finishing the Web Service
Once created the typed DataSet is usable within the application as a first class object. This means that when the object is created IntelliSense is now aware of the objects and methods available within the object and displays these in the object viewer as shown in Figure 9.
Figure 9: Exposed typed DataSet objects.
This enables the object to be created and using the following code to fill the typed DataSet for return to the client.
<WebMethod()> Public Function GetCandyTypes() As candyTypeDataset
'db connection
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim strConstring As String
Dim intUserID As Integer
strConstring = ConfigurationSettings.AppSettings("constring")
sqlConn = New SqlConnection(strConstring)
sqlConn.Open()
sqlCmd = New SqlCommand
With sqlCmd
.Connection = sqlConn
.CommandTimeout = 30
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_GetCandyType"
End With
Dim CandyDA As SqlDataAdapter = New SqlDataAdapter
CandyDA.SelectCommand = sqlCmd
Dim CandyDS As New candyTypeDataset
CandyDA.Fill(CandyDS, CandyDS.Tables(0).TableName)
Return CandyDS
sqlConn.Close()
End Function
When creating the WebMethod, it is important to provide a return value that matches the name of the typed DataSet. This is how the client becomes aware of the structure and is able to use the schema defined within the return stream. For example, if we run the above WebMethod and review the WSDL generated. We can see that the Namespace we created for the schema has been added as shown in Figure 10.
Figure 10: WSDL definition showing defined namespace.
If you review further down the WSDL you can see that the new XSD has been appended as shown in Figure 11.
Figure 11: Appended XSD
Client Retrieval
Once the Web Service is completed and the reference is set, any client can retrieve the data exposed by the typed DataSet. Once created the typed DataSet becomes an object that can be retrieved using the named properties directly from the client as shown in the code example below.
Dim ws As New localhost.SweetsService
Dim ds As localhost.candyTypeDataset
ds = ws.GetCandyTypes
Dim i As Integer
For i = 0 To (ds.candyTypeDataset.Count - 1)
Dim locButton As New Button
Dim ButFace As String
locButton.Text = ds.candyTypeDataset.Item(i).candyType
Me.Controls.Add(locButton)
Next
End Sub
It is important to understand that the navigation is now by property as shown above. This means that we don't have to navigate the underlying structure using the standard navigation of the DataSet, but are able to access field by property name.
Summary
At the end of the meeting, the CIO and the development team looked closely at the code samples that we had created and had a better understanding. I spoke with them the other day and was happy to hear that they had adopted ADO.NET and the type DataSet as a way of consuming and rendering their data. Hopefully while reading this article you have come to understand the benefits of ADO.NET and the typed DataSet. These include type checking at design time which gives the advantage of statement completion. For a developer this means fewer errors during runtime from misspellings and consistent navigation that is similar to the ADO.
In this article we have covered a lot of information about the features available within ADO.NET. By no means have we covered every aspect, but only scratched the surface. Hopefully you can now understand the features and benefits of ADO.NET and how important the typed DataSet is to application development. I certainly challenge all readers as I did with the staff that I was meeting with to start exploring these as a way of better developing your data access operations.
About the Author
Thom is a Senior Technology Specialist with Microsoft Corporation in New England. He specializes in working with customers on developing and implementing .NET based applications. He is a frequent speaker and contributor to various magazines. He is also a regular writer for his personal Weblog at http://radio.weblogs.com/0131777/. If you have comments or questions about this article, please visit his Weblog and contact him.
