Nesting Manual Transactions with the SQL Server .NET Data Provider

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:4

Thêm vào BST

Báo xấu

118
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

[ Team LiB ] Recipe 6.3 Nesting Manual Transactions with the SQL Server .NET Data Provider Problem You need to create a nested transaction using the SQL Server .NET data provider, but the Begin( ) command that you need is only available with the OLE DB .NET data provider.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Nesting Manual Transactions with the SQL Server .NET Data Provider

[ Team LiB ] Recipe 6.3 Nesting Manual Transactions with the SQL Server .NET Data Provider Problem You need to create a nested transaction using the SQL Server .NET data provider, but the Begin( ) command that you need is only available with the OLE DB .NET data provider. The SQL Server data provider appears to provide no built-in support for nested transactions. You want to nest transactions when using it. Solution Simulate nested transactions with savepoints when using the SQL Server .NET data provider, manage and control the lifetime of the SqlTransaction class, and create the required exception handling. 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. A savepoint is created if the first record insert succeeds. If the insert of the second record fails, the transaction is rolled back to the savepoint and the first record insert is committed; otherwise, both record inserts are committed. The C# code is shown in Example 6-4. Example 6-4. File: NestedManualTransactionForm.cs // Namespaces, variables, and constants using 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 NestedTransactionForm_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( ); } catch (Exception ex) { // Exception occurred. Roll back the transaction. tran.Rollback( ); MessageBox.Show(ex.Message + Environment.NewLine + "Transaction rollback (records 1 and 2)."); conn.Close( ); return; } tran.Save("SavePoint1"); try { // Insert the records into the table. if (categoryName2TextBox.Text.Trim( ).Length == 0) // If CategoryName is empty, make it null (invalid). 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 (records 1 and 2)."); } catch (SqlException ex)
{ tran.Rollback("SavePoint1"); tran.Commit( ); MessageBox.Show(ex.Message + Environment.NewLine + "Transaction commit (record 1)." + Environment.NewLine + "Transaction rollback (record 2)."); } finally { conn.Close( ); } // Refresh the data. da.Fill(dt); } Discussion The OLE DB .NET data provider's transaction class OleDbTransaction has a Begin( ) method that is used to initiate a nested transaction. A nested transaction allows part of a transaction to be rolled back without rolling back the entire transaction. An InvalidOperationException is raised if the OLE DB data source does not support nested transactions. The SQL Server .NET data provider's transaction class SqlTransaction does not have a Begin( ) method to initiate a nested transaction. Instead, it has a Save( ) method that creates a savepoint in the transaction that can later be used to roll back a portion of the transaction—to the savepoint rather than rolling back to the start of the transaction. The savepoint is named using the only argument of the Save( ) method. An overload of the Rollback( ) method of the SqlTransaction class accepts an argument that you can use to specify the name of the savepoint to roll back to. [ Team LiB ]

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN

TRỢ GIÚP

HỖ TRỢ KHÁCH HÀNG

Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn