You need to retrieve the column metadata from a SQL Server command or stored procedure without returning any data.
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.
// 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;
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.