none
Batching stored procedures RRS feed

  • Question

  • Is there really no way to batch stored procedure calls that are not inserts or updates in ADO.NET ? I know you can do inserts and updates with a dataadapter, but what about statements that return result sets?
    Thursday, October 4, 2007 7:24 PM

Answers

  • Yes, you can do this but ADO.NET will not give you much help.

     

    It would be nice if you could just make an array of SqlCommand objects and hand that over to ADO.NET, but unfortunately you need to do some more work yourself.

     

    You can create an SqlCommand with CommandType of CommandType.Text.  For the text of the command, specify several SQL statements (use EXEC statements if you are calling SP's) separated by semicolons.

     

    If these statements have result sets, you will get multiple result sets in the SqlDataReader.  The SqlDataReader.NextResult() method can be used to advance to the next result set within the SqlDataReader.

     

    For example,

    Code Block

    Using cmd As New SqlCommand("EXEC procedure1 @P1A, @P1B, @P1C; EXEC procedure2 @P2A, @P2B, @P2C;", conn)

    cmd.Parameters.AddWithValue("@P1A", "value")

    ' Fill in the rest of the parameters here.

     

    Using reader = cmd.ExecuteReader()

    Do

    While reader.Read()

    'Process data here.

    End While

    Loop While reader.NextResult()

    End Using

    End Using

     

     

    Friday, October 5, 2007 12:06 AM

All replies

  • Yes, you can do this but ADO.NET will not give you much help.

     

    It would be nice if you could just make an array of SqlCommand objects and hand that over to ADO.NET, but unfortunately you need to do some more work yourself.

     

    You can create an SqlCommand with CommandType of CommandType.Text.  For the text of the command, specify several SQL statements (use EXEC statements if you are calling SP's) separated by semicolons.

     

    If these statements have result sets, you will get multiple result sets in the SqlDataReader.  The SqlDataReader.NextResult() method can be used to advance to the next result set within the SqlDataReader.

     

    For example,

    Code Block

    Using cmd As New SqlCommand("EXEC procedure1 @P1A, @P1B, @P1C; EXEC procedure2 @P2A, @P2B, @P2C;", conn)

    cmd.Parameters.AddWithValue("@P1A", "value")

    ' Fill in the rest of the parameters here.

     

    Using reader = cmd.ExecuteReader()

    Do

    While reader.Read()

    'Process data here.

    End While

    Loop While reader.NextResult()

    End Using

    End Using

     

     

    Friday, October 5, 2007 12:06 AM
  • I stumbled across that fact, but all of our stored procedures have return values. You can't seem to get them if you are not executing the command with a type of StoredProcedure. Unfortunatly we can't just change them all to output params because we have tons of external code that depends on those proc's at this point.

    This is an extention of a C++ project that we have and this type of thing is extreemly easy.
    Friday, October 5, 2007 1:03 AM
  • You can still do this because EXEC supports return values.

     

    EXEC @Ret = mystoredproc @P1, @P2, @P3

     

    Treat @Ret the same as you would an output parameter.

     

    That is,

    Code Block

    Dim pRet As SqlParameter = cmd.Parameters.Add("@Ret", SqlDbType.Int)

    pRet.Direction = ParameterDirection.Output

     

     

    As with any output parameter, you need to make sure that you check the Value at the correct time.  I believe that the value does not become visible until after you have processed the result set.

     

    The following from MSDN applies (http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx):

    While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

    Friday, October 5, 2007 1:19 AM