c#更新SQL错误怎样回滚？

Question

private void  Save()      {
            SqlConnection connection = OpenSql();
            try         {
                connection.Open();
                for (int i = 0; i <= (dataGridView1.Rows.Count) - 2; i++)                {
                    string sql1 = string.Format("insert into lingshoudan ( Id,Name,Address,Tel) values('{0}','{1}','{2}','{3}')", dataGridView1[0, i].Value, dataGridView1[1, i].Value, dataGridView1[2, i].Value, dataGridView1[3, i].Value);
                    SqlCommand command = new SqlCommand(sql1, connection);
                    command.ExecuteNonQuery();                  
                }
            }
            catch (Exception ex)            {
                MessageBox.Show(ex.Message, "保存失败", MessageBoxButtons.OK, MessageBoxIcon.Error); 
            }
            finally            {
                connection.Close(); connection.Dispose();
            }
        }我现在用上面的方法把dataGridView中数据逐条"插入/更新"到SQL中，当其中有一条出错的话，后果很严重，
请问有什么其他的"插入/更新"方法，出错的时候能够回滚？

Answer 1

        SqlConnection conn = new SqlConnection("asdf");
        SqlTransaction tran = conn.BeginTransaction();
        try
        {            tran.Commit();
        }
        catch (Exception ex)
        {
            tran.Rollback();   
        }

Answer 2

private void  Save()      {
            SqlConnection connection = OpenSql();
SqlTransaction tran = conn.BeginTransaction();            try         {
                connection.Open();
                for (int i = 0; i <= (dataGridView1.Rows.Count) - 2; i++)                {
                    string sql1 = string.Format("insert into lingshoudan ( Id,Name,Address,Tel) values('{0}','{1}','{2}','{3}')", dataGridView1[0, i].Value, dataGridView1[1, i].Value, dataGridView1[2, i].Value, dataGridView1[3, i].Value);
                    SqlCommand command = new SqlCommand(sql1, connection);
                    command.ExecuteNonQuery();  
                tran.Commit();//成功就提交
                }
            }
            catch (Exception ex)            {
tran.Rollback(); //有错就回滚
                MessageBox.Show(ex.Message, "保存失败", MessageBoxButtons.OK, MessageBoxIcon.Error); 
            }
            finally            {
                connection.Close(); connection.Dispose();
            }
        }

Answer 3

SqlTransaction tran = connection.BeginTransaction();

Answer 4

using (TransactionScope tsCope = new TransactionScope())
  {
  tsCope.Complete();
  }

Answer 5

1。如果OpenSql方法得到的Connection确保已打开的话，在try块中就不必再Open了！
2。如果OpenSql方法只得到一个Connection而不保证其打开，则要将try块中的Open拿出来放到BeginTransaction方法之前！！
3。finnally块中好像有一句就可以！（这个吃不准！！）

Answer 6

用事物磁场(姑且这麽叫 scope在物理上有场的意思)也可以，这是微软新推出的事物解决方案。

Answer 7

 private void Save11()
        {
            SqlConnection connection = OpenSql();
            connection.Open();
            SqlTransaction tran = connection.BeginTransaction();
            try            {               
                for (int i = 0; i <= (dataGridView1.Rows.Count) - 2; i++)                {
                    string sql1 = string.Format("insert into lingshoudan ( Name) values('{0}')", dataGridView1[1, i].Value);
                    SqlCommand command = new SqlCommand(sql1, connection);
                    command.ExecuteNonQuery();
                    tran.Commit();
                }
            }
            catch (Exception ex)            {
                tran.Rollback(); //有错就回滚
                MessageBox.Show(ex.Message, "保存失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally            {
                connection.Close(); connection.Dispose();
            }
        }
错误信息： 
当分配给命令的连接处于挂起的本地事务中时，命令必须具有事务对象才能执行。该命令的Transaction   属性尚未初始化。

Answer 8

不好意思 刚刚没测试string sql1 = string.Format("insert into lingshoudan ( Name) values('{0}')", dataGridView1[1, i].Value);
                SqlCommand command = new SqlCommand(sql1, connection);
                command.Transaction = tran;                command.ExecuteNonQuery();
                tran.Commit();

Answer 9

command.Transaction = tran;  

Answer 10

private void Save11()
        {
            SqlConnection connection = OpenSql();
            connection.Open();
            SqlTransaction tran = connection.BeginTransaction();
            try
            {
                for (int i = 0; i <= (dataGridView1.Rows.Count) - 2; i++)
                {
                    string sql1 = string.Format("insert into sqltest ( Name) values('{0}')", dataGridView1[1, i].Value);
                    SqlCommand command = new SqlCommand(sql1, connection);
                    command.Transaction = tran;
                    command.ExecuteNonQuery();                  
                }
                tran.Commit();
            }
            catch (Exception ex)
            {                
                MessageBox.Show(ex.Message, "保存失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
                tran.Rollback(); //有错就回滚
            }
            finally            {
                connection.Close(); connection.Dispose();
            }
        }问题解决 如上

Answer 11

SqlConnection connection = 打开数据库();
                    connection.Open();
                    SqlTransaction Tran = connection.BeginTransaction();                    
                                     try
                    {
                         SqlCommand command = new SqlCommand(sql1, connection);
                            command.Transaction = Tran;
                            command.ExecuteNonQuery();                            
                        }
                        Tran.Commit();//提交事务
                                           }
                    catch (Exception ex)
                    {
                        Tran.Rollback();//回滚事务
                                          }