我们现在写一个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;
}
//执行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;
}
//
// 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;
}
}
}
最新评论