我在T-SQL中的一些存储过程，每个存储过程都有一个固定的模式的结果集。

我需要的结果集的每一道工序映射到一个POCO对象，并需要列名和类型在结果集中的每一列。是否有访问信息的快捷方式？

我已经发现迄今从.NET访问每个存储过程，并写上的IDataReader / IDataRecord我自己的扩展方法用于转储信息（列名和类型）的。最好的办法

例如，一个存储过程执行以下查询：

  SELECT标识，IntField，NullableIntField，VarcharField，从的DateField SomeTable
 

需要我有映射信息：

  ID  - 的Guid
IntField  -  System.Int32的
NullableIntField  - 可空＆LT; System.Int32的＆GT;
VarcharField  - 字符串
的DateField  - 日期时间
 

解决方案

我想你应该可以使用 SqlDataReader.GetSchemaTable 方法来访问架构。

更多信息可以在这里找到。

http://support.microsoft.com/kb/310107

从上面的源代码示例

 的SqlConnection CN =新的SqlConnection（）;
的SqlCommand CMD =新的SqlCommand（）;
数据表schemaTable;
SqlDataReader的myReader;

//打开到SQL Server Northwind数据库的连接。
cn.ConnectionString =数据源=服务器;用户ID =登录;
                       密码=密码;初始目录= DB;
cn.Open（）;

//从雇员表记录到一个DataReader。
cmd.Connection = CN;
cmd.CommandText =SELECT标识，IntField，NullableIntField，VarcharField，从的DateField SomeTable;

myReader = cmd.ExecuteReader（CommandBehavior.KeyInfo）;

//检索列架构成一个DataTable。
schemaTable = myReader.GetSchemaTable（）;

//对于表中的每个字段...
的foreach（DataRow的MyField的在schemaTable.Rows）{
    //为现场的每个属性...
    的foreach（myProperty的DataColumn的在schemaTable.Columns）{
    //显示的字段名称和值。
    Console.WriteLine（myProperty.ColumnName +=+ MyField的[myProperty的]的ToString（））;
    }
    Console.WriteLine（）;

    //暂停。
    到Console.ReadLine（）;
}

//总是关闭DataReader和连接。
myReader.Close（）;
cn.Close（）;
 

I have a couple of stored procedures in T-SQL where each stored procedure has a fixed schema for the result set.

I need to map the result sets for each procedure to a POCO object and need the column name and type for each column in the result set. Is there a quick way of accessing the information?

The best way I have found so far is accessing each stored procedure from .NET and writing my own extension method on a IDataReader/IDataRecord for dumping the information (column names and types) out.

Example, a stored procedure executing the following query:

SELECT Id, IntField, NullableIntField, VarcharField, DateField FROM SomeTable

would require me to have the mapping information:

Id - Guid
IntField - System.Int32
NullableIntField - Nullable<System.Int32>
VarcharField - String
DateField - DateTime

I think you should be able to use SqlDataReader.GetSchemaTable method to access the schema.

More information can be found here.

http://support.microsoft.com/kb/310107

Example from above source

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=DB";
cn.Open();

//Retrieve records from the Employees table into a DataReader.
cmd.Connection = cn;
cmd.CommandText = "SELECT Id, IntField, NullableIntField, VarcharField, DateField FROM SomeTable";

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();