Using batch queries in stored procedures

 Using batch queries in stored procedures

Database Northwind has the following stored procedures for table [Order Details]:

      (1)–––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.SelectOrderDetails

            AS

                SELECT OrderID, ProductID, Quantity, TStamp FROM [Order Details] WHERE OrderID < 10254

           

           

      (2) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.UpdateOrderDetails

                (

                  @OrderID_New int,

                  @ProductID_New int,

                  @Quantity_New smallint,

                  @OrderID_Orig int,

                  @ProductID_Orig int,

                  @TStamp timestamp

                )

            AS

                UPDATE [Order Details]

                  SET OrderID = @OrderID_New, ProductID = @ProductID_New, Quantity = @Quantity_New

                  WHERE OrderID = @OrderID_Orig AND ProductID = @ProductID_Orig AND TStamp = @TStamp;

                 

                IF @@ROWCOUNT = 1

                SELECT TStamp FROM [Order Details] WHERE OrderID = @OrderID_New and ProductID = @ProductID_New

           

           

      (3) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.InsertOrderDetails

                (

                  @OrderID int,

                  @ProductID int,

                  @Quantity smallint

                )

            AS

                INSERT INTO [Order Details] (OrderID, ProductID, Quantity) VALUES(@OrderID, @ProductID, @Quantity);

               

                SELECT TStamp FROM [Order Details] WHERE OrderID = @OrderID and ProductID = @ProductID

           

           

      (4) –––––––––––––––––––––––––––––––––––––––––––––––

            ALTER PROCEDURE dbo.DeleteOrderDetails

               (

                  @OrderID int,

                  @ProductID int

                )

            AS

            DELETE FROM [Order Details] WHERE OrderID = @OrderID AND ProductID = @ProductID

The code is listed as follow:

Dim param As OleDbParameter

' SelectCommand

Dim selectCmd As OleDbCommand = mcn.CreateCommand()

selectCmd.CommandText = "SelectOrderDetails"

selectCmd.CommandType = CommandType.StoredProcedure

mda.SelectCommand = selectCmd

' UpdateCommand

Dim updateCmd As OleDbCommand = mcn.CreateCommand()

updateCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

updateCmd.CommandText = "UpdateOrderDetails"

updateCmd.CommandType = CommandType.StoredProcedure

updateCmd.Parameters.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")

updateCmd.Parameters.Add("ProductID_New", OleDbType.VarChar, 0, "ProductID")

updateCmd.Parameters.Add("Quantity_New", OleDbType.VarChar, 0, "Quantity")

param = updateCmd.Parameters.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("ProductID_Orig", OleDbType.VarChar, 0, "ProductID")

param.SourceVersion = DataRowVersion.Original

param = updateCmd.Parameters.Add("TimeStamp_Orig", OleDbType.Binary, 8, "TStamp")

param.SourceVersion = DataRowVersion.Original

mda.UpdateCommand = updateCmd

' DeleteCommand

Dim deleteCmd As OleDbCommand = mcn.CreateCommand()

deleteCmd.CommandText = "DeleteOrderDetails"

deleteCmd.CommandType = CommandType.StoredProcedure

deleteCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

deleteCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

mda.DeleteCommand = deleteCmd

' InsertCommand

Dim insertCmd As OleDbCommand = mcn.CreateCommand()

insertCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

insertCmd.CommandText = "InsertOrderDetails"

insertCmd.CommandType = CommandType.StoredProcedure

insertCmd.Parameters.Add("OrderID", OleDbType.Integer, 0, "OrderID")

insertCmd.Parameters.Add("ProductID", OleDbType.VarChar, 0, "ProductID")

insertCmd.Parameters.Add("Quantity", OleDbType.VarChar, 0, "Quantity")

mda.InsertCommand = insertCmd

mda.Fill(mds)

mdg.DataSource = mds

mdg.DataMember = "Table"

Note that in the UPDATE command the time stamp parameter is used for input only, and in the INSERT command there is no time stamp parameter.