ADO.NET

Overview

The Microsoft .NET Framework includes an entirely new data access framework that allows you to query data sources, fill disconnected DataSet containers, and continue to work with relational data long after the original connection has been closed. In this chapter, we tackle some of the most commonly asked ADO.NET questions and provide tips for improving performance, creating robust applications, and simplifying user interface code when using ADO.NET.

It goes without saying that you shouldn't use this chapter to learn basic ADO.NET programming. Straightforward tasks such as executing commands, using a DataReader, and iterating through a DataSet aren't covered (nor are the basics of SQL). If you need a comprehensive introduction to ADO.NET, consider David Sceppa's excellent book Microsoft ADO.NET Core Reference (Microsoft Press, 2002). You can then turn to the recipes in this chapter to resolve some common points of confusion or enhance your knowledge with some commonly requested tips and techniques.

The recipes at the beginning of this chapter discuss ADO.NET essentials such as connection pooling (recipe 14.1), using unique IDs (recipes 14.2 and 14.3), preventing SQL injection attacks (recipe 14.4), and executing batch queries (recipe 14.5). Later recipes show various ways to customize DataAdapter logic (recipes 14.7 and 14.8), handle concurrency problems (recipe 14.9), and deal with large binary fields (recipe 14.11). Finally, the recipes toward the end of the chapter dive into data binding with Microsoft Windows Forms and ADO.NET, showing you how to synchronize controls (recipe 14.13), filter and sort records (recipe 14.14), handle table relations (recipe 14.15), and deal with different types of data (recipes 14.16, 14.17, and 14.18).

Almost all the examples in this chapter use the Microsoft SQL Server provider and require that you import the System.Data.SqlClient namespace. The recipes present concepts that can be used with any data source and provider, but they assume you are using the Northwind database that's included with SQL Server and its scaled-down relative, Microsoft Desktop Engine (MSDE). If you don't have access to SQL Server, you can use the local MSDE engine that's installed with Microsoft Visual Studio .NET or the .NET Framework SDK. Refer to the installation documentation included with that product for more information.

Use Connection Pooling

Problem

You want to maintain a pool of open connections to improve scalability for a large system.

Solution

Use connection pooling by configuring the connection string settings.

Discussion

Creating a database connection imposes a small but measurable overhead. In a large system where connections are continuously being acquired and released, this overhead can become a bottleneck that limits the overall throughput of the system. To prevent this problem, you can use connection pooling. With connection pooling, the system retains a pool of open connections that it can assign as needed. When you close a pooled connection, the underlying connection isn't actually released. Instead, it's moved to the connection pool. The next time a client tries to open a new connection, the existing connection is reused automatically, saving the overhead of creating and initializing a new connection.

You don't need to take any extra steps to implement connection pooling in ADO.NET. The SQL Server provider and the Oracle provider (included with .NET 1.1) both perform connection pooling automatically. However, you can configure connection pooling using connection string settings, as indicated in Table 14-1.

Table 14-1: Connection String Settings

Setting	Description
Connection Lifetime	Specifies the maximum amount of time that a connection can live in the pool (in seconds). Every time a connection is returned to the pool, its creation time is compared against this value. If it's older than the specified lifetime, it's destroyed. The default is 0, which specifies that all connections be retained for the life of the process.
Connection Reset	If True, the connection state is reset when a pooled connection is reused. State includes session-level SET statements and the currently selected database. This setting is recommended and is True by default.
Max Pool Size	The maximum number of connections allowed in the pool (100 by default). If the maximum pool size is reached and no connection is free, the request is queued and performance suffers.
Min Pool Size	The minimum number of connections always retained in the pool (0 by default). This number of connections is created when the first connection is created, leading to a minor delay for the first request.
Pooling	When True (the default), connection pooling is used.

Here's a basic example of how you can configure connection pooling:

' Create a pool that always retains at least 5 connections,
' and won't allow more than 10 simultaneous connections.
Dim ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=10"
 
Dim con As New SqlConnection(ConnectionString)
 
' Open the connection.
' If there is an existing connection in the pool, it will be reused.
' If this is the first time you have opened a connection,
' the basic pool of 5 connections will be created.
con.Open()
 
' This returns the connection to the connection pool so it can be reused.
con.Close()

Note

The OLE DB and ODBC providers also perform a more limited type of connection pooling. OLE DB session pooling is implemented automatically and cannot be configured. ODBC pooling is implemented at the driver level and can be configured through the ODBC Data Sources section of Control Panel. These forms of connection pooling are more limited than the connection pooling provided with the SQL Server and Oracle providers because they do not allow you to enforce pool minimums and maximums.

There are two factors that can cause connection pooling to behave differently than you expect.

• Connection strings.If there's any difference in the connection string used by different clients, the connections will be created and maintained in separate pools. Separate pools are used if the connection string specifies a different user for authentication or even if the connection string just has an extra space or changes the order of identical settings. Thus, it's recommended that you place the connection string in a configuration file to make sure it's always exactly the same.
• The application process.Connection pooling is tied to the application process. Thus, if you have multiple client applications running on separate computers, and they use a local database component, each client will have its own local pool, and connections won't be shared between clients. If, on the other hand, all these clients access the same database component through XML Web services or through .NET Remoting, the remote component can use connection pooling and a single server-side pool can serve all clients.

Retrieve the Unique ID for a New Record

Problem

You need to insert a record and retrieve its unique ID number, which is generated by the database.

Solution

Use a database stored procedure that returns this information in an output parameter.

Discussion

Many database tables use an ID or a GUID (globally unique identifier) field to uniquely identify records. This field is generated by the database when the record is created, not by the client. However, the client often needs to show this information to the user, and it might serve as a registration number or a confirmation number. To make it easy for the client to retrieve this information, it's recommended that you use a stored procedure that explicitly returns this information.

For example, the Northwind database includes a Shippers table. Every shipper is identified by a company name, a phone number, and a unique ID. A stored procedure that inserts this record and returns the new ID value would look like this:

CREATE Procedure AddShipper
(
 @CompanyName nvarchar(40),
 @Phone nvarchar(24),
 @ID int OUTPUT
)
AS
 INSERT INTO Shippers
 (CompanyName, Phone)
 VALUES
 (@CompanyName, @Phone)
 
 SELECT @ID = @@Identity
GO

In this case, the stored procedure accepts two input parameters (the company name and phone number) and uses one output parameter. After the insert has been performed, the code uses the Transact-SQL global variable @@Identity, which provides the unique identity value that was just generated. This value is applied to the @ID output parameter. Here's a simple Console application that tests this stored procedure:

Public Module AutoIncrementInsert
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Public Sub Main()
 ' Create the connection and command.
 Dim Con As New SqlConnection(ConnectionString)
 Dim Cmd As New SqlCommand("AddShipper", con)
 cmd.CommandType = CommandType.StoredProcedure
 
 ' Add the input parameters.
 Dim Param As SqlParameter = cmd.Parameters.Add("@CompanyName", _
 SqlDbType.NVarChar, 40)
 param.Value = "Test Company"
 
 Param = Cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 24)
 Param.Value = "[phone]"
 
 ' Add the output parameter.
 Param = cmd.Parameters.Add("@ID", SqlDbType.Int)
 Param.Direction = ParameterDirection.Output
 
 Try
 ' Execute the command.
 con.Open()
 cmd.ExecuteNonQuery()
 
 ' Display the returned ID
 Console.WriteLine("The record you inserted has ID " & _
 Param.Value.ToString())
 
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 
 Finally
 Con.Close()
 End Try
 
 Console.ReadLine()
 End Sub
 
End Module

You can use a similar technique to retrieve GUID values. In this case, the stored procedure must first generate the GUID using the Transact-SQL function NEWID. It can then insert that value in the database, and return it to the user.

-- Create the GUID and assign it to the output parameter.
SELECT @GUID = NEWID()
 
-- (Now perform the insert using all the parameters.)

Retrieve the Unique ID for a New Record in a DataSet

Problem

When you perform an update with the DataAdapter, you want the ID numbers for newly added records to be inserted into the DataSet.

Solution

Use a database stored procedure that returns this information in an output parameter, and map the parameter to the appropriate column.

Discussion

Recipe 14.2 demonstrates how to return unique ID information with a stored procedure and configure the ADO.NET database command accordingly. This recipe shows how you can use this command with a DataSet so that the retrieved ID information is applied automatically to the appropriate field in the DataSet.

First you must map the parameters in the command to the fields in the DataSet by setting the Parameter.SourceColumn property to the corresponding field name. You also need to ensure that the Command.UpdatedRowSource property is set to UpdateRowSource.OutputParameters or UpdateRowSource.Both (the default).

The following Console application uses the AddShipper stored procedure from recipe 14.2 to insert new records into the Shippers table of the Northwind database. The DataSet is updated automatically with the value from the identity field.

Public Module AutoIncrementInsert
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Public Sub Main()
 ' Create the connection.
 Dim Con As New SqlConnection(ConnectionString)
 
 ' Create the command for filling the DataSet.
 Dim CmdSelect As New SqlCommand("SELECT * FROM Shippers", Con)
 
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 Dim Ds As New DataSet()
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Dim Rows As Integer = Adapter.Fill(Ds, "Shippers")
 Console.WriteLine("Retrieved " & Rows.ToString() & " rows.")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Add a new row.
 Dim Row As DataRow = Ds.Tables("Shippers").NewRow()
 Row("CompanyName") = "Test Company"
 Row("Phone") = "[phone]"
 Ds.Tables("Shippers").Rows.Add(Row)
 
 ' Create the insert command.
 Dim cmdInsert As New SqlCommand("AddShipper", Con)
 cmdInsert.CommandType = CommandType.StoredProcedure
 
 ' Map the columns to the input parameters.
 Dim Param As SqlParameter = cmdInsert.Parameters.Add("@CompanyName", _
 SqlDbType.NVarChar, 40, "CompanyName")
 Param = cmdInsert.Parameters.Add("@Phone", SqlDbType.NVarChar, 24, _
 "Phone")
 
 ' Map the output parameter to the ID field.
 Param = cmdInsert.Parameters.Add("@ID", SqlDbType.Int)
 Param.SourceColumn = "ShipperID"
 Param.Direction = ParameterDirection.Output
 
 ' Assign the command to the adapter.
 Adapter.InsertCommand = cmdInsert
 
 ' Perform the insert.
 Try
 ' Execute the command.
 Con.Open()
 
 Console.Write("ID before update: ")
 Console.WriteLine(Row("ShipperID"))
 
 Adapter.Update(Ds, "Shippers")
 Console.Write("ID after update: ")
 Console.WriteLine(Row("ShipperID"))
 
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 
 Finally
 Con.Close()
 End Try
 
 Console.ReadLine()
 End Sub
 
End Module

Protect Against SQL Injection Attacks

Problem

You want to reduce the opportunity for attackers to submit malicious SQL code in command parameter values.

Solution

Use a parameterized query.

Discussion

Many applications build SQL commands dynamically by parsing the pieces together into a large string. This approach poses problems when dealing with binary data, and it also raises the possibility that an attacker can execute malicious SQL code by "injecting" it along with a parameter value. This malicious code could be used to tamper with information in the database or even launch another application on the server. You can read some frightening examples for different database servers on the Open Web Application Security Project Web site at http://www.owasp.org/asac/input_validation/sql.shtml .

To prevent this problem, you should validate user input, checking that it has the expected data type, is not unusually long, and so on. In addition, you should properly escape the input with delimiters. The easy way to perform this step is to use a parameterized query. The Command class will automatically escape all parameter values for you when you use a parameterized query.

Parameterized queries are used for all stored procedure calls, but you can also use them with dynamic SQL statements. In the latter case, you simply need to take an ordinary SQL statement and replace the dynamic values with parameters. (The result will look like the body of a simple stored procedure.) Here's an example parameterized SQL statement:

INSERT INTO Shippers (CompanyName, Phone) VALUES (@CompanyName, @Phone)

To use this statement, you need to add the corresponding Parameter objects to the Command object (with the appropriate values). In this case, two parameters (@CompanyName and @Phone) are required. The following Console application uses this parameterized query to add a new record to the Shippers table of the Northwind database.

Public Module ParameterizedQuery
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Public Sub Main()
 ' Create the connection and command.
 Dim Con As New SqlConnection(ConnectionString)
 Dim UpdateSQL As String = "INSERT INTO Shippers " & _
 "(CompanyName, Phone) VALUES (@CompanyName, @Phone)"
 Dim Cmd As New SqlCommand(UpdateSQL, Con)
 
 ' Add the input parameters.
 Dim Param As SqlParameter = Cmd.Parameters.Add("@CompanyName", _
 SqlDbType.NVarChar, 40)
 Param.Value = "Test Company"
 
 Param = Cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 24)
 Param.Value = "[phone]"
 
 Try
 ' Execute the command.
 Con.Open()
 Dim Rows As Integer = Cmd.ExecuteNonQuery()
 Console.WriteLine(Rows.ToString() & " row(s) affected.")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 Console.ReadLine()
 End Sub
 
End Module

Note

In most providers, parameterized queries use named parameters as shown earlier. However, in the OLE DB provider, you must use question mark (?) placeholders instead. When adding the OleDbParameter objects to OleDbCommand, the parameter name is not important. What is important is ensuring that you add the OleDbParameter objects in the same order as the question mark placeholders appear in the SQL statement.

Execute Multiple SQL Statements at Once

Problem

You want to execute more than one SQL statement without requiring multiple trips to the server.

Solution

Use a batch query, and separate your commands with a semicolon.

Discussion

Most ADO.NET providers support batch queries, which allow you to execute more than one SQL statement. To create a batch query, you simply separate the SQL statements using a semicolon, as shown here:

SELECT * FROM Products;SELECT * FROM Categories

In this example, both SQL statements are queries. If you to execute this statement using Command.ExecuteReader, you'll first retrieve the results from the first query (the list of products). Once you have read these results, you'll need to use the DataReader.NextResult method to move the DataReader to the next result set (the list of categories). If you use this batch query with a DataAdapter, as shown in the following example, the DataAdapter will actually add two tables to the DataSet, one for each result set.

Public Module BatchQuery
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Public Sub Main()
 ' Create the ADO.NET objects.
 Dim Con As New SqlConnection(ConnectionString)
 Dim BatchQuery As String = "SELECT * FROM Products;" & _
 "SELECT * FROM Categories"
 Dim Cmd As New SqlCommand(BatchQuery, Con)
 
 Dim Adapter As New SqlDataAdapter(Cmd)
 Dim Ds As New DataSet()
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Dim Rows As Integer = Adapter.Fill(Ds)
 
 ' Display information about the retrieved tables.
 Console.WriteLine("The DataSet contains " & _
 Ds.Tables.Count.ToString() & " tables.")
 Dim Dt As DataTable, Dr As DataRow
 For Each Dt In Ds.Tables
 Console.WriteLine(Dt.TableName)
 
 ' Show the first column of each row in the table.
 For Each Dr In Dt.Rows
 Console.WriteLine(" " & Dt.Columns(0).ColumnName & _
 ": " & Dr(0))
 Next
 Console.WriteLine()
 Next
 
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 Console.ReadLine()
 End Sub
 
End Module

The output for this application looks like this:

The DataSet contains 2 tables.
Table
 ProductID: 1
 ProductID: 2
 ProductID: 3
 ProductID: 4
 . . .
 
Table1
 CategoryID: 1
 CategoryID: 2
 CategoryID: 3
 CategoryID: 4
 . . .

Note

When adding multiple tables, the DataAdapter uses default names such as Table, Table1, Table2, and so on. You can modify these names after the fact, or you can use the table mapping approach shown in recipe 14.6 to map the automatically generated names to the real table names.

Use Column and Table Mappings

Problem

You want to map database column and table names to the names expected by your code for the DataSet.

Solution

Create ColumnMapping objects for each column and DataTableMapping objects for each table, and assign them to the DataAdapter.TableMappings collection.

Discussion

The DataAdapter provides a layer of indirection between the data source and the DataSet. Using the DataAdapter, you can configure table and column mappings so that code remains unchanged when the data source changes (or vice versa).

Column mappings link a field in the data source with a differently named field in the DataSet. The following example shows how you might modify names in the Categories table when filling a DataSet: Source fields like ShipperID and Phone are mapped to ID and PhoneNumber in the destination DataTable.

Public Module MappingTest
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Public Sub Main()
 ' Create the connection.
 Dim Con As New SqlConnection(ConnectionString)
 
 ' Create the command for filling the DataSet.
 Dim CmdSelect As New SqlCommand("SELECT * FROM Shippers", Con)
 
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 Dim Ds As New DataSet()
 
 ' Map the default table name.
 Dim CustomerMap As System.Data.Common.DataTableMapping
 CustomerMap = Adapter.TableMappings.Add("Table", "Shippers")
 
 ' Add the column mappings to the table.
 CustomerMap.ColumnMappings.Add("ShipperID", "ID")
 CustomerMap.ColumnMappings.Add("CompanyName", "CompanyName")
 CustomerMap.ColumnMappings.Add("Phone", "PhoneNumber")
 
 ' Fill the DataSet.
 Adapter.Fill(Ds)
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Dim Rows As Integer = Adapter.Fill(Ds, "Shippers")
 
 Console.WriteLine("The DataTable is named: " & _
 Ds.Tables(0).TableName)
 Console.Write("The column names are: ")
 Dim Col As DataColumn
 For Each Col In Ds.Tables(0).Columns
 Console.Write(Col.ColumnName & " ")
 Next
 
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 Console.ReadLine()
 End Sub
 
End Module

The great advantage of column mappings is that they are bidirectional. In other words, the DataAdapter applies the mappings both when reading data as part of a fill operation and when applying changes as part of an update operation.

Use Last In Wins Concurrency with the DataAdapter

Problem

You want to customize the DataAdapter update logic so that changes are allowed, even to records that have been modified by other users.

Solution

Customize the UpdateCommand so that is selects records based on a single unique field, instead of a combination of fields.

Discussion

The automatically generated update logic provided by the CommandBuilder selects records for an update by attempting to match each field in the WHERE clause. Here's an example update command for the Shippers table from the Northwind database:

UPDATE Shippers SET CompanyName=[NewVal]
 WHERE ShipperID=[LastVal] AND CompanyName=[LastVal] AND Phone=[LastVal] ...

This approach is usually inefficient because it increases the amount of data sent over the network. It's also more difficult for the database engine to optimize than a targeted query that matches only a single, indexed field. In addition, if any of the fields have changed since the DataSet was filled, the command won't be able to match the record. At this point an error will occur, and the update will fail.

There's more than one way to resolve this problem. If you want to increase DataAdapter performance but retain strict concurrency checking, you should use a timestamp, as described in recipe 14.8. If you want to optimize performance and allow looser "last-in-wins" concurrency, you can select records based on a single unique field. This way, even if the other fields in the record have been changed, the command will still find the record and update it without generating an error.

Here's an example that uses last-in-wins concurrency to match records from the Shippers table based on the ShipperID:

UPDATE Shippers SET CompanyName=[NewVal]
 WHERE ShipperID=[LastVal]

Custom update commands can be stored as procedure commands or parameterized commands. The following Console application demonstrates a parameterized command for updating the Shippers table.

Public Module LastInWinsConcurrency
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Public Sub Main()
 ' Create the ADO.NET objects.
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdSelect As New SqlCommand("SELECT * FROM Shippers", Con)
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 Dim Ds As New DataSet()
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Adapter.FillSchema(Ds, SchemaType.Mapped, "Shippers")
 Adapter.Fill(Ds, "Shippers")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Define a custom update command.
 Dim UpdateSQL As String = "UPDATE Shippers SET " & _
 "CompanyName=@Company, Phone=@Phone WHERE ShipperID=@ID"
 Dim CmdUpdate As New SqlCommand(UpdateSQL, Con)
 
 ' Map the DataSet fields to the parameter values.
 CmdUpdate.Parameters.Add("@Company", SqlDbType.VarChar, 40, _
 "CompanyName")
 CmdUpdate.Parameters.Add("@Phone", SqlDbType.VarChar, 24, "Phone")
 Dim Param As SqlParameter = CmdUpdate.Parameters.Add("@ID", _
 SqlDbType.Int, Nothing, "ShipperID")
 Param.SourceVersion = DataRowVersion.Original
 Adapter.UpdateCommand = CmdUpdate
 
 ' Apply a change to row with ShipperID 1 ("Speedy Express").
 ' Make the change using a duplicate DataSet.
 Dim DuplicateDs As DataSet = Ds.Copy()
 Dim Row As DataRow = DuplicateDs.Tables(0).Rows.Find(1)
 Row("CompanyName") = "Deluxe Express"
 Try
 Con.Open()
 Adapter.Update(DuplicateDs, "Shippers")
 Console.WriteLine("Successfully applied change from DuplicateDs.")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Now apply a conflicting change using the original DataSet.
 ' This simulates a concurrency problem, because the original
 ' values in the row are different than the information in the 
 ' database.
 Row = Ds.Tables(0).Rows.Find(1)
 Row("CompanyName") = "Super Deluxe Express"
 Try
 Con.Open()
 
 ' This would fail with the auto-generated logic.
 Adapter.Update(Ds, "Shippers")
 Console.WriteLine("Successfully applied conflicting " & _
 "change from Ds.")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 Console.ReadLine()
 End Sub
 
End Module

There is a significant limitation with last-in-wins concurrency: your changes will overwrite the changes of any other users that might have been made in the time between the query and updating the data source. If you update a record that has been modified by another user, all the values that are in your copy of the DataRow will be applied. In other words, this sequence of events can occur:

1. User A retrieves a DataSet.
2. User B retrieves a DataSet.
3. User A commits some changes to a row.
4. User B commits a change to a single field in the same row. At the same time, all the original values from the DataRow are applied, overwriting any changes User A made.

To resolve this problem, you need to use a type of changed-values-only concurrency. Unfortunately, though the DataSet tracks which rows have been modified and which rows have not, there's no way to create changed-values-only commands with ADO.NET. The only alternative is to create your own DataAdapter type of object, which steps through the rows in the DataSet and creates a new SQL UPDATE statement for each row based on its changed values.

Optimize DataAdapter Performance with a Timestamp

Problem

You want to use strict concurrency checking but optimize DataAdapter performance.

Solution

Add a timestamp field to your table, and create a custom update command that uses it.

Discussion

You can use the CommandBuilder to generate update logic for your database tables. However, the autogenerated SQL statements it creates are usually inefficient because they attempt to match every field in a table. As the number of columns increases in the DataTable, the number of columns in the WHERE clause of the query also increases.

To improve on this system, you can add a special timestamp field to your table. Most database systems support some kind of timestamp data type, which the data source updates automatically every time the row is changed. You never need to modify the timestamp column manually. However, you can examine it for changes and thereby determine whether another user has recently applied an update.

Note

In some databases, a timestamp column records the date and time of the last update. In the case of SQL Server, the timestamp column actually represents an increasing counter stored in binary format that's unique within the database (which is also referred to as a row version column). A SQL Server stored procedure can return the most recently generated timestamp value by retrieving the value from the @@DBTS global variable.

The advantage of using a timestamp is that you only need to examine one column to determine if any changes have been made, which shortens the SQL statement and simplifies the lookup logic.

The following example mirrors the example shown in recipe 14.7. The difference is that this example requires a timestamp column named Version. If any other user modifies any field in the record, the Version column will be modified as well. Before running this test, you'll need to add the Version timestamp column to the table.

Public Module TimestampUpdating
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Public Sub Main()
 ' Create the ADO.NET objects.
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdSelect As New SqlCommand("SELECT * FROM Shippers", Con)
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 Dim Ds As New DataSet()
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Adapter.FillSchema(Ds, SchemaType.Mapped, "Shippers")
 Adapter.Fill(Ds, "Shippers")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Define a custom update command.
 Dim UpdateSQL As UpdateSQL = "UPDATE Shippers SET " & _
 "CompanyName=@Company, Phone=@Phone WHERE ShipperID=@ID " & _
 "AND Version=@Version"
 Dim CmdUpdate As New SqlCommand(UpdateSQL, Con)
 
 ' Map the DataSet fields to the parameter values.
 CmdUpdate.Parameters.Add("@Company", SqlDbType.VarChar, 40, _
 "CompanyName")
 CmdUpdate.Parameters.Add("@Phone", SqlDbType.VarChar, 24, "Phone")
 Dim Param As SqlParameter = CmdUpdate.Parameters.Add("@ID", _
 SqlDbType.Int, Nothing, "ShipperID")
 Param.SourceVersion = DataRowVersion.Original
 Param = CmdUpdate.Parameters.Add("@Version", SqlDbType.Timestamp, _
 Nothing, "Version")
 Adapter.UpdateCommand = CmdUpdate
 
 ' Apply a change to row with ShipperID 1 ("Speedy Express").
 ' Make the change using a duplicate DataSet.
 Dim DuplicateDs As DataSet = Ds.Copy()
 Dim Row As DataRow = DuplicateDs.Tables(0).Rows.Find(1)
 Row("CompanyName") = "Deluxe Express"
 Try
 Con.Open()
 Adapter.Update(DuplicateDs, "Shippers")
 Console.WriteLine("Successfully applied change from DuplicateDs.")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Now apply a conflicting change using the original DataSet.
 ' This simulates a concurrency problem, because the original
 ' values in the row are different than the information in the 
 ' database.
 Row = Ds.Tables(0).Rows.Find(1)
 Row("CompanyName") = "Super Deluxe Express"
 Try
 Con.Open()
 
 ' This attempt will fail because the timestamp will not match.
 Adapter.Update(Ds, "Shippers")
 Console.WriteLine("Successfully applied conflicting change")
 Catch Err As Exception
 Console.WriteLine("Attempt to apply conflicting change failed.")
 Finally
 Con.Close()
 End Try
 
 Console.ReadLine()
 End Sub
 
End Module

Note

When crafting a custom update command with a timestamp, you should write the WHERE clause so that it attempts to match both the timestamp and some unique field. Technically, the timestamp field is unique in the database, and it could be used to uniquely match a row. However, a primary key will provide faster lookup because the primary key column almost always has an index. You should never create an index on a timestamp value because it changes frequently.

Handle DataAdapter Concurrency Errors

Problem

You are performing an update that might fail because of a concurrency error.

Solution

Handle the DataAdapter.RowUpdated event and check for errors, or set the DataAdapter.ContinueUpdateOnError property to True.

Discussion

If the DataAdapter executes an update command and detects that the number of affected rows is 0, it throws a DBConcurrencyException. The entire update operation will be aborted, and no further rows in the DataSet will be examined.

Usually, a DBConcurrencyException occurs for one of two reasons:

• You have incorrectly written the SQL for a custom UPDATE, INSERT, or DELETE command.
• The row can't be found because the information being used to find it doesn't match the current values. This problem signals that another user has changed the row since the last time you retrieved the information.

You can prevent concurrency errors using last-in-wins concurrency (as discussed in recipe 14.7). You can also handle concurrency errors on your own and act accordingly. For example, you might respond by informing the user, modifying the update command to allow the change to proceed, or querying the current information from the database.

There are two choices for handling the error. One option is to handle the DataAdapter.RowUpdated event that fires after a command has been executed but before an error has been raised. You can use this event handler to log problems, and programmatically instruct the DataAdapter to ignore the error and continue processing other errors. Here's an example that displays and skips all errors:

Private Sub OnRowUpdated(ByVal sender As Object, _
 e As SqlRowUpdatedEventArgs) Handles Adapter.RowUpdated
 
 ' Check how many records were affected.
 ' If no records were affected, there was an error.
 If e.RecordsAffected() = 0 Then
 ' The following statement retrieves a field from the row.
 Console.WriteLine("Error updating row: " & e.Row("ShipperID"))
 
 ' Statement types include DELETE, INSERT, UPDATE, and SELECT.
 Console.WriteLine(e.StatementType)
 
 Console.WriteLine(e.Errors.ToString())
 
 ' Don't throw an exception. Continue with following rows.
 e.Status = UpdateStatus.SkipCurrentRow
 End If
 
End Sub

Another, simpler choice is to set the DataAdapter.ContinueUpdateOnError property to True. Then, after the update is complete, you can investigate errors, log them, or display them to the user. The DataAdapter will attempt every change.

Adapter.ContinueUpdateOnError = True
 
' (Perform update here. Concurrency errors will be suppressed.)
 
' Display errors.
Dim Errors() As DataRow = Ds.Tables(0).GetErrors()
Dim RowError As DataRow
For Each RowError In Errors
 Console.WriteLine("Did not update row: " & RowError("ShipperID"))
Next

Use a Transaction with the DataAdapter

Problem

You want to ensure that DataAdapter changes are committed or rolled back as a whole.

Solution

Create a Transaction object, and assign it the DataAdapter commands that are used for deleting, updating, and inserting records.

Discussion

The DataAdapter does not expose a Transaction property. However, the Command objects that the DataAdapter uses do provide this property. The following application shows how you can ensure that all commands in a DataAdapter update operation are grouped into a single transaction:

Dim Con As New SqlConnection(ConnectionString)
Dim CmdSelect As New SqlCommand("SELECT * FROM Shippers", Con)
Dim Ds As New DataSet()
Dim Adapter As New SqlDataAdapter(CmdSelect)
 
' (Define the custom update, insert, and delete commands
' for the DataAdapter here.)
 
' Fill the DataSet.
Try
 Con.Open()
 Adapter.Fill(Ds, "Shippers")
Catch Err As Exception
 Console.WriteLine(Err.ToString())
Finally
 Con.Close()
End Try
 
' (Make DataSet changes here.)
 
Dim Tran As SqlTransaction
Try
 Con.Open()
 
 ' Create a new transaction.
 Tran = Con.BeginTransaction()
 
 ' Enlist the DataAdapter commands in the transaction.
 Adapter.UpdateCommand.Transaction = Tran
 Adapter.InsertCommand.Transaction = Tran
 Adapter.DeleteCommand.Transaction = Tran
 
 ' Apply the update here.
 Adapter.Update(Ds, "Shippers")
 
 ' Commit the transaction.
 Tran.Commit()
 
Catch Err As Exception
 Console.WriteLine(Err.ToString())
 
 ' Roll back the transaction.
 Tran.Rollback()
 
Finally
 Con.Close()
End Try

This approach is a little more difficult if you're using the CommandBuilder object to automatically generate updating logic. The problem is that the CommandBuilder doesn't actually generate the updating logic when it's first created. In fact, the CommandBuilder will not actually build the updating logic until you call the DataAdapter.Update method. This poses a problem because the CommandBuilder retrieves metadata information from the database using the DataAdapter.SelectCommand. This command is not part of the transaction, so invoking it once the update has started and the transaction is underway will cause an exception.

To circumvent this problem, you can force the CommandBuilder to generate the logic early by calling a method such as GetUpdateCommand before you start the transaction. You can then enlist the insert, update, and delete commands. A complete Console application that demonstrates this approach is shown here:

Public Module DataAdapterTransaction
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Public Sub Main()
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdSelect As New SqlCommand("SELECT * FROM Shippers", Con)
 Dim Ds As New DataSet()
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 Dim Builder As New SqlCommandBuilder(Adapter)
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Adapter.Fill(Ds, "Shippers")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' (Make DataSet changes here.)
 
 Dim Tran As SqlTransaction
 Try
 Con.Open()
 
 ' Call the GetUpdateCommand() method once so it
 ' retrieves the schema information it needs.
 Builder.GetUpdateCommand()
 
 ' Create a new transaction.
 Tran = Con.BeginTransaction()
 
 ' You can now enlist the commands in the transaction.
 Builder.GetUpdateCommand.Transaction = Tran
 Builder.GetInsertCommand.Transaction = Tran
 Builder.GetDeleteCommand.Transaction = Tran
 
 ' Apply the update here.
 Adapter.Update(Ds, "Shippers")
 
 ' Commit the transaction.
 Tran.Commit()
 Console.WriteLine("Transaction committed successfully.")
 
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 
 ' Roll back the transaction.
 Tran.Rollback()
 
 Finally
 Con.Close()
 End Try
 Console.ReadLine()
 End Sub
 
End Module

Read Large Binary Fields Efficiently with a DataReader

Problem

You want to read a large binary field without loading the entire row into memory at once.

Solution

Use the CommandBehavior.SequentialAccess option with the DataReader to use stream-based access to binary data.

Discussion

By default, the DataReader loads an entire row into memory every time you call the Read method. This approach is dangerously inefficient if your database records include extremely large fields—for example, binary data that might be tens of megabytes in size.

In this case, a better approach is to read the data in smaller blocks. This approach allows you to perform other processing as you read the data. For example, you might want to copy the data to the hard drive 1 KB at a time. To use this approach, you must pass the CommandBehavior.SequentialAccess value to the Command.ExecuteReader method. You can then read a block of bytes from the field using the DataReader.GetBytes method. To determine the total number of bytes in the field, pass a null reference (Nothing) to the GetBytes method.

The following example reads the binary data from a field in the Categories table and writes it to a file. No more than 1 KB of data is in memory at any one time. To use this example without modification, you must import the System.IO namespace along with the required System.Data namespaces.

Public Module StreamRead
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Public Sub Main()
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdSelect As New SqlCommand( _
 "SELECT CategoryID,Picture FROM Categories", Con)
 
 Try
 Con.Open()
 
 ' Open the reader in sequential mode.
 Dim Reader As SqlDataReader
 Reader = CmdSelect.ExecuteReader(CommandBehavior.SequentialAccess)
 
 ' Read all rows.
 Do While Reader.Read()
 
 ' Create a file where the binary data will be written.
 ' The filename is based on the first field (the CategoryID)
 Dim Filename As String = Reader.GetInt32(0).ToString() & _
 ".bin"
 Console.WriteLine("Creating file " & Filename)
 
 Dim fs As New FileStream(Filename, _
 FileMode.OpenOrCreate, FileAccess.Write)
 Dim w As New BinaryWriter(fs)
 
 ' Track the number of bytes read,
 ' and the position in the stream.
 Dim BytesRead As Integer
 Dim Offset As Integer = 0
 
 ' Read data in blocks of 1K.
 Dim Size As Integer = 1024
 Dim Bytes(1024) As Byte
 
 ' Write the data.
 Console.WriteLine("About to write " & _
 Reader.GetBytes(1, 0, Nothing, 0, 0).ToString() & " bytes.")
 Console.WriteLine()
 
 Do
 ' Open the second field (Picture) for sequential access.
 BytesRead = Reader.GetBytes(1, Offset, Bytes, 0, Size)
 
 w.Write(Bytes)
 w.Flush()
 
 Offset += BytesRead
 Loop While (BytesRead <> 0)
 
 ' Close the output file.
 w.Close()
 fs.Close()
 Loop
 Reader.Close()
 
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 Console.ReadLine()
 End Sub
 
End Module

Note

When using sequential access, you must read the fields in the same order that they are returned by the query. For example, if your query returns three columns, you must retrieve the values of the first and second fields before accessing the third field. If you access the third field first, you will not be able to access the first two fields. This is true regardless of whether the fields contain large binary data or simple data types.

Select a Subset of Data from a DataSet

Problem

You want to find specific rows in a DataSet.

Solution

Use the DataTable.Select method or the DataRowCollection.Find method.

Discussion

You can use several approaches to find individual DataRow objects in a DataSet. The most obvious approach is to simply iterate through the collection of DataRow objects until you find one that matches the criteria you are looking for.

Dim MatchedRows() As New ArrayList
 
Dim Row As DataRow
For Each Row In Ds.Tables(0).Rows
 If Row(FieldName) = FieldValue Then
 MatchedRows.Add(Row)
 End If
Next

However, ADO.NET also provides two more-convenient approaches. First, you can use the DataRowCollection.Find method to find a single row based on its unique primary key. For example, if you're using the Employees table, you can find the row with a specific EmployeeID like this:

Dim MatchRow As DataRow
Dim EmployeeID As Integer = 1
 
' Retrieve the row with EmployeeID 1.
MatchRow = Ds.Tables("Employees").Rows.Find(EmployeeID)

For this approach to work, the DataSet must have some basic schema information about the data source, so it can identify the primary key. To add this information automatically, you can use the DataAdapter.FillSchema method before using the DataAdapter.Fill method when filling the DataSet.

A more powerful option is to use the DataTable.Select method, which allows you to retrieve an array of DataRow objects based on an SQL expression (and optionally a combination of DataViewRowState values). When calling Select, you must supply a text string that filters the rows according to specific field criteria. It plays the same role as the WHERE clause in an SQL SELECT statement.

For example, in the following code snippet, the Select method is used to find all Employees that are based in London:

Dim Filter As String = "City = 'London'"
Dim MatchRows() As DataRow = Ds.Tables("Employees").Select(Filter)
 
Console.WriteLine("Listing all the employees in London.")
Dim Row As DataRow
For Each Row In MatchRows
 Console.WriteLine(Row("FirstName").ToString() & _
 " " & Row("LastName").ToString())
Next

The Select method supports a rich subset of SQL, including the operators listed in Table 14-2. In addition, you can use some built-in functions to evaluate square roots, manipulate strings, and convert data types. These functions are described in detail in the MSDN reference, under the class library reference description for the DataColumn.Expression property.

Table 14-2: Connection Operators That Can Be Used with DataTable.Select

Operator	Description
<, >, <=, >=, =, <>	Compares more than one value. If you use number data types, the comparisons will be numeric. If you use string data types, the comparisons will be alphabetic (and you must use apostrophes around all literal values).
AND	Combines multiple clauses. Records must match all criteria (for example, EmployeeID = 10 AND FirstName='Jones').
OR	Combines multiple clauses. Records must match at least one of the specified criteria (for example, EmployeeID = 10 OR FirstName='Jones').
NOT	Add before any expression to perform the reverse. (For example, NOT EmployeeID = 10 finds all employees except those with an EmployeeID of 10.)
BETWEEN	Specifies an inclusive range. (For example, Quantity BETWEEN 1 AND 10 selects rows that have a value in the Quantity column from 1 to 10.)
IS NULL	Tests the column for a null value (for example, BirthDate IS NULL).
LIKE	Performs limited pattern matching with string data types. You use the asterisk character (*) to specify zero or more characters. (So, FirstName LIKE John* will match Johnathan and any other name starting with John, and ProductName LIKE *Toy* will find products with the string Toy anywhere inside the name.)
+, -, *, /	Performs numeric addition, subtraction, multiplication, or division. The plus sign (+) can also be used to concatenate strings.
%	Finds the modulus (the remainder after one number is divided by another).

Create a Custom Record Browser

Problem

You want to synchronize multiple controls on a form.

Solution

Use data binding with the fields in a DataTable.

Discussion

Recipe 11.19 showed how you could use data binding to synchronize multiple controls on a Windows Form to a collection of custom objects. The same technique is possible with the DataSet.

As an example, consider the form shown in Figure 14-1. It includes four data-bound controls: a list box, two labels, and a text box. These controls are synchronized, so when you make a new selection in the list box, the other controls are updated with the data from the row. In addition, you can move from row to row using the Next and Prev buttons. Changes are supported for any editable control. For example, if the user types in a new price in the text box, that value will be applied to the UnitPrice field (assuming that the conversion can be made from a string to the SQL money type).

Figure 14-1: A data-bound form.

The form code is shown here:

Public Class BoundForm
 Inherits System.Windows.Forms.Form
 
 ' (Designer code omitted.)
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 Private Ds As New DataSet()
 
 Private Sub BoundForm_Load(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdSelect As New SqlCommand("SELECT * FROM Products", Con)
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Adapter.Fill(Ds, "Products")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Add the list box data binding.
 lstProductName.DataSource = Ds.Tables(0)
 lstProductName.DisplayMember = "ProductName"
 
 ' Connect three more controls using the DataBindings collection.
 txtUnitPrice.DataBindings.Add("Text", Ds.Tables(0), "UnitPrice")
 lblUnitsInStock.DataBindings.Add("Text", Ds.Tables(0), "UnitsInStock")
 lblUnitsOnOrder.DataBindings.Add("Text", Ds.Tables(0), "UnitsOnOrder")
 End Sub
 
 Private Sub cmdNext_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles cmdNext.Click
 ' Look up the binding context for this data source,
 ' and increment the position by 1.
 Me.BindingContext(Ds.Tables(0)).Position += 1
 End Sub
 
 Private Sub cmdPrev_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles cmdPrev.Click
 ' Look up the binding context for this data source,
 ' and decrement the position by 1.
 Me.BindingContext(Ds.Tables(0)).Position -= 1
 End Sub
 
End Class

Remember, when using editable data-binding, you're simply allowing the user to modify the information in the disconnected DataSet. To apply changes, you'll need to pass the DataSet to the DataAdapter.Update method.

Filter or Sort Data with a DataView

Problem

You want to show a subset of data from a DataTable or sort that data.

Solution

Set a filter or sort rows using the DataView.RowFilter and DataView.Sort properties.

Discussion

When you bind a DataTable to a Windows control, you're actually making use of another type of object that acts as a mediator: the DataView. The DataView exposes the information from the DataTable and allows you to filter rows or apply a sort order.

You can create a DataView for a DataTable and bind the DataView to a control. However, it's more common to bind controls directly to a DataTable. In this case, the DataView referenced by the DataTable.DefaultView property is used automatically. Thus, if you want to configure sorting or filtering, you must modify the properties of the DataTable.DefaultView.

The DataView.Sort property works much like the ORDER BY clause in an SQL statement. If you want to sort according to a single column, set the Sort property to the column name. If you want to sort using multiple columns, set the Sort property to a list of column names, separated by commas. You can add DESC after a column name to perform a descending sort (with the smallest value first), instead of the default ascending sort. String columns are sorted alphabetically without regard to case. Numeric columns are ordered using a numeric sort. Columns that contain binary data can't be sorted.

Here's a code snippet that sorts data in reverse order, with the most recent dates first:

Ds.Tables(0).DefaultDataView.Sort = "OrderDate DESC"

The DataView.RowFilter property works like the WHERE clause in an SQL statement. You can filter rows based on any column expression. The DataView.RowFilter property supports the same syntax as the DataTable.Select method. Table 14-2, earlier in this chapter, lists supported operators.

Here's a code snippet that shows only products in a specific category:

Ds.Tables(0).DefaultDataView.RowFilter = "CategoryID = 10"

The following example (shown in Figure 14-2) shows two DataGrid controls in different binding contexts. Both DataGrid controls draw their data from the same underlying DataTable, but because they use different DataView objects, the sort and filter settings are different.

Figure 14-2: Binding the same data with different DataView objects.

Public Class BoundForm
 Inherits System.Windows.Forms.Form
 
 ' (Designer code omitted.)
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 Private Ds As New DataSet()
 
 Private Sub BoundForm_Load(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdSelect As New SqlCommand("SELECT ProductID, " & _
 "ProductName, UnitsInStock, UnitsOnOrder, UnitPrice " & _
 "FROM Products", Con)
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Adapter.Fill(Ds, "Products")
 Catch Err As Exception
 Console.WriteLine(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Bind the default DataView (which currently has no
 ' filters or sorts applied).
 gridUnfiltered.DataSource = Ds.Tables("Products")
 
 ' Create a new DataView and configure its filter and sorting.
 Dim View As New DataView(Ds.Tables("Products"))
 View.RowFilter = "UnitsInStock = 0 AND UnitsOnOrder = 0"
 View.Sort = "UnitPrice"
 
 ' Bind the new DataView.
 gridFiltered.DataSource = View
 End Sub
 
End Class

Some controls also allow you to bind directly to the entire DataSet. (The DataGrid is one example.) In this case, the DataViewManager plays the role of intermediary. The DataViewManager contains a collection of DataViewSetting objects, one for each table in the DataSet. The DataViewManager creates DataView instances as needed to show the tables in a DataSet, using the settings from the corresponding DataViewSetting object. These settings provide the same information found in the DataView class, including the ever-important Sort and RowFilter properties.

Thus, to configure sorting or filtering when binding to a DataSet, you must modify the corresponding DataViewSetting in the DataViewManager that you're binding. Although you can create a DataViewManager by hand, every DataSet has a default DataViewManager that's referenced by the DataSet.DefaultViewManager property. Here's how your code might look:

' (Fill the DataSet.)
 
' Create a new DataViewManager.
Dim ViewManager As New DataViewManager(Ds)
 
' Customize the DataViewManager with a default filter and sort
' for the Products table.
ViewManager.DataViewSettings(Ds.Tables("Products")).RowFilter = _
 "UnitsInStock = 0 AND UnitsOnOrder = 0"
ViewManager.DataViewSettings(Ds.Tables("Products")).Sort = "UnitPrice"
 
' Bind the new DataViewManager.
gridFiltered.DataSource = ViewManager
 
' (When the Products table is selected, the ViewManager settings
' will be used to create the view.)

Create a Master Details Form

Problem

You need to create a form that allows users to navigate a parent-child relationship.

Solution

React to the CurrencyManager.PositionChanged event for the parent table, and update the DataView.RowFilter used for the child table.

Discussion

You can create a master-details form using data binding and row filtering. In this case, you need to bind two separate tables. The row position will be tracked separately for each table. When a row is selected in the parent table, you can configure the child table to show only the related rows by modifying the corresponding DataView.RowFilter property (as introduced in recipe 14.14). Figure 14-3 shows an example with two DataGrid controls. The top-most DataGrid shows a list of categories. The DataGrid under it shows the products in the currently selected category.

Figure 14-3: A master-details form.

The code for this form is fairly straightforward. The two tables are bound when the form first loads. In addition, the code connects an event handler that reacts to the CurrencyManager.PositionChanged event for the parent table. This event fires whenever the category row changes, whether the change is initiated by the user (for example, when the user chooses a new category in the DataGrid) or accomplished programmatically using the CurrencyManager properties (as shown in recipe 14.13). The event handler modifies the RowFilter property for the child table, which will affect all the controls that are bound to it. In this example, this changes the lists of products in the second DataGrid.

The complete form code is shown here:

Public Class BoundForm
 Inherits System.Windows.Forms.Form
 
 ' (Designer code omitted.)
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Private CategoriesTable As DataTable
 Private ProductsTable As DataTable
 
 Private Sub Form1_Load(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdSelect As New SqlCommand( _
 "SELECT * FROM Categories;SELECT * FROM Products", Con)
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 Dim Ds As New DataSet()
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Adapter.Fill(Ds)
 
 CategoriesTable = Ds.Tables(0)
 ProductsTable = Ds.Tables(1)
 Catch Err As Exception
 MessageBox.Show(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Bind the Categories table.
 gridCategories.DataSource = CategoriesTable
 
 ' Bind the Products table.
 gridProducts.DataSource = ProductsTable
 
 Dim Binding As BindingManagerBase = Me.BindingContext(CategoriesTable)
 AddHandler Binding.PositionChanged, AddressOf Binding_PositionChanged
 
 ' Invoke method once to update child table at startup.
 Binding_PositionChanged(Nothing, Nothing)
 End Sub
 
 Private Sub Binding_PositionChanged(ByVal sender As Object, _
 ByVal e As EventArgs)
 Dim Filter As String
 Dim SelectedRow As DataRow
 
 ' Find the current category row.
 Dim Index As Integer = Me.BindingContext(CategoriesTable).Position
 SelectedRow = CategoriesTable.Rows(Index)
 
 ' Create a filter expression using its CategoryID.
 Filter = "CategoryID='" & SelectedRow("CategoryID").ToString() & "'"
 
 ' Modify the view onto the product table.
 ProductsTable.DefaultView.RowFilter = Filter
 End Sub
 
End Class

Format Data Bound Fields

Problem

You want to apply specific formatting to certain fields in a database (for example, display numbers as currency strings, convert fixed constants to more descriptive text, and so on).

Solution

React to the Parse and Format data binding events, which allow you to "translate" values as they are retrieved from the DataTable and inserted back into the DataTable.

Discussion

In earlier versions of Microsoft Visual Basic, you had little ability to customize raw database values when displaying them through data binding. The data binding provided by Windows Forms improves on this situation with the Parse and Format events. The Format event fires just before a data value is displayed. It allows you to format the information for display. The Parse event happens when a change is committed (usually when the user navigates to another control or another record). It allows you to take a formatted value and convert it into a value that's valid for the appropriate field in the DataTable.

To demonstrate how you use these two events, you can enhance the data-binding example from recipe 14.13 so that unit prices are formatted and parsed as needed. To do so, you'll need to replace this line of code

txtUnitPrice.DataBindings.Add("Text", Ds.Tables(0), "UnitPrice")

with the code segment shown here:

Dim PriceBinding As New Binding("Text", Ds.Tables(0), "UnitPrice")
AddHandler PriceBinding.Format, AddressOf DecimalToCurrencyString
AddHandler PriceBinding.Parse, AddressOf CurrencyStringToDecimal
txtUnitPrice.DataBindings.Add(PriceBinding)

The text box is still bound to the same database field, only now the code can receive the Format and Parse events for this binding. Be aware that data binding events are handled on a control-by-control basis. However, if you need to format several fields in an analogous way (for example, if you need to convert several different numeric values to currency strings), you can use the same event handlers for each set of binding events.

Finally you need to create the event handlers that perform the conversion. The ConvertEventArgs.DesiredType property indicates the type expected by the control (in the Format event handler) or by the DataTable field (in the Parse event handler). For example, the Text property of a text box requires a string data type. You convert the value provided in the ConvertEventArgs.Value property.

Private Sub DecimalToCurrencyString(ByVal sender As Object, _
 ByVal e As ConvertEventArgs)
 If e.DesiredType Is GetType(String) Then
 ' Use the ToString method to format the value as currency.
 e.Value = CType(e.Value, Decimal).ToString("c")
 End If
End Sub
 
Private Sub CurrencyStringToDecimal(ByVal sender As Object, _
 ByVal e As ConvertEventArgs)
 If e.DesiredType Is GetType(Decimal) Then
 ' Convert the string back to a decimal using the Parse method.
 e.Value = Decimal.Parse(e.Value.ToString, _
 Globalization.NumberStyles.Currency, Nothing)
 End If
End Sub

Figure 14-4 shows the formatted results.

Figure 14-4: Formatting currency values.

Bind an Image Field to a PictureBox

Problem

You want to bind a PictureBox to a field that contains binary image data.

Solution

React to the Format data-binding event, and convert the binary data to an Image object.

Discussion

You can use data binding to bind any property from almost any control to a database field, including the Image property from the PictureBox control. However, the Image property requires an Image object—not just a byte array. Therefore, you need to perform some conversion to translate the binary data into an Image object.

You can use data-binding events to execute this conversion logic automatically. All you need to do is react to the Format event (first introduced in recipe 14.16) and create a new Image object using the supplied image field data. In the example shown in Figure 14-5, this technique allows an application to bind to the logo data contained in the pub_info table in the pubs database. (The Northwind databases also include some tables that use image data, but this image data is wrapped with the header information created by the Visual Basic 6 OLE Container control, and is thus more difficult to convert to pure image data.)

Figure 14-5: Binding to binary image data.

You can use the following code to bind the table to the list box and picture control when the form loads:

Private Sub BoundForm_Load(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load
 
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdSelect As New SqlCommand("SELECT * FROM pub_info", Con)
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Adapter.Fill(Ds, "pub_info")
 Catch Err As Exception
 MessageBox.Show(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Add the list box data binding.
 lstID.DataSource = Ds.Tables(0)
 lstID.DisplayMember = "pub_id"
 
 ' Add the picture box data binding.
 Dim LogoBinding As New Binding("Image", Ds.Tables(0), "logo")
 AddHandler LogoBinding.Format, AddressOf BindImage
 picLogo.DataBindings.Add(LogoBinding)
 
End Sub

The BindImage event handler converts the binary field data to an Image object.

Private Sub BindImage(ByVal sender As Object, ByVal e As ConvertEventArgs)
 
 If e.DesiredType Is GetType(Image) Then
 Dim ms As New System.IO.MemoryStream(CType(e.Value, Byte()))
 Dim Logo As Bitmap = Image.FromStream(ms)
 e.Value = Logo
 End If
 
End Sub

Bind a Field to a List Control, and Restrict It to the Values from Another Table

Problem

You want to fill a list control with allowed values from one table and use it to edit a field in another table.

Solution

Fill the list control with allowed values by setting the DataSource, DisplayMember, and ValueMember properties. Then bind the list control's SelectedValue to the field you want to edit using the DataBindings collection.

Discussion

List controls can play two roles in Windows data binding:

• Record navigation.In this case, you set the DataSource, DisplayMember, and ValueMember properties. For example, if you bind the list control in this way to the ProductName column in a Products table, the list will be filled with all the product names in the table. Every time you change the selection in the list box, you navigate to a different product record, and all other bound controls will be updated accordingly.
• Single-value editing.You can bind the SelectedValue or SelectedText property of the list control to any field in a table using the DataBindings collection. In this case, when you change the value in the list control, the corresponding field in the record is modified. For example, if you bind a combo box to the ProductName field in a Products table, every time the user changes the text in the combo box, the product record will be modified.

It's often useful to combine these two approaches, and use a list control to edit a field based on a list of allowed values. This approach is particularly useful when you need to deal with application-specific codes. For example, you might create an Orders table with a Status field. You can then create a StatusValues table that has all the allowed values for the Status field. You can fill the list control with the list of allowed StatusValues by setting the DataSource property, and you can bind the list control to the Status field in the Orders table using the DataBindings collection.

The Northwind sample database doesn't include any state tables. However, the same technique can be demonstrated with any two related tables. The following example shows how you can use this technique to edit the relation between territory and region records.

Figure 14-6 shows a custom record browser for editing territory records. Each territory is linked to a record in the Region table through a foreign RegionID field. In the custom record browser, the list control is pre-filled with the list of allowed region names. The RegionID field is used as the value for list control items, while the RegionDescription field is used for the display text. The user can change the selection in the list to link a territory to a different region.

Figure 14-6: A record browser that allows you to modify a record relationship.

The full form code is shown here:

Public Class BoundForm
 Inherits System.Windows.Forms.Form
 
 ' (Designer code omitted.)
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Private dtRegions, dtTerritories As DataTable
 
 Private Sub Form1_Load(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdRegions As New SqlCommand("SELECT * FROM Region", Con)
 Dim CmdTerritories As New SqlCommand("SELECT * FROM Territories", Con)
 Dim Adapter As New SqlDataAdapter(CmdRegions)
 Dim Ds As New DataSet()
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Adapter.Fill(Ds, "Regions")
 Adapter.SelectCommand = CmdTerritories
 Adapter.Fill(Ds, "Territories")
 Catch Err As Exception
 MessageBox.Show(Err.Message)
 Return
 Finally
 Con.Close()
 End Try
 
 dtRegions = Ds.Tables("Regions")
 dtTerritories = Ds.Tables("Territories")
 
 ' Bind the two text boxes to the territory record information.
 txtTerritoryID.DataBindings.Add("Text", dtTerritories, "TerritoryID")
 txtTerritoryDescription.DataBindings.Add("Text", dtTerritories, _
 "TerritoryDescription")
 
 ' Fill the list box with region information.
 ' It will display the RegionDescription, but link to the RegionID
 ' through the value property.
 lstRegionID.DataSource = dtRegions
 lstRegionID.DisplayMember = "RegionDescription"
 lstRegionID.ValueMember = "RegionID"
 
 ' Bind the list control's SelectedValue property to the
 ' RegionID field in the Territories table.
 lstRegionID.DataBindings.Add("SelectedValue", dtTerritories, _
 "RegionID")
 End Sub
 
 Private Sub cmdNext_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles cmdNext.Click
 Me.BindingContext(dtTerritories).Position += 1
 End Sub
 
 Private Sub cmdPrev_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles cmdPrev.Click
 Me.BindingContext(dtTerritories).Position -= 1
 End Sub
End Class

Create a Calculated Column That Uses a Relationship

Problem

You want to add a calculated column to a parent table that displays information from a child table.

Solution

Create a new DataColumn for the parent table. Set the DataColumn.Expression property to count or sum information from the related rows.

Discussion

You can create a simple calculated column by instantiating a new DataColumn object, setting its Expression property, and adding it the DataTable.Columns collection for the appropriate table. For example, the following code creates a column that multiplies the information from two other columns:

Dim Col As New DataColumn("InventoryValue", GetType(Decimal), _
 "UnitPrice * UnitsInStock")
Ds.Tables(0).Columns.Add(Col)

When writing the expression, you can use all the operators described in Table 14-2. They allow you to build a column that's based on any combination of values in the current row.

You can also create a calculated column that incorporates information from related rows. For example, you might add a column in a Categories table that indicates the number of related product rows. To create a calculated column that uses a relationship, you must first define the relationship with a DataRelation object. You'll also need to use one of the aggregate functions shown in Table 14-3. These functions allow you to calculate a single number from a series of values.

Table 14-3: Connection Supported Aggregate Functions

Function	Description
Avg(fieldname)	Calculates the average of all values in a given numeric field
Sum(fieldname)	Calculates the sum of all values in a given numeric field
Min(fieldname) and Max(fieldname)	Finds the minimum or maximum value in a number field
Count(fieldname)	Returns the number of rows in the result set
Count(DISTINCT fieldname)	Returns the number of unique (and non-null) rows in the result set

The following example creates three calculated columns, all of which use a table relationship. These calculated columns provide the total number of products, the maximum price, and the total number of items in inventory for each category. The results are shown in a bound DataGrid. (See Figure 14-7.)

Figure 14-7: Binding with calculated columns.

Public Class BoundForm
 Inherits System.Windows.Forms.Form
 
 ' (Designer code omitted.)
 
 Private ConnectionString As String = "Data Source=localhost;" & _
 "Integrated Security=SSPI;Initial Catalog=Northwind"
 
 Private Sub BoundForm_Load(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load
 Dim Con As New SqlConnection(ConnectionString)
 Dim CmdSelect As New SqlCommand( _
 "SELECT CategoryID, CategoryName FROM Categories;" & _
 "SELECT * FROM Products", Con)
 Dim Adapter As New SqlDataAdapter(CmdSelect)
 Dim Ds As New DataSet()
 
 ' Fill the DataSet.
 Try
 Con.Open()
 Adapter.Fill(Ds)
 Catch Err As Exception
 MessageBox.Show(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 ' Define a parent-child relationship between categories and products.
 Dim ParentCol As DataColumn = Ds.Tables(0).Columns("CategoryID")
 Dim ChildCol As DataColumn = Ds.Tables(1).Columns("CategoryID")
 Dim Relation As New DataRelation("Cat_Prod", ParentCol, ChildCol)
 Ds.Relations.Add(Relation)
 
 ' Create the calculated columns.
 Dim ColCount As New DataColumn("# Of Products", GetType(Integer), _
 "COUNT(Child(Cat_Prod).CategoryID)")
 Ds.Tables(0).Columns.Add(ColCount)
 
 Dim ColMax As New DataColumn("Max Price", GetType(Decimal), _
 "MAX(Child(Cat_Prod).UnitPrice)")
 Ds.Tables(0).Columns.Add(ColMax)
 
 Dim ColSum As New DataColumn("Total Inventory", GetType(Integer), _
 "SUM(Child(Cat_Prod).UnitsInStock)")
 Ds.Tables(0).Columns.Add(ColSum)
 
 ' Bind the table.
 grid.DataSource = Ds.Tables(0)
 End Sub
 
End Class

Create a Typed DataSet

Problem

You want to access table names and field values using strongly typed property names instead of field-name lookup.

Solution

Create a typed DataSet using Visual Studio .NET or the XSD.exe command-line utility.

Discussion

A strongly typed DataSet is actually a set of classes that inherit from the DataSet, DataTable, and DataRow classes. Although the basic DataSet can be used with any table structure, a strongly typed DataSet is designed for use with a specific set of tables. For example, you might create a strongly typed Northwind DataSet that includes a set of DataTable and DataRow classes customized for use with tables such as Products, Employees, Categories, Shippers, and so on. The strongly typed classes allow you to use named properties to retrieve tables and column values, and these classes include dedicated methods for navigating table relationships.

For example, with a strongly typed DataSet and DataTable based on the Employees table, you can replace code such as this

' Display the ID of the first row using string-lookup.
Console.WriteLine(ds.Tables("Employees").Rows(0)("EmployeeID"));

with this syntax:

' Display the ID of the first row using strongly typed properties.
Console.WriteLine(ds.Employees.Rows(0).EmployeeID);

This code is clearer and can be checked for name and data type errors at compile time. An error in string-based column lookup will generate a runtime error that might not be discovered until much later. The disadvantage of a strongly typed DataSet is that you need to manage and possibly distribute the assembly that contains the strongly typed classes.

There are two basic ways to create a typed DataSet:

• Use Visual Studio .NET to generate it.
• Use the XSD.exe command-line utility.

Both of these approaches require an XSD schema file describing the structure of the database. You can create this file using the DataSet.WriteXmlSchema method. Here's an example of the code you could use to create an XSD file that describes the structure of the Categories and Products tables:

Dim Con As New SqlConnection(ConnectionString)
Dim CmdProd As New SqlCommand("SELECT * FROM Products", Con)
Dim CmdCat As New SqlCommand("SELECT * FROM Categories", Con)
' (Define more SELECT commands here for all the tables you want to use.)
 
Dim Adapter As New SqlDataAdapter(CmdProd)
Dim Ds As New DataSet()
 
' Fill the DataSet.
Try
 Con.Open()
 Adapter.FillSchema(Ds, SchemaType.Mapped, "Products")
 
 ' Modify the command and re-execute it.
 Adapter.SelectCommand = cmdCat
 Adapter.FillSchema(Ds, SchemaType.Mapped, "Categories")
 
 ' (Repeat this process for all additional commands.)
Catch Err As Exception
 Console.WriteLine(Err.ToString())
Finally
 Con.Close()
End Try
 
' Save the XSD schema file.
Ds.WriteXmlSchema("Northwind.xsd")

You can now create a strongly typed DataSet in Visual Studio .NET. Open a project, right-click on the project in Solution Explorer, and choose Add Existing Item from the Add menu. Then browse to the XSD file you created.

Visual Studio .NET provides two display modes to examine and configure the XSD file: as XML or as a DataSet. The XML view allows you to edit the text of the XSD file with the benefit of a few IDE niceties such as automatic statement completion. The DataSet view provides a tabular diagram where you can easily modify data types and add relations. Figure 14-8 shows a portion of the DataSet view, with the Categories table definition. You'll notice that the CategoryName and Description fields have been created as special types with a character length restriction.

Figure 14-8: Configuring a DataSet schema.

You can use the Visual Studio .NET designer to modify the type restrictions that will be applied to various fields. However, you shouldn't edit the column names because doing so will create a typed DataSet that references nonexistent fields.

You can also define relationships by right-clicking on the parent field and choosing New Relation from the Add menu. Figure 14-9 shows the custom Visual Studio .NET window that allows you to configure the relation and add the implied constraints. In this example, a relation is being defined between the Categories and Products tables.

Figure 14-9: Adding a relationship.

Finally, to generate the DataSet, right-click on the designer window and select the Generate Dataset check box. (You can also select Preview Dataset to see the names, data types, and structures that will be used for the various data objects.) To actually see the strongly typed DataSet file, you'll need to select Show All Files from the Project menu. Then you'll find a Northwind.vb item under the Northwind.xsd node.

You can also create a typed DataSet from a schema file using the XSD.exe command-line utility that's included with the .NET Framework. To do so, use the following command at the command-line prompt:

xsd Northwind.xsd /d /l:VB /n:Northwind

The /d switch specifies that you want source code for a DataSet to be created, and the /l switch specifies that the utility should use the Visual Basic language. The / n parameter specifies the namespace for the generated types. (The default is Schemas.) The resulting file will have the name Northwind.vb. You can add this file to an existing project or compile it to an assembly using the vbc.exe command-line compiler. MSDN describes all the parameters supported by the XSD.exe utility.

Read an Excel File with ADO NET

Problem

You want to retrieve or insert data in a Microsoft Excel document using ADO.NET.

Solution

Use the ODBC provider in conjunction with the Microsoft Excel ODBC Driver.

Discussion

There's no managed provider or OLE DB provider for Excel. However, you can use the Microsoft Excel ODBC driver, which is installed by default with Excel, in conjunction with the ODBC .NET provider. The ODBC .NET provider is included with the .NET Framework 1.1 (and Visual Studio .NET 2003). If you are using .NET Framework 1.0, you can download the ODBC .NET provider as an add-on from the MSDN site at http://msdn.microsoft.com/library/default.asp?url=/downloads/list/netdevframework.asp.

In your connection string, you'll need to specify the driver you are using and the filename of the Excel file. Here's an example that points to a file named test.xls in the application's startup directory:

Private ConnectionString As String = _
 "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;" & _
 "Dbq=" & Application.StartupPath & "	est.xls;"

After connecting, you can perform two types of operations: SELECT or INSERT commands. Instead of using tables, you select or insert using sheet names. Sheet names must end with a dollar sign ($) and be grouped in square brackets, or they'll generate a syntax error. You can also use page names or region names in your queries. Formatting is ignored, and the first row is automatically used for column names.

The following code example extracts and displays all the rows in Sheet1. The original Excel file is shown in Figure 14-10. The data is shown in a form in Figure 14-11.

Figure 14-10: The Excel file.

Figure 14-11: The Excel data in a .NET application.

Here's the code that queries the Excel file and binds the retrieved DataSet to the DataGrid:

Private Sub ExcelView_Load(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles MyBase.Load
 
 Dim Con As New OdbcConnection(ConnectionString)
 Dim CmdSelect As New OdbcCommand("SELECT * FROM [Sheet1$]", Con)
 Dim Adapter As New OdbcDataAdapter(CmdSelect)
 Dim Ds As New DataSet
 
 Try
 Con.Open()
 Adapter.Fill(Ds, "Sheet1")
 Catch Err As Exception
 MessageBox.Show(Err.ToString())
 Finally
 Con.Close()
 End Try
 
 grid.DataSource = Ds.Tables("Sheet1")
 
End Sub

Note

An alternate choice is to use Automation to "drive" Excel through the COM interfaces it exposes. This approach requires you to use COM Interop and the proprietary Excel objects, and it only works if you have Excel installed on the same computer. However, it exposes a richer set of functionality for interacting with spreadsheet data. Recipe 19.5 introduces this approach.