Now that you understand the purpose of ADO.NET and where it fits into the overall Visual Studio .NET architecture, it’s time to take a closer look at the technology. In this chapter, we’ll take a brief look at the ADO.NET object model and see how it differs from past Microsoft data access technologies.
ADO.NET is designed to help developers build efficient multi-tiered database applications across intranets and the Internet, and the ADO.NET object model provides the means. Figure 1-1 shows the classes that comprise the ADO.NET object model. A dotted line separates the object model into two halves. The objects to the left of the line are “connected” objects. These objects communicate directly with your database to manage the connection and transactions as well as to retrieve data from and submit changes to your database. The objects to the right of the line are “disconnected” objects that allow a user to work with data offline.
The objects that comprise the disconnected half of the ADO.NET object model do not communicate directly with the connected objects. This is a major change from previous Microsoft data access object models. In ADO, the Recordset object stores the results of your queries. You can call its Open method to fetch the results of a query and call its Update (or UpdateBatch) method to submit changes stored within the Recordset to your database.
The ADO.NET DataSet, which we’ll discuss shortly, is comparable in functionality to the ADO Recordset. However, the DataSet does not communicate with your database. In order to fetch data from your database into a DataSet, you pass the DataSet into the Fill method of a connected ADO.NET object—the DataAdapter. Similarly, to submit the pending changes stored in your DataSet to your database, you pass the DataSet to the DataAdapter object’s Update method.
A .NET data provider is a collection of classes designed to allow you to communicate with a particular type of data store. The .NET Framework includes two such providers, the SQL Client .NET Data Provider and the OLE DB .NET Data Provider. The OLE DB .NET Data Provider lets you communicate with various data stores through OLE DB providers. The SQL Client .NET Data Provider is designed solely to communicate with SQL Server databases, version 7 and later.
Each .NET data provider implements the same base classes—Connection, Command, DataReader, Parameter, and Transaction—although their actual names depend on the provider. For example, the SQL Client .NET Data Provider has a SqlConnection object, and the OLE DB .NET Data Provider includes an OleDbConnection object. Regardless of which .NET data provider you use, the provider’s Connection object implements the same basic features through the same base interfaces. To open a connection to your data store, you create an instance of the provider’s connection object, set the object’s ConnectionString property, and then call its Open method.
Each .NET data provider has its own namespace. The two providers included in the .NET Framework are subsets of the System.Data namespace, where the disconnected objects reside. The OLE DB .NET Data Provider resides in the System.Data.OleDb namespace, and the SQL Client .NET Data Provider resides in System.Data.SqlClient.
A namespace is a logical grouping of objects. The .NET Framework is large, so to make developing applications with the .NET Framework a little easier, Microsoft has divided the objects into different namespaces. Figure 1-2 shows a portion of the hierarchy of namespaces in the .NET Framework.
The most important reason for using namespaces is to prevent name collisions in assemblies. With different namespaces, programmers working on different components combined into a single solution can use the same names for different items. Since these names are separated, they don’t interfere with each other at compile time. A more practical reason for namespaces is that grouping objects can make them easier to locate. Sometimes I forget the exact name of the object I’m looking for. If the objects in the .NET Framework were not broken out into smaller namespaces, I would have to find the desired object in an alphabetical list of all of the objects in the framework. Thankfully, I can usually remember the namespace of the desired object. Finding the object within its namespace is simpler because there are fewer objects to examine.
For more information on using namespaces in the Microsoft .NET Framework or Visual Studio .NET, see the documentation in MSDN.
Because each .NET data provider implements the same basic features, the code you write will look fairly similar regardless of the provider you use. As you can see in the following code snippets, all you need to do to switch from using the OLE DB .NET Data Provider to the SQL Client .NET Data Provider is to change the class you instantiate and the contents of the connection string to conform to the provider’s standards.
'Open and close a connection using the SQL Client .NET Data Provider. Dim cnOleDb As New OleDbConnection cnOleDb.ConnectionString = "Provider=SQLOLEDB; "Data Source=(local);InitialCatalog=Northwind;..." cnOleDb.Open() ... cnOleDb.Close() 'Open and close a connection using the SQL Client .NET Data Provider. Dim cnSql As New SqlConnection cnSql.ConnectionString = "Data Source=(local);" & _ "Initial Catalog=Northwind;..." cnSql.Open() ... cnSql.Close()
//Open and close a connection using the OLE DB .NET Data Provider. OleDbConnection cnOleDb = new OleDbConnection(); cnOleDb.ConnectionString = "Provider=Provider=SQLOLEDB; "Data Source=(local);InitialCatalog=Northwind;..."; cnOleDb.Open(); ... cnOleDb.Close(); //Open and close a connection using the SQL Client .NET Data Provider. SqlConnection cnSql = new SqlConnection(); cnSql.ConnectionString = "Data Source=(local);" + "Initial Catalog=Northwind;..."; cnSql.Open(); ... cnSql.Close();
No previous Microsoft data access technology has used separate libraries and classes for different data stores. Many developers have asked why Microsoft has made such a major change. There are three main reasons: performance, extensibility, and proliferation.
How does moving to .NET data providers improve performance? When you write ADO code, you’re essentially using the ADO interfaces as a “middleman” when communicating with your data store. You tell ADO which provider you want to use, and ADO forwards your calls to the appropriate provider. The provider performs the requested action and returns the result to you through the ADO library.
.NET data providers don’t involve a middle layer. You communicate directly with the data provider, which communicates with your data store using the data store’s low-level programming interfaces. Communicating with SQL Server using the SQL Client .NET Data Provider in ADO.NET is faster than using ADO and the SQL Server OLE DB provider because one less layer is involved.
When SQL Server 2000 introduced XML features, the ADO development team faced an interesting challenge. In order to add features to ADO that would let developers retrieve XML data from SQL Server 2000, they had to add new interfaces to the OLE DB API and to the SQL Server OLE DB provider.
.NET data providers are more easily extensible. They need to support only the same basic interfaces and can provide additional provider-specific features when appropriate. The SQL Client .NET Data Provider’s Command object (SqlCommand) exposes all of the same methods and properties that its OLE DB .NET Data Provider counterpart does, but it also adds a method to fetch the results of a query as XML.
Microsoft first shipped OLE DB providers for SQL Server, Microsoft Access, and Oracle with the release of the Microsoft Data Access Components (MDAC) version 2.0 in July 1998. Microsoft and other development teams have created native OLE DB providers to communicate with other data stores, but not a whole lot of OLE DB providers are available. If you’re using ADO but aren’t using a Microsoft-built OLE DB provider, there’s a high probability that you’re using an ODBC (OLE DB’s predecessor) driver instead. Many more ODBC drivers are available, primarily because they were easier to develop. Many developers simply found it too difficult to build their own OLE DB providers.
By comparison, a .NET data provider is simple to write. There are far fewer interfaces to implement. Microsoft simplified the process of building providers for ADO.NET so that developers can build .NET data providers more easily. The more .NET data providers there are, the more different data sources you can access via ADO.NET.
Because each .NET data provider implements the same base interfaces, there’s no need for me to cover using these interfaces for every .NET data provider. Instead, I’ll mostly focus on one provider: the OLE DB .NET Data Provider. I’ve chosen to focus on this provider because it’s included with the .NET Framework, provides basic provider-independent features, and is extremely flexible because it can be used against any database that has a native OLE DB provider. The features of the OLE DB .NET Data Provider covered here are also available through other providers and work the same way unless otherwise noted.
Appendix B will cover the features of the other .NET data providers. Chapter 12 will use the SQL Client Data Provider and the SQL XML .NET Data Provider to demonstrate the use of some of ADO.NET’s XML features.
Until then, if I’m discussing an object that’s common to all managed providers, I’ll generally refer to it by its provider-independent name—for example, DataAdapter rather than OleDbDataAdapter or SqlDataAdapter.
The ADO.NET object model includes classes designed to help you communicate directly with your data source. I’ll refer to such objects, which appear to the left of the dotted line in Figure 1-1 (shown earlier), as ADO.NET’s “connected” objects. Most of these objects represent basic data access concepts such as the physical connection to the database, a query, and the query’s results.
A Connection object represents a connection to your data source. You can specify the type of data source, its location, and other attributes through the various properties of the Connection object. A Connection object is roughly equivalent to an ADO Connection object or a DAO Database object; you use it to connect to and disconnect from your database. A Connection object acts as a conduit through which other objects, such as DataAdapter and Command objects, communicate with your database to submit queries and retrieve results.
Command objects are similar in structure to ADO Command or DAO QueryDef objects. They can represent a query against your database, a call to a stored procedure, or a direct request to return the contents of a specific table.
Databases support many different types of queries. Some queries retrieve rows of data by referencing one or more tables or views or by calling a stored procedure. Other queries modify rows of data, and still others manipulate the structure of the database by creating or modifying objects such as tables, views, or stored procedures. You can use a Command object to execute any of these types of queries against your database.
Using a Command object to query your database is rather straightforward. You set the Connection property to a Connection object that connects to your database and then specify the text for your query in the CommandText property. You can supply a standard SQL query such as this one:
SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers
You can also supply just the name of a table, view, or stored procedure and use the Command object’s CommandType property for the type of query you want to execute. The Command object offers different ways to execute your query. If the query does not return rows, simply call the ExecuteNonQuery method. The Command object also has an ExecuteReader method, which returns a DataReader object that you can use to examine the rows returned by your query. The SqlCommand includes a third execution method, ExecuteXmlReader, that is similar to ExecuteReader but is designed to handle queries that return results in XML format.
The DataReader is designed to help you retrieve and examine the rows returned by your query as quickly as possible. You can use the DataReader object to examine the results of a query one row at a time. When you move forward to the next row, the contents of the previous row are discarded. The DataReader doesn’t support updating. The data returned by the DataReader is read-only. Because the DataReader object supports such a minimal set of features, it’s extremely fast and lightweight.
At times, you might want to group a number of changes to your database and treat them as a single unit of work. In database programming, that unit of work is called a transaction. Let’s say your database contains banking information and has tables for checking and savings accounts and a user wants to transfer money from a savings account to a checking account. In your code, you’ll want to make sure that the withdrawal from savings and the deposit to checking complete successfully as a single unit or that neither change occurs. You use a transaction to accomplish this.
The Connection object has a BeginTransaction method that you can use to create Transaction objects. You use a Transaction object to either commit or cancel the changes you make to your database during the lifetime of the Transaction object. In our banking example, the changes to both the savings and checking accounts would be included in a single transaction and, therefore, would be either committed or cancelled as a single unit of work.
Say you want to query your Orders table for all the orders for a particular customer. Your query will look something like this:
SELECT CustomerID, CompanyName, CompanyName, Phone FROM Customers WHERE CustomerID = 'ALFKI'
The value you use for the CustomerID column in the query’s WHERE clause depends on the customer whose orders you want to examine. But if you use this type of query, you have to modify the text for the query each time you want to examine the orders for a different customer.
To simplify the process of executing such queries, you can replace the value for the CustomerID column with a parameter marker, as shown in the following query:
SELECT CustomerID, CompanyName, CompanyName, Phone FROM Customers WHERE CustomerID = ?
Then, prior to executing the query, you supply a value for the parameter. Many developers rely heavily on parameterized queries because they can help simplify your programming and make for more efficient code.
To use a parameterized Command object, you create Parameter objects for each of the parameters in your query and append them to the Command object’s Parameters collection. The ADO.NET Parameter object exposes properties and methods that let you define the data type and value for your parameters. To work with a stored procedure that returns data through output parameters, you set the Parameter object’s Direction property to the appropriate value from the ParameterDirection enumeration.
The DataAdapter object represents a new concept for Microsoft data access models; it has no true equivalent in ADO or DAO, although you can consider the ADO Command and DAO QueryDef objects to be its second cousins, once removed.
DataAdapter objects act as a bridge between your database and the disconnected objects in the ADO.NET object model. The DataAdapter object’s Fill method provides an efficient mechanism to fetch the results of a query into a DataSet or a DataTable so you can work with your data off line. You can also use DataAdapter objects to submit the pending changes stored in your DataSet objects to your database.
The ADO.NET DataAdapter object exposes a number of properties that are actually Command objects. For instance, the SelectCommand property contains a Command object that represents the query you’ll use to populate your DataSet object. The DataAdapter object also has UpdateCommand, InsertCommand, and DeleteCommand properties that correspond to Command objects you use when you submit modified, new, or deleted rows to your database, respectively.
These Command objects provide updating functionality that was automatic (or “automagic,” depending on your perspective) in the ADO and DAO Recordset objects. For example, when you run a query in ADO to generate a Recordset object, the ADO cursor engine asks the databases for metadata about the query to determine where the results came from. ADO then uses that metadata to build the updating logic to translate changes in your Recordset object into changes in your database.
So why does the ADO.NET DataAdapter object have separate UpdateCommand, InsertCommand, and DeleteCommand properties? To allow you to define your own updating logic. The updating functionality in ADO and DAO is fairly limited in the sense that both object models translate changes in Recordset objects into action queries that directly reference tables in your database. To maintain the security and integrity of the data, many database administrators restrict access to the tables in their databases so that the only way to change the contents of a table is to call a stored procedure. ADO and DAO don’t know how to submit changes using a stored procedure; neither provides mechanisms that let you specify your own updating logic. The ADO.NET DataAdapter does.
With a DataAdapter object, you can set the UpdateCommand, InsertCommand, and DeleteCommand properties to call the stored procedures that will modify, add, or delete rows in the appropriate table in your database. Then you can simply call the Update method on the DataAdapter object and ADO.NET will use the Command objects you’ve created to submit the cached changes in your DataSet to your database.
As I stated earlier, the DataAdapter object populates tables in the DataSet object and also reads cached changes and submits them to your database. To keep track of what goes where, a DataAdapter has some supporting properties. The TableMappings collection is a property used to track which table in your database corresponds to which table in your DataSet object. Each table mapping has a similar property for mapping columns, appropriately called a ColumnMappings collection.
You’ve seen that you can use objects in a .NET data provider to connect to a data source, submit queries, and examine their results. However, these connected classes let you examine data only as a forward-only, read-only stream of data. What if you want to sort, search, filter, or modify the results of your queries?
The ADO.NET object model includes classes to provide such functionality. These classes act as an offline data cache. Once you’ve fetched the results of your query into a DataTable (which we’ll discuss shortly), you can close the connection to your data source and continue to work with the data. As mentioned earlier, because these objects do not require a live connection to your data source we call them “disconnected” objects.
Let’s take a look at the disconnected objects in the ADO.NET object model.
The ADO.NET DataTable object is similar to the ADO and DAO Recordset objects. A DataTable object allows you to examine data through collections of rows and columns. You can store the results of a query in a DataTable through the DataAdapter object’s Fill method, as shown in the following code snippet:
Dim strSQL As String = "SELECT CustomerID, CompanyName FROM Customers" Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local);..." Dim daCustomers As New OleDbDataAdapter(strSQL, strConn) Dim tblCustomers As New DataTable() daCustomers.Fill(tblCustomers)
string strSQL = "SELECT CustomerID, CompanyName FROM Customers"; string strConn = "Provider=SQLOLEDB;Data Source=(local);..." OleDbDataAdapter daCustomers = new OleDbDataAdapter(strSQL, strConn); DataTable tblCustomers = new DataTable(); daCustomers.Fill(tblCustomers);
Once you’ve fetched the data from your database and stored it in a DataTable object, that data is disconnected from the server. You can then examine the contents of the DataTable object without creating any network traffic between ADO.NET and your database. By working with the data off line, you no longer require a live connection to your database, but remember that you also won’t see any changes made by other users after you’ve run your query.
The DataTable class contains collections of other disconnected objects, which I’ll cover shortly. You access the contents of a DataTable through its Rows property, which returns a collection of DataRow objects. If you want to examine the structure of a DataTable, you use its Columns property to retrieve a collection of DataColumn objects. The DataTable class also lets you define constraints, such as a primary key, on the data stored within the class. You can access these constraints through the DataTable object’s Constraints property.
Each DataTable has a Columns collection, which is a container for DataColumn objects. As its name implies, a DataColumn object corresponds to a column in your table. However, a DataColumn object doesn’t actually contain the data stored in your DataTable. Instead, it stores information about the structure of the column. This type of information, data about data, is called metadata. For example, DataColumn exposes a Type property that describes the data type (such as string or integer) that the column stores. DataColumn has other properties such as ReadOnly, AllowDBNull, Unique, Default, and AutoIncrement that allow you to control whether the data in the column can be updated, restrict what can be stored in the column, or dictate how values should be generated for new rows of data.
The DataColumn class also exposes an Expression property, which you can use to define how the data in the column is calculated. A common practice is to base a column in a query on an expression rather than on the contents of a column in a table in your database. For example, in the sample Northwind database that accompanies most Microsoft database-related products, each row in the Order Details table contains UnitPrice and Quantity columns. Traditionally, if you wanted to examine the total cost for the order item in your data structure, you would add a calculated column to the query. The following SQL example defines a calculated column called ItemTotal:
SELECT OrderID, ProductID, Quantity, UnitPrice, Quantity * UnitPrice AS ItemTotal FROM [Order Details]
The drawback to this technique is that the database engine performs the calculation only at the time of the query. If you modify the contents of the UnitPrice or Quantity columns in your DataTable object, the ItemTotal column doesn’t change.
The ADO.NET DataColumn class defines an Expression property to handle this scenario more elegantly. When you check the value of a DataColumn object based on an expression, ADO.NET evaluates the expression and returns a newly calculated value. In this way, if you update the value of any column in the expression, the value stored in the calculated column is accurate. Here are two code snippets illustrating the use of the Expression property:
Dim col As New DataColumn() ... With col .ColumnName = "ItemTotal" .DataType = GetType(Decimal) .Expression = "UnitPrice * Quantity" End With
DataColumn col = new DataColumn(); col.ColumnName = "ItemTotal"; col.DataType = typeof(Decimal); col.Expression = "UnitPrice * Quantity";
The Columns collection and DataColumn objects can be roughly compared to the Fields collection and Field objects in ADO and DAO.
The DataTable class also provides a way for you to place constraints on the data stored locally within a DataTable object. For example, you can build a Constraint object that ensures that the values in a column, or multiple columns, are unique within the DataTable. Constraint objects are maintained in a DataTable object’s Constraints collection.
To access the actual values stored in a DataTable object, you use the object’s Rows collection, which contains a series of DataRow objects. To examine the data stored in a specific column of a particular row, you use the Item property of the appropriate DataRow object to read the value for any column in that row. The DataRow class provides several overloaded definitions of its Item property. You can specify which column to view by passing the column name, index value, or associated DataColumn object to a DataRow object’s Item property. Because Item is the default property of the DataRow object, you can use it implicitly, as shown in the following code snippets:
Dim row As DataRow
row = MyTable.Rows(0)
Console.WriteLine(row(0))
Console.WriteLine(row("CustomerID"))
Console.WriteLine(row(MyTable.Columns("CustomerID")))DataRow row; row = MyTable.Rows[0]; Console.WriteLine(row[0]); Console.WriteLine(row["CustomerID"]); Console.WriteLine(row[MyTable.Columns["CustomerID"]]);
Rather than returning the data for just the current row, the DataTable makes all rows of data available through a collection of DataRows. This is a marked change in behavior from the ADO and DAO Recordset objects, which expose only a single row of data at a time, requiring you to navigate through its contents using methods such as MoveNext. The following code snippet is an example of looping through the contents of an ADO Recordset:
Dim strConn As String, strSQL As String
Dim rs As ADODB.Recordset
strConn = "Provider=SQLOLEDB;Data Source=(local);..."
strSQL = "SELECT CustomerID, CompanyName FROM Customers"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, strConn, adOpenStatic, adLockReadOnly, adCmdText
Do While Not rs.EOF
MsgBox rs("CustomerID")
rs.MoveNext
LoopTo examine the contents of an ADO.NET DataTable, you loop through the DataRow objects contained in the DataTable object’s Rows property, as shown in the following code snippet:
Dim strSQL, strConn As String ... Dim da As New OleDbDataAdapter(strSQL, strConn) Dim tbl As New DataTable() da.Fill(tbl) Dim row As DataRow For Each row In tbl.Rows Console.WriteLine(row(0)) Next row
string strSQL, strConn; ... OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataTable tbl = new DataTable(); da.Fill(tbl); foreach (DataRow row in tbl.Rows) Console.WriteLine(row[0]);
The DataRow object is also the starting point for your updates. For example, you can call the BeginEdit method of a DataRow object, change the value of some columns in that row through the Item property, and then call the EndEdit method to save the changes to that row. A DataRow object’s CancelEdit method lets you cancel the changes made in the current editing session. A DataRow object also exposes methods to delete or remove an item from the DataTable object’s collection of DataRows.
When you change the contents of a row, the DataRow object caches those changes so that you can submit them to your database at a later time. Thus, when you change the value of a column in a row, the DataRow object maintains that column’s original value as well as its current value in order to successfully update the database. The Item property of a DataRow object also allows you to examine the original value of a column when the row has a pending change.
A DataSet object, as its name indicates, contains a set of data. You can think of a DataSet object as the container for a number of DataTable objects (stored in the DataSet object’s Tables collection). Remember that ADO.NET was created to help developers build large multi-tiered database applications. At times, you might want to access a component running on a middle-tier server to retrieve the contents of many tables. Rather than having to repeatedly call the server in order to fetch that data one table at a time, you can package all the data into a DataSet object and return it in a single call. But a DataSet object does a great deal more than act as a container for multiple DataTable objects.
The data stored in a DataSet object is disconnected from your database. Any changes you make to the data are simply cached in each DataRow. When it’s time to send these changes to your database, it might not be efficient to send the entire DataSet back to your middle-tier server. You can use the GetChanges method to extract just the modified rows from your DataSet. In this way, you pass less data between the different processes or servers.
The DataSet also exposes a Merge method, which can act as a complement to the GetChanges method. The middle-tier server you use to submit changes to your database, using the smaller DataSet returned by the Merge method, might return a DataSet that contains newly retrieved data. You can use the DataSet class’s Merge method to combine the contents of two DataSet objects into a single DataSet. This is another example that shows how ADO.NET was developed with multi-tiered applications in mind. Previous Microsoft data access models have no comparable feature.
You can create a DataSet object and populate its Tables collection with information without having to communicate with a database. In previous data access models, you generally need to query a database before adding new rows locally, and then later submit them to the database. With ADO.NET, you don’t need to communicate with your database until you’re ready to submit the new rows.
The DataSet object also has features that allow you to write it to and read it from a file or an area of memory. You can save just the contents of the DataSet object, just the structure of the DataSet object, or both. ADO.NET stores this data as an XML document. Because ADO.NET and XML are so tightly coupled, moving data back and forth between ADO.NET DataSet objects and XML documents is a snap. You can thus take advantage of one of the most powerful features of XML: its ability to easily transform the structure of your data. For example, you can use an Extensible Stylesheet Language Transformation (XSLT) template to convert data exported to an XML document into HTML.
The tables in your database are usually related in some fashion. For example, in the Northwind database, each entry in the Orders table relates to an entry in the Customers table, so you can determine which customer placed which orders. You’ll probably want to use related data from multiple tables in your application. The ADO.NET DataSet object is designed to handle this through the DataRelation object.
The DataSet class defines a Relations property, which is a collection of DataRelation objects. You can use a DataRelation object to indicate a relationship between different DataTable objects in your DataSet. Once you’ve created your DataRelation object, you can use code such as the following to retrieve an array of DataRow objects for the orders that correspond to a particular customer:
Dim dsNorthwind As DataSet
Dim rowCustomer, rowOrder As DataRow
'The code for creating the DataSet goes here.
dsNorthwind.Relations.Add("CustomersOrders", _
dsNorthwind.Tables("Customers").Columns("CustomerID"), _
dsNorthwind.Tables("Orders").Columns("CustomerID"))
For Each rowCustomer In dsNorthwind.Tables("Customers").Rows
Console.WriteLine("Orders for customer " & rowCustomer("CompanyName"))
For Each rowOrder In rowCustomer.GetChildRows("CustomersOrders")
Console.WriteLine(vbTab & rowOrder("OrderID"))
Next rowOrder
Next rowCustomerDataSet dsNorthwind;
//Create and initialize DataSet.
dsNorthwind.Relations.Add("CustomersOrders",
dsNorthwind.Tables["Customers"].Columns["CustomerID"],
dsNorthwind.Tables["Orders"].Columns["CustomerID"]);
foreach (DataRow rowCustomer in dsNorthwind.Tables["Customers"].Rows)
{
Console.WriteLine("Orders for customer " +
rowCustomer["CompanyName"].ToString());
foreach (DataRow rowOrder in rowCustomer.GetChildRows("CustomersOrders"))
Console.WriteLine('\t' + rowOrder["OrderID"].ToString());
}DataRelation objects also expose properties that allow you to enforce referential integrity. For example, you can set a DataRelation object so that if you modify the value of the primary key field in the parent row, the change cascades down to the child rows automatically. You can also set your DataRelation object so that if you delete a row in one DataTable, the corresponding rows in any child DataTable objects, as defined by the relation, are automatically deleted as well.
Once you’ve retrieved the results of a query into a DataTable object, you can use a DataView object to view the data in different ways. If you want to sort the contents of a DataTable object based on a column, simply set the DataView object’s Sort property to the name of that column. You can also use the Filter property on DataView so that only the rows that match certain criteria are visible.
You can use multiple DataView objects to examine the same DataTable at the same time. For example, you can have two grids on a form, one showing all customers in alphabetical order, and the other showing the rows ordered by a different field, such as state or region. To show each view, you bind each grid to a different DataView object, but both DataView objects reference the same DataTable. This feature prevents you from having to maintain two copies of your data in separate structures. We’ll discuss this in more detail in Chapter 8.
ADO and DAO allow you to create a Recordset based on the results returned by your query. The data access engine examines the columns of data in the result set and populates the Recordset object’s Fields collection based on this information, setting the name, data type, and so forth.
ADO.NET offers you a choice. You can use just a couple lines of code and let ADO.NET determine the structure of the results automatically, or you can use more code that includes metadata about the structure of the results of your query.
Why would you choose the option that involves writing more code? The main benefits are increased functionality and better performance. But how could having more code make your application run faster? That seems counterintuitive, doesn’t it?
Unless you’re writing an ad-hoc query tool, you’ll generally know what the structure of your query results will look like. For example, most ADO code looks something like the following:
Dim rs as Recordset 'Declare other variables here.'Initialize variables and establish connection to database.
rs.Open strSQL, cnDatabase, adOpenStatic, adLockOptimistic, adCmdText Do While Not rs.EOF List1.AddItem rs.Fields("UserName").Value rs.MoveNext Loop
In this code snippet, the programmer knows that the query contains a column named UserName. The point is that as a developer, you generally know what columns your query will return and what data types those columns use. But ADO doesn’t know what the results of the query will look like ahead of time. As a result, ADO has to query the OLE DB provider to ask questions such as “How many columns are there in the results of this query?,” “What are the data types for each of those columns?,” “Where did this data come from?,” and “What are the primary key fields for each table referenced in this query?” The OLE DB provider can answer some of these questions, but in many cases it must call back to the database.
To retrieve the results of your query and store this data in a DataSet object, ADO.NET needs to know the answers to such questions. You can supply this information yourself or force ADO.NET to ask the provider for this information. Your code will run faster using the former option because ADO.NET won’t have to ask the provider for this information at run time.
Writing code to prepare the structure for your DataSet can become tedious, even if it improves the performance of your application. Thankfully, Visual Studio .NET includes design-time data-access features that offer the best of both worlds. For example, you can create a DataSet object based on a query, a table name, or a stored procedure, and then a configuration wizard will generate ADO.NET code to run the query and support submitting updates back to your database. We’ll take a close look at many of these Visual Studio features in upcoming chapters.
Visual Studio .NET also helps you simplify the process of building data-access applications by generating strongly typed DataSet. Let’s say we have a simple table named Orders that contains two columns, CustomerID and CompanyName. You don’t have to write code such as shown below.
Dim ds As DataSet
'Create and fill DataSet.
Console.WriteLine(ds.Tables("Customers").Rows(0)("CustomerID"))DataSet ds; //Create and fill DataSet. Console.WriteLine(ds.Tables["Customers"].Rows[0]["CustomerID"]);
Instead, we can write code like this:
Dim ds As CustomersDataSet 'Create and fill DataSet. Console.WriteLine(ds.Customers(0).CustomerID)
CustomersDataSet ds; //Create and fill DataSet. Console.WriteLine(ds.Customers[0].CustomerID);
The strongly typed DataSet is simply a class that Visual Studio builds with all the table and column information available through properties. Strongly typed DataSet objects also expose custom methods for such features as creating new rows. So instead of code that looks like the following:
Dim ds as DataSet
'Code to create DataSet and customers DataTable
Dim rowNewCustomer As DataRow
rowNewCustomer = ds.Tables("Customers").NewRow()
rowNewCustomer("CustomerID") = "ALFKI"
rowNewCustomer("CompanyName") = "Alfreds Futterkiste"
ds.Tables("Customers").Rows.Add(rowNewCustomer)DataSet ds; //Code to create DataSet and customers DataTable DataRow rowNewCustomer; rowNewCustomer = ds.Tables["Customers"].NewRow(); rowNewCustomer["CustomerID"] = "ALFKI"; rowNewCustomer["CompanyName"] = "Alfreds Futterkiste"; ds.Tables["Customers"].Rows.Add(rowNewCustomer);
We can create and add a new row to our table in a single line of code, such as this:
ds.Customers.AddCustomersRow("ALFKI", "Alfreds Futterkiste")We’ll take a closer look at strongly typed DataSet objects in Chapter 9.