The SQL Client .NET Data Provider

ADO.NET Ad Hoc Query Tool

When I'm building data access applications, I often find myself wanting to check the contents of tables in my database. There are ways to examine the contents of your database in Visual Studio .NET, but there isn't a way to execute ad hoc queries against your database using the .NET data provider of your choice. So, I decided to create a tool to do just this.

The ADO.NET Ad Hoc Query Tool, shown in Figure B-1, lets you execute ad hoc queries against the .NET data provider of your choice. It also lets you modify the results of your queries and submit those changes back to your database. You can use the tool to view schema information for the results of your query and to generate the ADO.NET code required to build a DataTable to store the results of your query.

Figure B-1

The ADO.NET Ad Hoc Query Tool

Let's take a quick tour of the tool.

Connecting to Your Database

To connect to your database, choose Connect from the application's main menu. You'll see the dialog box shown in Figure B-2, where you can specify the connection string and .NET data provider you want to use.

Figure B-2

Creating a connection using the Ad Hoc Query Tool

Adding .NET Data Providers

By default, the Ad Hoc Query Tool lists the two .NET data providers included with the Microsoft .NET Framework: the OLE DB .NET Data Provider and the SQL Client .NET Data Provider. However, I've designed the tool to work with other .NET data providers as well.

If you want to use a different .NET data provider that's installed on your machine, select <Add Provider> from the list of available providers in the Connect dialog box. This will open a dialog box that lets you add a .NET data provider, as shown in Figure B-3.

Figure B-3

Adding a .NET data provider in the Ad Hoc Query Tool

There is no generic way in the ADO.NET object model to determine what delimiters to use for table and column names for a particular connection, so the Add .NET Data Provider dialog box lets you specify delimiters for the provider. The Ad Hoc Query Tool uses these settings when it generates updating logic.

The OLE DB and ODBC .NET Data Providers are designed to talk to various back ends. When you use these .NET data providers, the Ad Hoc Query Tool determines the appropriate delimiters when you establish a new connection to your database.

Executing Queries

Once you've connected to your database, the Ad Hoc Query Tool displays a window in which you can enter and execute your queries. Simply enter the text for the query and click the Execute button. The tool executes the queries and displays the results in a grid on the Results tab of the window, as shown in Figure B-4.

You can also view the ADO.NET code required to build the resulting DataTable by selecting the DataTable Code tab.

Figure B-4

Querying your database using the Ad Hoc Query Tool

Viewing Query Schema Information

You can view the schema information returned by the query by clicking on the Schema tab (shown in Figure B-5). The Ad Hoc Query Tool uses the GetSchemaTable method of the DataReader object to generate this schema information. The Extended MetaData option controls whether the Ad Hoc Query Tool will use CommandBehavior.KeyInfo in its call to the Command object's ExecuteReader method. If you select this option, you're asking the database to supply the names of the tables and columns referenced in your query, as well as indicating which columns are considered key columns for the query results.

Many developers have had problems using the CommandBuilder object or the Visual Studio .NET Data Adapter Configuration Wizard to generate updating logic for their queries. These problems often happen because the OLE DB providers and ODBC drivers don't return the schema information that these tools require to generate updating logic—table names, column names, and key column information. You can use the Ad Hoc Query Tool to easily see the schema information that's returned with your queries.

One more column in the schema information is worth mentioning. Most of the column names that you see in Figure B-5 are rather intuitive—ColumnName, ColumnSize, DataType, and so forth. However, what's arguably the most helpful column in the schema table is also the least discoverable: ProviderType. This column contains an integer, which on its own isn't terribly helpful.

Figure B-5

Viewing the schema information for your result set

Looking at the figure, the numbers 130 and 202 won't mean much to you, but the contents of the column are quite meaningful to the .NET data provider. This example uses the OLE DB .NET Data Provider. If you execute the following code, you'll find that, in that data provider, CustomerID is a WChar column and CompanyName is a VarWChar column:

Visual Basic .NET

Dim typCustomerID, typCompanyName As OleDbType typCustomerID = CType(130, OleDbType) typCompanyName = CType(202, OleDbType) Console.WriteLine("CustomerID is " & typCustomerID.ToString()) Console.WriteLine("CompanyName is " & typCompanyName.ToString())

Visual C# .NET

OleDbType typCustomerID, typCompanyName; typCustomerID = (OleDbType) 130; typCompanyName = (OleDbType) 202; Console.WriteLine("CustomerID is " + typCustomerID.ToString()); Console.WriteLine("CompanyName is " + typCompanyName.ToString());

Based on this information, I'm sure you'll agree that the data available in the ProviderType column can be very helpful.

The Ad Hoc Query Tool uses the DataReader object's GetSchemaTable method to retrieve the schema information that you see in Figure B-5. You might have noticed that the column to the right of ProviderType is OleDbType, which displays the corresponding value from the OleDbType enumeration. The OleDbType column is not actually part of the DataTable returned by the GetSchemaTable method. The Ad Hoc Query Tool inserts a column into that schema table using the appropriate enumeration for the .NET data provider that you're using. It then uses the data available in the ProviderType column to supply values to this new column.

Disclaimer: There really isn't a well-defined set of requirements for .NET data providers. The SQL XML .NET Data Provider is a prime example. It doesn't implement all of the same objects or constructors as the OLE DB and SQL Client .NET data providers.

Thankfully, Microsoft's OLE DB, SQL Client, ODBC, and Oracle Client .NET Data Providers all work much the same way. As a result, I've been able to build the Ad Hoc Query Tool and DataAdapter Builder in such a way that the tools work quite well with each of these .NET data providers.

The tools use reflection and make assumptions about class names. For example, if you use the Ad Hoc Query Tool with the ODBC .NET Data Provider, the tool searches through the classes available in the provider to find one whose name ends in CommandBuilder in order to provide support for submitting updates. Similarly, the DataAdapter Builder looks for classes whose names end in DataAdapter. The DataAdapter Builder also looks for specific columns in the DataTable returned by the DataReader object's GetSchemaTable method to determine how to generate its updating logic.

Because there is no well-defined set of requirements for .NET data providers (yet), building tools such as the ones included on the companion CD is an inexact science and I can't guarantee they'll work with other .NET data providers.

Submitting Changes

The Query window has an Updateable check box. When you execute a query, the Ad Hoc Query Tool will create a CommandBuilder to generate updating logic for your query if this check box is selected. If the CommandBuilder successfully generates that updating logic, you'll see a Submit Changes button at the bottom of the window as shown in Figure B-4. Click this button to have the Ad Hoc Query Tool use the CommandBuilder to submit the changes you've made to your database and let you know whether the update attempt succeeded.

Application Settings

The Ad Hoc Query Tool uses a strongly typed DataSet to maintain application settings, including the list of .NET data providers, past connections, and past queries. You can modify these settings through the Settings command on the tool's main menu. When the tool shuts down, it stores this information in an XML file in \Documents and Settings\username\Application Data\Microsoft ADO.NET\Ad Hoc Query Tool\. The tool then loads this information the next time it is launched.