To look at the code that your project runs when you click the Load button, close the form and return to the Visual Studio .NET development environment. Double-click the Load button, and you’ll see that the code in the button’s Click event calls the LoadDataSet procedure. Scroll down to the definition of this procedure and you’ll see that it calls another procedure, FillDataSet. If you examine the code in this procedure, you’ll find that it calls the Fill method on two OleDbDataAdapter objects, as shown in Figure 2-16.

Calling the Fill method of the OleDbDataAdapter object executes the query stored in the SelectCommand property in the DataAdapter and stores the results in the DataSet or DataTable objects specified. The Data Form Wizard created these DataAdapter objects to fetch the contents of each table that you chose to display. The SelectCommand for each DataAdapter contains a query in the following format:

SELECT Field1, Field2, ... , FieldN FROM MyTable

Most of the buttons on the form are self-explanatory—for example, clicking the Add button adds a new customer row and clicking the Delete button deletes the current customer row. The buttons with the arrows are navigation controls that take you to the first, last, previous, and next records.

Rather than discuss these methods in the order in which they appear in the code, I’d like to focus first on the Update method—the most critical part of the process.

In Article 1, you learned that the DataAdapter object acts as a bridge between the DataSet object and the database. The DataAdapter object’s Update method submits changes stored in the DataSet to the database. Each DataAdapter object corresponds to one of the DataTable objects in our DataSet. In order to submit changes stored in both DataTable objects, you have to call the Update method on both DataAdapter objects.

When you call the Update method on a DataAdapter object, you must specify what data you want to submit to the database. The DataAdapter object is flexible and can accept a number of different structures in its Update method. The code generated by the Data Form Wizard uses a DataSet object, but you can also submit a DataTable object or an array of DataRow objects to the Update method.

The DataAdapter object examines the contents of the data structure to determine which rows it can handle. For example, the DataAdapter that the Data Form Wizard built based on the Customers table will look only at the DataTable that corresponds to the Customers table. The DataAdapter object knows which table to examine because of its TableMappings collection, which I described briefly in Article 1.

When the DataAdapter object detects a modified row, it determines the type of change—insert, update, or delete—and submits it to the database based on this type. If the row has been modified, the DataAdapter object executes the DataCommand stored in its UpdateCommand property, using the current contents of the row. Similarly, the DataAdapter object uses its InsertCommand to submit new rows and its DeleteCommand to delete rows.

The code in the form’s UpdateRowSource function calls the GetChanges method on the DataSet. The GetChanges method generates a new DataSet object named objDataSetChanges that contains only modified rows. The GetChanges method accepts an optional parameter that you can use to indicate whether you want all changes or just a specific type of change—inserts, updates, or deletes.

There’s actually no need for the Data Form Wizard to use the GetChanges method to create a new DataSet object that contains only modified rows. If you call a DataAdapter object’s Update method and the DataSet you supply as a parameter contains unmodified rows, those rows are simply ignored. So when would you use GetChanges?

The Data Form Wizard generates two-tier applications. The client application communicates directly with your database. If you build a multi-tiered application that uses Web services or COM+ components that run on a middle-tier server, you’ll want to limit the amount of data that you pass back and forth between machines. The less data you have to pass across the wire, the faster your application will run.

If the client application has a DataSet that contains modified rows to submit to the database in such a multi-tiered application, the client sends data to the middle. There’s no need to send unmodified rows back to the middle tier. Intelligent use of the GetChanges method can dramatically improve the performance of multi-tiered ADO.NET applications.

The Data Form Wizard builds two-tier applications, but the code that it generates is appropriate for multi-tiered applications.

When the DataAdapter object examines a modified row and successfully submits the pending change to the database, it marks the row as no longer containing a pending change. This way, the DataAdapter object doesn’t send the same change to the database over and over again on subsequent calls to its Update method.

Earlier we discussed the DataSet class’s GetChanges method. The code uses the DataSet object returned by the GetChanges method in the call to the DataAdapter object’s Update method.

When the updates succeed, the DataAdapter objects mark the appropriate rows in the objDataSetChanges as having successfully updated the database. But the objDataSetChanges is separate from the main DataSet for the form. Somehow, we need to merge the changes that the DataAdapter objects made to the objDataSetChanges back into our main DataSet.

The DataSet class has a Merge method that you can use to merge data from two DataSet objects. If the rows in the DataSet objects represent different rows, ADO.NET simply places all the rows into the DataSet whose Merge method you called. In this case, the rows in the objDataSetChanges reference the same data in our main DataSet. We want the rows in the objDataSetChanges to overwrite the corresponding rows in the main DataSet. ADO.NET compares the primary key values stored in the rows to determine which rows represent the same row of data. By default, ADO.NET overwrites the row in the DataSet whose Merge method you’ve called. Thus, the changes that the DataAdapter objects make to the dsDelta DataSets are passed along to our main DataSet, and we can handle subsequent updates successfully.

The Microsoft .NET Framework supports constructors, a feature not available in classic Component Object Model (COM) programming. You can think of a constructor as a class method that you call when you initialize an object. The constructor generally accepts parameters that correspond to the most commonly used property or properties on the class. For example, the OleDbConnection class defines a constructor that accepts a value for the ConnectionString property of the OleDbConnection object that it creates.

The following code snippets are equivalent. For each language example, there are two code segments. The first instantiates an OleDbConnection and then initializes it, the second initializes the object as it’s created by passing a parameter to its constructor:

Dim strConn As String
strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As OleDbConnection
cn = New OleDbConnection()
cn.ConnectionString = strConn

is equivalent to

Dim strConn As String
strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As OleDbConnection
cn = New OleDbConnection(strConn)

string strConn;
strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
          "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn;
cn = New OleDbConnection();
cn.ConnectionString = strConn;

is equivalent to

string strConn;
strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
          "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn;
cn = New OleDbConnection(strConn);

Visual Basic .NET and C# also let you initialize variables as you declare them. We can combine this feature with the constructor to simplify the code snippets above to declare, instantiate, and initialize our objects in a single line of code, as shown here:

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;" 
Dim cn As New OleDbConnection(strConn)

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);

If you’re connecting to a SQL Server database, you can specify the native OLE DB provider, the location of your SQL Server, and the database you want to use, as well as a username and password:

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;
    User ID=MyUID;Password=MyPassword;

Starting with SQL Server 2000, you can have multiple instances of SQL Server installed on the same machine. You can specify the instance you want to connect to by using the following syntax in the Data Source attribute:

Provider=SQLOLEDB;Data Source=MyServer\MyInstance;
    Initial Catalog=MyDatabase;User ID=MyUID;Password=MyPassword;

If you want to connect to SQL Server using your network credentials, use the Integrated Security attribute and omit the username and password:

Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;
    Integrated Security=SSPI;

Some old habits are hard to break. When connecting to SQL Server using the previous technology (ODBC), you can use your network credentials by using the Trusted_Connection attribute. The SQL Server OLE DB provider accepts this same attribute as an alias for Integrated Security. I continue to use this slightly older syntax primarily because the value Yes is easier to remember than SSPI:

Provider=SQLOLEDB;Data Source=MyServer;
    Initial Catalog=MyDatabase;Trusted_Connection=Yes;

See the Microsoft Data Access SDK for the full list of options available through this provider.

Developers who want to use ADO.NET with Oracle databases need to do a little more than just install ADO.NET and build a connection string. Both the Microsoft OLE DB Provider for Oracle and the Microsoft ODBC Driver for Oracle communicate with Oracle’s client components rather than directly with the Oracle database. In order to use ADO.NET with Oracle, you have to install the appropriate version of the Oracle client utilities (SQL*Net) and create a database alias. Then you can use a connection string such as this:

Provider=MSDAORA;Data Source=MyDatabaseAlias;
    User ID=MyUID;Password=MyPassword;

If you’re looking to learn more about any of the above database provider options, see the documentation for these OLE DB providers in the Microsoft Data Access SDK.

If you’re connecting to an Access database, you can use the OLE DB provider for Access databases, Microsoft Jet 4.0 OLE DB Provider. To use this provider, you specify the provider name and version and the location of your database in the connection string, as follows:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\To\MyDatabase.MDB;

If you don’t specify the entire path to your database, ADO will look for the database in your application’s working path. You can also use relative paths. For example, if your database is in the Data subdirectory of your application, you can use the following connection string:

Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=Data\MyDatabase.MDB;

A number of other options are available when you connect using the Jet OLE DB provider. See the Microsoft Data Access SDK for the exhaustive list. I’ll show you examples of the two most commonly used options here. One option is connecting to an Access database that uses Jet security:

Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=C:\...\MySecure.MDB;
    Jet OLEDB:System database=C:\...\MySystem.MDW;
    User ID=MyUserName;Password=MyPassword;

Another option is connecting to an Access database that has a database password:

Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=C:\...\MyPasswordProtected.MDB;
    Jet OLEDB:Database Password=MyPassword;

Developers who have used ADO might be familiar with the OLE DB Provider for ODBC Drivers, which is often referred to by its code name, Kagera. Until version 2, this was the only provider included with the Microsoft Data Access Components. Kagera acts as a bridge from OLE DB to the earlier data access technology, ODBC, by translating OLE DB API calls to ODBC API calls. This provider enabled developers to use ADO to talk to ODBC drivers.

Using the OLE DB .NET data provider to talk to this OLE DB provider to then talk to ODBC drivers might sound overly complex. It is. This is why the Microsoft development team developed the ODBC .NET data provider, which I’ll cover in Appendix A.

If you want to communicate with your data source through an ODBC driver, use the ODBC .NET data provider. Attempts to use Kagera with the OLE DB .NET data provider will generate an exception.

If you want to build connection strings in code, you can use the same user interface that Visual Studio 6 and Visual Studio .NET use—the Data Links dialog box. This tabbed dialog box lets you select an OLE DB provider and then enter values for a data source, user name, password, and other provider-specific attributes. You might remember this dialog box from when we created a new Connection using the Data Form Wizard in Article 2.

To use the Data Links dialog box in your Visual Studio .NET application, you must first add a reference to the Data Link’s library. From your project, right-click on your project in Solution Explorer and choose Add Reference. Click on the COM tab of the Add Reference dialog box and add references to Microsoft ActiveX Data Objects 2.7 Library (commonly referred to as ADO) and the Microsoft OLE DB Service Component 1.0 Type Library. See Figure 3-2.

These libraries contain COM components. When you add a reference to them, Visual Studio .NET will ask whether you want a wrapper generated for the libraries. For the purposes of this sample, click Yes. For more information on COM interoperability, see the MSDN documentation.

You can then use the following code to launch the Data Links dialog box and retrieve the connection string that it returns based on the user’s input:

Dim objDataLink As New MSDASC.DataLinks()
Dim cn As New ADODB.Connection()

objDataLink.PromptEdit(cn)
Console.WriteLine(cn.ConnectionString)

MSDASC.DataLinks objDataLink = New MSDASC.DataLinksClass();
ADODB.Connection cn = New ADODB.ConnectionClass();
object objCn = (object) cn;
objDataLink.PromptEdit(ref objCn);
Console.WriteLine(cn.ConnectionString);

If, like me, you have trouble remembering the different connection string attributes, the Data Links dialog box will simplify your work by letting you quickly set options and then examine the resulting connection string. This approach lets me worry about more important things, like what Peter Gammons has to say about my beloved Red Sox in his columns on ESPN’s Web site.

You don’t have to write code to examine the connection strings that the Data Links dialog box builds. You simply create a file with a .udl extension, and it will be associated with the Data Links dialog box. You then double-click on the file and set the appropriate properties on the dialog box’s tabs. The file is a simple text file that you can examine in a text editor such as Notepad. Voila! You have your new connection string.

Rather than hard-code a connection string in your application or build one dynamically, you can reference a Data Link file in your connection string. In this way, you can let the installation program (or the user, if you’re trusting by nature) build the appropriate connection string and store it in the Data Link file.

To use a Data Link file in a connection string, you can use a name-value pair in the string, such as the following:

File Name=MyDataLink.udl;

If you don’t specify a full path to the data link file, the OLE DB .NET data provider will look in the current working directory for your application. You can also use relative paths in your connection string:

File Name=SettingsSubDir\MyDataLink.udl;

Connection pooling is a fairly simple concept. Imagine a multi-tiered application, such as the one shown in Figure 3-3.

Whenever a client application actively communicates with the middle-tier server, that server creates a business object that connects to and queries a database. Each business object maintains its own connection. When the middle tier creates a new business object, the business object creates a new Connection object. When the middle tier releases an existing business object, the business object closes and releases its connection.

Generally, the business object will close its connection in its clean-up code. As I mentioned earlier, database connections are expensive. Rather than close the database connection, what if we stored it in a pool? Then, when a new business object starts up, it will check the pool for an existing connection. If the pool contains an open connection, the business object can use it. Otherwise, the business object can create a new connection. Figure 3-4 shows an example of such an application.

Connection pooling in ADO.NET is that simple. In fact, in some ways it’s even simpler. The .NET data providers included with ADO.NET each implement connection pooling. When you request a new connection, the .NET data provider examines the credentials you’ve supplied (database location, user name, and so forth) and searches the pool for an open connection with matching credentials. If it locates such a connection, it hands you that connection. Otherwise, it creates and returns a new connection.

When you close a connection object, the .NET data provider doesn’t really close the actual database connection. It marks the connection object as closed but stores the database connection in a pool. If the database connection is not reused within a specified amount of time (60 seconds by default), the .NET data provider closes the connection.

This is the simplest part. Connection pooling is turned on by default. The following code snippet opens and closes the same Connection object five times. Because connection pooling is turned on by default, the actual connection to the database isn’t actually closed when you call the Close method. Instead, the database connection is sent to the pool where it is later reused.

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
Dim intCounter As Integer
For intCounter = 1 To 5
    cn.Open()
    cn.Close()
Next

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
for (int intCounter = 1; intCounter <= 5; intCounter++)
{
    cn.Open();
    cn.Close();
}

Connection pooling is handled on a separate thread. As a result, the code might create a second connection to your database. When the code reaches the call to the Open method in the second iteration of the For Loop, the connection pooling routine might not have finished storing the initial database connection to the pool. You can add the following line of code between the call to the Connection object's Close method and the end of the For Loop to suspend the current thread and allow other threads to execute. The code will then use only a single connection:

System.Threading.Thread.Sleep(0)

OK, here’s the not-quite-so-simple part. Sometimes you won’t want to use connection pooling. For example, if you’re working with a classic two-tiered application in which the client application communicates directly with the database, you probably won’t want to use connection pooling. Connection pooling is turned on by default, so how do you turn it off?

The OleDbConnection class implements a ReleaseConnectionPool method. You could try to use this method in conjunction with the Collect method on the global garbage collection object to truly close the physical connection to your database. However, there’s a more elegant way. You can add the following attribute to your OLE DB connection string:

OLE DB Services=-4;

When you use this connection string attribute, the OLE DB .NET data provider will mark your connection so that it doesn’t participate in connection pooling. When you call the Close method on your OleDbConnection object, you’ll close the actual connection to your database.

If you’re using the SqlConnection object, you can add the following connection string attribute to tell the .NET data provider that you don’t want to pool the connection:

Pooling=False;

There are many ways to check the number of connections to SQL Server, but some are more elegant than others. I prefer using SQL Profiler or Performance Monitor to watch the number of connections to my databases. You can also use Enterprise Manager or you can check the results of repeated calls to a system stored procedure.

The Command object, which I’ll cover in Article 4, is the object you use to query your data source. In order to execute a query, you must set the Command object’s Connection property to a Connection object. The Connection object provides a CreateCommand method that you can use to simplify the process. This method returns a new Command object that’s already initialized to use your Connection object.

The following code snippets are equivalent:

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim cmd As New OleDbCommand()
cmd.Connection = cn

is equivalent to

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim cmd As OleDbCommand = cn.CreateCommand()

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
OleDbCommand cmd = New OleDbCommand();
cmd.Connection = cn;

is equivalent to

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
OleDbCommand cmd = cn.CreateCommand();

I occasionally use the CreateCommand method in my Visual Basic .NET code when I want to create and use a Command object that I’ll use once. Using CreateCommand along with a With block allows you to create and use a Command object without explicitly using a variable name:

With cn.CreateCommand()
    .CommandText = "CREATE TABLE MyTable ..."
    .ExecuteNonQuery()
    .Dispose()
End With

C# offers somewhat similar functionality through the using statement. One major difference between the two languages is that the C# code implicitly calls the Dispose method on the OleDbCommand object at the end of the block, while the Visual Basic .NET With block does not. Thus, I’ve added the call to the OleDbCommand object’s Dispose method in the Visual Basic .NET code:

using (OleDbCommand cmd = cn.CreateCommand() {
    cmd.CommandText = "CREATE TABLE MyTable ...";
    cmd.ExecuteNonQuery();
}

Whether it’s wise to use such programming constructs is debatable. Some might say that the preceding code snippet is simple and elegant. Others might argue that it lends itself to sloppy code that might be difficult to maintain. Personally, I think it’s pretty slick. In the course of writing this book, I’ve written countless code snippets in Visual Studio .NET against the OLE DB, SQL, and ODBC .NET data providers. If I change the definition of the Connection object from an OleDbConnection to a SqlConnection (along with the connection string, of course), the code to create and execute the Command does not need to be changed. Sometimes there’s something to be said for stupid coding tricks.

You can also use the Connection object to start transactions. The Connection object’s BeginTransaction method returns a new open Transaction object on your connection. We’ll discuss the Transaction object in detail in Article 10.

The following code snippets are equivalent:

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim txn As New OleDbTransaction()
txn.Connection = cn
txn.Begin()

is equivalent to

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim txn As OleDbTransaction = cn.BeginTransaction()

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + 
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
OleDbTransaction txn = New OleDbTransaction();
txn.Connection = cn;
txn.Begin();

is equivalent to

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + 
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
OleDbTransaction txn = cn.BeginTransaction();

Adding connections to Server Explorer is straightforward in most, but not all, cases. To understand why things aren’t always so simple, we’ll need to discuss some low-level data access technologies, both past and present. The next couple of pages might look like condensed alphabet soup as a result. This information is geared toward developers who want to use ODBC drivers or .NET data providers other than those built for OLE DB or SQL Server.

The Data Link dialog box was originally designed as an interface for building connection strings in Visual Studio 6. The central data access technology in version 6 is ADO, which is based on a lower-level data access technology called OLE DB.

The Providers tab of the Data Link dialog box displays a list of OLE DB providers built to help you connect to a particular type of database such as SQL Server, Oracle, or Microsoft Access.

Before Microsoft developed OLE DB, the most prevalent data access technology was ODBC, OLE DB’s predecessor. Developers communicated with their databases through ODBC drivers. In fact, many developers still build applications that rely on ODBC drivers because some databases have ODBC drivers but not an OLE DB provider.

The first OLE DB provider that Microsoft developed acted as a bridge between the two technologies by translating OLE DB calls to ODBC calls. This allows developers to use ADO, which uses OLE DB providers, with ODBC drivers. You’ll see this provider listed on the Providers tab of the Data Link dialog box as Microsoft OLE DB Provider For ODBC Drivers. If you select this provider and then click on the Connection tab, you can select an ODBC data source or enter an ODBC connection string.

So far, we’ve discussed how to use the Data Link dialog box to connect to a data source using OLE DB and ODBC. But what about .NET data providers? As I mentioned earlier, the Data Link dialog box was built for Visual Studio 6. As of the release of Visual Studio .NET, there is no user interface for building connection strings for .NET data providers in general. Thus, you can add Data Connections to Server Explorer only if the desired data source has an OLE DB provider or ODBC driver.

You can enter a password in the Data Link dialog box and click the Test button to verify that you can connect successfully. For security reasons, the Data Link dialog box will not save the password that you entered into the new connection string unless you select the Allow Saving Password check box. If you select this check box, you’ll see the warning shown in Figure 3-8.

If you choose to save the password into the connection string, the password information will be used both at design time by Server Explorer and at run time by the applications you build. Thus, you won’t have to enter the password again when Server Explorer connects to your database. However, this also means that if you use this connection string in your application, the password will be built into the application.

Microsoft doesn’t recommend storing the password in your application in this fashion because it is not secure. Visual Studio .NET builds managed code, which can be decompiled. This means that users can decompile your application and find the connection string. If this possibility makes you uncomfortable, you should omit the password from the connection string, and prompt the user for the password—or use integrated security, which we’ll discuss next.

Who says you need a password to connect to your database? Some database systems, such as SQL Server, allow you to connect using your network identity. This feature is known by a number of different names—network authentication, Windows authentication, Windows NT authentication, or integrated security. It’s a fairly simple concept. Rather than relying on the user to specify a username and password, the database asks the network to identify the user. The database then checks its user list to determine whether that user has permission to connect.

For more information on using SQL Server with integrated security, look under Administering SQL Server\ Managing Security\ Security Levels\ Authentication Modes in SQL Server Books Online.

Many Visual Studio .NET project items—such as Windows forms, component classes, Web forms, and ASP.NET Web services—allow you to interact with controls and components in a visual fashion. If you’re working with a simple Windows form, you can place visual controls such as buttons and text boxes on the form. You can set the properties of these controls in the Properties window. This feature is old hat to developers who’ve used development tools such as Visual Basic or Microsoft Visual InterDev.

But what if you want to add a component that, unlike a control on a form, has no user interface? Previous versions of Visual Basic treated such components as controls, and those components appeared as icons on your forms, but only at design time. Visual Studio .NET takes a slightly different approach. Components that have no user interface appear in a tray below the designer—the component tray. The component tray lets you interact with these components and set their properties at design time in the Properties window.

When you drag-and-drop a data connection from Server Explorer onto a designer, you create a Connection object in the designer’s component tray. The type of Connection will depend on the type of data connection you selected in Server Explorer. Dragging and dropping a SQL Server connection creates a SqlConnection, while dragging and dropping a connection in Server Explorer that uses any other data source creates an OleDbConnection.

Figure 3-9 shows what you’ll see if you drag-and-drop a SQL Server data connection from Server Explorer onto a designer such as a Windows form:

Because we dragged a SQL Server connection onto the designer, we received a SqlConnection. Visual Studio .NET automatically names new components based on the class name. This new SqlConnection is already initialized. Its ConnectionString property is set to the connection string for the corresponding data connection in Server Explorer.

You can also create Connections by dragging and dropping items from the Data tab in the Visual Studio .NET Toolbox. This lets you specify which .NET data provider’s Connection you want to use. Figure 3-10 shows a new OleDbConnection created by dragging and dropping the OleDbConnection from the Toolbox.

Note that none of the properties are set…yet. The main property for a Connection object, regardless of the .NET data provider you’re using, is the ConnectionString property. You can select the ConnectionString property in the Properties window and type a value for the property by hand. Or, you can ask Visual Studio .NET for a little help.

You can select the ConnectionString property in the Properties window and view a drop-down list of available connections from Server Explorer. (Figure 3-9 shows an example.) At the end of the list, you’ll see a <New Connection…> entry. Select this entry, and you’ll launch the Data Link dialog box.

The ConnectionString property controls how the Connection object will attempt to connect to your data source. You can set this property only when your Connection is not connected to your data source. When it’s connected to your data source, the property is read-only.

The ConnectionTimeout property indicates the amount of time, in seconds, that the OLE DB provider will wait on an attempt to connect to your data source before timing out.

This property is read-only because not all OLE DB providers support this feature. For example, the Microsoft OLE DB provider for SQL Server supports this feature, while the Microsoft OLE DB providers for Jet and Oracle databases do not.

So how do you tell the OLE DB provider how you long you want to wait before timing out? By using the connection string attribute Connect Timeout. Here’s an example of a connection string that uses the Microsoft OLE DB Provider for SQL Server and the Connect Timeout attribute:

"Provider=SQLOLEDB;Data Source=(local)\NetSDK;Initial Catalog=Northwind;
    Trusted_Connection=Yes;Connect Timeout=11;"

If you set a value for the Connect Timeout attribute in your connection string and the OLE DB provider you’re using doesn’t support this feature, you’ll throw an exception when you call the Open method on the OleDbConnection.

The terms database and data source are often used interchangeably, as they are in this book. But the Connection object exposes each as a separate property. So how do they differ?

In our discussion of connection strings, you might have noticed that each connection string used the Data Source attribute followed by the location of the data source we wanted to connect to. Similarly, the Connection object’s DataSource property contains the location of the data source referenced in our connection string. If you’re working with a server-based data store, such as SQL Server or Oracle, the DataSource property will return the name of the machine acting as the server. For file-based databases, such as Access, the DataSource property will return the location of the data file.

When we discussed connection strings that use the SQL Server OLE DB provider, you learned how to specify an instance of SQL Server in a connection string as part of the Data Source attribute. If you use a connection string that includes information specifying the desired instance of SQL Server, that information will be returned in the DataSource property, just as it appears in the connection string.

So, what information does the Database property return? This property is designed for data sources that support multiple databases, such as SQL Server. When we examined SQL Server connection strings, we specified the database on the server we wanted to connect to in the Initial Catalog attribute of the connection string.

The SQL Server ODBC driver supports the same functionality in the Database attribute. For the SQL Server OLE DB provider, the two attributes are interchangeable.

The OleDbConnection class exposes the Provider property to let you determine the OLE DB provider specified in the connection string. The OdbcConnection property exposes a similar property, Driver, which returns the name of the ODBC driver specified in the connection string. Because the SqlConnection class supports connecting only to SQL Server databases, there is no need for such a property.

Most database systems introduce new features with each successive version. SQL Server 2000, for example, supports returning the results of a query as XML. For this reason, you can check the ServerVersion property to ensure you don’t make unsupported calls to a server. The SourceVersion property returns a string containing the version of the database to which you’re connected. Developers with a SQL Server background might be familiar with the SELECT @@Version query. The SourceVersion property returns a subset of the information returned by SELECT @@Version—the database’s version number.

Let’s say your application communicates with different SQL Server databases, using the OLE DB, ODBC or SQL .NET data provider, and there are certain queries that you can run only against servers running SQL Server 2000 or later. In such a situation, you can use the following code to determine whether to run your query:

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
If cn.ServerVersion >= "08" Then
    'Run your query here.
End If

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
if (cn.ServerVersion >= "08") {
    //Run your query here.
}

The Connection object’s State property returns the current state of the connection as a member of the ConnectionState enumeration in the System.Data namespace. Table 3-2 contains the constants, values, and descriptions of possible connection states.

The ConnectionState enumeration contains a number of values that aren’t used in the initial release of ADO.NET. Currently, the Connection object’s State property will return either Open or Closed. Future releases might support combinations of these values to indicate that a connection is open but is currently executing a query.

You can use the Connection object’s StateChange event to determine when the value of the State property changes.

If you want to start a transaction on your connection—to lock data or to ensure that you can commit or roll back a series of changes to your data store—call the BeginTransaction method on the Connection object. This method returns a new Transaction object, a class we’ll discuss in depth in Article 10 when we discuss updating your database.

Developers who’ve used the connection objects in ADO, RDO, or DAO might expect methods of the Connection object to commit or roll back a transaction. In the ADO.NET object model, the BeginTransaction method generates a new Transaction object. When you want to commit or roll back a transaction, call Commit or Rollback on the Transaction object.

Because BeginTransaction creates a new transaction, associates it with the connection that created it, and initializes the transaction, using this method of the Connection object can simplify your code slightly. The following code snippets are functionally equivalent:

Dim txn As OleDb.OleDbTransaction = cn.BeginTransaction()

is equivalent to

Dim txn As New OleDb.OleDbTransaction()
txn.Connection = cn
txn.Begin()

OleDbTransaction txn = cn.BeginTransaction();

is equivalent to

OleDbTransaction txn = New OleDbTransaction();
txn.Connection = cn;
txn.Begin();

Earlier in the Article, we talked about SQL Server’s ability to support multiple databases on a single server. If you’re working with SQL Server, you can change the database you’re communicating with by executing a query such as this one:

USE Northwind

ADO.NET also offers a simpler method for changing the database. The Connection object has a ChangeDatabase method that simplifies the process. The following code snippets are equivalent:

Dim cn As New OleDbConnection(strConn)
cn.Open()
...
cn.ChangeDatabase("Northwind")

is equivalent to

Dim cn As New OleDbConnection(strConn)
cn.Open()
...
Dim cmd As OleDbCommand = cn.CreateCommand()
cmd.CommandText = "USE Northwind"
cmd.ExecuteNonQuery()

OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
...
cn.ChangeDatabase("Northwind");

is equivalent to

OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
...
OleDbCommand cmd = cn.CreateCommand();
cmd.CommandText = "USE Northwind";
cmd.ExecuteNonQuery();

To close a Connection, you call the object’s Close method. Remember that if you’re using connection pooling, you’re simply sending the physical connection to your data source to the pool.

Calling the Close method on a Connection object that’s already marked as closed will not generate an exception.

You can also create new Command objects by using the Connection class’s CreateCommand method. This method accepts no arguments and returns a new Command object whose Connection property is set to the Connection object that created it.

The following code snippets are functionally equivalent:

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
Dim cmd As OleDb.OleDbCommand = cn.CreateCommand()

is equivalent to

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
Dim cmd As New OleDb.OleDbCommand()
cmd.Connection = cn

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
OleDbCommand cmd = cn.CreateCommand();

is equivalent to

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
OleDbCommand cmd = New OleDbCommand();
cmd.Connection = cn;

The OleDbConnection lets you retrieve schema information about your database through the GetOleDbSchemaTable method. You supply a value from the OleDbSchemaGuid enumeration to specify the type of schema information you want, such as tables, columns, and procedures.

The GetOleDbSchemaTable method also requires a parameter called Restrictions, which acts as a filter on the schema information returned by the method. For example, rather than retrieving information for all columns in your database, you can retrieve information for just the columns in a particular table. The Restrictions parameter contains an array of values. Each schema type allows a different set of restrictions.

If you want to retrieve information about all of the columns in all of the tables in your database, you should omit the Restrictions parameter, as shown in the following code:

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim tbl As DataTable
tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
DataTable tbl;
tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

However, if you want to retrieve the columns from just a specific table, use the Restrictions parameter and supply the name of the table whose columns you want to examine. The MSDN documentation for the Tables member of the OleDbSchemaGuid enumeration states that the Restrictions array for the member should have the following structure:

{"TABLE_CATALOG", "TABLE_SCHEMA", "TABLE_NAME", "COLUMN_NAME"}

You can use the following code to retrieve just the columns from the Customers table:

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim objRestrictions As Object()
objRestrictions = New Object() {Nothing, Nothing, "Customers", Nothing}
Dim tbl As DataTable
tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objRestrictions)

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
object[] objRestrictions;
objRestrictions = New object[] {null, null, "Customers", null};
DataTable tbl;
tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objRestrictions);

For information on the Restrictions parameter for a particular OleDbSchema­Guid value, see the MSDN documentation for that value.

The GetOleDbSchemaTable method returns a DataTable (a structure we’ll examine in detail in Article 6), which contains the schema information you requested. The structure of the DataTable that the method returns will depend on the type of schema you requested. You can use the following code to Loop through the rows in the schema table of column information that we just retrieved:

...
Dim row as DataRow
tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objRestrictions)
Console.WriteLine("Columns in Customers table:")
For Each row In tbl.Rows
    Console.WriteLine(vbTab & row("COLUMN_NAME").ToString())
Next row

...
tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, objRestrictions);
Console.WriteLine("Columns in Customers table:");
foreach(DataRow row in tbl.Rows)
    Console.WriteLine("\t" + row["COLUMN_NAME"].ToString());

You can build a fairly simple application that uses the GetOleDbSchema­Table method to display schema information about your database (such as tables, views, and stored procedures) similar to how Server Explorer displays that information.

The GetOleDbSchemaTable method relies on functionality in the OLE DB provider that your OleDbConnection is using. Not all OLE DB providers support all schema methods. If you request a schema that your OLE DB provider doesn’t support, you’ll throw a trappable exception.

To open a connection to your data source, call the Connection object’s Open method. The Connection object will attempt to connect to your data source based on the information provided in the object’s ConnectionString property. If the attempt to connect fails, the Connection object will throw an exception.

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
Try
    cn.Open()
Catch ex As Exception
    Console.WriteLine("Attempt to connect failed!" & vbCrLf & ex.Message)
End Try

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
try 
{
    cn.Open();
}
catch (Exception ex) 
{
    Console.WriteLine("Attempt to connect failed!\n" + ex.Message);
}

Calling the Open method on a Connection object that’s already opened will close and then reopen the connection. If connection pooling is enabled, this scenario might generate an additional connection to your data source. When the initial connection is closed, it’s sent to the pool. However, because pooling is handled on another thread, the initial connection might not be available when the Connection object requests a connection to the data source based on the connection string.

The ReleaseObjectPool method can help you manage OLE DB connection pooling within your components. Calling ReleaseObjectPool on your connection, or on the OleDbConnection class itself, releases your reference to the pool.

In all honesty, you’ll rarely need to use this method. With the Visual Studio .NET beta, most developers wanted to use this method to truly close a physical connection to the data store rather than to simply send the physical connection to the pool. In such cases, you’re better off creating your connection so it won’t be pooled in the first place. To do that, include the following snippet in the connection string:

OLE DB Services=-4;

If you use this attribute and value in your connection string, your connection to the data source will be closed rather than pooled when you call the Close method on the OleDbConnection class.

Some database systems, such as SQL Server, support informational messages. SQL Server lets you send messages to the client via the PRINT command. These messages are not returned as errors, nor are they included with the results of a query.

You can use the Connection object’s InfoMessage event to trap for such messages. The following code snippet shows how you can log informational messages.

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
AddHandler cn.InfoMessage, AddressOf cn_InfoMessage
cn.Open()
With cn.CreateCommand()
    .CommandText = "PRINT 'Hello ADO.NET!'"
    .ExecuteNonQuery()
End With

Public Sub cn_InfoMessage(ByVal sender As Object, _
           ByVal e As System.Data.OleDb.OleDbInfoMessageEventArgs)
    Console.WriteLine("InfoMessage event occurred")
    Console.WriteLine(vbTab & "Message received: " & e.Message)
End Sub

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.InfoMessage += New OleDbInfoMessageEventHandler(cn_InfoMessage);
cn.Open();
OleDbCommand cmd = cn.CreateCommand();
cmd.CommandText = "PRINT 'Hello ADO.NET'";
cmd.ExecuteNonQuery();

static void cn_InfoMessage(object sender, OleDbInfoMessageEventArgs e)
{
    Console.WriteLine("InfoMessage event occurred");
    Console.WriteLine("\tMessage received: " + e.Message);
}

Visual Basic .NET code snippets involving events

Visual Basic .NET provides two ways for you to add code to handle the events that an object exposes. The first way, which we’ll use throughout this book, is by using the AddHandler statement. There’s also a way to add code to handle events that involves less typing.

In Figure 3-13 you see the Visual Basic .NET code editor. There’s a variable named cn with module-level scope. The Dim statement includes the WithEvents keyword. When you declare a variable using this keyword, you can use Visual Basic .NET to easily create the procedures to handle the object’s events.

Just above the code are two drop-down list boxes. The list box on the left lists the code module and any object variables with module scope that expose events. You’ll see that the OleDbConnection object has been selected in this list box. When you select an object variable that exposes events in the list box on the left, the list box on the right lists the events that the object exposes.

Once you select one of the available events, Visual Basic .NET creates a procedure with the appropriate signature that you can use to handle the event of that object.

While this is an extremely handy feature for developers, it poses a challenge to authors. Object variables declared with the WithEvents keyword must be at module-level scope, which means I need to denote that anytime I declare variables in code. The code snippets would need to look like this:

'At module level
Dim WithEvents cn As OleDbConnection

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
cn = New OleDbConnection(strConn)
AddHandler cn.StateChange, AddressOf cn_StateChange
cn.Open()
cn.Close()

Public Sub cn_StateChange(ByVal sender As Object, _
                          ByVal e As System.Data.StateChangeEventArgs)
    Console.WriteLine("StateChange event occurred")
    Console.WriteLine(vbTab & "From " & e.OriginalState.ToString)
    Console.WriteLine(vbTab & "To " & e.CurrentState.ToString)
End Sub

Using AddHandler simplifies the code snippets slightly. More important, if you’re working with the electronic version of the book, code snippets that use AddHandler are a little easier to cut and paste.

Figure 3-13

Adding code to handle events in Visual Basic .NET

The Connection object’s StateChange event fires whenever the value of its State property changes. This event can prove handy if you display the current state of your connection in, say, a status bar at the bottom your application’s main form.

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
AddHandler cn.StateChange, AddressOf cn_StateChange
cn.Open()
cn.Close()

Public Sub cn_StateChange(ByVal sender As Object, _
                          ByVal e As System.Data.StateChangeEventArgs)
    Console.WriteLine("StateChange event occurred")
    Console.WriteLine(vbTab & "From " & e.OriginalState.ToString)
    Console.WriteLine(vbTab & "To " & e.CurrentState.ToString)
End Sub

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.StateChange += New StateChangeEventHandler(cn_StateChange);
cn.Open();
cn.Close();

static void cn_StateChange(object sender, StateChangeEventArgs e)
{
    Console.WriteLine("StateChange event occurred");
    Console.WriteLine("\tFrom " & e.OriginalState.ToString());
    Console.WriteLine("\tTo " & e.CurrentState.ToString());
}

The following code snippet shows how to examine the results of a simple query using a DataReader object:

Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
strSQL = "SELECT CustomerID, CompanyName FROM Customers"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
while rdr.Read()
    Console.WriteLine(rdr("CustomerID") & " – " & rdr("CompanyName"))
End While
rdr.Close()

string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + 
          "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
strSQL = "SELECT CustomerID, CompanyName FROM Customers";
OleDbCommand cmd = New OleDbCommand(strSQL, cn);
OleDbDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
    Console.WriteLine(rdr["CustomerID"] + " – " + rdr["CompanyName"]);
rdr.Close();

Note that the code calls the Read method before reading the first row of the result set because the first row is not available immediately after you call ExecuteReader. This represents a change from previous object models such as ADO. The DataReader that the Command object returns does not make the first row of data available until you call the Read method.

The first time you call the Read method, the DataReader moves to the first row in the result set. Subsequent calls to the Read method move to the next row. The method also returns a Boolean value to indicate whether the Data­Reader had another row available. So, if Read returns True, the DataReader moved to the next available row. When the Read method returns False, you’ve reached the end of the results.

The DataReader has a default parameterized property named Item. The preceding code snippet implicitly used the Item property to access the values stored in the CustomerID and CompanyName columns in the result set. This code is inefficient, however. We can improve the performance of the code in two ways.

...
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
Dim intCustomerIDOrdinal As Integer = rdr.GetOrdinal("CustomerID")
Dim intCompanyNameOrdinal As Integer = rdr.GetOrdinal("CompanyName")
while rdr.Read()
    Console.WriteLine(rdr(intCustomerIDOrdinal) & " – " & _
                      rdr(intCompanyNameOrdinal))
End While
rdr.Close()

...
OleDbDataReader rdr = cmd.ExecuteReader();
int intCustomerIDOrdinal = rdr.GetOrdinal("CustomerID");
int intCompanyNameOrdinal = rdr.GetOrdinal("CompanyName");
while (rdr.Read())
    Console.WriteLine(rdr[intCustomerIDOrdinal] + " – " + 
                      rdr[intCompanyNameOrdinal]);
rdr.Close();

...
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
Dim intCustomerIDOrdinal As Integer = rdr.GetOrdinal("CustomerID")
Dim intCompanyNameOrdinal As Integer = rdr.GetOrdinal("CompanyName")
while rdr.Read()
    Console.WriteLine(rdr.GetString(intCustomerIDOrdinal) & " – " & _
                      rdr.GetString(intCompanyNameOrdinal))
End While
rdr.Close()

...
OleDbDataReader rdr = cmd.ExecuteReader();
int intCustomerIDOrdinal = rdr.GetOrdinal("CustomerID");
int intCompanyNameOrdinal = rdr.GetOrdinal("CompanyName");
while (rdr.Read())
    Console.WriteLine(rdr.GetString(intCustomerIDOrdinal) + " – " + 
                      rdr.GetString(intCompanyNameOrdinal));
rdr.Close();

Some databases, such as SQL Server, allow you to execute a batch of queries that return multiple results. Let’s say we want to issue the following query against the sample Northwind database:

SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers;
SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders;
SELECT OrderID, ProductID, Quantity, UnitPrice FROM [Order Details]

In our previous DataReader code snippets, we looped through the results of our query until the Read method returned False. Using that same code with a batch query will Loop through the results of the only first query in the batch.

The DataReader exposes a NextResult method that lets you move to the results of the next row-returning query. The NextResult method is similar to the Read method in that it returns a Boolean value to indicate whether there are more results. However, unlike with the Read method, you should not call this method initially.

When the Read method returns False, you can check to see whether there are additional results to fetch by calling the NextResult method. When the Next­Result method returns False, there are no more result sets. The following code snippet shows how to use the NextResult method to fetch the results of a batch query:

...
cn.Open()
Dim strSQL As String
strSQL = "SELECT CustomerID, CompanyName FROM Customers;" & _
         "SELECT OrderID, CustomerID FROM Orders;" & _
         "SELECT OrderID, ProductID FROM [Order Details]"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
Do
    Do While rdr.Read()
        Console.WriteLine(rdr(0) & " – " & rdr(1))
    Loop
    Console.WriteLine()
Loop while rdr.NextResult()

...
cn.Open();
string strSQL = "SELECT CustomerID, CompanyName FROM Customers;" +
                "SELECT OrderID, CustomerID FROM Orders;" +
                "SELECT OrderID, ProductID FROM [Order Details]";
OleDbCommand cmd = New OleDbCommand(strSQL, cn);
OleDbDataReader rdr = cmd.ExecuteReader();
do
{
    while (rdr.Read())
        Console.WriteLine(rdr[0] + " – " + rdr[1]);
    Console.WriteLine();
} while (rdr.NextResult());

Developers often encountered a problem when they used ADO with SQL Server to retrieve the result set generated by a stored procedure. If you call a SQL Server stored procedure using the SQL Server OLE DB provider and the stored procedure executes an action query prior to the row returning query, your Recordset will be marked as closed instead of containing the results of the row-returning query.

This behavior is actually by design. The closed Recordset corresponds to the action query. More precisely, it corresponds to the informational message “n row(s) affected” that the query returns. You have to call NextRecordset to move to the results of the next query. Or you can add the statement SET NOCOUNT ON in your stored procedure to suppress these messages, which allows ADO to move immediately to the results of the first row-returning query.

This behavior is not restricted to stored procedures. You can create a similar situation by executing batch queries. Let’s take a look at such a batch query with ADO 2.x and Visual Basic “Classic.”

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim strConn As String, strSQL As String
Dim intRecordsAffected As Integer

strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Set cn = New ADODB.Connection
cn.Open strConn

strSQL = "INSERT INTO Customers ...;" & _
         "SELECT CustomerID, CompanyName FROM Customers WHERE ...;" & _
         "UPDATE Customers SET CompanyName = ... WHERE ...;" & _
         "SELECT CustomerID, CompanyName FROM Customers WHERE ..."

Set rs = cn.Execute(strSQL, intRecordsAffected, adCmdText)
Do Until rs Is Nothing
    Debug.Print "rs.State = " & rs.State & vbTab & _
                "intRecordsAffected = " & intRecordsAffected
    Set rs = rs.NextRecordset(intRecordsAffected)
Loop

Initially, the Recordset is closed and the intRecordsAffected variable contains 1. These results correspond to the INSERT query, which does not return rows and modifies one record in the database. Once we call NextRecordset, the Recordset is open and contains the results of the first SELECT query. Because the SELECT query doesn’t modify any records in the database, the intRecords­Affected variable returns -1. The second call to NextRecordset returns a closed Recordset, and intRecordsAffected now contains the number of records affected by the UPDATE query. Calling NextRecordset again returns the results of the second SELECT query and sets intRecordsAffected to -1. The last call to Next­Recordset returns a Recordset set to Nothing, indicating that there are no more results to process.

ADO.NET handles this same batch query differently. The ADO.NET development team saw this scenario as one of the top developer frustrations with ADO. To simplify the process of working with batch queries, the DataReader automatically moves you to the results of the first row-returning query. I think most developers will be pleased with this change in behavior. Unfortunately, it comes with a trade-off.

As a result of this change, the DataReader does not provide a way for you to determine the number of rows modified by each individual action query. The RecordsAffected property on the DataReader acts as a running total. Probably the best way to explain this behavior is by showing an example:

Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
cn.Open()
strSQL = "INSERT INTO Customers ...;" & _
         "SELECT CustomerID, CompanyName FROM Customers WHERE ...;" & _
         "UPDATE Customers SET CompanyName = ... WHERE ...;" & _
         "SELECT CustomerID, CompanyName FROM Customers WHERE ..."
Dim cmd As New OleDbCommand(strSQL, cn)
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
Do
    Console.WriteLine("RecordsAffected = " & rdr.RecordsAffected)
    Do While rdr.Read()
        Console.WriteLine(vbTab & rdr.GetName(0) & " – " & rdr.GetValue(0))
    Loop
    Console.WriteLine()
Loop while rdr.NextResult()

string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
          "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
strSQL = "INSERT INTO Customers ...;" +
         "SELECT CustomerID, CompanyName FROM Customers WHERE ...;" +
         "UPDATE Customers SET CompanyName = ... WHERE ...;" +
         "SELECT CustomerID, CompanyName FROM Customers WHERE ...";
OleDbCommand cmd = New OleDbCommand(strSQL, cn);
OleDbDataReader rdr = cmd.ExecuteReader();
do
{
    Console.WriteLine("RecordsAffected = " + rdr.RecordsAffected);
    while (rdr.Read())
        Console.WriteLine("\t" + rdr.GetName[0].ToString() + 
                          " – " + rdr.GetValue[0].ToString());
    Console.WriteLine();
}
while (rdr.NextResult());

You probably noticed that the ADO.NET code looks similar to the ADO code. The results, however, are slightly different. When we create the Data­Reader by calling the ExecuteReader method on the Command, the DataReader is ready to return the results of the first SELECT query immediately. When we call NextResult, we move to the results of the second SELECT query. The second call to NextResult returns False because there are no more row-returning queries to process, and we leave the Loop.

The other major change in behavior from ADO is the behavior of the RecordsAffected property of the DataReader. Let’s assume that the INSERT query and the UPDATE query each modify one record in the database. The RecordsAffected property will return the sum of the records affected by all action queries preceding the row-returning query that the DataReader is currently fetching.

So, when the ExecuteReader method returns the DataReader, its Records­Affected property will return 1. After we call the NextResult method, the Records­Affected property will return 2.

Keep in mind that non-DML action queries (such as CREATE PROCEDURE and DROP TABLE) return -1 for the number of records they affect because they’re not designed to affect records.

If you need to determine the number of rows affected by individual queries using ADO.NET, split the batch into its individual queries and execute each query separately.

In the ADO.NET object model, it’s vitally important that you close your Data­Reader objects as quickly as possible. As of this writing, a Connection object that has an open DataReader is considered blocked. If you try to open a second DataReader before closing the first one, you’ll receive an exception whose text indicates that the operation “requires an open and available connection.”

Developers who have some experience with ADO might be surprised by this restriction, but those who’ve used RDO might not. Different Microsoft data access technologies have handled this scenario differently.

If you try to open two firehose cursors against a SQL Server database using ADO, everything will just work and you won’t receive an error. This is because the OLE DB specification states that when the current connection is blocked, the OLE DB provider will perform the requested action on a new connection.

RDO developers might recognize the error message “Connection is busy with results from another hstmt.” ODBC does not do any behind-the-scenes work to try to help you out. If you try to use a connection that’s busy, you’ll simply receive an error message.

Which of these approaches (raising an error or performing the desired action on a new connection) is better? Developers, both inside and outside of Microsoft, can’t seem to agree. In fact, each successive Microsoft data access technology has handled the scenario differently than its predecessor: VBSQL raises an error, DAO/Jet creates a new connection, RDO raises an error, ADO creates a new connection, and ADO.NET raises an error. As they say in New England, “If you don’t like the weather, just wait a while.”

The DataReader is built for performance. Regardless of the restriction that an open DataReader blocks a Connection, you should pull the results of your query off the wire as quickly as possible after issuing the query. If you need to move back and forth between the results of separate queries, you should use a DataSet or consider storing the results of your queries in a business object of some sort.

The CommandTimeout property determines how long, in seconds, the Command will wait for the results of your query before timing out. By default, this property is set to 30. If the query does not complete by the time specified in the CommandTimeout property, the Command will throw an exception.

Keep in mind that once the query starts returning results, the query won’t time out. Let’s say you want to use a DataAdapter to fetch the contents of a table into a DataSet. For the sake of argument, let’s imagine that your table is so absurdly large that the process of fetching its contents takes more than 30 seconds, the default value for the Command object’s CommandTimeout property. Because the Command that the DataAdapter uses retrieved the first row in less than the time specified in the CommandTimeout property, the query won’t time out no matter how long it takes to retrieve the contents of the table—a minute, a day, or a year.

ADO.NET can simplify the process of setting the text for your query through the CommandType property. You can set this property to any of the values in the CommandType enumeration (available in System.Data), which are described in Table 4-2.

By default, this property is set to Text. Using this default setting, the Command will use whatever text you’ve specified in the CommandText property to execute your query. In my opinion, you should leave the property set to the default. Here’s why.

If you set the property to TableDirect, the Command will prepend "SELECT * FROM " to the contents of the CommandText property when you execute the query. This means that the Command will fetch all rows and all columns from the table—if the query succeeds.

If you query a table that has a space in its name, such as the sample Northwind database’s Order Details table, the query will fail unless you surround the table name with a delimiter that the database can handle. I try to use square brackets rather than having to jump through hoops to embed double quote characters into my strings. Setting CommandType to TableDirect will not delimit your table name automatically. You still have to do that work yourself.

Of course, you can avoid such problems by not using spaces in the names of your tables, columns, views, and stored procedures. Seriously, how many times have database developers said, “Thank goodness I was able to put a space in that object name”?

The StoredProcedure constant simplifies the process of calling a stored procedure, as shown here:

Dim cmd As New OleDbCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MyStoredProc"

cmd.CommandType = CommandType.CommandText
cmd.CommandText = "{CALL MyStoredProc}"

OleDbCommand cmd = New OleDbCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "MyStoredProc";

cmd.CommandType = CommandType.CommandText;
cmd.CommandText = "{CALL MyStoredProc}";

The code snippet shows the standard syntax for calling stored procedures: {CALL MyStoredProc}. SQL Server also supports the EXEC MyStoredProc syntax. In fact, the SQL Server OLE DB provider will translate calls that use the CALL syntax and actually use the EXEC syntax when communicating directly with the database. For this reason, you might want to use the EXEC syntax to try to get your code to run just a tiny bit faster. I avoid using this syntax because I often have to write code that’s back-end independent. Later in the Article, we’ll look at how to call parameterized stored procedures, which can add a touch of complexity to the syntax.

Like the TableDirect constant, setting CommandType to StoredProcedure doesn’t delimit the stored procedure name when you execute your query. For that reason, I prefer leaving CommandType as Text and using the CALL syntax in the CommandText property.

The Parameters property returns an OleDbParameterCollection, which contains a collection of OleDbParameter objects. We’ll examine the properties and methods of the OleDbParameter object later in the Article.

You use the Command object’s Transaction property to execute your Command within a transaction. If you’ve opened a Transaction object on your Connection and try to execute your Command without associating it with that Transaction via this property, the Command object will generate an exception.

The UpdatedRowSource property is designed to help you refetch data for the row you’re updating using a DataAdapter and Command objects that contain updating logic. Table 4-3 lists the values accepted by UpdatedRowSource. We’ll discuss the use of this property in Article 11.

You can use the Cancel method to cancel the execution of a query. If the Command object whose Cancel method you’ve called is not currently executing a query, the Cancel method does nothing.

The Cancel method also causes the Command object to discard any unread rows on a DataReader object. The following sample code fetches the results of a simple query. The code displays the results, followed by the number of rows retrieved. In the code, there’s a call to the Cancel method that’s commented out. Remove the comment character(s) and re-run the code to demonstrate that the Cancel method discards the results of the query.

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
                        "Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim strSQL As String = "SELECT CustomerID FROM Customers"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim cmd As New OleDbCommand(strSQL, cn)
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
Dim intRowsRetrieved As Integer
'cmd.Cancel()
Do While rdr.Read
    Console.WriteLine(rdr.GetString(0))
    intRowsRetrieved += 1
Loop
Console.WriteLine(intRowsRetrieved & " row(s) retrieved")
rdr.Close()
cn.Close()

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
string strSQL = "SELECT CustomerID FROM Customers";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
OleDbCommand cmd = New OleDbCommand(strSQL, cn);
OleDbDataReader rdr = cmd.ExecuteReader();
int intRowsRetrieved = 0;
//cmd.Cancel();
while (rdr.Read())
{
    Console.WriteLine(rdr.GetString(0));
    intRowsRetrieved++;
}
Console.WriteLine(intRowsRetrieved + " row(s) retrieved");
rdr.Close();
cn.Close();

The ExecuteNonQuery method executes the query without creating a Data­Reader to fetch the rows returned by the query. Use ExecuteNonQuery if you want to issue an action query or don’t want to examine the rows returned by the query. Values for return and output parameters are available upon completion of the call to ExecuteNonQuery.

ExecuteNonQuery returns an integer to indicate the number of rows modified by the query you’ve executed. If you’re using batch queries, see the discussion of batch queries and the return value of ExecuteNonQuery earlier in this Article.

If you want to examine the row(s) returned by a query, use the Command object’s ExecuteReader method to return that data in a new DataReader object. We discussed the basic use of this method earlier in the Article. However, there are some interesting options on the method.

The Command object’s ExecuteReader method is overloaded and can accept a value from the CommandBehavior enumeration. Table 4-5 describes each of these options.

The ExecuteScalar method is similar to ExecuteReader except that it returns the first column of the first row of the result set in a generic Object data type. If the query returns more than one cell of data, this data is discarded.

If your query returns a single cell of data, like the following query does, you can improve the performance of your code by using ExecuteScalar.

SELECT COUNT(*) FROM MyTable

One of the major benefits of stored procedures is that they generally run faster than dynamic queries. This is because the database system can prepare an execution plan for them ahead of time. It’s sort of like the difference between script code and compiled code. Script code is often more flexible because you can generate it at run time, but compiled code runs faster.

Most database systems support the notion of a “prepared” query. You can think of a prepared query as a temporary stored procedure. If you’re going to execute the same query multiple times, you might get better performance by preparing the query.

To prepare a Command, you simply call its Prepare method.

If you’re building multi-tiered applications, prepared queries aren’t likely to improve the performance. In fact, I don’t recommend using prepared queries in such situations. With multi-tiered applications, the code in your server components will likely connect, run a query or two, and then disconnect. Most multi-tiered applications take advantage of connection pooling at the middle-tier level.

Simply put, a connection pool will hold onto a connection for a brief amount of time. If your code requests a connection that matches one in a pool, your code will receive an open connection from the pool rather than opening a new one. This process can greatly improve the performance of your code at the middle tier.

However, if your connections are constantly recycled through connection pooling rather than being truly closed, your database won’t have a chance to discard all of the temporary stored procedures that it created for your prepared queries. Recent versions of SQL Server have changed how these temporary stored procedures are stored in order to better handle this scenario, but you’re better off not preparing your queries if you’re building multi-tiered applications.

Calling the ResetCommandTimeout method resets the Command object’s CommandTimeout property to its default value of 30 seconds. If you find yourself wondering “Why would I need a property to do that?,” you’re not alone.

The Depth property and the GetData method are reserved for queries that return hierarchical data. These features are not supported in the current release of ADO.NET.

The FieldCount property returns an integer to indicate the number of columns of data in the result set.

The IsClosed property returns a Boolean value to indicate whether the Data­Reader object is closed.

The DataReader object’s Item property is similar, in form and function, to the DataRow object’s Item property. You can supply the name of a column as a string or the integer position of a column, and the property will return the value stored in that column in the generic object data type.

If you know the data type of the column, you’ll get better performance by calling the Get<DataType> method (such as GetInteger or GetString) instead.

You can use the RecordsAffected property to determine the number of rows that your action query (or queries) modified. If you want to execute a single action query, use the ExecuteNonQuery method of the Command object instead. The ExecuteNonQuery method returns the number of rows the action query affected.

If you’re executing a batch of queries and you want to determine the number of rows affected, see the section on batch queries earlier in the Article.

The Read method accesses the next row of data. Remember that the first row in the result set will not be available through the DataReader until you call the Read method. The first time you call the Read method, the DataReader will move to the first row in the result set. Subsequent calls to Read will move to the next row of data.

The Read method also returns a Boolean value to indicate whether there are any more results for the query. The sample code we examined earlier continually examines results until the Read method returns False.

The GetValue method is similar to the Item property. Supply an integer, and the GetValue method will return the contents of that column in the generic object type. The GetValue method and the various Get<DataType> methods accept only integers for the column index and do not perform string-based lookups such as the Item property.

The DataReader is designed for speed; referencing an item in a collection by its ordinal value is faster than having the collection locate the item by its name.

The DataReader also offers methods that return specific data types. If you know that a column contains string data, you can call the GetValue method of the DataReader and convert the data to a string or simply call the GetString method, as shown here:

Dim strCompanyName As String
Dim rdr As OleDbDataReader
...
strCompanyName = rdr.GetString(intCompanyNameIndex)
'or
strCompanyName = rdr.GetValue(intCompanyNameIndex).ToString

string strCompanyName;
OleDbDataReader rdr;
...
strCompanyName = rdr.GetString(intCompanyNameIndex);
//or
strCompanyName = rdr.GetValue(intCompanyNameIndex).ToString();

The DataReader has methods to return each of the data types available in the .NET Framework—GetByte, GetChar, GetDateTime, and so on.

The GetValues method lets you store the contents of a row in an array. If you want to retrieve the contents of each column as quickly as possible, using the GetValues method will provide better performance than checking the value of each column separately.

The DataAdapter uses a DataReader to fetch data from your database to store the results in DataTables. To provide the best performance possible, the DataAdapter objects in the .NET data providers included in Visual Studio .NET use the DataReader object’s GetValues method. Here’s a simple example of how to use GetValues:

Dim rdr As OleDbDataReader = cmd.ExecuteReader()
Dim aData(rdr.FieldCount – 1) As Object
while rdr.Read
    rdr.GetValues(aData)
    Console.WriteLine(aData(0).ToString)
End While

OleDbDataReader rdr = cmd.ExecuteReader();
object[] aData = New object[rdr.FieldCount];
while (rdr.Read())
{
    rdr.GetValues(aData);
    Console.WriteLine(aData[0].ToString());
}

If you’re working with batch queries that return multiple result sets, use the NextResult method to move to the next set of results. Like the Read method, NextResult returns a Boolean value to indicate whether there are more results.

The sample code under "Questions That Should Be Asked More Frequently" shows how to use a DataReader to examine the contents of a batch query. It also shows how to use the NextResult method in a Loop.

When you’re using DataReader objects, it’s important that you Loop through the results and close the DataReader as quickly as possible. Your Connection object is blocked from performing any other work while a live firehose cursor is open on the connection. If you try to use a Connection that has an open DataReader on it, you’ll receive an exception that states that the operation “requires an open and available connection.”

Developers who have some experience with ADO might be surprised by this restriction, but those who’ve used RDO might not. Various Microsoft data access technologies have handled this scenario differently.

If you try to open two firehose cursors against a SQL Server database using ADO, everything will work and you won’t receive an error. This is because the OLE DB specification states that when the current connection is blocked, the OLE DB provider will perform the requested action on a new connection.

RDO developers might recognize the error message “Connection is busy with results from another hstmt.” ODBC does not do any behind-the-scenes work to try to help you out. If you try to use a connection that’s busy, you simply receive an error message.

Which of these approaches (raising an error or performing the desired action on a new connection) is better? Developers, both inside and outside of Microsoft, can’t seem to agree. In fact, each successive Microsoft data access technology has handled the scenario differently than its predecessor. VBSQL raises an error, DAO/Jet creates a new connection, RDO raises an error, ADO creates a new connection, and ADO.NET raises an error.

The DataReader has methods that you can use to learn more about the results returned by your query. If you want to determine the name of a particular column, you can call the GetName method. If you already know the column name you want to access but don’t know its ordinal position within the result set, you can pass the column name into the GetOrdinal method to retrieve its ordinal position. The GetDataTypeName method accepts an integer denoting the ordinal position of the column and returns the data type for that column as a string.

The DataReader object’s GetSchemaTable method is similar to the DataAdapter object’s FillSchema method. Each method lets you create a DataTable containing DataColumn objects that correspond to the columns returned by your query. The GetSchemaTable method accepts no parameters and returns a new Data­Table. The DataTable contains a DataColumn for each column returned by your query, but the Rows collection of the DataTable is empty. GetSchemaTable populates the new DataTable with schema information only.

The data that GetSchemaTable returns might be a little difficult to grasp initially. The GetSchemaTable method returns a DataTable with a predefined structure. Each DataRow in the DataTable returned by this method corresponds to a different column in the query results, and the DataColumn objects represent properties or attributes for those columns.

The following code snippet prints the name and database data type for each column the query returns.

Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim cmd As New OleDbCommand(strSQL, cn)
Dim rdr As OleDbDataReader = cmd.ExecuteReader
Dim tbl As DataTable = rdr.GetSchemaTable
Dim row As DataRow
For Each row In tbl.Rows
    Console.WriteLine(row("ColumnName").ToString & " - " & _
                      CType(row("ProviderType"), OleDbType).ToString)
Next row

string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + 
          "Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders";
OleDbConnection cn = New OleDbConnection(strConn);
cn.Open();
OleDbCommand cmd = New OleDbCommand(strSQL, cn);
OleDbDataReader rdr = cmd.ExecuteReader();
DataTable tbl = rdr.GetSchemaTable();
foreach (DataRow row in tbl.Rows)
    Console.WriteLine(row["ColumnName"] + " – " +
                      ((OleDbType) row["ProviderType"]).ToString());

Various .NET data providers use different table schemas in the DataTable returned by GetSchemaTable. For example, the DataTable returned by the SQL Server .NET data provider DataReader object’s GetSchemaTable method includes columns not available through the OLE DB .NET data provider.

The Depth property and the GetData method are reserved for queries that return hierarchical data. These features are not supported in the current release of ADO.NET.

Generally speaking, the ParameterName property of the Parameter is designed solely to let you locate the desired Parameter in a Command object’s Parameters collection. If you’re calling a stored procedure with the OLE DB .NET data provider, for example, you don’t need to have the ParameterName property on your Parameter objects match the names of the parameters in your stored procedure. But setting the ParameterName property on your Parameter objects can make your code easier to read.

    SELECT OrderID, CustomerID, EmployeeID, OrderDate
           FROM Orders WHERE CustomerID = @CustomerID

If you’re calling a stored procedure and you want to use output or return parameters, you should set the Direction property of your Parameter to one of the values listed in Table 4-9.

Because the default value for Direction is Input, you need to explicitly set this property only on Parameter objects that are not input-only.

Most code generation tools will query your database for parameter information, including the direction of the parameters. Even if you’re using a robust code generation tool, such as the ones included in Visual Studio .NET, you might still need to modify the Direction value of your Parameter objects in some cases.

Why, you ask? Most databases support the use of input, output, and input-output parameters on stored procedures, but not all databases have language constructs to let you explicitly specify the direction for your stored procedure parameters. SQL Server, for example, supports the OUTPUT keyword in stored procedure definitions to specify that the parameter can return a value. However, the definition of the parameter in the stored procedure is the same regardless of whether the parameter is input/output or output-only. As a result, code generation tools cannot determine whether the parameter is input/output or output-only. The Visual Studio .NET tools assume that the parameter is input/output. If you want an output-only parameter, you must set the direction explicitly in your code.

Use the Value property to check or set the value of your Parameter. This property contains an Object data type. As a result, you might need to convert the data in order to store it in a data type such as a string or integer.

The SourceColumn and SourceVersion properties control how the Parameter fetches data from a DataRow when you submit pending changes to your database by calling the Update method on the DataAdapter.

I’ll cover this feature in much more depth in Article 10 when I cover updating your database.

The Parameter is the only class in the ADO.NET object model that requires you to use the data types used by your database.

For example, when you retrieve the CustomerID field from the Customers table into a DataSet using a DataAdapter, you don’t need to know whether the field in the database is a fixed length or a variable length, nor do you need to know whether the field in the database can handle Unicode data. The data type for the DataColumn object is simply a string.

The DataColumn object’s DataType controls the data type that ADO.NET will use to store the contents of the column and accepts a .NET type as returned by the GetType or typeof function, depending on your language of choice. This data type has a loose connection to the data type that the database uses to store the data. String-based database data types (such as char and varchar) are mapped to the .NET data type String, noninteger numeric database data types (money, decimal, numeric) are mapped to the .NET data type Decimal, and so forth.

The data type for the Parameter must be more precise. In the earlier code snippet, we used the query

SELECT OrderID, CustomerID, EmployeeID, OrderDate
    FROM Orders WHERE CustomerID = ?

with a parameter whose data type is wchar (the w stands for wide to indicate that the string handles double-byte Unicode characters rather than single-byte ANSI characters) and whose length is 5. If we don’t use the appropriate data type for the parameter, the database might not handle the information stored in the parameter the way we expect.

Each Parameter exposes a DbType property and a data type property that’s specific to the .NET data provider. For example, the OleDbParameter has an OleDbType property and the SqlParameter has a SqlDbType property.

The DbType and OleDbType properties are closely related. Setting the value of one of these properties affects the value of the other. For example, if you set the DbType property of an OleDbParameter to DbType.Int32, you’re implicitly setting the OleDbType to OleDbType.Integer. Similarly, if you set the OleDbType to OleDbType.UnsignedTinyInt, you’re implicitly setting the DbType property to DbType.Byte.

When you define the structure for a table in a database, some data types require that you specify additional information beyond simply the name of the data type. Binary and character-based columns often have a maximum size. If you’re using a Parameter with such data, you must set the Size property to the desired size. Numeric data types often let you specify the scale (number of digits) and precision (number of digits to the right of the decimal point).