Operating data 61 in asp.net 2.0: encapsulation of database Modifications in transactions _ self-study process

Introduction:

As we discussed in the 16th chapter, "Overview inserts, updates, and deletes data," the features built in the GridView control support editing and deletion of data for each row, You just have to move your mouse a little bit to create a rich data modification interface without having to write a single line of code. However, in some cases, this is not enough, we need to enable users to process data in batches.

For example, many web-based (web-based) e-mail clients come out and each message contains a CheckBox control in addition to the message information (subject, sender, and so on). These interfaces allow users to delete multiple messages at the same time, and users only need to select messages, click the Delete selected Message button again. It is ideal to provide a batch editing interface when a user wants to edit several different records. We don't have to let the user select a record to edit each time, then make related changes, the last point "update" button, in the batch editing interface, each record has its own editing options, and users can quickly edit multiple records and then click the "Update All" button to save their changes. This series we will examine how to create an interface for adding, editing, and deleting batches of data.

If you want to perform a atomic operation (atomic operation) on a batch, the first thing you do is either succeed or fail, and extend the data access layer to support database transactions (DB transactions). Database transactions ensure that the atomicity (atomic number) executed by the INSERT, UPDATE, and DELETE statements are placed under the protection of the database transaction. In addition, most contemporary database systems support database transactions.

In this series we'll look at how to expand the data access layer to support database transactions, and then we'll see how to create a page to contain a batch interface for adding, updating, and deleting data, so let's get started.

　　Note: The number of atoms (atomicity) is not necessary when modifying data in a batch transaction. In some cases of batch processing, some modification failures are acceptable for some modifications to succeed. For example, when you delete an e-mail message, some messages have a database error during the deletion process, some messages do not have an error, and a message that doesn't happen The batch process removes it. In this case, we do not need to set the data access layer Dal to support database transactions. In some other cases, however, the number of atoms is crucial. For example, a customer wants to transfer money from one bank account to another, and the following 2 operations must be successful: first, deduct the first account money and then The funds are transferred to the second account. If the first step succeeds, the second step fails, the bank is certainly happy, the client is afraid to go crazy. In a later article we'll create a batch interface for adding, updating, and deleting, and even if you're not going to use database transactions on those pages, I'd like you to follow this article, Extend one support database transaction to the data access layer.

Transactions Overview

The vast majority of databases support transactions, which can treat multiple database commands as a logical unit. The commands that contain the transaction either execute successfully or fail.

In general, transactions are performed through SQL commands, using the following pattern:

1. DECLARE transaction start
2. Execute the SQL commands that make up the transaction
3. Perform transaction rollback if any of the commands in step two are wrong (rollback the transaction)
4. If all the commands in step two are executed successfully, commit the transaction

These SQL commands can be entered in handwritten form, such as writing SQL scripts, creating stored procedures, or programmatically, such as using ado.net technology or invoking System.Transactions Namespace a class of namespaces. In this article, we only examine the use of ado.net technology management Services. In the following tutorial we'll look at how to use stored procedures in the data access layer, and then we'll examine the SQL commands for creating, rolling back, submitting things. Also, for more information, please refer to the article "Managing Transactions in SQL Server Stored procedures" (http://www.4guysfromrolla.com/webtech/ 080305-1.shtml)

　　Note: The TransactionScope class class of the System.Transactions namespace namespace allows developers to programmatically obtain a series of commands in a transaction and allow the transaction to contain multiple data sources, or even a different type , such as Microsoft SQL Server database, or Oracle database, or even Web service. In this tutorial we use ado.net technology rather than TransactionScope class classes, This is because ado.net specifies the database transaction in more detail and in many cases consumes less resources. In addition, in some cases, TransactionScope class classes are used with Microsoft distributed Transaction Coordinator (MSDTC), the configuration, execution, and performance issues surrounding MSDTC are more professional, advanced issues that are slightly beyond the scope of this tutorial.

In Ado.net, this method returns a SqlTransaction object object by invoking the SqlConnection class class's BeginTransaction methods method to start the transaction. Put the data operation commands that make up the transaction in a try ... catch area, if a command in the try region fails, the program goes to the catch area, where the transaction rollback is performed through the SqlTransaction object's Rollback method. If all the commands execute successfully, the commit method of the SqlTransaction object object, located at the bottom of the try area, is invoked to commit the transaction. The following code fragment reveals the pattern. To see more examples of using transactions in Ado.net, see the article "Maintaining Database consistency with transactions" (http://aspnet.4guysfromrolla.com/ articles/072705-1.aspx).

Create the SqlTransaction object
sqltransaction mytransaction = sqlconnectionobject.begintransaction ();

Try
{
 * * * ... Perform the database transaction ' s data modification statements
 ... *

 //If We reach this, no errors, so commit the transaction
 mytransaction.commit ();
}
Catch
{
 //If We reach this, there was a error, so rollback the transaction
 mytransaction.rollback (); 
   
    throw;
}


   

By default, the TableAdapters in a strongly typed dataset (Typed DataSet) does not use transactions. To do this, we extend the TableAdapter classes class to include additional methods to execute transactions using the above pattern. In the second step, we'll see how to add these methods using a partial classes class.

First step: Create a page of batch data

Before we examine how to extend the data access layer Dal to support database transactions, let's take a moment to create some asp.net Web pages that we'll use in this chapter and later in chapter three.

Add a new folder named Batchdata, and add the following ASP. NET page, be sure to apply the Site.master template page.

Default.aspx
Transactions.aspx
Batchupdate.aspx
Batchdelete.aspx
Batchinsert.aspx

Figure 1: Adding the relevant pages

Just like the Default.aspx page in other folders, use the Sectionleveltutoriallisting.ascx user control to list the chapters in this section. Drag it from the Solution Explorer onto the Default.aspx page.

Figure 2: Adding the Sectionleveltutoriallisting.ascx user control to the Default.aspx page

Finally add the following code to the Web.sitemap file, and add it to the "customizing" Site Map <siteMapNode> later:

<sitemapnode title= "Working with batched Data" url= "~/batchdata/default.aspx" description= "Learn," perform H operations as opposed to per-row operations. "  > <sitemapnode title= "Adding Support for transactions" Url= "~/batchdata/transactions.aspx" description= " To extend the Data Access Layer to support database transactions. "/> <sitemapnode title=" Batch updating "url="  ~/batchdata/batchupdate.aspx "description=" builds a batch updating interface, where each row in a GridView is editable. /> <sitemapnode title= "Batch deleting" url= "~/batchdata/batchdelete.aspx" description=
 interface for batch deleting by adding a CheckBox to each GridView row. "/> <sitemapnode title=" batch inserting " Url= "~/batchdata/batchinsert.aspx" description= "examine" steps needed to create a batch inserting interface, where
 Multiple records can be created at the click of a button. "/> </siteMapNode>

When finished, take a few minutes to log in to the page in the browser and the menu on the left lists the items in this section

Figure 3:site Map now contains this chapter

Step two: Update the data access layer to support database transactions

As we discussed in the first chapter, "Creating a data access layer," a strongly typed dataset (Typed DataSet) at the data access layer consists of DataTables and TableAdapters. DataTables saves the data, and TableAdapters provides the appropriate way to read the data from the database and update the database according to DataTables changes, and so on. Remember TableAdapters there are 2 modes of updating data--batch Update and Db-direct. In the batch update mode, TableAdapter can pass in a dataset, DataTable, or DataRows set, traversing the data to add, modify, The deleted rows perform the corresponding InsertCommand, UpdateCommand, or DeleteCommand methods. In the case of Db-direct mode, TableAdapter is passing in the values of the columns of a record that need to be added, updated, deleted, and then use those values to perform the associated InsertCommand, UpdateCommand, or DeleteCommand command.

TableAdapter automatically generated methods do not use transactions. By default, each insert, update, or delete operation performed by TableAdapter is treated as a separate, Irrelevant. Assuming that you add 10 records to the database using the Db-direct pattern in the business logic layer BLL, the code will call the TableAdapter insert Method 10 times. If the first 5 records are added correctly, and an exception occurs when the sixth record is added, The first 5 records are still stored in the database. Similarly, using batch update mode to operate, the effect is the same.

In some cases, we want to make sure that the number of atoms (atomicity) is introduced when a series of changes are made. To do this, we have to manually extend the TableAdapter by adding some new methods to InsertCommand, UpdateCommand, and Deletecommands commands are placed under the transaction. In the first chapter, "Creating a data access layer," we examined the use of partial classes (partial classes) for strongly typed datasets (Typed datasets) The function of the DataTable is expanded. The same technique applies to TableAdapter.

Strongly typed DataSet northwind.xsd located in App_ The Dal subfolder of the Code folder. In the Dal folder, create a subfolder named Transactionsupport, and add a new class in it named ProductsTableAdapter.TransactionSupport.cs (see Figure 4). This class contains productstableadapter methods for using transactions.

Figure 4: Create a new folder named Transactionsupport and add a new class named ProductsTableAdapter.TransactionSupport.cs

Type the following code in the ProductsTableAdapter.TransactionSupport.cs file:

Using System;
Using System.Data;
Using System.Data.SqlClient;
Using System.Configuration;
Using System.Web;
Using System.Web.Security;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Web.UI.WebControls.WebParts;

Using System.Web.UI.HtmlControls;
 namespace Northwindtableadapters {public partial class ProductsTableAdapter {private SqlTransaction _transaction;
 Private SqlTransaction Transaction {get {return this._transaction;
 } set {this._transaction = value; } public void BeginTransaction () {//Open the connection, if needed if (this. Connection.state!= ConnectionState.Open) this.

 Connection.Open (); Create the transaction and assign it to the transaction property. Transaction = this.

 Connection.begintransaction (); Attach the transaction to the adapters foreach (SqlCommand command in this.commandcollection) {command. Transaction = this.
 Transaction; } this. Adapter.InsertCommand.Transaction = this.
 Transaction; This. AdaptEr. Updatecommand.transaction = this.
 Transaction; This. Adapter.DeleteCommand.Transaction = this.
 Transaction;

 public void CommitTransaction () {//Commit the transaction this.Transaction.Commit (); Close to the connection this.
 Connection.close (); } public void RollbackTransaction () {//Rollback the transaction this.

 Transaction.rollback (); Close to the connection this.
 Connection.close ();

 }
 }
}

The keyword partial in the class declaration indicates to the compiler that the member (members) added to the code is added to the namespace Northwindtableadapters ProductsTableAdapter Class classes. We notice that there is a using System.Data.SqlClient declaration at the top of the file because the TableAdapter is set to use SqlClient provider, Use a SqlDataAdapter object objects internally to issue commands to the database. Therefore, we need to start the transaction using SqlTransaction class classes, and then commit or rollback the transaction. If Microsoft SQL is not used Server database, you need to invoke the appropriate provider.

These methods are marked public and can be invoked in ProductsTableAdapter, or other classes of the data Access layer DAL, or even other layers such as the business Logic layer BLL.
The BeginTransaction () method opens the internal SqlConnection of the TableAdapter (if needed), opens the transaction and assigns the transaction property, and assigns the transaction (attache) SqlCommand objects object to SqlDataAdapter. The CommitTransaction () and RollbackTransaction () methods invoke the Commit and rollback methods of the Transaction object object, respectively, before closing the internal connection object object.

After adding the above code, we will add a method in the productsdatatable or business logic layer BLL to execute a series of commands placed under the transaction. The following code uses a transaction to update a Productsdatatable instance instance in batch update pattern mode. It calls the BeginTransaction method to start a transaction and then uses a try ... catch module to publish data change commands. If an exception is made to the Update method that invokes the adapter object, it will go to the catch area and rollback the transaction. Remember to perform batch Update The pattern-mode Update method traverses all rows in the productsdatatable and executes the corresponding InsertCommand, UpdateCommand, and Deletecommands commands. If one of these commands has an exception, the transaction rolls back and revokes the changes made in the transaction. If the update command performs no exceptions, then commits the transaction.

public int updatewithtransaction (northwind.productsdatatable dataTable)
{this
 . BeginTransaction ();

 Try
 {
 //Perform the update on the DataTable
 int returnvalue = this. Adapter.Update (dataTable);

 If we reach this, no errors, so commit the transaction
 this.committransaction ();

 return returnvalue;
 }
 Catch
 {
 //If We have here, there is a error, so rollback the transaction
 . RollbackTransaction ();

 Throw
 }
}

Add the above Updatewithtransaction () method to the ProductsTableAdapter class class in the file ProductsTableAdapter.TransactionSupport.cs. In addition, you can add this method to the business logic Layer's PRODUCTSBLL class classes, but make some modifications: this. BeginTransaction (), This.committransaction (), and this. The keyword "This" in the method in RollbackTransaction () is replaced with "Adapter" (we know that the ProductsTableAdapter Name property in the Productsbll class is Adapter).

The Updatewithtransaction () method uses the batch update mode, but it can also invoke Db-direct mode in a transaction, as shown in the following code. The Deleteproductswithtransaction () method accepts a list<t> of type int; The productids that is to be deleted. The method starts the transaction by calling BeginTransaction, and then calls the Delete method of the Db-direct mode for each ProductID value in the Try module. If any call to delete fails, it goes to the catch module, the transaction is rolled back, and if all calls to delete are successful, the transaction is committed. Add this method to PRODUCTSBLL class classes.

public void Deleteproductswithtransaction
 (system.collections.generic.list<int> productids)
{
 / /Start The transaction
 adapter.begintransaction ();

 Try
 {
 //Delete each product specified in the list
 foreach (int productID in productids)
 {
 ADAPTER.D Elete (ProductID);
 }

 Commit the transaction
 adapter.committransaction ();
 }
 Catch
 {
 //There was a error-rollback the transaction
 adapter.rollbacktransaction ();

 Throw
 }
}

Applying transactions in multiple TableAdapters

So far we have been examining the use of atomic operations on multiple commands in the productstableadapter. What if we make changes to several different database tables and perform atomic operations on those changes? For example, when deleting a category, we want to assign the corresponding products to the other category before deleting it. For this 2-step operation-Assigning products and deleting category-- Atomic operations should be performed. But ProductsTableAdapter only contains methods for modifying the Products table, and Categoriestableadapter only contains methods to modify the Categories table. Then how to use a transaction that contains these 2 TableAdapters It?

One option is to add a categoriestableadapter named Deletecategoryandreassignproducts (Categoryidtodelete, Reassigntocategoryid) to the . Then define a method to invoke a stored procedure that uses transactions to allocate products and delete category. We'll look at the following to start, commit, and rollback transactions in a stored procedure.

Another approach is to add a class to the data access layer to contain deletecategoryandreassignproducts (Categoryidtodelete, Reassigntocategoryid) Method. This method creates an instance of the Categoriestableadapter and the ProductsTableAdapter and sets the connection properties of these 2 TableAdapters to the same SqlConnection instance. In this way, they all invoke BeginTransaction to open the transaction. Then execute the method of assigning products and deleting category in the Try...catch module, and finally committing or rolling back the transaction.

Step Fourth: Add the Updatewithtransaction method to the business logic layer

In the third step, we add a updatewithtransaction method to the ProductsTableAdapter in the data Access layer DAL. We will add the appropriate method to the business logic layer. Although the presentation layer can call the Updatewithtransaction method directly to the DAL, we still separate them here.

Open the PRODUCTSBLL class classes and add a method named Updatewithtransaction The method simply invokes the corresponding Dal method. Now there are 2 methods in the PRODUCTSBLL class: The Updatewithtransaction method--the one we added; and deleteproductswithtransaction-- We added in the third step.

public int updatewithtransaction (northwind.productsdatatable products)
{
 return Adapter.updatewithtransaction (products);
}


public void Deleteproductswithtransaction
 (system.collections.generic.list<int> productids)
{
 / /Start The transaction
 adapter.begintransaction ();

 Try
 {
 //Delete each product specified in the list
 foreach (int productID in productids)
 Adapter.dele Te (ProductID);

 Commit the transaction
 adapter.committransaction ();
 }
 Catch
 {
 //There was a error-rollback the transaction
 adapter.rollbacktransaction ();

 Throw
 }
}

　　Note: Most methods in the root PRODUCTSBLL class are different, and the above method does not contain the Dataobjectmethodattribute attribute. This is because we will call these methods directly in the background code of the ASP.net page, and remember that the Dataobjectmethodattribute method is meant to indicate which methods should appear on some labels of the ObjectDataSource control's Settings Data Source Wizard (SELECT, UPDATE, INSERT, or delete). Because the GridView control lacks built-in features that support "batch edit" or "batch deletion," We will invoke these methods in a way that is edited.

Step Fifth: Update database data at the presentation layer

To illustrate the role of transactions when updating a batch of records, we will create a user interface to display all products with a GridView control and include a button Web control. When the button is clicked, a valid CategoryID value is assigned to the product. Specifically, the rival products are assigned a valid CategoryID value, while the remainder assigns an invalid (non-existent) CategoryID value when we try to product--on such a Its CategoryID value does not match the existing category CategoryID-when updated, the foreign key constraint is violated. An exception is thrown. In the example in this article, you will see that when a transaction is used, an exception that violates the FOREIGN KEY constraint causes the previous operation to correctly assign the CategoryID value to produce a rollback. If you do not use a transaction, these correct actions will execute successfully.

First, open the Transactions.aspx page in the Batchdata folder and drag a GridView control from the toolbox to the page. Set its ID as a products, bind it from its smart tag to a ObjectDataSource control named Productsdatasource, and set the control to invoke the GetProducts () method of the Productsbll class class. Since the GridView is "Read Only", click "(None)" In the update, INSERT, and Delete tabs to finish.

Figure 5: Setting the GetProducts method for ObjectDataSource using PRODUCTSBLL class classes

Figure 6: Select (None) in Update, INSERT, and delete tags

When Setup is complete, Visual studio automatically adds BoundFields and a checkboxfield, deleting columns other than ProductID, ProductName, CategoryID, and CategoryName And rename the HeaderText properties of the ProductName and CategoryName columns to "Product" and "Category". Enable the paging feature in smart tags. After these modifications, the GridView and The Declaration code for the ObjectDataSource control should look similar to the following:

 <asp:gridview id= "Products" runat= "server" allowpaging= "True" autogeneratecolumns= "False" datakeynames= "ProductID" datasourceid= "Productsdatasource" > <Columns> <asp : BoundField datafield= "ProductID" headertext= "ProductID" insertvisible= "False" readonly= "True" sortexpression= " ProductID "/> <asp:boundfield datafield= ProductName" headertext= "Product" sortexpression= "ProductName"/> & Lt;asp:boundfield datafield= "CategoryID" headertext= "CategoryID" sortexpression= "CategoryID"/> <asp: BoundField datafield= "CategoryName" headertext= "Category" sortexpression= "CategoryName"/> </Columns> </ asp:gridview> <asp:objectdatasource id= "Productsdatasource" runat= "Server" oldvaluesparameterformatstring= " original_{0} "selectmethod=" GetProducts typename= "PRODUCTSBLL" > </asp:ObjectDataSource> 

Then, add 3 button Web controls on the GridView control, set the Text property of the first button to "Refresh Grid", and the Text property of the second button is "Modify Categories (with TRANSACTION)" The Text property of the third button is "Modify Categories (without TRANSACTION)".

<p>
 <asp:button id= "Refreshgrid" runat= "Server" text= "Refresh Grid"/>
</p>
<p>
 <asp:button id= "modifycategorieswithtransaction" runat= "Server"
 text= "Modify Categories (with TRANSACTION)"/>
</p>
<p>
 <asp:button id= " Modifycategorieswithouttransaction "runat=" Server "
 text=" Modify Categories (without TRANSACTION) "/>
</p>

At this point, in Visual Studio design mode, the interface looks similar to the following screenshot:

Figure 7: The page contains a GridView control and three button Web controls

Create an event handler for the Click events event for these 3 buttons, as follows:

protected void Refreshgrid_click (object sender, EventArgs e) {products.databind ();} protected void Modifycategorieswithtransaction_click (object sender, EventArgs e) {//Get the set of products PRODUCTSBL
 L Productsapi = new PRODUCTSBLL ();

 Northwind.productsdatatable products = productsapi.getproducts (); Update each of the product ' s CategoryID foreach (Northwind.productsrow product in products) {product. CategoryID = Product.
 ProductID;

 }//Update the data using a transaction productsapi.updatewithtransaction (products);
Refresh the Grid products.databind (); } protected void Modifycategorieswithouttransaction_click (object sender, EventArgs e) {//Get-the set of products Prod
 UCTSBLL Productsapi = new PRODUCTSBLL ();

 Northwind.productsdatatable products = productsapi.getproducts (); Update each of the product ' s CategoryID foreach (Northwind.productsrow product in products) {product. CategoryID = Product.
 ProductID;
 }//Update the data without using a transactionNorthwindtableadapters.productstableadapter productsadapter = new Northwindtableadapters.productstableadapter ();

 Productsadapter.update (products);
Refresh the Grid products.databind ();

 }

The Click event handler for the Refresh button simply invokes the DataBind method of the products gridview to rebind the data to the Ridview control.

The second event handler assigns a value to the CategoryID property of the products. and call the new transaction method in the BLL layer to perform the database update. We note that assigning the ProductID value of each product to its CategoryID property has no problem with the first few products, but as the ProductID value becomes larger, the CategoryID value becomes larger, and the Categ After all, the categories defined in the Ory are limited, so the problem comes out.

The third event handler also assigns the ProductID value to the CategoryID property, but updates the database using the ProductsTableAdapter default Update method. The Update method does not use transactions to encapsulate these commands, so it succeeds only if updates that do not violate the FOREIGN KEY constraint are executed.

Log in to the page in the browser to verify. In the beginning you will see the picture shown in Figure 8, then point to "Modify Categories (with TRANSACTION)". This will cause the page to be returned and the question update the CategoryID value of all products. This will result in a violation of the FOREIGN KEY constraint (see Figure 9).

Figure 8:products will be displayed in a page-splitting GridView control

Figure 9: Causing violation of FOREIGN key constraints

Now click on the Back button of the browser and click on the "Refresh Grid" button, and the interface you see is the same as the one in Figure 8. This is because a violation of the FOREIGN KEY constraint has occurred, causing the rollback, and all operations failed.

The Modify Categories (without TRANSACTION) button will also violate the FOREIGN KEY constraint (see Figure 9), but this time, those operations that assign a valid value to the CategoryID property are not rolled back. Click the browser Back button, then click Refresh the Grid button. As shown in Figure 10, the CategoryID value of the first 8 products has changed, for example, in Figure 8, Chang's CategoryID value is 1, and in Figure 10 it becomes 2.

Figure 10: Some of the product's CategoryID values have changed, while the others are not

Conclusion:

By default, the TableAdapter method does not use transactions to execute database commands, but with just a little more work we can add a few ways to create, commit, and roll back transactions. In this tutorial, we are in ProductsTableAdapter The 3 methods are created in the class class: BeginTransaction, CommitTransaction, and rollbacktransaction. We examined how to do this in a try ... The catch module uses these methods to perform a series of modification commands. Specifically, we created the Updatewithtransaction method in ProductsTableAdapter, which employs batch The update mode performs the necessary changes for each row of records in the productsdatatable; we also add deleteproductswithtransaction methods to the PRODUCTSBLL class classes in BLL. It takes a series of ProductID values as input parameters and deletes each product using the Db-direct mode. These methods begin to create a transaction and then execute the data Change command in the Try...catch module. If an exception is thrown, the transaction is rolled back, otherwise the transaction is committed.

The fifth step demonstrates the role of the transaction. In the next 3 chapters we will create a batch update, batch deletion, batch-added user interface based on this chapter.

I wish you a happy programming!

Author Introduction

The author of this series of tutorials, Scott Mitchell, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.