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

xiaui520

　　我们现在写一个SQL SERVER的数据库简单的操作类。
　　包括事务，存储过程调用。
　　类文件如下：
　　using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Data;namespace DALFactory{public class DBLib{private SqlConnection con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connstr"]);//private SqlConnection con = new SqlConnection("Server=SUNLEI;DataBase=WEB;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(){try{Tx.Rollback();con.Close();Tx = null;}catch{}}public void CloseConnetion(){try{if(con.State!=ConnectionState.Closed)con.Close();}catch{}}//给存储过程的参数赋值public SqlCommand SetParams(SqlTypeBean[] bean, String ProcedureName){if (bean == null)bean = new SqlTypeBean[] { };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.GetName() + "=" + bean.GetValueString();SqlParameter param = null;if (object.Equals(bean.GetClumType(), SqlDbType.VarChar)){param = new SqlParameter(bean.GetName(), SqlDbType.VarChar, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = bean.GetValueString();cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.Int)){param = new SqlParameter(bean.GetName(), SqlDbType.Int, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = bean.GetValueLong();cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.DateTime)){param = new SqlParameter(bean.GetName(), SqlDbType.DateTime, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = DateTime.Parse(bean.GetValueString());cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.Char)){param = new SqlParameter(bean.GetName(), SqlDbType.Char, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = char.Parse(bean.GetValueString());cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.Bit)){param = new SqlParameter(bean.GetName(), SqlDbType.Bit, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = bean.GetValueString();cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.Decimal)){param = new SqlParameter(bean.GetName(), SqlDbType.Decimal, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = bean.GetValueDouble();cmd.Parameters.Add(param);continue;}}Log.WriteLog("执行存储过程为:" + ProcedureName + "\n参数为:"+para);if (cmd.Connection.State != ConnectionState.Open)cmd.Connection.Open();return cmd;}//给绑定变量的参数赋值public SqlCommand SetParamsSql(SqlTypeBean[] Bean, String Sql){SqlCommand cmd = new SqlCommand(Sql);cmd.Connection = con;if (Bean == null)Bean = new SqlTypeBean[] { };String para = "";for (int i = 0; i < Bean.Length; i++){para = para + Bean.GetName() + "=" + Bean.GetValueString();SqlParameter param = null;if (object.Equals(Bean.GetClumType(), SqlDbType.VarChar)){param = new SqlParameter(Bean.GetName(), SqlDbType.VarChar, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = Bean.GetValueString();cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.Int)){param = new SqlParameter(Bean.GetName(), SqlDbType.Int, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = Bean.GetValueLong();cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.DateTime)){param = new SqlParameter(Bean.GetName(), SqlDbType.DateTime, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = DateTime.Parse(Bean.GetValueString());cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.Char)){param = new SqlParameter(Bean.GetName(), SqlDbType.Char, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = char.Parse(Bean.GetValueString());cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.Bit)){param = new SqlParameter(Bean.GetName(), SqlDbType.Bit, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = Bean.GetValueString();cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.Decimal)){param = new SqlParameter(Bean.GetName(), SqlDbType.Decimal, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = Bean.GetValueDouble();cmd.Parameters.Add(param);continue;}}Log.WriteLog("执行存储过程为:" + Sql + "\n参数为:" + para);if (cmd.Connection.State!=ConnectionState.Open)cmd.Connection.Open();return cmd;}//给存储过程的参数赋值，这方法需要在事务中使用public SqlCommand SetParamsTransactionProce(SqlTypeBean[] bean, String ProcedureName){if (bean == null)bean = new SqlTypeBean[] { };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.GetName() + "=" + bean.GetValueString();SqlParameter param = null;if (object.Equals(bean.GetClumType(), SqlDbType.VarChar)){param = new SqlParameter(bean.GetName(), SqlDbType.VarChar, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = bean.GetValueString();cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.Int)){param = new SqlParameter(bean.GetName(), SqlDbType.Int, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = bean.GetValueLong();cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.DateTime)){param = new SqlParameter(bean.GetName(), SqlDbType.DateTime, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = DateTime.Parse(bean.GetValueString());cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.Char)){param = new SqlParameter(bean.GetName(), SqlDbType.Char, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = char.Parse(bean.GetValueString());cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.Bit)){param = new SqlParameter(bean.GetName(), SqlDbType.Bit, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = bean.GetValueString();cmd.Parameters.Add(param);continue;}else if (object.Equals(bean.GetClumType(), SqlDbType.Decimal)){param = new SqlParameter(bean.GetName(), SqlDbType.Decimal, bean.GetClumLength());if (bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = bean.GetValueDouble();cmd.Parameters.Add(param);continue;}}Log.WriteLog("执行存储过程为:" + ProcedureName + "\n参数为:" + para);return cmd;}//给绑定变量赋值，此方法需要事务控制public SqlCommand SetParamsTransactionSql(SqlTypeBean[] Bean, String Sql){if (Bean == null)Bean = new SqlTypeBean[] { };SqlCommand cmd = new SqlCommand(Sql);cmd.Connection = con;cmd.Transaction = Tx;String para = "";for (int i = 0; i < Bean.Length; i++){para = para + Bean.GetName() + "=" + Bean.GetValueString();SqlParameter param = null;if (object.Equals(Bean.GetClumType(), SqlDbType.VarChar)){param = new SqlParameter(Bean.GetName(), SqlDbType.VarChar, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = Bean.GetValueString();cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.Int)){param = new SqlParameter(Bean.GetName(), SqlDbType.Int, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = Bean.GetValueLong();cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.DateTime)){param = new SqlParameter(Bean.GetName(), SqlDbType.DateTime, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = DateTime.Parse(Bean.GetValueString());cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.Char)){param = new SqlParameter(Bean.GetName(), SqlDbType.Char, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = char.Parse(Bean.GetValueString());cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.Bit)){param = new SqlParameter(Bean.GetName(), SqlDbType.Bit, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = Bean.GetValueString();cmd.Parameters.Add(param);continue;}else if (object.Equals(Bean.GetClumType(), SqlDbType.Decimal)){param = new SqlParameter(Bean.GetName(), SqlDbType.Decimal, Bean.GetClumLength());if (Bean.GetIsOutPut()){param.Direction = ParameterDirection.Output;}param.Value = Bean.GetValueDouble();cmd.Parameters.Add(param);continue;}}Log.WriteLog("执行存储过程为:" + Sql + "\n参数为:" + para);return cmd;}//执行readerpublic 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();}//执行普通的sqlpublic bool ExecSql(String Sql, SqlTypeBean[] Bean){SqlCommand command = SetParamsSql(Bean, Sql);command.ExecuteNonQuery();command.Connection.Close();return true;}//执行事务控制的sqlpublic 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;}//// Summary://     Initializes a new instance of the System.Data.SqlClient.SqlCommand class//     with the text of the query and a System.Data.SqlClient.SqlConnection.//// Parameters://   Sql://     The text of the query.////   Bean://     A System.Data.SqlClient.SqlConnection that represents the connection to an//     instance of SQL Server.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