asp.net2.0 data access layer to create data operations (3)

Asp.net| Create | access | data

　　 Step Fourth: Insert, UPDATE, and delete data

There are two types of patterns used for inserting, updating, and deleting data. The first pattern, which I call the DB Direct mode, when the method involved is invoked, sends an INSERT, or update, or delete command to the database, which operates on a single database record. Methods like this generally accept a series of scalar parameters (such as integers, strings, Boolean values, date times, and so on) that correspond to values that are inserted, updated, or deleted. For example, using this pattern to manipulate the Products table, the Delete method takes an integer argument that represents the ProductID of the record that needs to be deleted, and the insertion method accepts a string corresponding to the ProductName, corresponding to the decimal value of UnitPrice. An integer corresponding to Unitsonstock, and so on.


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

Another pattern, which I call the batch update pattern, is to update the entire dataset, or the entire DataTable, or a DataRow collection in one method call. In this pattern, the developer deletes, inserts, modifies DataRow in a DataTable, and then passes these DataRow or the entire DataTable to an update method. The method then rounds through the incoming DataRow, DataRow the RowState property properties to determine whether the DataRow has been altered, the new record, or the deleted record, and then sends the appropriate database command for each record.


Figure 22: After the Update method call, all changes are synchronized with the database

By default, TableAdapter uses batch update mode, but also supports DB direct mode. Because we chose the "Generate INSERT, UPDATE, and DELETE statements" option in the advanced options for creating our TableAdapter, ProductsTableAdapter contains an update () method that implements batch update mode. Specifically, TableAdapter contains an update () < Yun 敳 Mongoose 搨??? Oёcode> method, you can pass in a strongly typed DataSet, or a strongly typed DataTable, or one and multiple DataRow. If you do not clear the "Generate DB Direct Method (GenerateDBDirectMethods)" checkbox when you first created the TableAdapter option, the DB Direct mode will also pass insert (), Update (), and delete () method to implement.

Both of these data modification patterns use the TableAdapter Insertcommand,updatecommand, and the DeleteCommand property to emit the corresponding insert,update and delete commands to the database. You can click TableAdapter in the DataSet Designer and then view and change the Insertcommand,updatecommand, and DeleteCommand properties in the Properties window. (Confirm that you have selected the TableAdapter and that the ProductsTableAdapter object is the selected item in the Drop-down box in the Properties window)


Figure 23:tableadapter contains attributes such as Insertcommand,updatecommand, and DeleteCommand

To view or change the properties of these database commands, click CommandText, which starts the corresponding query builder.


Figure 24: Configuring Insert, UPDATE, and DELETE statements in Query Builder

The following code example demonstrates how to use batch update mode to double the price of a product that is not terminated and has an inventory equal to or less than 25 units:

C#
	Northwindtableadapters.productstableadapter productsadapter = New Northwindtableadapters.productstableadapter (); For each product, the double it price if it isn't discontinued and //There are items in the stock or less NORTHWIND.P Roductsdatatable products = productsadapter.getproducts (); foreach (Northwind.productsrow product in) if (!product. Discontinued && product. UnitsInStock <= 25)

The following code demonstrates how to use DB Direct mode to remove 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 productsadapter.update ("Chai", 1, 1, "Boxes x ba GS ", 18.0m, M, 1, false, etc. ADD a new product Productsadapter.insert ("New Product", 1, 1, "tins per carton", 14.95m, 0, false);

　　 Create a custom insert, UPDATE, delete method

The Insert (), Update (), and Delete () methods generated by the DB direct method sometimes feel a bit inconvenient, especially if the datasheet has many fields. Looking at the previous code example, without IntelliSense help, it's not clear which field in the Products table corresponds to which input parameter in the update () and insert () methods. Sometimes we just need to update one or two fields or a custom insert () method, which needs to return the identity (self-added) field value of the record that you just inserted.

To create such a custom method, return to the DataSet Designer. Press the right mouse on the TableAdapter, select Add Query, and then go back to the TableAdapter Configuration Wizard. On the second screen, we can indicate the type of query to be generated. Let's build a method that adds a new product record and then returns a ProductID value for the newly added record. Therefore, you choose 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 InsertCommand CommandText properties. After the query statement, add a query that selects scope_identity (), which returns the last identity value of the Insert Identity field within the current range of operations. (See the technical documentation for SCOPE_IDENTITY () and why you should use Scope_identity () instead of @ @IDENTITY). Make sure that you add a semicolon after the INSERT statement before adding a SELECT statement.


Figure 26: Add query returns SCOPE_IDENTITY () value

Finally, the new method is named Insertproduct.


Figure 27: Put the method name into Insertproduct

When you return to the DataSet Designer, you will see ProductsTableAdapter a new method, Insertproduct. If the new method does not have a corresponding parameter for each field of the Products table, the most likely reason is that you forgot to add a semicolon (semi-colon) to the end of the INSERT statement. Reconfigure the Insertproduct method to confirm that there is a semicolon between the INSERT and SELECT statements.

By default, the Insert method calls a Non-query method, meaning that they only return the number of records affected. However, we want the Insertproduct method to return the value returned by a query, not the number of records affected. This can be achieved by changing the Executemode attribute of the Insertproduct method to scalar (scalar).


Figure 28: Changing the Executemode property 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, "t Ins per carton ",