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