Scott Mitchell

June 2006

Download the ASPNET_Data_Tutorial_1_CS.exe sample code.

Contents of Tutorial 1 (Visual C#)

Introduction
Step 1: Creating a Web Project and Connecting to the Database
Step 2: Creating the Data Access Layer
Step 3: Adding Parameterized Methods to the Data Access Layer
Step 4: Inserting, Updating, and Deleting Data
Step 5: Completing the Data Access Layer
Summary

 

Step 4: Inserting, Updating, and Deleting Data

There are two patterns commonly used for inserting, updating, and deleting data. The first pattern, which I'll call the database direct pattern, involves creating methods that, when invoked, issue an INSERT, UPDATE, or DELETE command to the database that operates on a single database record. Such methods are typically passed in a series of scalar values (integers, strings, Booleans, DateTimes, and so on) that correspond to the values to insert, update, or delete. For example, with this pattern for the Products table the delete method would take in an integer parameter, indicating the ProductID of the record to delete, while the insert method would take in a string for the ProductName, a decimal for the UnitPrice, an integer for the UnitsOnStock, and so on.

Figure 21. Each Insert, Update, and Delete Request Is Sent to the Database Immediately

The other pattern, which I'll refer to as the batch update pattern, is to update an entire DataSet, DataTable, or collection of DataRows in one method call. With this pattern a developer deletes, inserts, and modifies the DataRows in a DataTable and then passes those DataRows or DataTable into an update method. This method then enumerates the DataRows passed in, determines whether or not they've been modified, added, or deleted (via the DataRow's RowState property value), and issues the appropriate database request for each record.

Figure 22. All Changes are Synchronized with the Database When the Update Method is Invoked

The TableAdapter uses the batch update pattern by default, but also supports the DB direct pattern. Since we selected the "Generate Insert, Update, and Delete statements" option from the Advanced Properties when creating our TableAdapter, the ProductsTableAdapter contains an Update() method, which implements the batch update pattern. Specifically, the TableAdapter contains an Update() method that can be passed the Typed DataSet, a strongly-typed DataTable, or one or more DataRows. If you left the "GenerateDBDirectMethods" checkbox checked when first creating the TableAdapter the DB direct pattern will also be implemented via Insert(), Update(), and Delete() methods.

Both data modification patterns use the TableAdapter's InsertCommand, UpdateCommand, and DeleteCommand properties to issue their INSERT, UPDATE, and DELETE commands to the database. You can inspect and modify the InsertCommand, UpdateCommand, and DeleteCommand properties by clicking on the TableAdapter in the DataSet Designer and then going to the Properties window. (Make sure you have selected the TableAdapter, and that the ProductsTableAdapter object is the one selected in the drop-down list in the Properties window.)

Figure 23. The TableAdapter has InsertCommand, UpdateCommand, and DeleteCommand Properties

To examine or modify any of these database command properties, click on the CommandText subproperty, which will bring up the Query Builder.

Figure 24. Configure the INSERT, UPDATE, and DELETE Statements in the Query Builder

The following code example shows how to use the batch update pattern to double the price of all products that are not discontinued and that have 25 units in stock or less:

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new 
NorthwindTableAdapters.ProductsTableAdapter(); 

// For each product, double its price if it is not discontinued and
// there are 25 items in stock or less
Northwind.ProductsDataTable products = productsAdapter.GetProducts();
foreach (Northwind.ProductsRow product in products)
   if (!product.Discontinued && product.UnitsInStock <= 25)
      product.UnitPrice *= 2;

// Update the products
productsAdapter.Update(products);
                

The code below illustrates how to use the DB direct pattern to programmatically delete a particular product, then update one, and then add a new one:

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter(); 

// Delete the product with ProductID 3
productsAdapter.Delete(3);

// Update Chai (ProductID of 1), setting the UnitsOnOrder to 15
productsAdapter.Update("Chai", 1, 1, "10 boxes x 20 bags", 18.0m, 39, 
15, 10, false, 1);

// Add a new product
productsAdapter.Insert("New Product", 1, 1, "12 tins per carton", 
14.95m, 15, 0, 10, false);
                

Creating Custom Insert, Update, and Delete Methods

The Insert(), Update(), and Delete() methods created by the DB direct method can be a bit cumbersome, especially for tables with many columns. Looking at the previous code example, without IntelliSense's help it's not particularly clear what Products table column maps to each input parameter to the Update() and Insert() methods. There may be times when we only want to update a single column or two, or want a customized Insert() method that will, perhaps, return the value of the newly inserted record's IDENTITY (auto-increment) field.

To create such a custom method, return to the DataSet Designer. Right-click on the TableAdapter and choose Add Query, returning to the TableAdapter wizard. On the second screen we can indicate the type of query to create. Let's create a method that adds a new product and then returns the value of the newly added record's ProductID. Therefore, opt to create an INSERT query.

Figure 25. Create a Method to Add a New Row to the Products Table

On the next screen the InsertCommand's CommandText appears. Augment this query by adding SELECT SCOPE_IDENTITY() at the end of the query, which will return the last identity value inserted into an IDENTITY column in the same scope. (See the technical documentation for more information about SCOPE_IDENTITY() and why you probably want to use SCOPE_IDENTITY() in lieu of @@IDENTITY.) Make sure that you end the INSERT statement with a semi-colon before adding the SELECT statement.

Figure 26. Augment the Query to Return the SCOPE_IDENTITY() Value

Finally, name the new method InsertProduct.

Figure 27. Set the New Method Name to InsertProduct

When you return to the DataSet Designer you'll see that the ProductsTableAdapter contains a new method, InsertProduct. If this new method doesn't have a parameter for each column in the Products table, chances are you forgot to terminate the INSERT statement with a semi-colon. Configure the InsertProduct method and ensure you have a semi-colon delimiting the INSERT and SELECT statements.

By default, insert methods issue non-query methods, meaning that they return the number of affected rows. However, we want the InsertProduct method to return the value returned by the query, not the number of rows affected. To accomplish this, adjust the InsertProduct method's ExecuteMode property to Scalar.

Figure 28. Change the ExecuteMode Property to Scalar

The following code shows this new InsertProduct method in action:

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter();

// Add a new product
int new_productID = Convert.ToInt32(productsAdapter.InsertProduct("New Product", 1, 1, "12 tins per carton", 14.95m, 10, 0, 10, false));

// On second thought, delete the product
productsAdapter.Delete(new_productID);
                

 

 

 

Go to top of page or next part of tutorial