Getting SQL Server Column Metadata Without Returning Data

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:2

Thêm vào BST

Báo xấu

92
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Getting SQL Server Column Metadata Without Returning Data

[ Team LiB ] 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. [ Team LiB ]