看下图:(用户信息列表里面是 银行用户的信息),左下角 提交按钮(更新数据源),
在这里,我用的是非连接环境下的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(); //刷新界面数据
}
}
}
}