获取存储过程结果的 .NET 架构

发布于 2024-08-30 21:13:27 字数 502 浏览 6 评论 0原文

我在 T-SQL 中有几个存储过程,其中每个存储过程都有一个固定的结果集架构。

我需要将每个过程的结果集映射到 POCO 对象,并且需要结果集中每列的列名称和类型。有没有快速获取信息的方法?

到目前为止,我发现的最好方法是从 .NET 访问每个存储过程,并在 IDataReader/IDataRecord 上编写我自己的扩展方法,以转储信息(列名称和类型)。

例如,执行以下查询的存储过程:

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

将要求我拥有映射信息:

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

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

戒ㄋ 2024-09-06 21:13:27

我认为您应该能够使用 SqlDataReader.GetSchemaTable 方法来访问架构。

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

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

上述来源的示例

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

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

我认为你正在做的可能是最好的方法。实际上,没有一个神奇的存储库可以保存所有这些信息。您可以从系统目录视图中找到有关存储过程参数的信息,但遗憾的是,结果集的形状、字段名称和类型并未存储在 SQL Server 系统视图中的任何位置。

I think what you're doing is probably the best approach. There's no magic repository that holds all that information, really. You can find out about the stored proc parameters from the system catalog views, but the shape and field names and types of the result set aren't stored anywhere in SQL Server system views, unfortunately.

糖粟与秋泊 2024-09-06 21:13:27

另一种选择(可能比 GetSchemaTable() 更好或更差,具体取决于您的需要)。
以下代码为您提供了一个列结构与结果集相同的 DataTable:

DataTable table = new DataTable();
var cmd = new SqlCommand("...");
using (var reader = cmd.Execute(CommandBehaviour.SchemaOnly))
    table.Load(reader);

Another option (might be better or worse than GetSchemaTable() depending on your needs).
The following code gives you a DataTable with column structure identical to your resultset:

DataTable table = new DataTable();
var cmd = new SqlCommand("...");
using (var reader = cmd.Execute(CommandBehaviour.SchemaOnly))
    table.Load(reader);
゛时过境迁 2024-09-06 21:13:27

如果这是您需要做的一次性事情,而不是每次调用该过程时在运行时执行的操作,那么只需修改结果集查询以使用 INTO ThrowArayTable将行转储到新表中/code>:

SELECT
    Col1, col2, col3, ...
    INTO ThrowArayTable     ----<<<add this line to existing result set query
    FROM ...
    WHERE ...

运行应用程序或手动调用过程来生成 ThrowArayTable。现在,您可以使用任何方法(SSMS 或 INFORMATION_SCHEMA.COLUMNS)查找列 aames 和数据类型,

只需记住将过程更改回来(删除 INTO ThrowArayTable 值),以便它实际上返回结果集。

if this is a one time thing you need to do, and not something you'll do at runtime each time the procedure is called, then just modify the result set query to dump the rows into a new table using INTO ThrowArayTable:

SELECT
    Col1, col2, col3, ...
    INTO ThrowArayTable     ----<<<add this line to existing result set query
    FROM ...
    WHERE ...

Run the application or call the procedure manually to generate the ThrowArayTable. You can now look up the column aames and datatypes using any method, SSMS or INFORMATION_SCHEMA.COLUMNS

Just remember to change the procedure back (remove the INTO ThrowArayTable value) so it will actually return the results set.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文