Part II: Getting Connected: Using a .NET Data Provider

Using Connection Objects

You can use the properties of the Connection object to specify user credentials and the location of your data source. The Connection object's methods allow you to control when you connect to and disconnect from your data source. You can also use the Connection object as a starting point for creating Command and Transaction objects. Let's look at how you can create and use connections in your application by working with the Connection object in code.

SQL Server for the masses!

The Microsoft .NET Framework SDK includes files that let you install Microsoft Desktop Engine 2000 (MSDE).

Like Access databases, MSDE is a royalty-free, redistributable database package. However, unlike Access databases and the Jet database engine, MSDE is a true client/server database system, just like SQL Server. You can create tables, views, and stored procedures in an MSDE database that will also run on SQL Server databases. Also, like SQL Server, MSDE supports both standard and integrated security. MSDE offers many of the same features of SQL Server because it's built on the same code base as SQL Server. As a result, you can take an application built to work with an MSDE database and port it to SQL Server with minimal effort.

There are some important differences between MSDE and SQL Server to keep in mind. MSDE does not provide many of the same "high end" features that SQL Server does. For example, the MSDE engine is geared to handle work on up to five simultaneous connections. Beyond that, you'll encounter performance degradation. SQL Server is designed to handle more simultaneous users. MSDE can handle databases up to 2 GB in size, whereas SQL Server can handle larger databases. Also, MSDE does not include the developer tools that ship with SQL Server, such as Enterprise Manager, Query Analyzer, and SQL Profiler.

For more information on the differences between SQL Server and MSDE, see the MSDN and SQL Server Web sites.

MSDE 2000 is designed to run on Windows 9x operating systems (Windows 98 or later), as well as all versions of Windows NT 4.0, Windows 2000, and Windows XP.

The connection and query strings used throughout this book are written to communicate with the local instance of MSDE included with the .NET Framework SDK. This way, you can copy code from the digital version of the book and run the code without having to change the connection string in order to make the code connect to your database.

To install MSDE, locate the Microsoft .NET Framework SDK program group on the Start menu and select the sub-item Samples And QuickStart Tutorials. This will call up the SDK's QuickStarts, Tutorials, And Samples page in your browser. If you have not already installed MSDE from this page, you'll see the screen shown in Figure 3-1 in your browser.

Figure 3-1

Installing MSDE from the Microsoft.NET Framework SDK

If you click on Install The .NET Framework Samples Database, you'll install MSDE on your machine. Clicking on Set Up The QuickStarts will install the sample databases (the standard SQL Server pubs and Northwind sample databases, along with the databases that the .NET Frameworks Samples use) as well as set up virtual roots in Internet Information Services (IIS) and the QuickStart Tutorial Web pages.

The following sample code connects to the .NET Framework install of MSDE using the SQL DMO library. You'll need to add a reference to the SQL DMO library in order to run the code. The SQL DMO library is a COM library rather than a .NET library, so remember to switch to the COM tab of the Add References dialog box when trying to locate the library.

The code demonstrates how to execute the contents of a script file, as well as how to examine information about logins and the structure of your databases. For more information on using the SQL DMO object library, see the help file (SQLDMO.chm).

Visual Basic .NET

Dim dmoServer As New SQLDMO.SQLServer() dmoServer.LoginSecure = True dmoServer.Connect("(local)\NetSDK") Dim filSqlScript As IO.StreamReader Dim strPathToFile As String Console.WriteLine("Installing the sample Northwind database") strPathToFile = "C:\VS.NET\FrameworkSDK\Samples\Setup\instnwnd.sql" filSqlScript = IO.File.OpenText(strPathToFile) dmoServer.ExecuteImmediate(filSqlScript.ReadToEnd) filSqlScript.Close() Console.WriteLine("Installing the sample pubs database") strPathToFile = "C:\VS.NET\FrameworkSDK\Samples\Setup\instpubs.sql" filSqlScript = IO.File.OpenText(strPathToFile) dmoServer.ExecuteImmediate(filSqlScript.ReadToEnd) filSqlScript.Close() Dim dmoDatabase As SQLDMO.Database Dim dmoTable As SQLDMO.Table Console.WriteLine("Databases:") For Each dmoDatabase In dmoServer.Databases     If Not dmoDatabase.SystemObject Then         Console.WriteLine(vbTab & dmoDatabase.Name)         For Each dmoTable In dmoDatabase.Tables             If Not dmoTable.SystemObject Then                 Console.WriteLine(vbTab & vbTab & dmoTable.Name)             End If         Next dmoTable         Console.WriteLine()     End If Next dmoDatabase Dim dmoLogin As SQLDMO.Login Console.WriteLine("Logins:") For Each dmoLogin In dmoServer.Logins     Console.WriteLine(vbTab & dmoLogin.Name) Next dmoLogin Console.WriteLine() dmoServer.DisConnect()

Visual C# .NET

SQLDMO.SQLServer dmoServer = new SQLDMO.SQLServer(); dmoServer.LoginSecure = true; dmoServer.Connect("(local)\\NetSDK", null, null); System.IO.StreamReader filSqlScript; string strPathToFile; Console.WriteLine("Installing the sample Northwind database"); strPathToFile = "C:\\VS.NET\\FrameworkSDK\\Samples\\Setup\\instnwnd.sql"; filSqlScript = System.IO.File.OpenText(strPathToFile); dmoServer.ExecuteImmediate(filSqlScript.ReadToEnd(),                            SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default,                            null); filSqlScript.Close(); Console.WriteLine("Installing the sample pubs database"); strPathToFile = "C:\\VS.NET\\FrameworkSDK\\Samples\\Setup\\instpubs.sql"; filSqlScript = System.IO.File.OpenText(strPathToFile); dmoServer.ExecuteImmediate(filSqlScript.ReadToEnd(),                            SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default,                            null); filSqlScript.Close(); Console.WriteLine("Databases:"); foreach (SQLDMO.Database dmoDatabase in dmoServer.Databases)     if (!dmoDatabase.SystemObject)     {         Console.WriteLine("\t" + dmoDatabase.Name);         foreach (SQLDMO.Table dmoTable in dmoDatabase.Tables)             if (!dmoTable.SystemObject)                 Console.WriteLine("\t\t" + dmoTable.Name);         Console.WriteLine();     } Console.WriteLine("Logins:"); foreach (SQLDMO.Login dmoLogin in dmoServer.Logins)     Console.WriteLine("\t" + dmoLogin.Name); Console.WriteLine(); dmoServer.DisConnect();

Creating Connection Objects

There are two ways to create connections using OleDbConnection at runtime. You can simply create a new uninitialized OleDbConnection object, as shown here:

Visual Basic .NET

Dim cn As OleDbConnection cn = New OleDbConnection()

Visual C# .NET

OleDbConnection cn; cn = new OleDbConnection();

Or, you can initialize an OleDbConnection object using the class's constructor.

Constructors

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:

Visual Basic .NET

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)

Visual C# .NET

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:

Visual Basic .NET

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

Visual C# .NET

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

Connection Strings

In the code snippets above, we supplied a connection string for our new OleDbConnection objects. A connection string consists of a series of name-value pairs delimited by semicolons:

strConn = "Setting1=Value1;Setting2=Value2;..."

The settings and values depend on the data source you want to connect to, as well as on the technology you're using to connect to your data source.

The OLE DB .NET data provider is extremely flexible when it comes to connecting to databases, and it provides a variety of ways to build a connection string. Let's take a quick look at building connection strings for the three most commonly used OLE DB providers: the Microsoft OLE DB providers for Microsoft Access, Microsoft SQL Server, and Oracle databases.

OLE DB Provider for SQL Server Databases

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.

OLE DB Provider for Oracle Databases

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.

OLE DB Provider for Access Databases

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;

OLE DB Provider for ODBC Drivers

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.

Using Data Links to Build Connection Strings in Code

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

Figure 3-2

The Add Reference dialog box

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:

Visual Basic .NET

Dim objDataLink As New MSDASC.DataLinks() Dim cn As New ADODB.Connection() objDataLink.PromptEdit(cn) Console.WriteLine(cn.ConnectionString)

Visual C# .NET

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.

Using Data Links to Build Connection Strings Manually

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.

Using Data Link Files in a 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;

Opening and Closing Connections

Once you have a OleDbConnection object with a valid connection string, you should open the connection so you can communicate with your data store. To open the connection, you simply call its Open method:

Visual Basic .NET

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

Visual C# .NET

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

To close a Connection object, you simply call its Close method:

Visual Basic .NET

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

Visual C# .NET

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

Closing the Connection object will not close the actual connection to your data source if you're using connection pooling.

Connection Pooling

Opening and closing database connections is expensive. If you're developing a multi-tiered application, connection pooling will probably improve your application's performance.

What Is Connection Pooling?

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

Figure 3-3

A simple multi-tiered application

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.

Figure 3-4

A multi-tiered application with connection pooling

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.

How Do I Turn On Connection Pooling?

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.

Visual Basic .NET

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

Visual C# .NET

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)

What if I Don't Want to Pool My Connections?

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;

How Do I Know Whether I've Truly Closed My Connection or Have Simply Pooled It?

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.

Destroying Connections

Many of the classes in the ADO.NET object model, such as the Connection class, expose a Dispose method. Calling this method on an object allows the object to free its resources prior to garbage collection. If you don't call the Dispose method explicitly, the object will release its resources when the common language runtime's garbage collection routine instructs the object to release its resources.

Releasing an open Connection by letting the object go out of scope or by setting the object variable to Nothing or null (depending on your language of choice) won't close the connection to your data source until the object is cleaned up by garbage collection. Calling Dispose on a Connection marked as open will implicitly call its Close method.

As a general rule, if an object exposes a Dispose method, you should call it when you want to release the object's resources.

Using Connections to Create Other Objects

You can use Connection objects to create other objects—Command objects and Transaction objects. Creating objects in this fashion might save you a couple lines of code.

Creating Commands

The Command object, which I'll cover in Chapter 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:

Visual Basic .NET

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

Visual C# .NET

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:

Visual Basic .NET

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:

Visual C# .NET

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.

Starting Transactions

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 Chapter 10.

The following code snippets are equivalent:

Visual Basic .NET

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

Visual C# .NET

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();

Retrieving Database Schema Information

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 that the method returns. 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:

Visual Basic .NET

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)

Visual C# .NET

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, you should 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"}

Therefore, you can use the following code to retrieve just the columns from the Customers table:

Visual Basic .NET

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)

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); string strRestrictions; 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 OleDbSchemaGuid value, see the MSDN documentation for that value.

The GetOleDbSchemaTable method returns a DataTable (a structure we'll examine in detail in Chapter 6) that contains the schema information you requested. The structure of the DataTable that the method returns depends on the type of schema you requested. We can use the following code to loop through the rows in the schema table of column information that we just retrieved:

Visual Basic .NET

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

Visual C# .NET

... tbl = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, strRestrictions); 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 GetOleDbSchemaTable method to display schema information about your database (tables, views, stored procedures, and so forth), much like Server Explorer does.

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.