SqlDataAdapter的Updata方法，确保里面的操作为同一个事务

 

看下图：（用户信息列表里面是 银行用户的信息），左下角 提交按钮（更新数据源），

在这里，我用的是非连接环境下的SqlDataAdapter对象

下面看数据库：数据库只有一张表tb_bank,

USE Trans
GO
/****** Object:  Table [dbo].[tb_bank]
    Script Date: 09/07/2012 10:15:41
    Author yangyang
  ******/
  ---创建表
CREATE TABLE [dbo].[tb_bank]
(
[cardno] [varchar](20) NOT NULL,
[name] [varchar](20) NOT NULL,
[curentmoney] [money] NOT NULL, 
CONSTRAINT [PK_tb_bank] PRIMARY KEY  [cardno]   ----设置cardno为主键
  )

--添加列check约束----------------------------------------------------------currentmoney至少有一块钱
alter table tb_bank
add CONSTRAINT ck_money check (curentmoney>=1)

--初始化数据
insert into tb_bank values('123','张三',1001)
insert into tb_bank values('456','李四',1)

select * from  tb_bank

后台实现：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TransTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            Load += new EventHandler(Form1_Load);
        }
        SqlConnection conn;  //连接
        string constr = "Server=.;DataBase=trans; User ID=sa;Pwd=89097544";
        SqlDataAdapter sda; //同一个适配器
        DataTable dt; 

        //页面首次加载
        void Form1_Load(object sender, EventArgs e)
        {
            LoadDate();
        }

        //（刷新）加载DataGridView的数据

        private void LoadDate()
        {
            dt = new DataTable();
            dataGridView1.DataSource = null;
            conn = new SqlConnection(constr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            sda = new SqlDataAdapter("select * from tb_bank", conn);
            sda.Fill(dt);
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            dataGridView1.DataSource = dt;
        }
       
        //转账操作
        private void btnCommit_Click(object sender, EventArgs e)
        {
            SqlTransaction tans;
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
          //给连接添加事务
          tans = conn.BeginTransaction();

          sda.SelectCommand.Connection = conn;
          sda.SelectCommand.Transaction = tans;

          SqlCommandBuilder sb = new SqlCommandBuilder(sda); //使用sqlCommandBuilder必须有selectCommond
          SqlCommand cmd= sb.GetUpdateCommand();
          //给数据适配的命令通道也添加上事务
          sda.UpdateCommand = cmd;
          sda.UpdateCommand.Connection = conn;
          sda.UpdateCommand.Transaction = tans;

 

          try
          {
              sda.Update(dt);  //修改datatable
              tans.Commit();  //提交事务
              MessageBox.Show("成功");
          }
          catch (Exception ecp)
          {
              tans.Rollback(); //出现异常，回滚事务
              MessageBox.Show("异常");
          }
          finally
          {
              if (conn.State == ConnectionState.Open)
              {
                  conn.Close();
              }
              LoadDate();    //刷新界面数据
          }
        }
    }
}