ASP. NET 2.0 data Tutorial: insert, update, and delete data

Step 4 of ASP. NET 2.0 data Tutorial: insert, update, and delete data

There are two common insertion, update, and deletion modes in ASP. NET 2.0. The first mode is called DB direct mode. When the involved method is called, an INSERT, UPDATE, or DELETE command is sent to the database, this command only performs operations on a single database record. Methods like this generally accept a series of scalar parameters (such as integers, strings, Boolean values, and datetime values) that correspond to values inserted, updated, or deleted ). For example, if you use this mode to operate the Products table, the delete method accepts an integer parameter, representing the ProductID of the record to be deleted, the insert method accepts a string corresponding to ProductName, corresponding to the decimal value of UnitPrice, and corresponding to the integer of UnitsOnStock.

 

Figure 21: each insert, update, and delete request is immediately sent to the database

Another mode is called the batch update mode. You can update the entire DataSet, the entire able, or a DataRow set in a method call. In this mode, the developer deletes, inserts, modifies, and transmits the DataRow or the entire DataRow to an update method. This method then rounds the passed DataRow to determine whether the DataRow has been modified, a new record, or a deleted record through the RowState attribute of DataRow, then, appropriate database commands are issued for each record.

 

Figure 22: After the Update method is called, all changes are synchronized with the database.

By default, ASP. NET 2.0 adopts the batch update mode, but also supports the direct DB mode. Because we select the "generate insert, Update, and delete statement" option in the advanced options when creating our TableAdapter, The ProductsTableAdapter contains an Update () method, this method implements the batch update mode. Specifically, TableAdapter contains an Update () method that can be used to pass in a strongly typed DataSet, a strongly typed able, or one or more DataRow. If you do not clear the "GenerateDBDirectMethods" check box in the options when creating the TableAdapter at the beginning, the DB direct mode will also use Insert (), Update () and the Delete () method.

Both data modification modes use the InsertCommand, UpdateCommand, and DeleteCommand attributes of TableAdapter to send the corresponding INSERT, UPDATE, and DELETE commands to the database. You can click TableAdapter in the DataSet designer, and then view and modify the InsertCommand, UpdateCommand, and DeleteCommand attributes in the Properties window. (Make sure you have selected TableAdapter, And the ProductsTableAdapter object is selected in the drop-down box of the Property Window)

 

Figure 23: TableAdapter contains attributes such as InsertCommand, UpdateCommand, and DeleteCommand.

To view or modify the attributes of these database commands, click the CommandText subattribute to start the corresponding query generator.

 

Figure 24: Configure insert, update, and delete statements in the Query Builder

The following code example demonstrates how to use the batch update mode to double the prices of products that are not terminated and whose inventory is equal to or less than 25 units:

 
 

  
  
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 demonstrates how to use the DB direct mode to delete a product, update a product, and then add a new product:

C #

 
 

  
  
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);  
  
  
   
 
 

Create custom insert, update, and delete Methods

The Insert (), Update (), and Delete () methods generated using the DB direct method sometimes feel a little inconvenient, especially when the data table has many fields. Take a look at the preceding encoding example. Without the help of intelliisense, it is not clear which field in the Products table corresponds to the input parameter in the Update () and Insert () methods. Sometimes we only need to update one or two fields or a custom Insert () method. This method needs to return the value of the IDENTITY (auto-increment) field of the newly inserted record.

To create such a custom method, return to the DataSet Designer. Press the right mouse on the TableAdapter, select "add query", and return to the TableAdapter Configuration Wizard. On the second screen, we can specify the type of the query to be generated. Let's generate a method to add a new product record and then return the ProductID value of the newly added record. Therefore, select to generate an INSERT query.

 

Figure 25: Create a method to add a new record to the Products table

The next screen displays the CommandText attribute of InsertCommand. Add a SELECT SCOPE_IDENTITY () query to the end of the query statement. This query returns the last IDENTITY value inserted into the identity field in the same operation range. (For more information, see the technical documentation on SCOPE_IDENTITY () and why you should http://weblogs.sqlteam.com/travisl/archive/2003/10/29/405.aspx ). Make sure that you add a semicolon after the INSERT statement before adding the SELECT statement.

 

Figure 26: added the returned SCOPE_IDENTITY () value for the query.

Finally, name the new method InsertProduct.

 

Figure 27: Set the method name to InsertProduct

When you return the DataSet Designer, you will see that the ProductsTableAdapter has a new method, InsertProduct. If there is no corresponding parameter for this new method for each field in the Products table, it is very likely that, you forgot to add a semicolon (semi-colon) to the end of the INSERT statement ). Reconfigure the InsertProduct method and confirm that there is a semicolon between the INSERT and SELECT statements.

Under the default condition of ASP. NET 2.0, the insert method calls the non-query (non-query) method, meaning that they only return the number of affected records. However, we want the InsertProduct method to return the value returned by a query, rather than the number of affected records. This can be achieved by changing the ExecuteMode attribute of the InsertProduct method to Scalar (Scalar.

 

Figure 28: Change the ExecuteMode attribute to Scalar

The following code demonstrates how to use this new InsertProduct method:

C #

 
 

  
  
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);  
  
  
 
 
 

1. How to deploy the asp.net mvc program in IIS6.0
2. Use Winform to build the asp.net mvc Framework
3. Programming idea of ASP. NET Session failure
4. ASP. NET Session state storage
5. Understand ASP. NET Web Application Models