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 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 Database

You'll 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.

Let's take a look at these procedures.

The AddProduct4() Procedure

AddProduct4() adds a row to the Products table. It uses the number 4 because previous chapters 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 if you need to find out how to run this script to create the procedure in the database.

Listing 11.4: ADDPRODUCT4.SQL

start example
 /*   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 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 
end example

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

start example
 /*   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 money AS   -- update the row in the Products table   UPDATE Products   SET     ProductName = @NewProductName,     UnitPrice = @NewUnitPrice   WHERE ProductID = @OldProductID   AND ProductName = @OldProductName   AND UnitPrice = @OldUnitPrice 
end example

Because the WHERE clause contains the old column values in the UPDATE statement of this procedure, the UPDATE uses optimistic concurrency described earlier. This means that one user doesn't overwrite another user's changes.

The DeleteProduct() Procedure

DeleteProduct() deletes a row from the Products table. Listing 11.6 shows the DeleteProduct.sql file that you use to create the DeleteProduct() procedure.

Listing 11.6: DELETEPRODUCT.SQL

start example
 /*   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 
end example

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) to record the attempt to delete the row from the Products table. In this example, you must use SET NOCOUNT ON before performing the INSERT into the ProductAudit table, as shown in Listing 11.7.

Listing 11.7: DELETEPRODUCT2.SQL

start example
 /*   DeleteProduct2.sql creates a procedure that removes a row   from the Products table */ CREATE PROCEDURE DeleteProduct2   @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   -- use SET NOCOUNT ON to suppress the return of the   -- number of rows affected by the INSERT statement   SET NOCOUNT ON   -- add a row to the Audit table   IF @@ROWCOUNT = 1     INSERT INTO ProductAudit (       Action     ) 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'     ) 
end example

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, the InsertCommand, UpdateCommand, and DeleteCommand properties will contain Command objects that call the stored procedures shown earlier.

First, the following example creates a SqlCommand object containing a SELECT statement and sets the SelectCommand property of a SqlDataAdapter to that SqlCommand:

 SqlCommand mySelectCommand = mySqlConnection.CreateCommand(); mySelectCommand.CommandText =   "SELECT " +   "  ProductID, ProductName, UnitPrice " +   "FROM Products " +   "ORDER BY ProductID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySelectCommand; 

The SELECT statement is then run when you call the mySqlDataAdapter object's Fill() method to retrieve rows from the Products table into a DataSet.

Before you can push changes to the database, you must set the InsertCommand, UpdateCommand, and DeleteCommand properties of your DataAdapter with Command objects. These Command objects will contain calls to the AddProduct4(), UpdateProduct(), and DeleteProduct() stored procedures that you created earlier. When you then add, modify, or remove DataRow objects from your DataSet, and then call the Update() method of your DataAdapter, the appropriate stored procedure is run to push your changes to the database.

Let's take a look at how to set the InsertCommand, UpdateCommand, and DeleteCommand properties of your DataAdapter.

Setting the InsertCommand Property of a DataAdapter

The following example creates a SqlCommand object named myInsertCommand that contains a call to the AddProduct4() stored procedure:

 SqlCommand myInsertCommand = mySqlConnection.CreateCommand(); myInsertCommand.CommandText =   "EXECUTE @MyProductID = AddProduct4 @MyProductName, @MyUnitPrice"; myInsertCommand.Parameters.Add(   "@MyProductID", SqlDbType.Int, 0, "ProductID"); myInsertCommand.Parameters["@MyProductID"].Direction =   ParameterDirection.Output; myInsertCommand.Parameters.Add(   "@MyProductName", SqlDbType.NVarChar, 40, "ProductName"); myInsertCommand.Parameters.Add(   "@MyUnitPrice", SqlDbType.Money, 0, "UnitPrice"); 

As you can see from the previous code, the direction of the @MyProductID parameter is set to ParameterDirection.Output, which indicates that this parameter is an output parameter. Also, the maximum length of the @MyProductID and @MyUnitPrice parameters is set to 0 in the third parameter to the Add() method. Setting them to 0 is fine because the maximum length doesn't apply to fixed length types such as numbers, only to types such as strings.

Next, the following example sets the InsertCommand property of mySqlDataAdapter to myInsertCommand:

 mySqlDataAdapter.InsertCommand = myInsertCommand; 

Setting the UpdateCommand Property of a DataAdapter

The following example creates a SqlCommand object named myUpdateCommand that contains a call to the UpdateProduct() stored procedure and sets the UpdateCommand property of mySqlDataAdapter to myUpdateCommand:

 SqlCommand myUpdateCommand = mySqlConnection.CreateCommand(); myUpdateCommand.CommandText =   "EXECUTE UpdateProduct @OldProductID, @NewProductName, " +   "@NewUnitPrice, @OldProductName, @OldUnitPrice"; myUpdateCommand.Parameters.Add(   "@OldProductID", SqlDbType.Int, 0, "ProductID"); myUpdateCommand.Parameters.Add(   "@NewProductName", SqlDbType.NVarChar, 40, "ProductName"); myUpdateCommand.Parameters.Add(   "@NewUnitPrice", SqlDbType.Money, 0, "UnitPrice"); myUpdateCommand.Parameters.Add(   "@OldProductName", SqlDbType.NVarChar, 40, "ProductName"); myUpdateCommand.Parameters.Add(   "@OldUnitPrice", SqlDbType.Money, 0, "UnitPrice"); myUpdateCommand.Parameters["@OldProductID"].SourceVersion =   DataRowVersion.Original; myUpdateCommand.Parameters["@OldProductName"].SourceVersion =   DataRowVersion.Original; myUpdateCommand.Parameters["@OldUnitPrice"].SourceVersion =   DataRowVersion.Original; mySqlDataAdapter.UpdateCommand = myUpdateCommand; 

Setting the DeleteCommand Property of a DataAdapter

The following example creates a SqlCommand object named myDeleteCommand that contains a call to the DeleteProduct() stored procedure and sets the DeleteCommand property of mySqlDataAdapter to myDeleteCommand:

 SqlCommand myDeleteCommand = mySqlConnection.CreateCommand(); myDeleteCommand.CommandText =   "EXECUTE DeleteProduct @OldProductID, @OldProductName, @OldUnitPrice"; myDeleteCommand.Parameters.Add(   "@OldProductID", SqlDbType.Int, 0, "ProductID"); myDeleteCommand.Parameters.Add(   "@OldProductName", SqlDbType.NVarChar, 40, "ProductName"); myDeleteCommand.Parameters.Add(   "@OldUnitPrice", SqlDbType.Money, 0, "UnitPrice"); myDeleteCommand.Parameters["@OldProductID"].SourceVersion =   DataRowVersion.Original; myDeleteCommand.Parameters["@OldProductName"].SourceVersion =   DataRowVersion.Original; myDeleteCommand.Parameters["@OldUnitPrice"].SourceVersion =   DataRowVersion.Original; mySqlDataAdapter.DeleteCommand = myDeleteCommand; 

This completes the setup of the DataAdapter object.

Adding a DataRow to a DataTable

In this section, you'll learn how to add a DataRow to a DataTable. First, the following code creates a DataSet object named myDataSet and populates it by calling mySqlDataAdapter.Fill():

 DataSet myDataSet = new DataSet(); mySqlConnection.Open(); int numOfRows =   mySqlDataAdapter.Fill(myDataSet, "Products"); mySqlConnection.Close(); 

The int returned by the Fill() method is the number of rows retrieved from the database and copied to myDataSet. The myDataSet object now contains a DataTable named Products, which contains the rows retrieved by the following SELECT statement set earlier in the SelectCommand property of mySqlDataAdapter:

 SELECT ProductID, ProductName, UnitPrice FROM Products ORDER BY ProductID 

To add a new row to a DataTable object, you use the same four steps as shown earlier in the section "Modifying a DataRow in a DataTable." The following method, named AddDataRow(), uses those steps to add a new row to a DataTable:

 public static int AddDataRow(   DataTable myDataTable,   SqlDataAdapter mySqlDataAdapter,   SqlConnection mySqlConnection ) {   Console.WriteLine("\nIn AddDataRow()");   // step 1: use the NewRow() method of the DataTable to   // create a new DataRow   Console.WriteLine("Calling myDataTable.NewRow()");   DataRow myNewDataRow = myDataTable.NewRow();   Console.WriteLine("myNewDataRow.RowState = " +     myNewDataRow.RowState);   // step 2: set the values for the DataColumn objects of   // the new DataRow   myNewDataRow["ProductName"] = "Widget";   myNewDataRow["UnitPrice"] = 10.99;   // step 3: use the Add() method through the Rows property   // to add the new DataRow to the DataTable   Console.WriteLine("Calling myDataTable.Rows.Add()");   myDataTable.Rows.Add(myNewDataRow);   Console.WriteLine("myNewDataRow.RowState = " +     myNewDataRow.RowState);     // step 4: use the Update() method to push the new     // row to the database     Console.WriteLine("Calling mySqlDataAdapter.Update()");     mySqlConnection.Open();     int numOfRows = mySqlDataAdapter.Update(myDataTable);     mySqlConnection.Close();     Console.WriteLine("numOfRows = " + numOfRows);     Console.WriteLine("myNewDataRow.RowState = " +       myNewDataRow.RowState);     DisplayDataRow(myNewDataRow, myDataTable);     // return the ProductID of the new DataRow     return (int) myNewDataRow["ProductID"]; } 

Notice that no value for the ProductID DataColumn is set in step 2. This is because the ProductID is automatically generated by the database when the new row is pushed to the database by the Update() method in step 4.

When the Update() method is called, the AddProduct4() stored procedure is run to add the new row to the Products table. The database then generates a new ProductID for the row, which is then returned by the AddProduct4() stored procedure. You can then read the new ProductID using myNewDataRow["ProductID"], which now contains the new ProductID. This ProductID is then returned at the end of the AddDataRow() method.

The output from AddDataRow() and its call to DisplayDataRow() are as follows:

 In AddDataRow() Calling myDataTable.NewRow() myNewDataRow.RowState = Detached Calling myDataTable.Rows.Add() myNewDataRow.RowState = Added Calling mySqlDataAdapter.Update() numOfRows = 1 myNewDataRow.RowState = Unchanged In DisplayDataRow() ProductID = 180 ProductName = Widget UnitPrice = 10.99 

As you can see, after myDataTable.NewRow() is called to create myNewDataRow its RowState is Detached, which indicates myNewDataRow isn't yet part of myDataTable.

Next, myDataTable.Rows.Add() is called to add myNewDataRow to myDataTable. This causes the RowState of myNewDataRow to change to Added, which indicates myNewDataRow has been added to myDataTable.

Finally, mySqlDataAdapter.Update() is called to push the new row to the database. The AddProduct4() stored procedure is run to add the new row to the Products table, and the RowState of myNewDataRow changes to Unchanged.

Modifying a DataRow in a DataTable

The following method, named ModifyDataRow(), uses four steps to modify a DataRow in a DataTable object. Notice that the ProductID to modify is passed as a parameter:

 public static void ModifyDataRow(   DataTable myDataTable,   int productID,   SqlDataAdapter mySqlDataAdapter,   SqlConnection mySqlConnection ) {   Console.WriteLine("\nIn ModifyDataRow()");   // step 1: set the PrimaryKey property of the DataTable   myDataTable.PrimaryKey =     new DataColumn[]     {       myDataTable.Columns["ProductID"]     };   // step 2: use the Find() method to locate the DataRow   // in the DataTable using the primary key value   DataRow myEditDataRow = myDataTable.Rows.Find(productID);   // step 3: change the DataColumn values of the DataRow   myEditDataRow["ProductName"] = "Advanced Widget";   myEditDataRow["UnitPrice"] = 24.99;   Console.WriteLine("myEditDataRow.RowState = " +     myEditDataRow.RowState);   Console.WriteLine("myEditDataRow[\" ProductID\", " +     "DataRowVersion.Original] = " +     myEditDataRow["ProductID", DataRowVersion.Original]);   Console.WriteLine("myEditDataRow[\" ProductName\", " +     "DataRowVersion.Original] = " +     myEditDataRow["ProductName", DataRowVersion.Original]);   Console.WriteLine("myEditDataRow[\" UnitPrice\", " +     "DataRowVersion.Original] = " +     myEditDataRow["UnitPrice", DataRowVersion.Original]);   Console.WriteLine("myEditDataRow[\" ProductName\", " +     "DataRowVersion.Current] = " +     myEditDataRow["ProductName", DataRowVersion.Current]);   Console.WriteLine("myEditDataRow[\" UnitPrice\", " +     "DataRowVersion.Current] = " +     myEditDataRow["UnitPrice", DataRowVersion.Current]);   // step 4: use the Update() method to push the update   // to the database   Console.WriteLine("Calling mySqlDataAdapter.Update()");   mySqlConnection.Open();   int numOfRows = mySqlDataAdapter.Update(myDataTable);   mySqlConnection.Close();   Console.WriteLine("numOfRows = " + numOfRows);   Console.WriteLine("myEditDataRow.RowState = " +     myEditDataRow.RowState);   DisplayDataRow(myEditDataRow, myDataTable); } 

Notice this method displays the original values for the ProductID, ProductName, and UnitPrice DataColumn objects using the DataRowVersion.Original constant. These are the DataColumn values before they are changed. The method also displays the current values for the ProductName and UnitPrice DataColumn objects using the DataRowVersion.Current constant. These are the DataColumn values after they are changed. When the Update() method is called in step 4, the UpdateProduct() stored procedure is run behind the scenes to perform the update.

The output from ModifyDataRow() and its call to DisplayDataRow() is as follows:

 In ModifyDataRow() myEditDataRow.RowState = Modified myEditDataRow["ProductID", DataRowVersion.Original] = 180 myEditDataRow["ProductName", DataRowVersion.Original] = Widget myEditDataRow["UnitPrice", DataRowVersion.Original] = 10.99 myEditDataRow["ProductName", DataRowVersion.Current] = Advanced Widget myEditDataRow["UnitPrice", DataRowVersion.Current] = 24.99 Calling mySqlDataAdapter.Update() numOfRows = 1 myEditDataRow.RowState = Unchanged In DisplayDataRow() ProductID = 180 ProductName = Advanced Widget UnitPrice = 24.99 

Notice that the RowState property of myEditDataRow changes to Modified after it is changed, and then to Unchanged after mySqlDataAdapter.Update() is called.

Removing a DataRow from a DataTable

The following method, named RemoveDataRow(), uses four steps to remove a DataRow from a DataTable. Notice that the ProductID to modify is passed as a parameter:

 public static void RemoveDataRow(   DataTable myDataTable,   int productID,   SqlDataAdapter mySqlDataAdapter,   SqlConnection mySqlConnection ) {   Console.WriteLine("\nIn RemoveDataRow()");   // step 1: set the PrimaryKey property of the DataTable   myDataTable.PrimaryKey =     new DataColumn[]     {       myDataTable.Columns["ProductID"]     };   // step 2: use the Find() method to locate the DataRow   DataRow myRemoveDataRow = myDataTable.Rows.Find(productID);   // step 3: use the Delete() method to remove the DataRow   Console.WriteLine("Calling myRemoveDataRow.Delete()");   myRemoveDataRow.Delete();   Console.WriteLine("myRemoveDataRow.RowState = " +     myRemoveDataRow.RowState);   // step 4: use the Update() method to push the delete   // to the database   Console.WriteLine("Calling mySqlDataAdapter.Update()");   mySqlConnection.Open();   int numOfRows = mySqlDataAdapter.Update(myDataTable);   mySqlConnection.Close();   Console.WriteLine("numOfRows = " + numOfRows);   Console.WriteLine("myRemoveDataRow.RowState = " +     myRemoveDataRow.RowState); } 

The output from RemoveDataRow() is as follows:

 In RemoveDataRow() Calling myRemoveDataRow.Delete() myRemoveDataRow.RowState = Deleted Calling mySqlDataAdapter.Update() numOfRows = 1 myRemoveDataRow.RowState = Detached 

Notice that the RowState property of myRemoveDataRow is set to Deleted after myRemoveData .Delete() is called, and then to Detached after mySqlDataAdapter.Update() is called. When the Update() method is called in step 4, the DeleteProduct() stored procedure is run behind the scenes to perform the delete.

Note 

You'll find a complete program named PushChangesUsingProcedures.cs in the ch11 directory that illustrates the use of the AddDataRow(), ModifyDataRow(), and RemoveDataRow() methods. This listing is omitted from this book for brevity.




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net