Now that you've seen the ADO.NET objects, it's time to see what you can do with them. Of course, in a single appendix, I can't possibly cover everything you can do with ADO.NET. So I'm going to concentrate on some of the basic operations, including
Populating a DataSet from a database
Moving around in DataSets and retrieving data
Using strongly typed DataSets
Using DataSets with multiple tables
Finding and sorting data in DataSets
Editing data with ADO.NET
Mastering these skills will allow you to work with ADO.NET in the context of applications such as Web services and remoted servers. If you're interested in exploring ADO.NET in more depth, you'll find a list of references at the end of the chapter.
Before you can do anything with data in a DataSet, you must get the data into the DataSet (see Step By Step A.5). In general, you can follow a four-step pattern to move data from the database to a DataSet object:
Create a SqlConnection object to connect to the database.
Create a SqlCommand object to retrieve the desired data.
Assign the SqlCommand to the SelectCommand property of a SqlDataAdapter object.
Call the Fill method of the SqlDataAdapter object.
STEP BY STEP
A.5 Filling a DataSet
This example demonstrates a couple of shortcuts that you can use in your ADO.NET code. First, the constructor for the SqlConnection object has an overloaded form that lets you supply the connection string when you create the object. Second, this code doesn't call the Open and Close methods of the SqlConnection explicitly.
Instead, it lets the SqlDataAdapter make those calls when it needs the data. Doing this not only cuts down the amount of code that you need to write, but it also improves the scalability of your application by keeping the SqlConnection open for the shortest possible period of time.
Choose a Table Name The second parameter to the DataAdapter.Fill method is the name of the DataTable that the method will create. The DataTable is then filled with the data supplied by the SelectCommand. The DataTable name does not have to match the table name in the underlying database. This example would work just as well if you placed data from the Products table into a DataTable named Starship (although that would be a pretty poor idea from the standpoint of code maintainability).
If you're familiar with classic ADO, you're used to Recordsets : collections of records that have a pointer to a current record. In classic ADO, you move through a Recordset with code such as this:
Do Until rst.EOF rst.MoveNext Loop
DataSets have no equivalent to this code because DataSets have no concept of a current record pointer. Instead, you move through a DataSet by working with the collections that the DataSet contains (see Step By Step A.6).
STEP BY STEP
A.6 Moving Through a DataSet
This example shows how to visit every piece of data in a DataSet by a proper selection of nested For Each loops . It also shows a general syntax for retrieving data: Locate the data row and column whose intersection contains the data that you're interested in, and use the dr(dc) syntax to retrieve the actual data value. You can use a variety of other syntaxes to retrieve data. Given a DataTable variable named dt that refers to the data from the Customer table, for example, any of these statements will retrieve the value in the first column of the first row of data in the DataTable:
dt.Rows(0).Item(0) dt.Rows(0)(0) dt.Rows(0).Item("CustomerID") dt.Rows(0)("CustomerID") dt.Rows(0)!CustomerID
All the syntaxes for retrieving data that you saw in the previous section have one thing in common: They're all late-bound . That is, the .NET Framework doesn't know until runtime that "CustomerID" is a valid column name. One of the innovations of ADO.NET is a provision to create strongly typed DataSets. In a strongly typed DataSet, columns actually become properties of the row. This allows you to write an early-bound version of the data-retrieval expression:
In addition to being faster than the late-bound syntaxes, the early-bound syntax has the advantage of making column names show up in IntelliSense tips as you type code.
You can build strongly typed DataSets by using the XSD designer, as in Step By Step A.7.
STEP BY STEP
A.7 Designing a Strongly Typed DataSet
Using the Suppliers class to define the DataSet in this case gives several syntactical benefits. You can refer to the Suppliers DataTable as a property of the DataSet. You can also refer to the columns in the DataRows in this DataTable as properties of the DataRow.
The strongly typed DataSet automatically defines a class named SuppliersRow to represent one DataRow with strong typing.
The DataSets you've seen so far in this appendix have contained a single DataTable. But DataSets are not limited to a single DataTable; in fact, no practical limit exists on the number of DataTables that a DataSet can contain. By using multiple DataAdapter objects, you can connect a single DataSet to more than one table in the SQL Server database (see Step By Step A.8). You can also define DataRelation objects to represent the relationship between the DataTables in the DataSet.
STEP BY STEP
A.8 Building a DataSet Containing Multiple DataTables
This code uses three different SqlDataAdapter objects to move data from three different database tables into a single DataSet. The data from each SqlDataAdapter is stored in a separate DataTable. You could also use a single SqlDataadapter object for the same purpose by changing its SelectCommand property each time you want to load a separate table. The code then adds DataRelation objects to specify the relationships between these DataTables. The Add method of the DataSet.Relations collection takes three parameters:
A name for the DataRelation object to be created
The DataColumn object representing the primary key side of the relationship
The DataColumn object representing the foreign key side of the relationship
Although the DataGrid in this example initially displays only the Customer data, all the data is available. The DataGrid control contains built-in logic to help navigate between related DataTables in a DataSet. If you click the + sign to the left of a row of Customer data, the DataGrid will show a list of the relationships that involve that row, as shown in Figure A.9.
The name of the relationship is a hot link. Clicking the link loads all the related rows on the other side of that relationship into the DataGrid, as shown in Figure A.10. Note that the Parent Rows area of the DataGrid contains information on the Customers row where the navigation started.
Because this DataSet has another level of detail, you can repeat the process. Click the + sign next to an order to see the relations in which that order is involved. Finally, clicking the hot link beneath an order will reveal all the Order Detail rows for that order. The Parent Rows area now contains the details on both the customer and the order that were used to get to this point.
The .NET Framework offers several object-oriented ways to find and sort data. In this section, I'll show how to use two of these ways: the DataTable.Select method and the filtering and sorting capabilities of the DataView object.
The Select method of the DataTable object is a convenient way to find particular DataRow objects within the DataTable (see Step By Step A.9). This method extracts an array of DataRow objects that you can work with.
STEP BY STEP
A.9 Using the DataTable.Select Method
The Select method of the DataTable constructs an array of DataRows based on up to three factors: a filter expression, a sort expression, and a state constant.
Filter expressions are essentially SQL WHERE clauses constructed according to these rules:
Column names containing special characters should be enclosed in square brackets.
String constants should be enclosed in single quotes.
Date constants should be enclosed in pound signs.
Numeric expressions can be specified in decimal or scientific notation.
Expressions can be created using AND , OR , NOT , parentheses, IN , LIKE , comparison operators, and arithmetic operators.
The + operator is used to concatenate strings.
Either * or % can be used as a wildcard to match any number of characters. Wildcards can be used only at the start or end of strings.
Columns in a child table can be referenced with the expression Child.Column . If the table has more than one child table, use the expression Child(RelationName).Column to choose a particular child table.
The Sum, Avg, Min, Max, Count, StDev, and Var aggregates can be used with child tables.
Supported functions include CONVERT , LEN , ISNULL , IIF , and SUBSTRING .
In the sample code, the filter expression is built by concatenating the text from the txtCountry control with a column comparison.
If you don't specify a sort order in the Select method, the rows are returned in primary key order or in the order of addition if the table doesn't have a primary key. You can also specify a sort expression consisting of one or more column names and the keywords ASC or DESC to specify ascending or descending sorts. For example, this is a valid sort expression:
Country ASC, CompanyName DESC
That expression will sort first by country in ascending order, and then by company name within each country in descending order.
Finally, you can also select DataRows according to their current state by supplying one of the DataViewRowState constants. Table A.11 shows these constants.
New rows that have not yet been committed
All current rows, whether unchanged, modified, or new
Original data from modified rows
Original data, including rows that have been modified or deleted
Rows that have not been changed
DataTable to Array You can quickly create an array that holds all the content of a DataTable by calling the Select method with no parameters:
Dim adr() As DataRow = dt.Select()
You can also sort and filter data by using a DataView (see Step By Step A.10). The DataView has the same structure of rows and columns as a DataTable, but it also lets you specify sorting and filtering options as properties of the DataView object. Typically you'll create a DataView by starting with a DataTable and specifying options to include a subset of the rows in the DataTable.
STEP BY STEP
A.10 Using a DataView to Sort and Filter Data
The constructor for the DataView specifies the DataTable that includes the data from which the DataView can draw. By setting the RowFilter, Sort, and RowStateFilter properties of the DataView, you can control which rows are available in the DataView, as well as the order in which they are presented. RowStateFilter allows you to select, for example, only rows that have been changed since the DataTable was loaded.