Using Stored Procedures to Add, Modify, and Remove Rows from the Database
You can get a DataAdapter object to call stored procedures to add, modify, and remove
rows from the database. These procedures are called instead of the INSERT, UPDATE,
and DELETE statements you've seen how to set in a DataAdapter object's
InsertCommand, UpdateCommand, and DeleteCommand properties.
The ability to call stored procedures using a DataAdapter is a very powerful addition to
ADO.NET. For example, you can use a stored procedure to add a row to a table
containing an identity column, and then retrieve the new value for that column generated
by the database. You can also do additional work in a stored procedure such as inserting a
row into an audit table when a row is modified. You'll see examples of both these
scenarios in this section.
Tip Using stored procedures instead of INSERT, UPDATE, and DELETE statements can
also improve performance. You should use stored procedures if your database
supports them. SQL Server and Oracle support stored procedures. Oracle stored-
procedures are written in PL/SQL.
The ProductID column of the Products table is an identity column, and you saw a number
of stored procedures in Chapter 4
, "Introduction to Transact-SQL Programming," that
added a row to the Products table and returned the ProductID.
In this section, you'll see how to
•
Create the required stored procedures in the Northwind database.
•
Set up a DataAdapter to call the stored procedures.
•
Add, modify, and remove a DataRow to from a DataTable.
The C# methods shown in this section follow the same steps as shown in the earlier
section, "Modifying Rows in a DataTable
."

Note You'll find a complete program named PushChangesUsingProcedures.cs in the

@MyUnitPrice money
AS

-- declare the @MyProductID variable
DECLARE @MyProductID int

-- insert a row into the Products table
INSERT INTO Products (
ProductName, UnitPrice
) VALUES (
@MyProductName, @MyUnitPrice
)

-- use the SCOPE_IDENTITY() function to get the last
-- identity value inserted into a table performed within
-- the current database session and stored procedure,
-- so SCOPE_IDENTITY returns the ProductID for the new row
-- in the Products table in this case
SET @MyProductID = SCOPE_IDENTITY()

RETURN @MyProductID

Note You'll find the AddProduct4.sql file in the ch11 directory.
The UpdateProduct() Procedure
UpdateProduct() updates a row in the Products table. Listing 11.5
shows the
UpdateProduct.sql file that you use to create the UpdateProduct() procedure.
Listing 11.5: UPDATEPRODUCT.SQL

/*


/*
DeleteProduct.sql creates a procedure that removes a row
from the Products table
*/

CREATE PROCEDURE DeleteProduct
@OldProductID int,
@OldProductName nvarchar(40),
@OldUnitPrice money
AS

-- delete the row from the Products table
DELETE FROM Products
WHERE ProductID = @OldProductID
AND ProductName = @OldProductName
AND UnitPrice = @OldUnitPrice

Using SET NOCOUNT ON in Stored Procedures
In Chapter 4
, "Introduction to Transact-SQL Programming," you saw that you use the
SET NOCOUNT ON command to prevent Transact-SQL from returning the number of
rows affected. Typically, you must avoid using this command in your stored procedures
because the DataAdapter uses the returned number of rows affected to know whether the
update succeeded.
There is one situation when you must use SET NOCOUNT ON: when your stored
procedure performs an INSERT, UPDATE, or DELETE statement that affects another
table besides the main one you are pushing a change to. For example, say the
DeleteProduct() procedure also performed an INSERT statement to add a row to the
ProductAudit table (described in Chapter 4

) VALUES (
'Product deleted with ProductID of ' +
CONVERT(nvarchar, @OldProductID)
)
ELSE
INSERT INTO ProductAudit (
Action
) VALUES (
'Product with ProductID of ' +
CONVERT(nvarchar, @OldProductID) +
' was not deleted'
)

By using SET NOCOUNT ON before the INSERT, only the number of rows affected by
the DELETE statement is returned, and the DataAdapter therefore gets the correct value.
Transact-SQL also has a SET NOCOUNT ON command to turn on the number of rows
affected. You can use a combination of SET NOCOUNT OFF and SET NOCOUNT ON
if you need to perform an INSERT, UPDATE, or DELETE statement before the main
SQL statement in your stored procedure.
Setting Up a DataAdapter to Call Stored Procedures
As mentioned in the earlier section "Modifying Rows in a DataTable
," you need to create
a DataAdapter object and set its SelectCommand, InsertCommand, UpdateCommand,
and DeleteCommand properties with appropriate Command objects. This time, however,