ADO.NET Architecture

   

---

Using DataSets

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

• Updating data

• Adding data

• Deleting data

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.

Populating a DataSet from a Database

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:

1. Create a SqlConnection object to connect to the database.

2. Create a SqlCommand object to retrieve the desired data.

3. Assign the SqlCommand to the SelectCommand property of a SqlDataAdapter object.

4. Call the Fill method of the SqlDataAdapter object.

STEP BY STEP

A.5 Filling a DataSet

1. Add a new form to your Visual Basic .NET project.

2. Place a Button control named btnLoad and a DataGrid control named dgProducts on the form. Set the CaptionText property of the DataGrid control to Products.

3. Double-click the Button control to open the form's module. Enter these statements at the top of the module:

    Imports System.Data Imports System.Data.SqlClient 

4. Enter this code to handle the Click event of the Button control:

    Private Sub btnLoad_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnLoad.Click     ' Create a SqlConnection     Dim cnn As SqlConnection = _      New SqlConnection("Data Source=(local);" & _      "Initial Catalog=Northwind;" & _      "Integrated Security=SSPI")     ' Create a SqlCommand     Dim cmd As SqlCommand = cnn.CreateCommand()     cmd.CommandType = CommandType.Text     cmd.CommandText = _      "SELECT * FROM Products ORDER BY ProductName"     ' Set up the DataAdapter and fill the DataSet     Dim da As SqlDataAdapter = New SqlDataAdapter()     da.SelectCommand = cmd     Dim ds As DataSet = New DataSet()     ' Display the data on the user interface     da.Fill(ds, "Products")     dgProducts.DataSource = ds     dgProducts.DataMember = "Products" End Sub 

5. Set the form as the startup object for the project.

6. Run the project and click the button. The code will connect to the SQL Server database on the local computer and fill the DataGrid control with the result of executing the SQL statement, as shown in Figure A.4.

   Figure A.4. A DataGrid control bound to data stored in 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.

EXAM TIP

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).

Moving Around in DataSets and Retrieving Data

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

1. Add a new form to your Visual Basic .NET project.

2. Place a Button control named btnLoadData and a ListBox control named lbData on the form.

3. Double-click the Button control to open the form's module. Enter these statements at the top of the module:

    Imports System.Data Imports System.Data.SqlClient 

4. Enter this code to handle the Click event of the Button control:

    Private Sub btnLoadData_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnLoadData.Click     ' Create a SqlConnection     Dim cnn As SqlConnection = _      New SqlConnection("Data Source=(local);" & _      "Initial Catalog=Northwind;" & _      "Integrated Security=SSPI")     ' Create a SqlCommand     Dim cmd As SqlCommand = cnn.CreateCommand()     cmd.CommandType = CommandType.Text     cmd.CommandText = _      "SELECT * FROM Customers WHERE Country = 'France'"     ' Set up the DataAdapter and fill the DataSet     Dim da As SqlDataAdapter = New SqlDataAdapter()     da.SelectCommand = cmd     Dim ds As DataSet = New DataSet()     da.Fill(ds, "Customers")     ' Dump the contents of the DataSet     Dim dt As DataTable     Dim dr As DataRow     Dim dc As DataColumn     lbData.Items.Add("DataSet: " & ds.DataSetName)     For Each dt In ds.Tables         lbData.Items.Add("  DataTable: " & _          dt.TableName)         For Each dr In dt.Rows             lbData.Items.Add("    DataRow")             For Each dc In dt.Columns                 lbData.Items.Add("      " & dr(dc))             Next         Next     Next End Sub 

5. Set the form as the startup object for the project.

6. Run the project and click the button. The code will dump the contents of the DataSet to the ListBox control, as shown in Figure A.5.

   Figure A.5. Dumping the contents of a DataSet to a ListBox control.

   

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 

Using Strongly Typed DataSets

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:

 dt.Rows(0).CustomerID 

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

1. Select Project, Add New Item in your Visual Basic .NET project.

2. In the Add New Item dialog box, select the DataSet template. Name the new DataSet Suppliers.xsd . Click Open to create the XSD file, and open it in the Designer.

3. Open Server Explorer.

4. Expand the tree under Data Connections to show a SQL Server data connection that points to the Northwind sample database, and then the Tables node of the SQL Server. Drag the Suppliers table from Server Explorer and drop it on the design surface for the DataSet. Figure A.6 shows the resulting XSD Design view. The E icons for each column of the database table indicate that those columns have been rendered as XML elements.

   Figure A.6. A strongly typed DataSet open in the XSD Designer.

   

5. Save the DataSet. At this point, your project will contain a new class named Suppliers, which is a strongly typed DataSet that you can use in code.

6. Add a new form to your Visual Basic .NET project.

7. Place a Button control named btnLoadData and a ListBox control named lbData on the form.

8. Double-click the Button control to open the form's module. Enter these statements at the top of the module:

    Imports System.Data Imports System.Data.SqlClient 

9. Enter this code to handle the Click event of the Button control:

    Private Sub btnLoadData_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnLoadData.Click     ' Create a SqlConnection     Dim cnn As SqlConnection = _      New SqlConnection("Data Source=(local);" & _      "Initial Catalog=Northwind;" & _      "Integrated Security=SSPI")     ' Create a SqlCommand     Dim cmd As SqlCommand = cnn.CreateCommand()     cmd.CommandType = CommandType.Text     cmd.CommandText = "SELECT * FROM Suppliers"     ' Set up the DataAdapter and fill the DataSet     Dim da As SqlDataAdapter = New SqlDataAdapter()     da.SelectCommand = cmd     Dim ds As Suppliers = New Suppliers()     da.Fill(ds, "Suppliers")     ' Dump the contents of the DataSet     Dim suppRow As Suppliers.SuppliersRow     For Each suppRow In ds.Suppliers         lbData.Items.Add(suppRow.SupplierID & " " & _          suppRow.CompanyName)     Next End Sub 

10. Set the form as the startup object for the project.

11. Run the project and click the button. The code will display two columns from the DataSet in the ListBox control, as shown in Figure A.7.

    Figure A.7. Data dumped from 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.

DataSets with Multiple Tables

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

1. Add a new form to your Visual Basic .NET project.

2. Place a Button control named btnLoadData and a DataGrid control named dgMain on the form.

3. Double-click the Button control to open the form's module. Enter these statements at the top of the module:

    Imports System.Data Imports System.Data.SqlClient 

4. Enter this code to handle the Click event of the Button control:

    Private Sub btnLoadData_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnLoadData.Click     ' Create a SqlConnection and a DataSet     Dim cnn As SqlConnection = _      New SqlConnection("Data Source=(local);" & _      "Initial Catalog=Northwind;" & _      "Integrated Security=SSPI")     Dim ds As DataSet = New DataSet()     ' Add the customers data to the DataSet     Dim cmdCustomers As SqlCommand = _      cnn.CreateCommand()     cmdCustomers.CommandType = CommandType.Text     cmdCustomers.CommandText = _      "SELECT * FROM Customers"     Dim daCustomers As SqlDataAdapter = _      New SqlDataAdapter()     daCustomers.SelectCommand = cmdCustomers     daCustomers.Fill(ds, "Customers")     ' Add the Orders data to the DataSet     Dim cmdOrders As SqlCommand = cnn.CreateCommand()     cmdOrders.CommandType = CommandType.Text     cmdOrders.CommandText = "SELECT * FROM Orders"     Dim daOrders As SqlDataAdapter = _      New SqlDataAdapter()     daOrders.SelectCommand = cmdOrders     daOrders.Fill(ds, "Orders")     ' Add the Order Details data to the DataSet     Dim cmdOrderDetails As SqlCommand = _      cnn.CreateCommand()     cmdOrderDetails.CommandType = CommandType.Text     cmdOrderDetails.CommandText = _      "SELECT * FROM [Order Details]"     Dim daOrderDetails As SqlDataAdapter = _      New SqlDataAdapter()     daOrderDetails.SelectCommand = cmdOrderDetails     daOrderDetails.Fill(ds, "OrderDetails")     ' Add Relations     Dim relCustOrder As DataRelation = _      ds.Relations.Add("CustOrder", _      ds.Tables("Customers").Columns("CustomerID"), _      ds.Tables("Orders").Columns("CustomerID"))     Dim relOrderOrderDetails As DataRelation = _      ds.Relations.Add("OrderOrderDetails", _      ds.Tables("Orders").Columns("OrderID"), _      ds.Tables("OrderDetails").Columns("OrderID"))     ' And show the data on the user interface     dgMain.DataSource = ds     dgMain.DataMember = "Customers" End Sub 

5. Set the form as the startup object for the project.

6. Run the project and click the button. The code will load all three database tables into the DataSet and then display the customers' information on the DataGrid, as shown in Figure A.8.

   Figure A.8. Customers' data from a multitable DataSet.

   

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:

1. A name for the DataRelation object to be created

2. The DataColumn object representing the primary key side of the relationship

3. 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.

Figure A.9. Viewing the relationships for a row of customer data.

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.

Figure A.10. Orders data from a multitable DataSet.

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.

Finding and Sorting Data in DataSets

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

1. Add a new form to your Visual Basic .NET project.

2. Place a Button control named btnSelect , a TextBox control named txtCountry , and a ListBox control named lbSelected on the form.

3. Double-click the Button control to open the form's module. Enter these statements at the top of the module:

    Imports System.Data Imports System.Data.SqlClient 

4. Enter this code to handle the Click event of the Button control:

    Private Sub btnSelect_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnSelect.Click     ' Create a SqlConnection     Dim cnn As SqlConnection = _      New SqlConnection("Data Source=(local);" & _      "Initial Catalog=Northwind;" & _      "Integrated Security=SSPI")     ' Create a SqlCommand     Dim cmd As SqlCommand = cnn.CreateCommand()     cmd.CommandType = CommandType.Text     cmd.CommandText = "SELECT * FROM Customers"     ' Set up the DataAdapter and fill the DataSet     Dim da As SqlDataAdapter = New SqlDataAdapter()     da.SelectCommand = cmd     Dim ds As DataSet = New DataSet()     da.Fill(ds, "Customers")     ' Use the Select method to     ' get a sorted array of DataRows     Dim adr() As DataRow = _      ds.Tables("Customers").Select(_      "Country = '" & txtCountry.Text & _       "'", "ContactName ASC")     ' Dump the result to the user interface     lbSelected.Items.Clear()     Dim dr As DataRow     For Each dr In adr         lbSelected.Items.Add(dr(0) & _          " " & dr(1) & " " & dr(2))     Next End Sub 

5. Set the form as the startup object for the project.

6. Run the project. Enter a country name and click the button. You'll see the first three columns from DataRows for customers in that country, as shown in Figure A.11.

   Figure A.11. Results of 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.

Table A.11. DataViewRowState Constants

Constant	Meaning
Added	New rows that have not yet been committed
CurrentRows	All current rows, whether unchanged, modified, or new
Deleted	Deleted rows
ModifiedCurrent	Modified rows
ModifiedOriginal	Original data from modified rows
None	No rows
OriginalRows	Original data, including rows that have been modified or deleted
Unchanged	Rows that have not been changed

EXAM TIP

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

1. Add a new form to your Visual Basic .NET project.

2. Place a Button control named btnLoad and a DataGrid control named dgCustomers on the form.

3. Double-click the Button control to open the form's module. Enter these statements at the top of the module:

    Imports System.Data Imports System.Data.SqlClient 

4. Enter this code to handle the Click event of the Button control:

    Private Sub btnLoad_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnLoad.Click     ' Create a SqlConnection     Dim cnn As SqlConnection = _      New SqlConnection("Data Source=(local);" & _      "Initial Catalog=Northwind;" & _      "Integrated Security=SSPI")     ' Create a SqlCommand     Dim cmd As SqlCommand = cnn.CreateCommand()     cmd.CommandType = CommandType.Text     cmd.CommandText = "SELECT * FROM Customers"     ' Set up the DataAdapter and fill the DataSet     Dim da As SqlDataAdapter = New SqlDataAdapter()     da.SelectCommand = cmd     Dim ds As DataSet = New DataSet()     da.Fill(ds, "Customers")     ' Create a DataView based on     ' the Customers DataTable     Dim dv As DataView = _      New DataView(ds.Tables("Customers"))     dv.RowFilter = "Country = 'France'"     dv.Sort = "CompanyName ASC"     dgCustomers.DataSource = dv End Sub 

5. Set the form as the startup object for the project.

6. Run the project and click the button. The DataGrid will display only the customers from France, sorted in ascending order by company name.

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.

---

Top