从C#调用存储过程 [英] Calling a stored procedure from C#

问题描述

我有一个奇怪的问题，从c#代码调用存储过程.
这是场景:

我有一个数据库浏览器应用程序，可以使用它在SQL Server数据库上运行各种查询(例如，选择，更新，执行存储过程...)

因此，在我的应用程序中，我有一个编辑窗口，在其中编写使用此代码运行的T-SQL文本:

 IDbCommand命令= connection.CreateCommand();
command.Connection =连接;
command.CommandTimeout = connection.CommandTimeout;
command.CommandType = CommandType.Text;
command.CommandText = textBoxQuery.Text

IDbReader reader = command.ExecuteReader(); 

非常直截了当.

而且我有一个存储过程，其结构如下:

 创建 过程 dbo.test( as   AS 
开始
    打印  @ a 
    打印 ' 测试'

  如果 @ a = 1 开始
     exec 测试 @ a 
  结束
 END  

(显然，这只是一个真正的SP的原型，可以执行某些操作)
一个简单的测试过程应该会失败，并在@ a = 1的情况下出现最大嵌套异常，并仅打印具有任何其他值的内容.

我尝试运行以下查询:

exec test 0

我得到一个 SqlException以达到最大嵌套级别.
实际上，对于传递给SP的任何值，我都会得到此异常！为确保正确传递值，我注释掉了递归部分，并打印了正确的值.

如果我在Management Studio中运行相同的查询，则它可以使用任何值正确运行，并且仅在 @ a = 1
时失败
我对正在发生的事情一无所知...有什么建议吗?

谢谢
Fabio

解决方案

尝试更改以下所示的关注对象

 command.CommandType = CommandType.StoredProcedure;
command.CommandText = '  test @a =' + textBoxQuery.Text; 

您好，
你可以这样做:
sqlcommand.commantype = commandtype.storeProcedure;
sqlcommand.parameter(new sqlparamete(您的存储过程参数"));
dqldatareader dr = sqlcommand.executeDataReader();
您的解决方案将与此相同.

您正在执行递归，该递归不会在任何地方停止.
当您将1作为sp的输入时，它将连续调用，直到出现错误为止.所以
您应该更改逻辑.为什么要使用递归?

I have a strange problem calling a stored procedure from c# code.
This is the scenario:

I have a DB browser application with which I run different kinds of queries on a SQL Server DB (eg. Select, Update, execute stored procedure...)

So in my app I have an edit window where i write the T-SQL text that I run with this code:

IDbCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = connection.CommandTimeout;
command.CommandType = CommandType.Text;
command.CommandText = textBoxQuery.Text

IDbReader reader = command.ExecuteReader();

Quite strightforward.

And I have a stored procedure with a structure like:

CREATE PROCEDURE dbo.test (@a as int)
AS
BEGIN
    print @a
    print 'test'

  if @a=1 begin
    exec test @a
  end
END

(obviously this is just the prototype of a real SP that does something)
A simple test procedure that should fail with maximum nesting exception with @a=1 and just print something with any other value.

I try to run the following query:

exec test 0

I get an SqlException for maximum nesting level reached.
Actually I get this exception for any value I pass to the SP! To be sure that the value is passed correctly, I commented out the recursion part, and the correct value is printed.

If I run the same query in Management Studio it runs correctly with any value, and fails only with @a=1

I don''t have any clue on what is happening... any advice?

thanks
Fabio

解决方案

Try Changing followings as shown below

command.CommandType = CommandType.StoredProcedure;
command.CommandText = 'test @a=' + textBoxQuery.Text;

hi there
you can do this :
sqlcommand.commantype=commandtype.storeProcedure;
sqlcommand.parameter(new sqlparamete("your storeprocedure parameter"));
dqldatareader dr=sqlcommand.executeDataReader();
your solution will be same this.

You are doing recursion which is not going to stop anywhere.
When you give 1 as input to sp it is continuously called till error comes. So
You should change you logic.Why you are using recursion?

这篇关于从C#调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！