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.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax



ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net