Using Manual Transactions

[ Team LiB ] Recipe 6.2 Using Manual Transactions Problem You need to explicitly begin, control, and end a transaction within a .NET application. Solution Use the Connection object with structured exceptions (try . . . catch . . . finally).
Nội dung trích xuất từ tài liệu:
Using Manual Transactions[ Team LiB ]Recipe 6.2 Using Manual TransactionsProblemYou need to explicitly begin, control, and end a transaction within a .NET application.SolutionUse the Connection object with structured exceptions (try . . . catch . . . finally).The sample code contains two event handlers:Form.Load Sets up the sample by filling a DataTable with the Categories table from the Northwind sample database. The default view of the table is bound to a data grid on the form.Insert Button.Click Inserts user-entered data for two Categories records into the Northwind database within a manual transaction. If either record insert fails, both inserts are rolled back; otherwise, both record inserts are committed.The C# code is shown in Example 6-3.Example 6-3. File: ManualTransactionForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Windows.Forms;using System.Data;using System.Data.SqlClient;private const String CATEGORIES_TABLE = Categories;private DataTable dt;private SqlDataAdapter da;// . . .private void ManualTransactionForm_Load(object sender, System.EventArgs e){ // Fill the categories table. String sqlText = SELECT CategoryID, CategoryName, + Description FROM Categories; da = new SqlDataAdapter(sqlText, ConfigurationSettings.AppSettings[Sql_ConnectString]); dt = new DataTable(CATEGORIES_TABLE); da.FillSchema(dt, SchemaType.Source); da.Fill(dt); // Bind the default view of the table to the grid. dataGrid.DataSource = dt.DefaultView;}private void insertButton_Click(object sender, System.EventArgs e){ String sqlText = INSERT + CATEGORIES_TABLE + + (CategoryName, Description) VALUES + (@CategoryName, @Description); // Create the connection. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings[Sql_ConnectString]); // Create the transaction. conn.Open( ); SqlTransaction tran = conn.BeginTransaction( ); // Create command in the transaction with parameters. SqlCommand cmd = new SqlCommand(sqlText, conn, tran); cmd.Parameters.Add(new SqlParameter(@CategoryName, SqlDbType.NVarChar, 15)); cmd.Parameters.Add(new SqlParameter(@Description, SqlDbType.NVarChar, 100)); try { // Insert the records into the table. if (categoryName1TextBox.Text.Trim( ).Length == 0) // If CategoryName is empty, make it null (invalid). cmd.Parameters[@CategoryName].Value = DBNull.Value; else cmd.Parameters[@CategoryName].Value = categoryName1TextBox.Text; cmd.Parameters[@Description].Value = description1TextBox.Text; cmd.ExecuteNonQuery( ); if (categoryName2TextBox.Text.Trim( ).Length == 0) cmd.Parameters[@CategoryName].Value = DBNull.Value; else cmd.Parameters[@CategoryName].Value = categoryName2TextBox.Text; cmd.Parameters[@Description].Value = description2TextBox.Text; cmd.ExecuteNonQuery( ); // If okay to here, commit the transaction. tran.Commit( ); MessageBox.Show(Transaction committed.); } catch (Exception ex) { // Exception occurred. Roll back the transaction. tran.Rollback( ); MessageBox.Show(ex.Message + Environment.NewLine + Transaction rollback.); } finally { conn.Close( ); } // Refresh the data. da.Fill(dt);}DiscussionManual transactions allow control over the transaction boundary through explicitcommands to start and end the transaction. There is no built-in support for distributedtransactions spanning multiple resources with manual transactions..NET data providers make available objects to enable manual transactions. TheConnection object has a BeginTransaction( ) method that is used to start a transaction. Ifsuccessful, the method returns a Transaction object that is used to perform all subsequentactions associated with the transaction, such as committing or aborting. Calling theBeginTransaction( ) method does not implicitly cause all subsequent commands toexecute within the transaction. The Transaction property of the Command object must beset to a transaction that has already been started for the command to execute within thetransaction.Once started, the transaction remains in a pending state until it is explicitly committed orrolled back using the Commit( ) or Rollback( ) methods of the Transaction object. TheCommit( ) method of the Transaction is used to commit the database transaction. TheRollback( ) method of the Transaction is used to roll back a database transaction from apending state. An InvalidOperationException will be raised if Rollback( ) is called afterCommit( ) has been called.The isolation level of the transaction can be specified through an overload of theBeginTransaction( ) method and if it is not specified, the default isolation levelReadCommitted is used.Unlike automatic transactions, manual transactions must be explicitly committed orrolled back using the Commit( ) or Rollback( ) method. If possible, use the .NET dataprovider transaction management exclusively; avoid using other transaction models, suchas the one provided by SQL Server. If this is necessary for any reason, Recipe 6.3discusses using the SQL Server transaction model together with the .NET SQL Serverdata provider transaction management.The IDbTransaction interface is implemented by .NET data providers that accessrelational databases. Applications create an instance of the class implementing theIDbTransaction interface rather than creating an instance of the interface directly. Classesthat inherit IDbTransaction must implement the inherited members and typically defineprovider-specific functionality by add ...