C# SqlTransaction.Dispose方法代码示例

 public static void TryForceDisposeTransactionAndConnection(SqlTransaction transaction)
 {
     if (transaction != null)
     {
         SqlConnection connection = null;
         try
         {
             connection = transaction.Connection;
             transaction.Dispose();
         }
         catch (Exception) { }
         if (connection != null)
         {
             try
             {
                 connection.Close();
             }
             catch (Exception) { }
             try
             {
                 connection.Dispose();
             }
             catch (Exception) { }
         }
     }
 }

        public SubmitOrderResult SaveOrder(SubmitOrderRequest request)
        {
            var result = new SubmitOrderResult();
            orderNumber = request.OrderNumber;

            try
            {
                trans = cn.BeginTransaction();

                var orderId = InsertOrderRecord(request);

                foreach (var orderLineItem in request.LineItems)
                {
                    InsertLineItems(orderId, orderLineItem);
                }

                trans.Commit();
                trans.Dispose();
                trans = null;

                result = GetOrderResults();
            }
            catch (SqlException ex)
            {
                result.HasException = true;
                result.Exception = ExceptionFactory.BuildSqlException(ex);
            }
            catch (Exception ex)
            {
                result.HasException = true;
                result.Exception = ExceptionFactory.BuildSystemException(ex);
            }
            finally
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                }
                cn.Close();
                cn.Dispose();
            }

            return result;
        }

 /// <summary>
 /// 保存前执行的方法
 /// </summary>
 /// <returns></returns>
 public override bool DoBeforeSave()
 {
     bool result = false;
     //非空验证
     if (dsMain.Current != null)
     {
         //主表非空验证
         foreach (DataRow dr in DynamicMasterTableData.Select("bSaveData=1 AND bNotNull=1"))
         {
             if (string.IsNullOrEmpty(((DataRowView)dsMain.Current).Row[dr["sFieldName"].ToString()].ToString()))
             {
                 string sMsg = string.Format("{0} {1}", LangCenter.Instance.IsDefaultLanguage ? dr["sCaption"].ToString() : dr["sEngCaption"].ToString(),
                               LangCenter.Instance.GetSystemMessage("NotNull"));
                 Public.SystemInfo(sMsg);
                 return false;
             }
         }
     }
     if (SqlTrans != null)
         SqlTrans.Dispose();
     SqlTrans = ConnectSetting.SysSqlConnection.BeginTransaction();
     try
     {
         result = DoBeforceSaveInTrans(SqlTrans);
     }
     catch
     {
         SqlTrans.Rollback();
         //回收Trans
         if (SqlTrans != null)
             SqlTrans.Dispose();
         return false;
     }
     return result;
 }

 public void CommitTransaction(SqlTransaction transaction)
 {
     if (Equals(transaction, null)) return;
     var connection = transaction.Connection;
     transaction.Commit();
     transaction.Dispose();
     connection.Close();
 }

    /// <summary>
    /// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
    /// using the provided parameters.
    /// </summary>
    /// <remarks>
    /// e.g.:  
    ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
    /// </remarks>
    /// <param name="transaction">A valid SqlTransaction</param>
    /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
    /// <param name="commandText">The stored procedure name or T-SQL command</param>
    /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
    /// <returns>An int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery_Trans(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (transaction == null)
        {
            throw new ArgumentNullException("transaction");
        }
        if (transaction != null && transaction.Connection == null)
        {
            throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        }

        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        bool mustCloseConnection = false;
        PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

        int retval;
        try
        {
            // Finally, execute the command
            retval = cmd.ExecuteNonQuery();
            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
        finally
        {
            transaction.Dispose();
        }
        return retval;
    }

 /// <summary>
 /// 执行一条返回第一条记录第一列的SqlCommand命令，通过已经存在的数据库连接。
 /// 使用参数数组提供参数
 /// </summary>
 /// <remarks>
 /// 使用示例：
 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
 /// </remarks>
 /// <param name="conn">一个已经存在的数据库连接</param>
 /// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
 /// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
 /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
 /// <returns>返回一个object类型的数据，可以通过 Convert.To{Type}方法转换类型</returns>
 public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
 {
     SqlCommand cmd = new SqlCommand();
     try
     {
         PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
         return cmd.ExecuteScalar();
     }
     catch
     {
         cmd.Parameters.Clear();
         trans.Dispose();
         throw;
     }
 }

        public void postRequest(int RequestorID, int SupervisorID, int VoucherType, string Descriptions,
            int Currency, double InvoiceAmount, double AdvanceUsed, double CashReturn, DateTime RequiredDate, 
            DateTime ExpectedReturnDate, string VendorPartnerID, string VendorPartnerName, string InvoiceNo,
            DateTime InvoiceDate, string PONumber, string OnBehalfID, string OnBehalfName, int Location, 
            int BudgetHolderID)
        {
            if (hasDetails == false) return;

            sqlStr = "INSERT INTO Request (RequestorID, SupervisorID, VoucherTypeID," +
            " Descriptions, CurrencyID, InvoiceAmount, AdvanceUsed, CashReturn, RequiredDate," +
            " ExpectedReturnDate, VendorPartnerID, VendorPartnerName, InvoiceNo, InvoiceDate," +
            " PONumber, OnBehalfID, OnBehalfName, LocationID, BudgetHolderID)" +
            " VALUES (@RequestorID, @SupervisorID, @VoucherTypeID, @Descriptions," +
            " @CurrencyID, @InvoiceAmount, @AdvanceUsed, @CashReturn, @RequiredDate," +
            " @ExpectedReturnDate, @VendorPartnerID, @VendorPartnerName, @InvoiceNo, @InvoiceDate," +
            " @PONumber, @OnBehalfID, @OnBehalfName, @LocationID, @BudgetHolderID)";

            sqlCmd = new SqlCommand(sqlStr);

            sqlCmd.Parameters.Add("@RequestorID", SqlDbType.Int);
            sqlCmd.Parameters["@RequestorID"].Value = RequestorID;
            sqlCmd.Parameters.Add("@SupervisorID", SqlDbType.Int);
            sqlCmd.Parameters["@SupervisorID"].Value = SupervisorID;
            sqlCmd.Parameters.Add("@VoucherTypeID", SqlDbType.Int);
            sqlCmd.Parameters["@VoucherTypeID"].Value = VoucherType;
            sqlCmd.Parameters.Add("@Descriptions", SqlDbType.VarChar);
            sqlCmd.Parameters["@Descriptions"].Value = Descriptions;
            sqlCmd.Parameters.Add("@CurrencyID", SqlDbType.Int);
            sqlCmd.Parameters["@CurrencyID"].Value = Currency;
            sqlCmd.Parameters.Add("@InvoiceAmount", SqlDbType.Decimal);
            sqlCmd.Parameters["@InvoiceAmount"].Value = InvoiceAmount;
            sqlCmd.Parameters.Add("@AdvanceUsed", SqlDbType.Decimal);
            sqlCmd.Parameters["@AdvanceUsed"].Value = AdvanceUsed;
            sqlCmd.Parameters.Add("@CashReturn", SqlDbType.Decimal);
            sqlCmd.Parameters["@CashReturn"].Value = CashReturn;
            sqlCmd.Parameters.Add("@RequiredDate", SqlDbType.DateTime);
            sqlCmd.Parameters["@RequiredDate"].Value = RequiredDate;
            sqlCmd.Parameters.Add("@ExpectedReturnDate", SqlDbType.DateTime);
            sqlCmd.Parameters["@ExpectedReturnDate"].Value = ExpectedReturnDate;
            sqlCmd.Parameters.Add("@VendorPartnerID", SqlDbType.VarChar);
            sqlCmd.Parameters["@VendorPartnerID"].Value = VendorPartnerID;
            sqlCmd.Parameters.Add("@VendorPartnerName", SqlDbType.VarChar);
            sqlCmd.Parameters["@VendorPartnerName"].Value = VendorPartnerName;
            sqlCmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar);
            sqlCmd.Parameters["@InvoiceNo"].Value = InvoiceNo;
            sqlCmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime);
            sqlCmd.Parameters["@InvoiceDate"].Value = InvoiceDate;
            sqlCmd.Parameters.Add("@PONumber", SqlDbType.VarChar);
            sqlCmd.Parameters["@PONumber"].Value = PONumber;
            sqlCmd.Parameters.Add("@OnBehalfID", SqlDbType.VarChar);
            sqlCmd.Parameters["@OnBehalfID"].Value = OnBehalfID;
            sqlCmd.Parameters.Add("@OnBehalfName", SqlDbType.VarChar);
            sqlCmd.Parameters["@OnBehalfName"].Value = OnBehalfName;
            sqlCmd.Parameters.Add("@LocationID", SqlDbType.Int);
            sqlCmd.Parameters["@LocationID"].Value = Location;
            sqlCmd.Parameters.Add("@BudgetHolderID", SqlDbType.Int);
            sqlCmd.Parameters["@BudgetHolderID"].Value = BudgetHolderID;

            sqlCon = new SqlConnection(cnStr);
            sqlCon.Open();
            //SqlTransaction sTrn;

            sTrn = sqlCon.BeginTransaction();

            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.Connection = sqlCon;
            sqlCmd.Transaction = sTrn;
            sqlCmd.ExecuteNonQuery();

            string RID = excScalar("SELECT IDENT_CURRENT('[PowerPayment].[dbo].[Request]')");

            sqlStr = "INSERT INTO [PowerPayment].[dbo].RequestDetails " +
            "SELECT " + RID + ", LineOrder, Narrative, Account, CostCenter, Project, SOF, " +
            "DEA, Analysis, Amount FROM PowerPayment.dbo.RequestDetailsTemp WHERE EmpID = '" + RequestorID + "'";
            sqlCmd = new SqlCommand(sqlStr);
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.Connection = sqlCon;
            sqlCmd.Transaction = sTrn;
            sqlCmd.ExecuteNonQuery();

            sTrn.Commit();
            sTrn.Dispose();

            sqlCon.Close();
            sqlCon.Dispose();

            //string sSQL = "DELETE FROM [PowerPayment].[dbo].[RequestDetailsTemp] WHERE EmpID='" + Convert.ToInt32(hfEID.Value) + "'";
            //dbo.ExectuteQuery(sSQL);

            // string sqlStr = "INSERT INTO PowerPayment.dbo.RequestDetailsTemp" +
            //"(EmpID, LineOrder, Narrative, Account, CostCenter, Project, SOF, DEA, Analysis, Amount) VALUES" +
            //"('" + Convert.ToInt32(hfEID.Value) + "','" + lOrder + "','" + tbxNarrative.Text.Replace("'", "") + "'," +
            //"'" + tbxAccount.Text.Replace("'", "") + "','" + cbxCostCenter.SelectedValue.Replace("'", "") + "','" + cbxProject.SelectedValue.Replace("'", "") + "'," +
            //"'" + cbxSOF.SelectedValue.Replace("'", "") + "','" + cbxDEA.SelectedValue.Replace("'", "") + "','" + tbxAnalysis.Text.Replace("'", "") + "','" + tbxAmount.Text + "')";
            //            dbo.ExectuteQuery(sqlStr);
        }

 private void TryDisposeTransactionAndConnection(SqlTransaction transaction)
 {
     if (transaction != null)
     {
         try
         {
             transaction.Dispose();
         }
         catch (Exception) { }
         if (transaction.Connection != null)
         {
             try
             {
                 transaction.Connection.Dispose();
             }
             catch (Exception) { }
         }
     }
 }

		public void Dispose ()
		{
			string sql;
			SqlCommand cmd = null;

			try {
				conn = new SqlConnection (connectionString);
				conn.Open ();

				trans = conn.BeginTransaction ();

				sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
				cmd = new SqlCommand (sql, conn, trans);
				cmd.ExecuteNonQuery ();
				cmd.Dispose ();

				trans.Save ("SAVE1");

				sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6667, 'BangaloreNovell', '1999-03-10', '2006-08-23')";
				cmd = new SqlCommand (sql, conn, trans);
				cmd.ExecuteNonQuery ();
				cmd.Dispose ();

				trans.Dispose ();
				trans.Dispose ();

				cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
				using (SqlDataReader reader = cmd.ExecuteReader ()) {
					Assert.IsFalse (reader.Read (), "#1");
				}

				cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6667", conn);
				using (SqlDataReader reader = cmd.ExecuteReader ()) {
					Assert.IsFalse (reader.Read (), "#2");
				}
			} finally {
				if (cmd != null)
					cmd.Dispose ();
				if (trans != null)
					trans.Dispose ();
				if (conn != null)
					conn.Close ();

				conn = new SqlConnection (connectionString);
				conn.Open ();
				DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
			}
		}

		public void Connection_Transaction_Rolledback ()
		{
			if (RunningOnMono)
				Assert.Ignore ("NotWorking");

			conn = new SqlConnection (connectionString);
			conn.Open ();

			trans = conn.BeginTransaction ();
			try {
				trans.Rollback ();
				Assert.IsNull (trans.Connection);
			} finally {
				trans.Dispose ();
			}

			trans = conn.BeginTransaction ();
			trans.Save ("SAVE1");
			try {
				trans.Rollback ("SAVE1");
				Assert.AreSame (conn, trans.Connection);
			} finally {
				trans.Dispose ();
			}
		}

		public void Connection_Transaction_Disposed ()
		{
			conn = new SqlConnection (connectionString);
			conn.Open ();

			trans = conn.BeginTransaction ();
			trans.Dispose ();

			Assert.IsNull (trans.Connection);
		}

		public void Commit_Transaction_Disposed ()
		{
			try {
				conn = new SqlConnection (connectionString);
				conn.Open ();

				using (trans = conn.BeginTransaction ()) {
					string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
					SqlCommand cmd = new SqlCommand (sql, conn, trans);
					cmd.ExecuteNonQuery ();
					cmd.Dispose ();

					trans.Dispose ();

					try {
						trans.Commit ();
						Assert.Fail ("#A1");
					} catch (InvalidOperationException ex) {
						// This SqlTransaction has completed; it is no
						// longer usable
						Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
						Assert.IsNull (ex.InnerException, "#A3");
						Assert.IsNotNull (ex.Message, "#A4");
					}

					cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
					using (SqlDataReader reader = cmd.ExecuteReader ()) {
						Assert.IsFalse (reader.Read (), "#B1");
					}
				}
			} finally {
				if (conn != null)
					conn.Close ();

				conn = new SqlConnection (connectionString);
				conn.Open ();
				DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
			}
		}

		public void Save_TransactionName_Null ()
		{
			if (RunningOnMono)
				Assert.Ignore ("NotWorking");

			try {
				conn = new SqlConnection (connectionString);
				conn.Open ();

				trans = conn.BeginTransaction ();

				string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
				SqlCommand cmd = new SqlCommand (sql, conn, trans);
				cmd.ExecuteNonQuery ();
				cmd.Dispose ();

				try {
					trans.Save ((string) null);
					Assert.Fail ("#A1");
				} catch (ArgumentException ex) {
					// Invalid transaction or invalid name
					// for a point at which to save within
					// the transaction
					Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#A2");
					Assert.IsNull (ex.InnerException, "#A3");
					Assert.IsNotNull (ex.Message, "#A4");
					Assert.IsNull (ex.ParamName, "#A5");
				}

				trans.Commit ();
				conn.Close ();

				conn = new SqlConnection (connectionString);
				conn.Open ();

				cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
				using (SqlDataReader reader = cmd.ExecuteReader ()) {
					Assert.IsTrue (reader.Read (), "#B1");
					Assert.AreEqual ("NovellBangalore", reader.GetString (0), "#B2");
					Assert.IsFalse (reader.Read (), "#B3");
				}
			} finally {
				if (trans != null)
					trans.Dispose ();
				if (conn != null)
					conn.Close ();

				conn = new SqlConnection (connectionString);
				conn.Open ();
				DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
			}
		}

		public void Commit_Reader_Open ()
		{
			if (RunningOnMono)
				Assert.Ignore ("NotWorking");

			SqlCommand cmd;

			conn = new SqlConnection (connectionString);
			conn.Open ();

			trans = conn.BeginTransaction ();

			try {
				string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
				cmd = new SqlCommand (sql, conn, trans);
				cmd.ExecuteNonQuery ();
				cmd.Dispose ();

				cmd = new SqlCommand ("select @@version", conn, trans);
				using (SqlDataReader reader = cmd.ExecuteReader ()) {
					try {
						trans.Commit ();
						Assert.Fail ("#A1");
					} catch (InvalidOperationException ex) {
						// There is already an open DataReader
						// associated with this Command which
						// must be closed first
						Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
						Assert.IsNull (ex.InnerException, "#A3");
						Assert.IsNotNull (ex.Message, "#A4");
					}
				}

				cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn, trans);
				using (SqlDataReader reader = cmd.ExecuteReader ()) {
					Assert.IsTrue (reader.Read (), "#B1");
				}

				trans.Dispose ();
				conn.Close ();

				conn.Open ();

				cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
				using (SqlDataReader reader = cmd.ExecuteReader ()) {
					Assert.IsFalse (reader.Read (), "#C1");
				}
			} finally {
				if (trans != null)
					trans.Dispose ();
				if (conn != null)
					conn.Close ();

				conn = new SqlConnection (connectionString);
				conn.Open ();
				DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
			}
		}

        public void ExecuteInTransaction(Action work)
        {
            _connection.Open();
            _transaction = _connection.BeginTransaction();

            try
            {
                work();
                _transaction.Commit();
            }
            catch
            {
                _transaction.Rollback();
                throw;
            }
            finally
            {
                _connection.Close();

                _transaction.Dispose();
                _transaction = null;
            }
        }