Shulou(Shulou.com)06/02 Report--

ASP.NET 2.0 how to insert, update and delete data, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

ASP.NET 2.0 data tutorial step 4: insert, update, and delete data

There are two common modes of inserting, updating, and deleting data in ASP.NET 2.0. * this mode, which I call DB direct mode, will issue an INSERT, or UPDATE, or DELETE command to the database when the method is called. This command only operates on a single database record. Methods like this generally accept a series of scalar parameters (such as integers, strings, Boolean values, dates and times, etc.) corresponding to inserted, updated, or deleted values. For example, if you manipulate the Products table with this schema, the delete method accepts an integer parameter representing the ProductID of the record to be deleted, while the insert method accepts a string corresponding to ProductName, the decimal value of UnitPrice, the 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 batch update mode, can update the entire DataSet, or the entire DataTable, or a DataRow collection in a single method call. In this mode, the developer deletes, inserts, modifies the DataRow in a DataTable, and then passes the DataRow or the entire DataTable to an update method. This method then iterates through the incoming DataRow, uses the RowState property of the DataRow to determine whether the DataRow has been changed, or a new record, or a deleted record, and then issues the appropriate database command for each record.

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

By default in ASP.NET 2.0, TableAdapter uses batch update mode, but also supports DB direct mode. Because we selected the option "generate insert, update, and delete statements" in the advanced options when creating our TableAdapter, ProductsTableAdapter includes a Update () method that implements batch update mode. Specifically, TableAdapter contains a Update () method that can pass in a strongly typed DataSet, or a strongly typed DataTable, or one or more DataRow. If you do not clear the "generate DB Direct method (GenerateDBDirectMethods)" check box in the initial TableAdapter creation options, the DB direct mode will also be implemented through the Insert (), Update () and Delete () methods.

Both data modification modes use the InsertCommand,UpdateCommand and DeleteCommand properties of TableAdapter to issue 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. (make sure you select TableAdapter and that the ProductsTableAdapter object is the selected item in the drop-down box in the properties window)

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

If you want to view or change the properties of these database commands, click the CommandTextchild property, which will launch the corresponding query builder.

Figure 24: configure insert, update, delete statements in query builder

The following coding example demonstrates how to use batch update mode to double the price of a product that has not been 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); 下面的编码示范如何使用DB直接模式删除一个产品，更新一个产品，然后添加一个新的产品： 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); 创建自定义的插入，更新，删除方法 用DB直接法生成的Insert(), Update(),和Delete()方法有时 候会感觉有点不方便，特别是当数据表有许多字段的时候。看一下前面这个编码例子，没有IntelliSense的帮助的话，不是很清楚Products表的哪个字段对应Update()和Insert()方法中的哪个输入参数。有时候我们只要更新一到二个字 段或者需要一个自定义的Insert()方法，这个方法需要返回刚插入的记录 的IDENTITY(自增)的字段值。 要创建这样的自定义方法，回到DataSet设计器。在TableAdapter上按右鼠标，选择"添加查询"，然后回 到TableAdapter配置向导。在第二屏上，我们可以指明要生成的查询的类型。让我们生成一个添加新 的product(产品)记录，然后返回新添加记录的ProductID值的方法。因此，选择生成一个插入(INSERT)型查询。 图25: 创建一个给Products表添加新记录的方法 下一个屏显示InsertCommand的CommandText属性。在查询语句后面，增添一个SELECT SCOPE_IDENTITY()的查询，这查询将返回当前同一个操作范围内插 入IDENTITY字段的***那个identity 值。(详见技术文档中关 于SCOPE_IDENTITY()的内容以及为什么你应该http://weblogs.sqlteam.com/travisl/archive/2003/10/29/405.aspx)。确认在添加SELECT语句前，你在INSERT语句后面添一个分号 。

Figure 26: add query returns SCOPE_IDENTITY () value

*, name this new method InsertProduct.

Figure 27: the name of the placement method is set to InsertProduct

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

By default in ASP.NET 2.0, non-query (non-query) methods are inserted method calls, that is, they return only the number of records affected. However, we want the InsertProduct method to return the value returned by a query instead of the number of records affected. This can be done by changing the ExecuteMode property of the InsertProduct method to Scalar (scalar).

Figure 28: change the ExecuteMode property to Scalar

The following coding 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); is it helpful for you to read the above? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.