CODEWITHSUNDEEP
c#sql-server-2008
Apollo
Apollo

Reputation: 2070

Multiple stored procedures in C#

I have this and it works fine:

SqlDataAdapter da = new SqlDataAdapter("SELECT * from tblA; SELECT * from tblB",cs);

I want this:

SqlDataAdapter da = new SqlDataAdapter("spA; spB",cs);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

It says I can only use one stored procedure at a time. How can I use this two queries with two stored procedures?

Upvotes: 0

Views: 1834

Answers (5)

Basuro
Basuro

Reputation: 1104

SQL

CREATE PROCEDURE spC AS
    @param_for_A int,          -- Parameter for SP 'spA'
    @param_for_B nvarchar(50)  -- Parameter for SP 'spB'
BEGIN
    EXEC spA @sqA = @param_for_A;
    EXEC spB @sqB = @param_for_B;
END

C#

SqlDataAdapter da = new SqlDataAdapter("spC", cs);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add(new SqlParameter("@param_for_A", 123));
da.SelectCommand.Parameters.Add(new SqlParameter("@param_for_B", "demo"));
DataSet myDS = new DataSet();
da.Fill(myDS);

The ds.Tables collection then contains all returned tables, in order:

  • ds.Tables[0] - Result of first 'SP in SP'
  • ds.Tables[1] - Result of second 'SP in SP'

Upvotes: 1

Gerardo Lima
Gerardo Lima

Reputation: 6712

T-SQL mandates that stored procedures are executed as EXEC MyProc; for all cases except when the batch has just one statement -- in which case, the use of "EXEC" is optional. To execute two stored procedures, in your example, you just have to add these to the statement string:

SqlDataAdapter da = new SqlDataAdapter("EXEC spA; EXEC spB",cs);
da.SelectCommand.CommandType = CommandType.Text; // optional (it's default)

more information on MSDN

Upvotes: 0

Rikalous
Rikalous

Reputation: 4574

Consider changing to use an SqlDataReader - it can cope with multiple result sets by using the NextResult method.

Upvotes: 0

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

Another way is that you can pass multiple store procedure as text . command.CommandType = CommandType.Text;

put comma between them .

Upvotes: 0

Icarus
Icarus

Reputation: 63970

One solution I can think of would be to create a third stored proc that calls both the stored procedures you want to call.

Upvotes: 0

Related Questions