Calling a stored procedure from C#

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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:

C#

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:

SQL

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

Posted 22-Nov-12 0:35am

Fabio Ottavi

Updated 22-Nov-12 0:40am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Anele Ngqandu · Answer 1 · 2012-11-22T01:03:00

Hi

C#

using (SqlConnection conn = new SqlConnection(ConnString))
           {
          SqlCommand cmd = new SqlCommand("procGetAllEmployeesNoXeception", conn);
          cmd.CommandType = CommandType.StoredProcedure;



          DataTable dTable = new DataTable("dTable");
          SqlDataAdapter adapter = new SqlDataAdapter(cmd);
          adapter.Fill(dTable);


               return dTable;
           }

pradiprenushe · Answer 2 · 2012-11-22T00:58:00

Solution 3

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?

Posted 22-Nov-12 0:58am

pradiprenushe

Comments

Fabio Ottavi 22-Nov-12 7:29am

Obviously this is not a real function, it's just a test. In my real SP I need a "controlled" recursion.
But this test should run fine if I pass any value but 1.

pradiprenushe 22-Nov-12 7:49am

Recursion is happening when input is 1. So same sp is getting called infinite times
i.e. nesting id happeing infinite times. I think nesting upto 32 is allowed in sql.
If you are having probleb in sp use then try this code

SqlConnection scn = new SqlConnection(connection);
SqlCommand spcmd = new SqlCommand("test" , scn);
spcmd.CommandType = CommandType.StoredProcedure
SqlParameter spara= new SqlParameter("@a", SqlDbType.Int);
spara= 0; // set value here
SqlDataReader dr;
spcmd.Parameters.Add(theOrderID);
scn.Open();
dr = spcmd.ExecuteReader();

mhd.sbt · Answer 3 · 2012-11-22T00:54:00

Solution 2

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.

Posted 22-Nov-12 0:54am

mhd.sbt

Comments

Fabio Ottavi 22-Nov-12 6:59am

Yes, I tried to change the query type to commandtype.storeProcedure, and it actually works. The problem is that I don't know in advance if the text represents a select/update query or a stored procedure. And also passing parameters is much more painful if I have to parse them from the query text and add them as SqlParameters.

The "exec" approach seemed fine and simple, and, first of all, is a correct T-SQL statement, but I can't get it to work.

hitesh_tech · Answer 4 · 2012-11-22T00:44:00

Solution 1

Try Changing followings as shown below

C#

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