获取表的SQL Server架构 [英] getting the SQL Server Schema for a table

问题描述

我尝试获得以下C＃代码以返回存储过程结果以及这些结果的模式。下面是我的代码（简化后）当前的样子……

I have the following C# code that I am trying to get to return the stored procedure results along with the schema of those results. Below is how my code (simplified) currently looks...

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("MyStoredProcedure");
IDataReader drData = db.ExecuteReader(dbCommand);

DataTable tblSchema;
tblSchema  = drData.GetSchemaTable();

GetSchemaTable返回空值。我已经读过我需要将CommandBehavior.KeyInfo传递给ExecuteReader方法，但是由于将dbCommand传递给ExecuteReader，因此我不清楚代码结构的外观。

The GetSchemaTable is returning empty. I have read that I need to pass CommandBehavior.KeyInfo to the ExecuteReader method but I am unclear how this would look in the way I have the code structured since I am passing the dbCommand to the ExecuteReader.

推荐答案

更好的答案如下：
2013 Microsoft支持
本文介绍了多种检索表信息的方法，这是一种方法。

The better answer would be as follows: 2013 Microsoft Support The article show multiple ways to retrieve table information, here is one method.

SqlConnection cn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
DataTable schemaTable; 
SqlDataReader myReader; 

//Open a connection to the SQL Server Northwind database.
cn.ConnectionString = "Data Source=server;User ID=login;
                       Password=password;Initial Catalog=Northwind";
cn.Open();

//Retrieve records from the Employees table into a DataReader.
cmd.Connection = cn;
cmd.CommandText = "SELECT * FROM Employees";
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

//Retrieve column schema into a DataTable.
schemaTable = myReader.GetSchemaTable();

//For each field in the table...
foreach (DataRow myField in schemaTable.Rows){
    //For each property of the field...
    foreach (DataColumn myProperty in schemaTable.Columns) {
    //Display the field name and value.
    Console.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString());
    }
    Console.WriteLine();

    //Pause.
    Console.ReadLine();
}

//Always close the DataReader and connection.
myReader.Close();
cn.Close();

这篇关于获取表的SQL Server架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！