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.