C# 基于Sql 数据访问类

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

namespace SqlDAL
{
    public class DataAccess
    {
        private SqlConnection conn;         //SQL连接
        private SqlTransaction sqlTran;     //事务处理
        private bool inTran = false;        //标识是否处于事务中
        private string connStr;             //连接字符串


        /// <summary>
        /// 默认构造函数 用于初始化数据库连接
        /// </summary>
        public DataAccess()
        {
            connStr = "";
            conn = new SqlConnection(connStr);
        }


        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void open()
        {
            if (this.conn.State.ToString().ToUpper() != "OPEN")
            {
                this.conn.Open();
            }
        }


        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void close()
        {
            if (this.conn.State.ToString().ToUpper() == "OPEN")
            {
                this.conn.Close();
            }
        }


        /// <summary>
        /// 开始一个事务
        /// </summary>
        public void beginTran()
        {
            this.sqlTran = this.conn.BeginTransaction();
            this.inTran = true;
        }


        /// <summary>
        /// 提交一个事务
        /// </summary>
        public void commitTran()
        {
            this.sqlTran.Commit();
            this.inTran = false;
        }


        /// <summary>
        /// 回滚当前事务
        /// </summary>
        public void rollbackTran()
        {
            this.sqlTran.Rollback();
            this.inTran = false;
        }


        /// <summary>
        /// 执行sql语句返回受影响的行数
        /// </summary>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <returns>受影响的行数</returns>
        public int execSql_ReInt(string sqlStr)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (this.inTran)
            {
                cmd.Transaction = this.sqlTran;
            }
            cmd.CommandText = sqlStr;
            int i = cmd.ExecuteNonQuery();
            return i;
        }

        /// <summary>
        /// 执行带参数的sql语句返回受影响的行数
        /// </summary>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <param name="para">参数列表</param>
        /// <returns>受影响的行数</returns>
        public int execSql_ReInt(string sqlStr, IDataParameter[] para)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTran)
            {
                cmd.Transaction = sqlTran;
            }
            cmd.CommandText = sqlStr;
            if (para != null)
            {
                foreach (SqlParameter paramete in para)
                {
                    cmd.Parameters.Add(paramete);
                }
            }
            int i = cmd.ExecuteNonQuery();
            return i;
        }

        /// <summary>
        /// 执行带参数sql语句返回受影响行数
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="ParamsName">参数名数组</param>
        /// <param name="ParamsValue">参数值数组</param>
        /// <returns>受影响函数</returns>
        public int execSql_ReInt(string sqlStr, string[] ParamsName, object[] ParamsValue)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTran)
            {
                cmd.Transaction = sqlTran;
            }

            if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
            {
                throw new System.Exception("参数和值不对应!");
            }

            cmd.CommandText = sqlStr;
            if (ParamsName != null)
            {
                SqlParameter SqlParams;
                for (int i = 0; i < ParamsName.Length; i++)
                {
                    SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
                    cmd.Parameters.Add(SqlParams);

                }
            }
            int k = cmd.ExecuteNonQuery();
            return k;
        }



        /// <summary>
        /// 执行sql语句返回数据集
        /// </summary>
        /// <param name="sqlStr">执行sql语句</param>
        /// <returns>返回的数据集</returns>
        public DataSet execSql_ReDs(string sqlStr)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTran)
            {
                cmd.Transaction = sqlTran;
            }
            cmd.CommandText = sqlStr;
            DataSet ds = new DataSet();
            using (SqlDataAdapter ad = new SqlDataAdapter())
            {
                ad.SelectCommand = cmd;
                ad.Fill(ds);
            }
            return ds;
        }

        /// <summary>
        /// 执行带参数sql语句返回数据集
        /// </summary>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <param name="para">参数列表</param>
        /// <returns>返回的数据集</returns>
        public DataSet execSql_ReDs(string sqlStr, IDataParameter[] para)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTran)
            {
                cmd.Transaction = sqlTran;
            }

            cmd.CommandText = sqlStr;
            if (para != null)
            {
                foreach (SqlParameter parateme in para)
                {
                    cmd.Parameters.Add(parateme);
                }
            }
            DataSet ds = new DataSet();
            using (SqlDataAdapter ad = new SqlDataAdapter())
            {
                ad.SelectCommand = cmd;
                ad.Fill(ds);
            }
            return ds;
        }

        /// <summary>
        /// 执行带参数sql语句返回数据集
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="ParamsName">参数名数组</param>
        /// <param name="ParamsValue">参数值数组</param>
        /// <returns>返回数据集</returns>
        public DataSet execSql_ReDs(string sqlStr, string[] ParamsName, object[] ParamsValue)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTran)
            {
                cmd.Transaction = sqlTran;
            }

            if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
            {
                throw new System.Exception("参数和值不对应!");
            }

            cmd.CommandText = sqlStr;
            if (ParamsName != null)
            {
                SqlParameter SqlParams;
                for (int i = 0; i < ParamsName.Length; i++)
                {
                    SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
                    cmd.Parameters.Add(SqlParams);
                }
            }

            DataSet ds = new DataSet();
            using (SqlDataAdapter ad = new SqlDataAdapter())
            {
                ad.SelectCommand = cmd;
                ad.Fill(ds);
            }
            return ds;
        }



        /// <summary>
        /// 执行存储过程返回return值 string 类型
        /// </summary>
        /// <param name="StoreName">过程名</param>
        /// <param name="ParamsName">参数名数组</param>
        /// <param name="ParamsValue">参数值数组</param>
        /// <returns>Return值</returns>
        public string execProc_ReRet(string StoreName, string[] ParamsName, object[] ParamsValue)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTran)
            {
                cmd.Transaction = sqlTran;
            }

            if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
            {
                throw new System.Exception("参数和值不对应!");
            }

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = StoreName;

            if (ParamsName != null)
            {
                SqlParameter SqlParams;
                for (int i = 0; i < ParamsName.Length; i++)
                {
                    SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
                    cmd.Parameters.Add(SqlParams);
                }

                //增加返回的参数
                SqlParameter Re_params = new SqlParameter("Return_Value", SqlDbType.VarChar);
                Re_params.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(Re_params);
            }
            cmd.ExecuteNonQuery();
            string k = cmd.Parameters["Return_Value"].Value.ToString();
            return k;
        }

        /// <summary>
        /// 执行带参数的存储过程返回Int类型
        /// </summary>
        /// <param name="StoreName">存储过程名称</param>
        /// <param name="ParamsName">参数名称列表</param>
        /// <param name="ParamsValue">参数值列表</param>
        /// <param name="OutName">返回参数名</param>
        /// <returns>返回的Int类型值</returns>
        public int execProc_ReInt(string StoreName, string[] ParamsName, object[] ParamsValue, string OutName)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTran)
            {
                cmd.Transaction = sqlTran;
            }

            if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
            {
                throw new System.Exception("参数和值不对应!");
            }

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = StoreName;

            if (ParamsName != null)
            {
                SqlParameter SqlParams;
                for (int i = 0; i < ParamsName.Length; i++)
                {
                    SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
                    if (ParamsName[i].ToString() == OutName)
                    {
                        SqlParams.Direction = ParameterDirection.Output;
                    }
                    cmd.Parameters.Add(SqlParams);
                }
            }
            cmd.ExecuteNonQuery();
            int k = Convert.ToInt32(cmd.Parameters[OutName].Value.ToString());
            return k;
        }

        /// <summary>
        /// 执行带参数的存储过程返回String类型
        /// </summary>
        /// <param name="StoreName">存储过程名称</param>
        /// <param name="ParamsName">参数名称列表</param>
        /// <param name="ParamsValue">参数值列表</param>
        /// <param name="OutName">返回参数名</param>
        /// <returns>返回的String类型值</returns>
        public string execProc_ReStr(string StoreName, string[] ParamsName, object[] ParamsValue, string OutName)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTran)
            {
                cmd.Transaction = sqlTran;
            }

            if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
            {
                throw new System.Exception("参数和值不对应!");
            }

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = StoreName;

            if (ParamsName != null)
            {
                SqlParameter SqlParams;
                for (int i = 0; i < ParamsName.Length; i++)
                {
                    SqlParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
                    if (ParamsName[i].ToString() == OutName)
                    {
                        SqlParams.Size = 255;
                        SqlParams.Direction = ParameterDirection.Output;                        
                    }
                    cmd.Parameters.Add(SqlParams);
                }
            }
            cmd.ExecuteNonQuery();
            string k = Convert.ToString(cmd.Parameters[OutName].Value.ToString());
            return k;
        }

        /// <summary>
        /// 执行带参数的存储过程返回数据集
        /// </summary>
        /// <param name="StoreName">过程名称</param>
        /// <param name="ParamsName">参数名称列表</param>
        /// <param name="ParamsValue">参数值列表</param>
        /// <returns>返回的数据集</returns>
        public DataSet execProc_ReDs(string StoreName, string[] ParamsName, object[] ParamsValue)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTran)
            {
                cmd.Transaction = sqlTran;
            }

            if ((ParamsName != null) && (ParamsName.Length != ParamsValue.Length))
            {
                throw new System.Exception("参数和值不对应!");
            }

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = StoreName;

            if (ParamsName != null)
            {
                SqlParameter OraParams;
                for (int i = 0; i < ParamsName.Length; i++)
                {
                    OraParams = new SqlParameter(ParamsName[i], ParamsValue[i]);
                    cmd.Parameters.Add(OraParams);
                }
            }

            DataSet ds = new DataSet();
            using (SqlDataAdapter ad = new SqlDataAdapter())
            {
                ad.SelectCommand = cmd;
                ad.Fill(ds);
            }
            return ds;
        }
    }
}