带事物处理的DBHelp和sql语句

时间:2021-11-06 16:01:55

DBHelp语句

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient; namespace 模拟ATM机转账
{
static class DBHelp
{
public static bool IDUBySql(string sql)
{
bool fa = false;
string str = "server=.;integrated security=true;database=BankDB";
SqlConnection sqlconn = new SqlConnection(str);
SqlCommand sqlcomm = new SqlCommand(sql, sqlconn); try
{
sqlconn.Open();
//开启事务
sqlcomm.Transaction = sqlconn.BeginTransaction();
fa = sqlcomm.ExecuteNonQuery() > ? true : false;
//提交事务
sqlcomm.Transaction.Commit();
}
catch (SqlException ex)
{
//回滚事务
sqlcomm.Transaction.Rollback();
}
finally
{
sqlconn.Close();
} return fa;
}
public static DataTable GetTableBySql(string sql)
{
DataTable ta = new DataTable();
string str = "server=.;integrated security=true;database=BankDB";
SqlConnection sqlconn = new SqlConnection(str);
SqlCommand sqlcomm = new SqlCommand(sql, sqlconn); SqlDataAdapter da = new SqlDataAdapter(sqlcomm);
da.Fill(ta); return ta;
}
}
}

sql语句

--G.事务:
--定义转账事务,并实现转账操作进行测试,注意:转账过程中实际上两个交易过程,一个是“存入”,一个是“支取”,注意添加交易记录到交易信息表中
begin tran tran_bank
declare @cou int
set @cou=0 update cardInfo set balance=balance-900 where cardID='1010 3576 1234 5678' update cardInfo set balance=balance+500 where cardID='1010 3576 1212 1134' set @cou=@@error+@cou if(@cou<>0)
begin
rollback tran --滚回
end
else
begin
commit tran
insert into transInfo(cardID,transType ,transMoney,remark) values ('1010 3576 1212 1134','存入',500,'李四存款500')
insert into transInfo(cardID,transType ,transMoney,remark) values ('1010 3576 1234 5678','支取',900,'张三取款900')
print '交易成功,以保存新数据'
end
go