带事物处理的DBHelp和sql语句

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() > 0 ? 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

带事物处理的DBHelp和sql语句

标签：