ASP.NET C# 调用存储过程 SQL SERVER 事务 数据库操作类

我们现在写一个SQL SERVER的数据库简单的操作类。

包括事务，存储过程调用。

类文件如下：

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

namespace SpLib.db
{
public class DBLib
{

// private SqlConnection con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connstr"]);
private SqlConnection con = new SqlConnection("Server=SUNLEI;DataBase=SUNLEI;UID=sa;PWD=123456");

//全局事务
private SqlTransaction Tx = null;
public DBLib()
{

}

//手动开始事务
public void BeginTransaction()
{
con.Open();
Tx = con.BeginTransaction();
}

//手动提交事务
public void CommitTransaction()
{
Tx.Commit();
con.Close();
Tx = null;
}

//手动回滚事务
public void RollbackTransaction()
{
Tx.Rollback();
con.Close();
Tx = null;

}

//给存储过程的参数赋值
public SqlCommand SetParams(SqlTypeBean[] bean, String ProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;


String para = "";
for (int i = 0; i < bean.Length; i++)
{
para = para + bean[i].GetName() + "=" + bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.VarChar, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Int, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;

}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.DateTime, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Char, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Bit, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}

}
SpLib.busi.Log.WriteLog("执行存储过程为:" + ProcedureName + "\n参数为:"+para);
cmd.Connection.Open();
return cmd;

}

//给绑定变量的参数赋值
public SqlCommand SetParamsSql(SqlTypeBean[] Bean, String Sql)
{
SqlCommand cmd = new SqlCommand(Sql);
cmd.Connection = con;

String para = "";
for (int i = 0; i < Bean.Length; i++)
{
para = para + Bean[i].GetName() + "=" + Bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(Bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.VarChar, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Int, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;

}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.DateTime, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Char, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Bit, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}

}
SpLib.busi.Log.WriteLog("执行存储过程为:" + Sql + "\n参数为:" + para);
cmd.Connection.Open();
return cmd;

}

//给存储过程的参数赋值，这方法需要在事务中使用
public SqlCommand SetParamsTransactionProce(SqlTypeBean[] bean, String ProcedureName)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = Tx;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = ProcedureName;

String para = "";
for (int i = 0; i < bean.Length; i++)
{
para = para + bean[i].GetName() + "=" + bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.VarChar, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Int, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;

}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.DateTime, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Char, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(bean[i].GetName(), SqlDbType.Bit, bean[i].GetClumLength());
if (bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}

}
SpLib.busi.Log.WriteLog("执行存储过程为:" + ProcedureName + "\n参数为:" + para);
return cmd;

}

//给绑定变量赋值，此方法需要事务控制
public SqlCommand SetParamsTransactionSql(SqlTypeBean[] Bean, String Sql)
{
SqlCommand cmd = new SqlCommand(Sql);
cmd.Connection = con;
cmd.Transaction = Tx;

String para = "";
for (int i = 0; i < Bean.Length; i++)
{
para = para + Bean[i].GetName() + "=" + Bean[i].GetValueString();
SqlParameter param = null;
if (object.Equals(Bean[i].GetClumType(), SqlDbType.VarChar))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.VarChar, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Int))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Int, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueLong();
cmd.Parameters.Add(param);
continue;

}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.DateTime))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.DateTime, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = DateTime.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Char))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Char, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = char.Parse(Bean[i].GetValueString());
cmd.Parameters.Add(param);
continue;
}
else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Bit))
{
param = new SqlParameter(Bean[i].GetName(), SqlDbType.Bit, Bean[i].GetClumLength());
if (Bean[i].GetIsOutPut())
{
param.Direction = ParameterDirection.Output;
}
param.Value = Bean[i].GetValueString();
cmd.Parameters.Add(param);
continue;
}

}
SpLib.busi.Log.WriteLog("执行存储过程为:" + Sql + "\n参数为:" + para);
return cmd;

}

//执行reader
public SqlDataReader GetReader(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);

return command.ExecuteReader();
}

//执行reader，需要在事务中使用。
public SqlDataReader GetTransactionReader(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsTransactionSql(Bean, Sql);

return command.ExecuteReader();
}

//执行普通的sql
public bool ExecSql(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
command.ExecuteNonQuery();
command.Connection.Close();
return true;
}

//执行事务控制的sql
public bool ExecTransactionSql(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsTransactionSql(Bean, Sql);
command.ExecuteNonQuery();
return true;
}

//取得记录的第一行第一列的值
public String GetTransactionOneString(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsTransactionSql(Bean, Sql);
String result = command.ExecuteScalar().ToString();
return result;
}

//取得记录的第一行第一列的值
public String GetOneString(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
String result = command.ExecuteScalar().ToString();
command.Connection.Close();
command.Dispose();
return result;
}

//取得DataSet
public DataSet GetDataSet(String Sql, SqlTypeBean[] Bean)
{
SqlCommand command = SetParamsSql(Bean, Sql);
command.Connection.Open();
SqlDataAdapter SqlAdapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
SqlAdapter.Fill(ds, "");
return ds;
}
}
}

上面这个类包括常用的操作数据库的方法，下面我们来写几个test用例

public class UnitTest1
{
public UnitTest1()
{
//
// TODO: Add constructor logic here
//
}

[TestMethod]

//这里演示怎么调用事务，用绑定变量。
public void TestMethod1()
{
DBLib lib = new DBLib();
try
{
lib.BeginTransaction();//要使用事务，这里必须要先BeginTransaction()，下面执行的方法都要调用带Transaction的方法。
String sql = "INSERT INTO testtable VALUES(@tid,@text)";
SqlTypeBean[] bean = new SqlTypeBean[2];
bean[0] = new SqlTypeBean(false, "@tid", "18", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@text", "good", 50, SqlDbType.VarChar);
lib.ExecTransactionSql(sql, bean);
System.Console.WriteLine("ok!");
sql = "INSERT INTO testtable VALUES(@tid,@text)";

bean[0] = new SqlTypeBean(false, "@tid", "17", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@text", "good", 50, SqlDbType.VarChar);
lib.ExecTransactionSql(sql, bean);
System.Console.WriteLine("ok!");

lib.CommitTransaction();
}
catch (Exception e)
{
lib.RollbackTransaction();
throw e;
}
}
[TestMethod]

//这里普通的调用
public void TestMethod2()
{
DBLib lib = new DBLib();
String sql = "INSERT INTO testtable VALUES(@tid,@text)";
SqlTypeBean[] bean = new SqlTypeBean[2];
bean[0] = new SqlTypeBean(false, "@tid", "7", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@text", "good", 50, SqlDbType.VarChar);
lib.ExecSql(sql, bean);
}

[TestMethod]

//这里是带返回值的存储
public void TestMethod3()
{
DBLib lib = new DBLib();
SqlTypeBean[] bean = new SqlTypeBean[2];
bean[0] = new SqlTypeBean(true, "@COUNT", "7", 4, SqlDbType.Int);
bean[1] = new SqlTypeBean(false, "@TEXT", "good", 50, SqlDbType.VarChar);
SqlCommand cmd = lib.SetParams(bean,"GETCOUNT");
cmd.ExecuteNonQuery();
String result = cmd.Parameters["@COUNT"].Value.ToString();
String result1 = cmd.Parameters["@COUNT"].Value.ToString();
cmd.Connection.Close();
}
}


存储存储过程变量的bean

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

namespace SpLib.db
{
//本类用于存放变量类型
public class SqlTypeBean
{
//这里设定变量是输入变量还是输出变量。默认是输入变量
private bool IsOutPut = false;
//这里存放字段变量的名称
private String Name;
//这里存放字段变量的值
private String Value;
//这里存放字段的长度
private int ClumLength;
//这里存放字段的类型
private object ClumType;


public SqlTypeBean(bool IsOutPut, String Name, String Value, int ClumLength, object ClumType)
{
this.IsOutPut = IsOutPut;
this.Name = Name;
this.Value = Value;
this.ClumLength = ClumLength;
this.ClumType = ClumType;

}

public SqlTypeBean( String Name, String Value, int ClumLength, object ClumType)
{
this.IsOutPut = false;
this.Name = Name;
this.Value = Value;
this.ClumLength = ClumLength;
this.ClumType = ClumType;

}

public bool GetIsOutPut()
{
return IsOutPut;
}

public String GetName()
{
return Name;
}

public object GetClumType()
{
return ClumType;
}

public String GetValueString()
{
return Value;
}

public long GetValueLong()
{
return long.Parse(Value);
}

public bool GetValueBool()
{
return bool.Parse(Value);
}

public int GetClumLength()
{
return ClumLength;
}

}
}

原创文章，转载请标明出处http://blog.csdn.net/keyboardsun

作者 keyboardsun