如何使用文本命令检索参数 [英] How to retrieve out parameter using text command

问题描述

我需要找到一种方法来检索以下查询的参数:

I need to find a way to retrieve out parameter for the following query:

query = @"declare @p7 int
          exec some_sproc @SomeArg=@p7
          select @p7";

sqlCommand.CommandText = query;
sqlCommand.CommandType = CommandType.Text;

我对添加SQL参数感到厌倦，但是随后整个代码只是用 exec sp_executesql 包装，并添加了参数.这根本行不通.您知道我如何在C#端获得@ p7的价值吗?

I tired with adding SQL paramater but then the whole code was simply wrapped with exec sp_executesql and the parameter was added. This simply doesn't work. Do you know how I could get value of @p7 on C# side?

我的存储过程使用表值参数，但是默认情况下它被评估为

My stored procedure uses table valued parameters, however by default it is evaluated to

declare @p11 acco.SomeUDT
insert into @p11 values(1,'2017-06-15 00:00:00',64.73)
insert into @p11 values(1,'2017-06-15 00:00:00',1.30)

但是我的意图是获取以下语法:

But my intention is to get following syntax:

declare @p11 acco.SomeUDT
insert into @p11 values(1,'2017-06-15 00:00:00',64.73), (1,'2017-06-15 00:00:00',1.30)

这就是为什么我手动构建SQL代码的原因.因此，我们有:

This is why I build manually SQL code. So we have:

query = @"declare @p7 int

          declare @p11 acco.SomeUDT
          insert into @p11 values(1,'2017-06-15 00:00:00',64.73), (1,'2017-06-15 00:00:00',1.30)

          exec some_sproc @SomeArg=@p7, @SomeUDTArg=@p11
          select @p7";
sqlCommand.CommandText = query;
sqlCommand.CommandType = CommandType.Text;

如果我使用存储过程类型和SQL参数，则不会获得第一个语法.

If I use stored procedure type and SQL parameters than I get first syntax.

一切正常，但我还需要将参数@SomeArg映射出来，对此我有疑问.

All works but I need to map also out parameter @SomeArg and I have problem with this.

推荐答案

只需在查询中不声明@ p7，而是将其作为参数添加到SQL CommandText中，就可以做到这一点

You should be able to do this by simply not declaring @p7 within the query, but instead adding it as a parameter to the SQL CommandText

query = @"declare @p11 acco.SomeUDT
          insert into @p11 
          values(1,'2017-06-15 00:00:00',64.73), (1,'2017-06-15 00:00:00',1.30)
          exec some_sproc @SomeArg=@p7, @SomeUDTArg=@p11;";

sqlCommand.CommandText = query;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add("@p7", SqlDbType.Int).Direction = ParameterDirection.Output;
sqlCommand.ExecuteNonQuery();

Console.WriteLine(sqlCommand.Parameters["@p7"].Value);

此外，如果它是您过程中的输出参数，则您可能应该使用:

Also, If it is an output parameter in your procedure then you probably should be using:

exec some_sproc @SomeArg=@p7 OUT, @SomeUDTArg=@p11; 
                             ^^^

编辑

如果您对Bobby表，转换问题或构造这样的SQL字符串的想法不好的所有原因有任何疑问，可以可以用c#创建表并将其作为参数传递也是:

If you have any concerns regarding Bobby tables, conversion problems or all the reasons why it's a bad idea to construct SQL strings like this, you could create the table in c# and pass it as a parameter too:

var table = new DataTable();
// TODO: Add the correct column names for your TVP here
table.Columns.Add("Column1", typeof(int));
table.Columns.Add("Column2", typeof(DateTime));
table.Columns.Add("Column3", typeof(decimal));

table.Rows.Add(1, new DateTime(2017, 6, 15), 64.73);
table.Rows.Add(1, new DateTime(2017, 6, 15), 1.3);

sqlCommand.CommandText = "some_sproc";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@SomeArg", SqlDbType.Int).Direction = ParameterDirection.Output;

var tvp = sqlCommand.Parameters.Add("@SomeUDTArg", SqlDbType.Structured);
tvp.TypeName = "acco.SomeUDT";
tvp.Value = table;

sqlCommand.ExecuteNonQuery();

Console.WriteLine(sqlCommand.Parameters["@SomeArg"].Value);

这篇关于如何使用文本命令检索参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！