2020-11-04

时间 2021-06-02 标签事务 3

事务的使用方法

sql中

create database Test3
go
use Test3
go

create table bank
(
id int primary key identity(1,1),
name varchar(20) not null,
money int default(0) check(money>=0)
)

insert into bank values('张一',90)
insert into bank values('张二',80)
insert into bank values('张三',91)

select * from bank

--储存过程

create proc exchange @from int ,@to int ,@money int,@error int output

as
begin tran
set @error=0
update bank set [email protected] where [email protected]
set @[email protected]@[email protected]
update bank set [email protected] where [email protected]
set @[email protected]@[email protected]

if @error<>0
begin
   rollback tran
   print '转账失败，交易取消'
   end
else
begin
   commit tran
   print '转账成功，交易取消'
   end

go
declare @error int

exec exchange 1,2,100,@error output

--删除储存过程

drop procedure exchange

VS中的使用方法

BLL中

namespace BLL
{
public class bankBLL
{
public static bool ExChange(int from,int to,int money)
{
return bankDAL.ExChange(from,to,money);
}
}
}

DAL中

namespace DAL
{
public class bankDAL
{
public static bool ExChange (int from,int to,int money)
{
SqlParameter[] parameters =
{
new SqlParameter("@from",from),
new SqlParameter("@to",to),
new SqlParameter("@money",money),
new SqlParameter("@error",0)
};
parameters[3].Direction = System.Data.ParameterDirection.Output;
DBherpel.ExecNonQuery("exchange",System.Data.CommandType.StoredProcedure,parameters);
return Convert.ToInt32(parameters[3].Value) == 0;
}
}
}

DAL中DBherper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
public class DBherpel
{

//查询一和查询二是相同的功能用的方法不同

//连接字符
private static string strConn = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
//查询1
public static DataTable ExecQuery(string sql, params SqlParameter[] parameters)
{
//using加载完之后自动关闭
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, strConn))
{
using (DataTable table = new DataTable())
{
//判断参数中是否有值
if (parameters != null && parameters.Count() > 0)
{
//将参数集合加载到SelectCommand
dataAdapter.SelectCommand.Parameters.AddRange(parameters);
}

try
{
dataAdapter.Fill(table);
}
catch (Exception ex) //执行出错，释放资源
{
table.Dispose();
dataAdapter.Dispose();
//抛出异常
throw ex;
}
return table;
}
}
}

//查询2
public static SqlDataReader ExecReader(string sql, CommandType type = CommandType.Text, params SqlParameter[] parameters)
{
//创建连接对象
//【为什么不能使用using？】
SqlConnection sqlConnection = new SqlConnection(strConn);
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
{
//判断参数中是否有值
if (parameters != null && parameters.Count() > 0)
{
sqlCommand.Parameters.AddRange(parameters);
}

try
{
sqlConnection.Open();
//将Reader与Connection进行绑定，关闭Reader的同时释放Connection
return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex) //执行出错，释放资源
{
sqlCommand.Dispose();
sqlConnection.Close();
sqlConnection.Dispose();
throw ex;
}
}
}

//非查询
public static int ExecNonQuery(string sql, CommandType type = CommandType.Text, params SqlParameter[] parameters)
{
using (SqlConnection sqlConnection = new SqlConnection(strConn))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
{
sqlCommand.CommandType = type;
if (parameters != null && parameters.Count() > 0)
{
sqlCommand.Parameters.AddRange(parameters);
}
var num = 0;
try
{
sqlConnection.Open();
//返回受影响的行数
num=sqlCommand.ExecuteNonQuery();
}
catch //(Exception ex)
{
num = 0;
//throw ex;
}
finally
{
sqlConnection.Close();
}
return num;
}
}
}
}
}

Model

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
public class bank
{

public int id { get; set; }

public string name { get; set; }

public int money { get; set; }

}
}

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="admin.aspx.cs" Inherits="Web.admin" %>

<!DOCTYPE html>

using System.Web.UI.WebControls;

namespace Web
{
public partial class admin : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
int from = Convert.ToInt32(this.TextBox1.Text);
int to = Convert.ToInt32(this.TextBox2.Text);
int money = Convert.ToInt32(this.TextBox3.Text);
int num = Convert.ToInt32(bankBLL.ExChange(from,to,money));
if (num!=0)
{
Response.Write("<script>alert('转账成功')</script>");
}
else
{
Response.Write("<script>alert('转账失败')</script>");
}
}
}
}

webconfig

<?xml version="1.0" encoding="utf-8"?>

</configuration>