ASP. NET2.0 data operations-data access layer creation (3)

Step 4: insert, update, and delete data

There are two common data insertion, update, and deletion modes. 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, TableAdapter 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 () <too many rows without question ??? O struct CODE> method, you can pass in a strong type DataSet, a strong type of 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. (If 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 multiply the prices of products that are not terminated and whose inventory is equal to or less than 25 units:

C #

1 2 3 4 5 6 7 8 9 10 11 12

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 #

1 2 3 4 5 6 7 8 9 10 11 12

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. Let's take a look at the preceding encoding example. Without the help of intelliisense, it is not clear which field of the Products table is 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-type query.

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

The next screen displays the CommandText attribute of InsertCommand. After the query statement, add a SELECT SCOPE_IDENTITY () query. This query returns the last IDENTITY value that is inserted into the identity field in the same operation range. (For details, refer to the content of SCOPE_IDENTITY () in the technical documentation and why you should use SCOPE_IDENTITY () instead of @ IDENTITY ). 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.

By default, 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 done 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 #
1 2 3 4 5 6 7	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);