Using ADO.NET and SQL Server DBMS Transactions Together

[ Team LiB ] Recip 6.4 Using ADO.NET and SQL Server DBMS Transactions Together Problem You need to use a DBMS transaction within a SQL Server stored procedure from an ADO.NET transaction with the SQL Server .NET data provider.
Nội dung trích xuất từ tài liệu:
Using ADO.NET and SQL Server DBMS Transactions Together[ Team LiB ]Recipe 6.4 Using ADO.NET and SQL Server DBMS Transactions TogetherProblemYou need to use a DBMS transaction within a SQL Server stored procedure from anADO.NET transaction with the SQL Server .NET data provider.SolutionUse error-checking within a catch block as shown in Example 6-5.The sample uses a single stored procedure:InsertCategories_Transacted Used to insert a single record into the Categories table in the Northwind database within a DBMS transaction. If the record insert fails, the transaction is rolled back; otherwise, the transaction is committed.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 the Categories records into the Northwind database within a manual transaction using a DBMS transacted stored procedure. The transaction is rolled back in the stored procedure if either the Force DBMS Rollback checkbox is checked or if no value is entered for the Category Name field. Otherwise, the ADO.NET manual transaction is committed.Example 6-5. Stored procedure: InsertCategories_TransactedCREATE PROCEDURE InsertCategories_Transacted @CategoryId int output, @CategoryName nvarchar(15), @Description ntext, @Rollback bit = 0AS SET NOCOUNT ON begin tran insert Categories( CategoryName, Description) values ( @CategoryName, @Description) if @@error0 or @@rowcount=0 or @Rollback=1 begin rollback tran set @CategoryID = -1 return 1 end commit tran set @CategoryID = Scope_Identity( ) select @CategoryID CategoryId return 0The C# code is shown in Example 6-6.Example 6-6. File: DbmsTransactionForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Windows.Forms;using System.Data;using System.Data.SqlClient;private SqlDataAdapter da;private DataTable dt;// . . .private void DbmsTransactionForm_Load(object sender, System.EventArgs e){ // Fill the table. String sqlText = SELECT CategoryID, CategoryName, Description + FROM Categories; da = new SqlDataAdapter(sqlText, ConfigurationSettings.AppSettings[Sql_ConnectString]); dt = new DataTable(Categories); 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){ // 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(InsertCategories_Transacted, conn, tran); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(@CategoryID, SqlDbType.Int).Direction = ParameterDirection.Output; cmd.Parameters.Add(@CategoryName, SqlDbType.NVarChar, 15); cmd.Parameters.Add(@Description, SqlDbType.NText); cmd.Parameters.Add(@Rollback, SqlDbType.Bit); try { // Set the parameters to the user-entered values. // Set the CategoryName to DBNull if not entered. if(categoryNameTextBox.Text.Trim( ).Length == 0) cmd.Parameters[@CategoryName].Value = DBNull.Value; else cmd.Parameters[@CategoryName].Value = categoryNameTextBox.Text; cmd.Parameters[@Description].Value = descriptionTextBox.Text; cmd.Parameters[@Rollback].Value = forceDbmsRollbackCheckBox.Checked ? 1 : 0; // Attempt to insert the record. cmd.ExecuteNonQuery( ); // Success. Commit the transaction. tran.Commit( ); MessageBox.Show(Transaction committed.);}catch (SqlException ex){ bool spRollback = false; foreach (SqlError err in ex.Errors) { // Check if transaction rolled back in the // stored procedure. if(err.Number == 266) { MessageBox.Show(ex.Message, DBMS transaction rolled back in + stored procedure, MessageBoxButtons.OK, MessageBoxIcon.Error); spRollback = true; break; } } if (!spRollback) { // transaction was not rolled back by the DBMS // SqlException error. Roll back the transaction. tran.Rollback( ); MessageBox.Show(ex.Message); }}catch (Exception ex){ // Other Exception. Roll back the transaction. tran.Rollback( ); MessageBox.Show(ex.Message);} finally { conn.Close( ); } // Refresh the data. da.Fill(dt);}Discus ...