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 ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.