Recipe 10.12 Getting SQL Server Column Metadata Without Returning Data

Recipe 10.12 Getting SQL Server Column Metadata Without Returning Data

Problem

You need to retrieve the column metadata from a SQL Server command or stored procedure without returning any data.

Solution

Use the SET FMTONLY ON statement.

The sample code creates and executes a query statement to retrieve only column metadata from the Orders table in the Northwind sample database. A new DataTable is created from this information.

The C# code is shown in Example 10-12.

Example 10-12. File: ColumnSchemaSPForm.cs
 // Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

//  . . . 

// Create the SQL statement to retrieve only the column schema.
String cmdText = "SET FMTONLY ON;" +
    "SELECT * FROM Orders;" +
    "SET FMTONLY OFF;";

// Use a DataAdapter to fill the DataTable.
SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommand cmd = new SqlCommand(cmdText, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable( );
da.Fill(dt);

// Bind the default view of the table to the grid.
dataGrid.DataSource = dt.DefaultView; 

Discussion

Recipe 10.9 discusses the SQL SET statement.

When SET FMTONLY is ON , no rows are processed or sent to a client when a SQL statement or stored procedure is executed; only metadata is returned to the client. The DataTable created is identical to one that would have been created if the SQL command used a WHERE clause that returned an empty result set.

For more information about the SET FMTONLY statement, see the topic "SET" in Microsoft SQL Server Books Online.

Recipe 10.13 Listing Installed OLE DB Providers

Problem

You need a list of the OLE DB providers installed on the machine running your code.

Solution

Use a SQL Server extended stored procedure or search the registry.

In the first case, the sample code executes the extended stored procedure xp_enum_oledb_providers . The result set containing the installed OLE DB providers is displayed.

In the second case, the sample code uses the Microsoft.Win32.Registry class to examine the registry, identify OLE DB provider subkeys, and retrieve and display the OLE DB provider names from these subkeys.

The C# code is shown in Example 10-13.

Example 10-13. File: OleDbProvidersForm.cs
 // Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using Microsoft.Win32;
using System.Data;
using System.Data.SqlClient;

//  . . .  // SQL Server extended stored procedure  StringBuilder result =
    new StringBuilder("Using SQL Server xp_enum_oledb_providers." +
    Environment.NewLine);
int count = 0;

SqlConnection conn = new SqlConnection(
    ConfigurationSettings.AppSettings["Sql_Master_ConnectString"]);

// Create a command to execute the extended stored procedure to
// retrieve OLE DB providers.
SqlCommand cmd = new SqlCommand("xp_enum_oledb_providers", conn);
cmd.CommandType = CommandType.StoredProcedure;

// Create the DataReader.
conn.Open( );
SqlDataReader rdr = cmd.ExecuteReader( );
// Iterate through the OLE DB providers in the DataReader.
while(rdr.Read( ))
{
    result.Append(++count + ": " + rdr["Provider Description"].ToString( )
        + Environment.NewLine);
}
conn.Close( );

resultTextBox.Text = result.ToString( );  // Registry Scan  StringBuilder result = new StringBuilder("Using Registry scan." +
    Environment.NewLine);
int count = 0;

// Get the HKEY_CLASSES_ROOT/CLSID key.
RegistryKey keyCLSID = Registry.ClassesRoot.OpenSubKey("CLSID", false);
// Iterate through the collection of subkeys.
String[] keys = keyCLSID.GetSubKeyNames( );
for(int i = 0; i < keys.Length; i++)
{
    // Look for the OLE DB Provider subkey and retrieve the value if found.
    RegistryKey key = keyCLSID.OpenSubKey(keys[i], false);
    RegistryKey subKey = key.OpenSubKey("OLE DB Provider", false);
    if(subKey != null)
    {
        result.Append(++count + ": " +
            subKey.GetValue(subKey.GetValueNames( )[0]) +
            Environment.NewLine);
    }
}

resultTextBox.Text = result.ToString( ); 

Discussion

The solution shows two ways to get a list of OLE DB providers installed on a computer.

The first technique uses an extended stored procedure xp_enum_oledb_providers available in SQL Server 7.0, or later. Executing the stored procedure against the master database returns a result set of all OLE DB providers installed on the SQL Server. The result set contains the information described in Table 10-8.

Table 10-8. xp_enum_oledb_providers result set

Column Name

Description

Provider Name

Default value of the class ID (CLSID) key

Parse Name

Class ID (CLSID)

Provider Description

Name of the OLE DB provider

The SQL Server extended stored procedure xp_enum_oledb_providers does not list all installed OLE DB providers. Providers such as MSDataShape are excluded because they do not work as linked servers. Other providers, such as Microsoft Jet 3.51 OLE DB, are excluded because a later version of the provider is installed, for example Microsoft Jet 4.0 OLE DB.

The second technique uses a registry scan and is necessary if SQL Server 7.0, or later, is not installed on the computer, although it can be used with later versions as well.

The .NET Framework classes that manipulate the registry are found in the Microsoft.Win32 namespace. The class IDs that represent OLE DB providers can be identified by the presence of a subkey OLE DB Provider in a class ID. So, to enumerate the OLE DB providers on a computer, iterate over all of the subkeys of the HKEY_CLASSES_ROOT\CLSID key and check for the presence of the OLE DB Provider subkey. The provider name returned by the SQL Server extended stored procedure is the default value for the ProgID subkey while the OLE DB provider name is the default value for the OLE DB Provider subkey.