使用ADO.NET事务

 

// 使用ADO.NET事务

// 使用b、bb、bbb做为新客户

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

 

namespace UseADONetTrans

{

    public partial class Transaction : Form

    {       

        public Transaction()

        {

            InitializeComponent();

        }

 

        private void button1_Click( object sender, EventArgs e)

        {

            string strConn = "data source=.\\MSSQL2012;"

              + "integrated security = true;"

              + "database = Northwind";

            SqlConnection conn = new SqlConnection (strConn);

            // 插入语句

            string sqlIns = "inser into customers(customerid, companyname)"

                          + " values(@newcustid, @newconame)" ;

            // 删除语句

            string sqlDel = "delete from customers "

                          + "where customerid=@oldcustid";           

            try

            {

                // 打开数据库连接

                conn.Open();

                // 开始事务

                SqlTransaction trans = conn.BeginTransaction();

                // 创建插入命令

                SqlCommand cmdins = conn.CreateCommand();

                cmdins.CommandText = sqlIns;

                cmdins.Transaction = trans;

                cmdins.Parameters.Add( "@newcustid", System.Data.SqlDbType.NVarChar, 5);

                cmdins.Parameters.Add( "@newconame", System.Data.SqlDbType.NVarChar, 30);

                // 创建删除命令

                SqlCommand cmddel = conn.CreateCommand();

                cmddel.CommandText = sqlDel;

                cmddel.Transaction = trans;

                cmddel.Parameters.Add( "@oldcustid", System.Data.SqlDbType.NVarChar, 5);

               

                // 添加客户

                cmdins.Parameters[ "@newcustid"].Value = textBox1.Text;

                cmdins.Parameters[ "@newconame"].Value = textBox2.Text;

                cmdins.ExecuteNonQuery();

                // 删除客户

                cmddel.Parameters[ "@oldcustid"].Value = textBox3.Text;

                cmddel.ExecuteNonQuery();

                // 提交事务

                trans.Commit();

                // 没有异常的话，提交事务，显示消息

                MessageBox.Show( "事务已提交" );

            }

            catch (System.Data.SqlClient. SqlException ex)

            {

                //trans.Rollback();

                MessageBox.Show( "回滚事务\n" + ex.Message, "回滚事务");

            }

            catch(System. Exception ex)

            {

                MessageBox.Show( "系统错误\n" + ex.Message, "错误");

            }

            finally

            {

                conn.Close();

            }

        }

    }

}