Sql-事务

private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"SERVER=localhost;UID=sa;DATABASE=GG;MultipleActiveResultSets =true;PWD=123456");
            con.Open();
            ////创建事务并赋值
            SqlTransaction tran= con.BeginTransaction();
            //创建SqlCommand实例
            SqlCommand _delete=con.CreateCommand();
            SqlCommand _insert = con.CreateCommand();
            SqlCommand _select = con.CreateCommand();
            SqlCommand _update = con.CreateCommand();
            //通过同一个SqlConnection创建的SqlCommand,每一个都需要事务,否则报错
            _delete.Transaction = tran;
            _insert.Transaction = tran;
            _select.Transaction = tran;
            _update.Transaction = tran;

            try
            {
                _delete.CommandText = "delete from t_user where no=11 or no=12";
                _delete.ExecuteNonQuery();
                _insert.CommandText = "insert t_user (no,age,name) values(11,111,1111)";
                _insert.ExecuteNonQuery();
                ////在SQL语句未执行完提交,后面的语句报错不会回滚
                ////tran.Commit();
                _update.CommandText = "update t_user set name=12,no=12,age=12 wh2ere no=11";
                _update.ExecuteNonQuery();
                _select.CommandText = "select * from t_user";
                SqlDataAdapter adt = new SqlDataAdapter(_select);
                DataTable dt = new DataTable();
                adt.Fill(dt);
                dataGridView1.DataSource = dt;
                //提交事务(在与本事务相关sql语句执行完之后提交)
                tran.Commit();
            }
            catch (Exception ex)
            {
                //从挂起状态回滚事务,不需要写tran.Rollback();,报错会自动运行
                //tran.Rollback();
                MessageBox.Show(ex.Message.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Question);
            }
            finally
            {
                con.Close();
            }
        }

  

posted @ 2015-09-25 16:31  人生为卒  阅读(163)  评论(0编辑  收藏  举报