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

我们现在写一个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[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;
                }

                else if (object.Equals(bean[i].GetClumType(), SqlDbType.Decimal))
                {
                    param = new SqlParameter(bean[i].GetName(), SqlDbType.Decimal, bean[i].GetClumLength());
                    if (bean[i].GetIsOutPut())
                    {
                        param.Direction = ParameterDirection.Output;
                    }
                    param.Value = bean[i].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[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;
                }

                else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Decimal))
                {
                    param = new SqlParameter(Bean[i].GetName(), SqlDbType.Decimal, Bean[i].GetClumLength());
                    if (Bean[i].GetIsOutPut())
                    {
                        param.Direction = ParameterDirection.Output;
                    }
                    param.Value = Bean[i].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[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;
                }
                else if (object.Equals(bean[i].GetClumType(), SqlDbType.Decimal))
                {
                    param = new SqlParameter(bean[i].GetName(), SqlDbType.Decimal, bean[i].GetClumLength());
                    if (bean[i].GetIsOutPut())
                    {
                        param.Direction = ParameterDirection.Output;
                    }
                    param.Value = bean[i].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[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;
                }
                else if (object.Equals(Bean[i].GetClumType(), SqlDbType.Decimal))
                {
                    param = new SqlParameter(Bean[i].GetName(), SqlDbType.Decimal, Bean[i].GetClumLength());
                    if (Bean[i].GetIsOutPut())
                    {
                        param.Direction = ParameterDirection.Output;
                    }
                    param.Value = Bean[i].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;
}

}
}

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

作者 keyboardsun