Using Stored Procedures to Add, Modify, and Remove Rows from the Database phần 1

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.
Nội dung trích xuất từ tài liệu:
Using Stored Procedures to Add, Modify, and Remove Rows from the Database phần 1Using Stored Procedures to Add, Modify, and Remove Rows from the DatabaseYou can get a DataAdapter object to call stored procedures to add, modify, and removerows from the database. These procedures are called instead of the INSERT, UPDATE,and DELETE statements youve seen how to set in a DataAdapter objectsInsertCommand, UpdateCommand, and DeleteCommand properties.The ability to call stored procedures using a DataAdapter is a very powerful addition toADO.NET. For example, you can use a stored procedure to add a row to a tablecontaining an identity column, and then retrieve the new value for that column generatedby the database. You can also do additional work in a stored procedure such as inserting arow into an audit table when a row is modified. Youll see examples of both thesescenarios 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 numberof stored procedures in Chapter 4, Introduction to Transact-SQL Programming, thatadded a row to the Products table and returned the ProductID.In this section, youll 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 earliersection, Modifying Rows in a DataTable.Note Youll find a complete program named PushChangesUsingProcedures.cs in the ch11 directory that illustrates the use of the methods shown in this section. The listing for this program is omitted from this book for brevity.Creating the Stored Procedures in the DatabaseYoull create the following three stored procedures in the Northwind database: • AddProduct4(), which adds a row to the Products table. • UpdateProduct(), which updates a row in the Products table. • DeleteProduct(), which deletes a row from the Products table.Lets take a look at these procedures.The AddProduct4() ProcedureAddProduct4() adds a row to the Products table. It uses the number 4 because previouschapters featured procedures named AddProduct(), AddProduct2(), and AddProduct3().Listing 11.4 shows the AddProduct4.sql file that you use to create the AddProduct4()procedure. Refer to Chapter 4 if you need a refresher on the Transact-SQL language or ifyou need to find out how to run this script to create the procedure in the database.Listing 11.4: ADDPRODUCT4.SQL/* AddProduct4.sql creates a procedure that adds a row to the Products table using values passed as parameters to the procedure. The procedure returns the ProductID of the new row using a RETURN statement*/CREATE PROCEDURE AddProduct4 @MyProductName nvarchar(40), @MyUnitPrice moneyAS -- 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 @MyProductIDNote Youll find the AddProduct4.sql file in the ch11 directory.The UpdateProduct() ProcedureUpdateProduct() updates a row in the Products table. Listing 11.5 shows theUpdateProduct.sql file that you use to create the UpdateProduct() procedure.Listing 11.5: UPDATEPRODUCT.SQL/* UpdateProduct.sql creates a procedure that modifies a row in the Products table using values passed as parameters to the procedure*/CREATE PROCEDURE UpdateProduct @OldProductID int, @NewProductName nvarchar(40), @NewUnitPrice money, @OldProductName nvarchar(40), @OldUnitPrice moneyAS -- update the row in the Products table UPDATE Products SET ProductName = @NewProductName, UnitPrice = @NewUnitPrice WHERE ProductID = @OldProductID AND ProductName = @OldProductName AND UnitPrice = @OldUnitPriceBecause the WHERE clause contains the old column values in the UPDATE statement ofthis procedure, the UPDATE uses optimistic concurrency described earlier. This meansthat one user doesnt overwrite another users changes.The DeleteProduct() ProcedureDeleteProduct() deletes a row from the Products table. Listing 11.6 shows theDeleteProduct.sql file that you use to create the DeleteProduct() procedure.Listing 11.6: DELETEPRODUCT.SQL/* Del ...