net数据库操作类(C#)——V2.0

ASP.NET中一般都是使用SQL Server作为后台数据库。一般的ASP.NET数据库操作示例程序都是使用单独的数据访问，就是说每个页面都写连接到数据库，存取数据，关闭数据库的代码。这种方式带来了一些弊端，一个就是如果你的数据库改变了，你必须一个页面一个页面的去更改数据库连接代码。第二个弊端就是代码冗余，很多代码都是重复的，不必要的。因此，我试图通过一种一致的数据库操作类来实现ASP.NET的数据访问,在我之前的文章 .net数据库操作类(C#)   中已经简单总结了对数据库的操作，现在 .net数据库操作类(C#)   的基础上进行了如下改进：
     1、添加了事务处理机制；
     2、添加了SQL参数化操作；
     3、添加了对存储过程的操作；
     4、添加了对SQL参数和存储过程的综合操作；
     现把.net数据库操作类代码公布出来，如有问题，欢迎大家指正！

using  System;
using  System.Data;
using  System.Data.SqlClient;
using  System.Collections;
using  System.IO;
using  System.Globalization;

namespace  VoteInfo
{
    public class DataAccess
    {
        #region 定义
        protected SqlCommand Comm;
        protected SqlDataAdapter Adap;
        protected SqlConnection Conn;    //SQL连接        
        private string _connectString;    //连接串
        private string _commandString;    //SQL命令
        private Hashtable _dict, _result, _mapTable;
        private DataSet _ds;    //返回结果数据集
        private DataRow _recordSet;        //纪录集
        private string _tableName;        //表名
        private int _recordCount;            //纪录集的行数
        private bool _eOF;                //结果集是否为空，是否已经到了结尾
        private string DB;
        private string _deleteOP;
        private string _path;
        private StreamWriter SWCreate, SWApp;
        private string _errorMessage;
        private bool _writeLog;
        #endregion

        #region 属性集
        /// <summary>
        /// 出错信息
        /// </summary>
        /// 
        public string ErrorMessage
        {
            get { return this._errorMessage; }
            set { this._errorMessage = value; }
        }

        /**/
        /// <summary>
        /// 设置或者取得删除的操作者
        /// </summary>
        public string DeleteOP
        {
            get { return this._deleteOP; }
            set { this._deleteOP = value; }
        }

        /**/
        /// <summary>
        /// 取得是否溢出
        /// </summary>
        public bool EOF
        {
            get { return this._eOF; }
            set { this._eOF = value; }
        }

        /**/
        /// <summary>
        /// 取得执行语句后得出的纪录条数
        /// </summary>
        public int RecordCount
        {
            get { return this._recordCount; }
            set { this._recordCount = value; }
        }

        /**/
        /// <summary>
        /// 数据库中的表名
        /// </summary>
        public string TableName
        {
            get { return this._tableName; }
            set { this._tableName = value; }
        }

        /**/
        /// <summary>
        /// 返回的记录集
        /// </summary>
        public DataRow RecordSet
        {
            get { return this._recordSet; }
            set { this._recordSet = value; }
        }

        /**/
        /// <summary>
        /// 返回的数据集
        /// </summary>
        public DataSet DS
        {
            get { return this._ds; }
            set { this._ds = value; }
        }

        /**/
        /// <summary>
        /// 字段和控件的映射表
        /// </summary>
        public Hashtable MapTable
        {
            get { return this._mapTable; }
            set { this._mapTable = value; }
        }

        /**/
        /// <summary>
        /// 修改数据时，作为修改结果
        /// </summary>
        public Hashtable Result
        {
            get { return this._result; }
            set { this._result = value; }
        }

        /**/
        /// <summary>
        /// 保存数据用的字段和值对应的哈希表，修改数据时用作条件
        /// </summary>
        public Hashtable Dict
        {
            get { return this._dict; }
            set { this._dict = value; }
        }

        /**/
        /// <summary>
        /// 查询语句
        /// </summary>
        public string CommandString
        {
            get { return this._commandString; }
            set { this._commandString = value; }
        }

        /**/
        /// <summary>
        /// 连接串
        /// </summary>
        public string ConnectString
        {
            get { return this._connectString; }
            set { this._connectString = value; }
        }

        #endregion 
       
        #region DataAccess的构造函数
        /// <summary>
        /// 空构造函数
        /// </summary>
        public DataAccess()
        {
            ConnectString = System.Configuration.ConfigurationSettings.AppSettings["DBConn"];
            Conn = new System.Data.SqlClient.SqlConnection(ConnectString);
            if (System.Configuration.ConfigurationSettings.AppSettings["WriteLog"] == "true")
            {
                _writeLog = true;
            }
            else
            {
                _writeLog = false;
            }

        }

        ~DataAccess()
        {
        }

        /**/
        /// <summary>
        /// DataAccess的构造函数
        /// </summary>
        /// <param name="DB1">要访问的数据库名，Web.config里设置的连接字符串对应的key</param>
        /// <param name="TableName1">要访问的数据表名</param>
        public DataAccess(string DB1, string TableName1)
        {
            this.ErrorMessage = "";
            DB = DB1;
            TableName = TableName1;
            try
            {
                ConnectString = System.Configuration.ConfigurationSettings.AppSettings[DB1];
                if (System.Configuration.ConfigurationSettings.AppSettings["WriteLog"] == "true")
                {
                    _writeLog = true;
                }
                else
                {
                    _writeLog = false;
                }
                Conn = new System.Data.SqlClient.SqlConnection(ConnectString);
                Dict = new Hashtable();
                Result = new Hashtable();
                MapTable = new Hashtable();
                DS = new DataSet();

                //            IS_Open = false;
                _path = "C:\\WebDebug.log";
                if (_writeLog)
                {
                    if (!File.Exists(_path))
                    {
                        using (SWCreate = File.CreateText(_path))
                        {
                            SWCreate.WriteLine(" ");
                            SWCreate.Close();
                        }
                    }
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine(" ");
                    }
                }
            }
            catch (Exception e)
            {
                this.ErrorMessage = e.ToString();
            }
        }

        /**/
        /// <summary>
        /// DataAccess的构造函数
        /// </summary>
        /// <param name="CST">数据库的连接字符串</param>
        /// <param name="TableName1">要访问的数据表名</param>
        /// <param name="flag">是否初始化</param>
        public DataAccess(string CST, string TableName1, bool flag)
        {
            if (flag == true)
            {
                this.ErrorMessage = "";
                TableName = TableName1;
                try
                {
                    if (System.Configuration.ConfigurationSettings.AppSettings["WriteLog"] == "true")
                    {
                        _writeLog = true;
                    }
                    else
                    {
                        _writeLog = false;
                    }
                    ConnectString = CST;
                    Conn = new System.Data.SqlClient.SqlConnection(ConnectString);
                    Dict = new Hashtable();
                    Result = new Hashtable();
                    MapTable = new Hashtable();
                    DS = new DataSet();

                    _path = "C:\\WebDebug.log";
                    if (_writeLog)
                    {
                        if (!File.Exists(_path))
                        {
                            using (SWCreate = File.CreateText(_path))
                            {
                                SWCreate.WriteLine(" ");
                                SWCreate.Close();
                            }
                        }
                        using (SWApp = File.AppendText(_path))
                        {
                            SWApp.WriteLine(" ");
                        }
                    }
                }
                catch (Exception e)
                {
                    this.ErrorMessage = e.ToString();
                }
            }
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// 执行无返回结果的SQL
        /// </summary>
        /// <param name="strSQL"></param>
        public void ExecuteNonQuery(string strSQL)
        {                   
            Comm = new SqlCommand();
            SqlTransaction Trans;
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandText = strSQL;
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;

            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  CommandString = " + strSQL);
                        SWApp.Close();
                    }
                }

                Comm.ExecuteNonQuery();
                Trans.Commit();
                this.Conn.Close();
            }
            catch (Exception e)
            {
                Trans.Rollback();
                this.Conn.Close();
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  Error Message ： " + e.ToString());
                    }
                }
                this.ErrorMessage = e.ToString();
                throw new Exception(e.ToString());
            }            
        }

        /// <summary>
        /// 执行无返回结果的SQL
        /// </summary>
        /// <param name="param">参数集合</param>
        /// <param name="strSQL"></param>
        public void ExecuteNonQuery(string strSQL, ICollection param)
        {           
            Comm = new SqlCommand();
            SqlTransaction Trans;
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandText = strSQL;
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;

            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  CommandString = " + strSQL);
                        SWApp.Close();
                    }
                }

                if (param != null)
                {
                    foreach (ParamInfo p in param)
                    {
                        IDbDataParameter pa = Comm.CreateParameter();
                        pa.ParameterName = p.Name;
                        pa.Value = p.Value;

                        //处理大文本
                        if (pa is System.Data.SqlClient.SqlParameter && pa.Value != null && pa.Value.ToString().Length >= 4000)
                        {
                            System.Data.SqlClient.SqlParameter p1 = pa as System.Data.SqlClient.SqlParameter;
                            p1.SqlDbType = System.Data.SqlDbType.NText;
                            Comm.Parameters.Add(p1);
                        }
                        else
                        {
                            Comm.Parameters.Add(pa);
                        }
                    }
                }

                Comm.ExecuteNonQuery();
                FillParameterValue(Comm.Parameters, param);
                Trans.Commit();
                this.Conn.Close();
            }
            catch (Exception e)
            {
                Trans.Rollback();
                this.Conn.Close();
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  Error Message ： " + e.ToString());
                    }
                }
                this.ErrorMessage = e.ToString();
                throw new Exception(e.ToString());
            }           
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 返回查询结果的第一行第一列的值
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public object ExecuteScalar(string strSQL)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandText = strSQL;
            Comm.Connection = Conn;
            Comm.Transaction =Trans ;             
            Comm.CommandTimeout = 60;
            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  CommandString = " + strSQL);
                        SWApp.Close();
                    }
                }

                object objResutl = Comm.ExecuteScalar();
                Trans.Commit();
                this.Conn.Close();
                return objResutl;
            }
            catch (Exception e)
            {
                Trans.Rollback();
                this.Conn.Close();
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  Error Message ： " + e.ToString());
                    }
                }
                this.ErrorMessage = e.ToString();
                throw new Exception(e.ToString());
            }
        }

        /// <summary>
        /// 返回查询结果的第一行第一列的值
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="param">参数集合</param>
        /// <returns></returns>
        public object ExecuteScalar(string strSQL,ICollection param)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandText = strSQL;
            Comm.Connection = Conn;
            Comm.Transaction =Trans ;             
            Comm.CommandTimeout = 60;
            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  CommandString = " + strSQL);
                        SWApp.Close();
                    }
                }
                if ( param != null )
                {
                    foreach ( ParamInfo p in param )
                    {
                        IDbDataParameter pa = Comm.CreateParameter();
                        pa.ParameterName = p.Name;
                        pa.Value = p.Value;
                        pa.Direction = p.Direction;

                        Comm.Parameters.Add(pa);
                    }
                }

                object objResutl = Comm.ExecuteScalar();
                FillParameterValue(Comm.Parameters,param);
                Trans.Commit();
                this.Conn.Close();
                return objResutl;

            }
            catch (Exception e)
            {
                Trans.Rollback();
                this.Conn.Close();
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  Error Message ： " + e.ToString());
                    }
                }
                this.ErrorMessage = e.ToString();
                throw new Exception(e.ToString());
            }
        }
        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// 执行SQL语句并返回DataTable对象
        /// </summary>
        public DataSet ExecuteDataSet(string strSQL)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandText = strSQL;
            Comm.Connection = Conn;
            Comm.Transaction =Trans ;             
            Comm.CommandTimeout = 60;
            DataSet ds = new DataSet();
            try
            {                
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
                        SWApp.WriteLine("CommandString = " + strSQL);
                        SWApp.Close();
                    }
                }

                try
                {
                    IDataReader dr = Comm.ExecuteReader();        
                    do
                    {
                        DataTable dt = new DataTable();
                        dt.Locale = CultureInfo.CurrentCulture;

                        DataColumn col = null;
                        DataRowCollection rows = dr.GetSchemaTable().Rows;
                        foreach(DataRow row in rows)
                        {
                            col = new DataColumn();
                            col.ColumnName = row["ColumnName"] == null ? null : row["ColumnName"].ToString();
                            col.Unique = Convert.ToBoolean(row["IsUnique"]);
                            col.AllowDBNull = Convert.ToBoolean(row["AllowDBNull"]);
                            col.ReadOnly = Convert.ToBoolean(row["IsReadOnly"]);
                            col.DataType = row["DataType"] as Type;

                            dt.Columns.Add(col);
                        }

                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();
                        
                            foreach(DataColumn c in dt.Columns)
                            {
                                row[c] = dr[c.ColumnName];
                            }

                            dt.Rows.Add(row);
                        }

                        ds.Tables.Add(dt);
                    }
                    while (dr.NextResult());
                    dr.Close();                    
                    Trans.Commit();
                }
                catch (Exception e)
                {
                    Trans.Rollback();                    
                    this.ErrorMessage = e.ToString();
                    if (_writeLog)
                    {
                        using (SWApp = File.AppendText(_path))
                        {
                            SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
                        }
                    }
                    throw new Exception(e.ToString());
                }

                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==>  End of Getting DataSet.");
                        SWApp.Close();
                    }
                }
            }
            finally
            {
                Conn.Close();
            }
            return ds;
        }

        /// <summary>
        /// 执行SQL语句并返回DataTable对象
        /// <param name="strSQL">SQL语句</param>
        /// <param name="param">参数集合</param>
        /// </summary>
        public DataSet ExecuteDataSet(string strSQL, ICollection param)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandText = strSQL;
            Comm.Connection = Conn;
            Comm.Transaction =Trans ;             
            Comm.CommandTimeout = 60;
            DataSet ds = new DataSet();
            try
            {                
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
                        SWApp.WriteLine("CommandString = " + strSQL);
                        SWApp.Close();
                    }
                }

                try
                {
                    if ( param != null )
                    {
                        foreach ( ParamInfo p in param )
                        {
                            IDbDataParameter pa = Comm.CreateParameter();
                            pa.ParameterName = p.Name;
                            pa.Value = p.Value;
                            pa.Direction = p.Direction;

                            Comm.Parameters.Add(pa);
                        }
                    }
                    IDataReader dr = Comm.ExecuteReader(); 

                    do
                    {
                        DataTable dt = new DataTable();
                        dt.Locale = CultureInfo.CurrentCulture;

                        DataColumn col = null;
                        DataRowCollection rows = dr.GetSchemaTable().Rows;
                        foreach(DataRow row in rows)
                        {
                            col = new DataColumn();
                            col.ColumnName = row["ColumnName"] == null ? null : row["ColumnName"].ToString();
                            col.Unique = Convert.ToBoolean(row["IsUnique"]);
                            col.AllowDBNull = Convert.ToBoolean(row["AllowDBNull"]);
                            col.ReadOnly = Convert.ToBoolean(row["IsReadOnly"]);
                            col.DataType = row["DataType"] as Type;

                            dt.Columns.Add(col);
                        }

                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();
                        
                            foreach(DataColumn c in dt.Columns)
                            {
                                row[c] = dr[c.ColumnName];
                            }

                            dt.Rows.Add(row);
                        }

                        ds.Tables.Add(dt);
                    }
                    while (dr.NextResult());
                    dr.Close();                    
                    Trans.Commit();
                }
                catch (Exception e)
                {
                    Trans.Rollback();                    
                    this.ErrorMessage = e.ToString();
                    if (_writeLog)
                    {
                        using (SWApp = File.AppendText(_path))
                        {
                            SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
                        }
                    }
                    throw new Exception(e.ToString());
                }

                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==>  End of Getting DataSet.");
                        SWApp.Close();
                    }
                }
            }
            finally
            {
                Conn.Close();
            }
            return ds;
        }
        #endregion

        #region ExecuteDataTable
        /// <summary>
        /// 执行SQL语句并返回DataTable对象
        /// </summary>
        public DataTable ExecuteDataTable(string strSQL)
        {
            return ExecuteDataSet(strSQL).Tables[0];
        }

        /// <summary>
        /// 执行SQL语句并返回DataTable对象
        /// <param name="strSQL">SQL语句</param>
        /// <param name="param">参数集合</param>
        /// </summary>
        public DataTable ExecuteDataTable(string strSQL, ICollection param)
        {
            return ExecuteDataSet(strSQL,param).Tables[0];
        }
        #endregion

        #region ExecuteDataReader
        /// <summary>
        /// <param name="strSQL">SQL语句</param>
        /// </summary>
        public IDataReader ExecuteDataReader(string strSQL)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandText = strSQL;
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;
            IDataReader dr ; 
            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataReader.");
                        SWApp.WriteLine("CommandString = " + strSQL);
                        SWApp.Close();
                    }
                }

                try
                {
                    dr=Comm.ExecuteReader();
                    Trans.Commit();
                }
                catch (Exception e)
                {
                    Trans.Rollback();
                    this.ErrorMessage = e.ToString();
                    if (_writeLog)
                    {
                        using (SWApp = File.AppendText(_path))
                        {
                            SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
                        }
                    }
                    throw new Exception(e.ToString());
                }

                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==>  End of Getting DataReader.");
                        SWApp.Close();
                    }
                }
            }
            finally
            {
                Conn.Close();
            }
            return dr;
        }

        /// <summary>
        ///
        /// <param name="strSQL">SQL语句</param>
        /// <param name="param">参数集合</param>
        /// </summary>
        public IDataReader ExecuteDataReader(string strSQL, ICollection param)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandText = strSQL;
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;
            IDataReader dr;            
            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataReader.");
                        SWApp.WriteLine("CommandString = " + strSQL);
                        SWApp.Close();
                    }
                }

                try
                {
                    if (param != null)
                    {
                        foreach (ParamInfo p in param)
                        {
                            IDbDataParameter pa = Comm.CreateParameter();
                            pa.ParameterName = p.Name;
                            pa.Value = p.Value;
                            pa.Direction = p.Direction;

                            Comm.Parameters.Add(pa);
                        }
                    }
                    dr = Comm.ExecuteReader();
                    Trans.Commit();                    
                }
                catch (Exception e)
                {
                    Trans.Rollback();
                    this.ErrorMessage = e.ToString();
                    if (_writeLog)
                    {
                        using (SWApp = File.AppendText(_path))
                        {
                            SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
                        }
                    }
                    throw new Exception(e.ToString());
                }

                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==>  End of Getting DataReader.");
                        SWApp.Close();
                    }
                }
            }
            finally
            {
                Conn.Close();
            }
            return dr;
        }
        #endregion

        #region ExecuteProcNonQuery
        /// <summary>
        /// 执行无返回结果的存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        public void ExecuteProcNonQuery(string procName)
        {            
            Comm = new SqlCommand();
            SqlTransaction Trans;
            Conn.Open();
            Trans = Conn.BeginTransaction();  
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.CommandText = procName;                      
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;

            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  StoredProcedureName = " + procName);
                        SWApp.Close();
                    }
                }
                Comm.ExecuteNonQuery();
                Trans.Commit();
                this.Conn.Close();
            }
            catch (Exception e)
            {
                Trans.Rollback();
                this.Conn.Close();
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  Error Message ： " + e.ToString());
                    }
                }
                this.ErrorMessage = e.ToString();
                throw new Exception(e.ToString());
            }           
        }

        /// <summary>
        /// 执行无返回结果的存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="param">参数集合</param>
        public void ExecuteProcNonQuery(string procName, ICollection param)
        {            
            Comm = new SqlCommand();
            SqlTransaction Trans;
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.CommandText = procName;
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;

            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  StoredProcedureName = " + procName);
                        SWApp.Close();
                    }
                }

                if (param != null)
                {
                    foreach (ParamInfo p in param)
                    {
                        IDbDataParameter pa = Comm.CreateParameter();
                        pa.ParameterName = p.Name;
                        pa.Value = p.Value;

                        //处理大文本
                        if (pa is System.Data.SqlClient.SqlParameter && pa.Value != null && pa.Value.ToString().Length >= 4000)
                        {
                            System.Data.SqlClient.SqlParameter p1 = pa as System.Data.SqlClient.SqlParameter;
                            p1.SqlDbType = System.Data.SqlDbType.NText;
                            Comm.Parameters.Add(p1);
                        }
                        else
                        {
                            Comm.Parameters.Add(pa);
                        }
                    }
                }

                Comm.ExecuteNonQuery();
                FillParameterValue(Comm.Parameters, param);
                Trans.Commit();
                this.Conn.Close();
            }
            catch (Exception e)
            {
                Trans.Rollback();
                this.Conn.Close();
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  Error Message ： " + e.ToString());
                    }
                }
                this.ErrorMessage = e.ToString();
                throw new Exception(e.ToString());
            }           
        }
        #endregion

        #region ExecuteProcScalar
        /// <summary>
        /// 执行存储过程并返回第一行第一列的值
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <returns></returns>
        public object ExecuteProcScalar(string procName)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.CommandText = procName;
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;
            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  StoredProcedureName = " + procName);
                        SWApp.Close();
                    }
                }

                object objResutl = Comm.ExecuteScalar();
                Trans.Commit();
                this.Conn.Close();
                return objResutl;
            }
            catch (Exception e)
            {
                Trans.Rollback();
                this.Conn.Close();
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  Error Message ： " + e.ToString());
                    }
                }
                this.ErrorMessage = e.ToString();
                throw new Exception(e.ToString());
            }
        }

        /// <summary>
        /// 执行存储过程并返回第一行第一列的值
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="param">参数集合</param>
        /// <returns></returns>
        public object ExecuteProcScalar(string procName, ICollection param)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.CommandText = procName;
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;
            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  StoredProcedureName = " + procName);
                        SWApp.Close();
                    }
                }
                if (param != null)
                {
                    foreach (ParamInfo p in param)
                    {
                        IDbDataParameter pa = Comm.CreateParameter();
                        pa.ParameterName = p.Name;
                        pa.Value = p.Value;
                        pa.Direction = p.Direction;

                        Comm.Parameters.Add(pa);
                    }
                }

                object objResutl = Comm.ExecuteScalar();
                FillParameterValue(Comm.Parameters, param);
                Trans.Commit();
                this.Conn.Close();
                return objResutl;

            }
            catch (Exception e)
            {
                Trans.Rollback();
                this.Conn.Close();
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]  Error Message ： " + e.ToString());
                    }
                }
                this.ErrorMessage = e.ToString();
                throw new Exception(e.ToString());
            }
        }
        #endregion

        #region  ExecuteProcDataSet
        /// <summary>
        ///执行存储过程并返回DataSet对象
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <returns>返回DataSet对象</returns>
        public DataSet ExecuteProcDataSet(string procName)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.CommandText = procName;
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;
            DataSet ds = new DataSet();
            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
                        SWApp.WriteLine("StoredProcedureName = " + procName);
                        SWApp.Close();
                    }
                }

                try
                {
                    IDataReader dr = Comm.ExecuteReader();
                    do
                    {
                        DataTable dt = new DataTable();
                        dt.Locale = CultureInfo.CurrentCulture;

                        DataColumn col = null;
                        DataRowCollection rows = dr.GetSchemaTable().Rows;
                        foreach (DataRow row in rows)
                        {
                            col = new DataColumn();
                            col.ColumnName = row["ColumnName"] == null ? null : row["ColumnName"].ToString();
                            col.Unique = Convert.ToBoolean(row["IsUnique"]);
                            col.AllowDBNull = Convert.ToBoolean(row["AllowDBNull"]);
                            col.ReadOnly = Convert.ToBoolean(row["IsReadOnly"]);
                            col.DataType = row["DataType"] as Type;

                            dt.Columns.Add(col);
                        }

                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();

                            foreach (DataColumn c in dt.Columns)
                            {
                                row[c] = dr[c.ColumnName];
                            }

                            dt.Rows.Add(row);
                        }

                        ds.Tables.Add(dt);
                    }
                    while (dr.NextResult());
                    dr.Close();
                    Trans.Commit();
                }
                catch (Exception e)
                {
                    Trans.Rollback();
                    this.ErrorMessage = e.ToString();
                    if (_writeLog)
                    {
                        using (SWApp = File.AppendText(_path))
                        {
                            SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
                        }
                    }
                    throw new Exception(e.ToString());
                }

                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==>  End of Getting DataSet.");
                        SWApp.Close();
                    }
                }
            }
            finally
            {
                Conn.Close();
            }
            return ds;
        }

        /// <summary>
        /// 执行存储过程并返回DataSet对象
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="param">参数集合</param>
        /// <returns>返回DataSet对象</returns>
        public DataSet ExecuteProcDataSet(string procName, ICollection param)
        {
            SqlTransaction Trans;
            Comm = new SqlCommand();
            Conn.Open();
            Trans = Conn.BeginTransaction();
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.CommandText = procName;
            Comm.Connection = Conn;
            Comm.Transaction = Trans;
            Comm.CommandTimeout = 60;
            DataSet ds = new DataSet();
            try
            {
                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Begin Get DataSet.");
                        SWApp.WriteLine("StoredProcedureName = " + procName);
                        SWApp.Close();
                    }
                }

                try
                {
                    if (param != null)
                    {
                        foreach (ParamInfo p in param)
                        {
                            IDbDataParameter pa = Comm.CreateParameter();
                            pa.ParameterName = p.Name;
                            pa.Value = p.Value;
                            pa.Direction = p.Direction;

                            Comm.Parameters.Add(pa);
                        }
                    }
                    IDataReader dr = Comm.ExecuteReader();

                    do
                    {
                        DataTable dt = new DataTable();
                        dt.Locale = CultureInfo.CurrentCulture;

                        DataColumn col = null;
                        DataRowCollection rows = dr.GetSchemaTable().Rows;
                        foreach (DataRow row in rows)
                        {
                            col = new DataColumn();
                            col.ColumnName = row["ColumnName"] == null ? null : row["ColumnName"].ToString();
                            col.Unique = Convert.ToBoolean(row["IsUnique"]);
                            col.AllowDBNull = Convert.ToBoolean(row["AllowDBNull"]);
                            col.ReadOnly = Convert.ToBoolean(row["IsReadOnly"]);
                            col.DataType = row["DataType"] as Type;

                            dt.Columns.Add(col);
                        }

                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();

                            foreach (DataColumn c in dt.Columns)
                            {
                                row[c] = dr[c.ColumnName];
                            }

                            dt.Rows.Add(row);
                        }

                        ds.Tables.Add(dt);
                    }
                    while (dr.NextResult());
                    dr.Close();
                    Trans.Commit();
                }
                catch (Exception e)
                {
                    Trans.Rollback();
                    this.ErrorMessage = e.ToString();
                    if (_writeLog)
                    {
                        using (SWApp = File.AppendText(_path))
                        {
                            SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==> Error Message： " + e.ToString());
                        }
                    }
                    throw new Exception(e.ToString());
                }

                if (_writeLog)
                {
                    using (SWApp = File.AppendText(_path))
                    {
                        SWApp.WriteLine("[" + DateTime.Now.ToString() + "]==>  End of Getting DataSet.");
                        SWApp.Close();
                    }
                }
            }
            finally
            {
                Conn.Close();
            }
            return ds;
        }
        #endregion

        #region ExecuteProcDataTable
        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <returns>返回DataTable对象</returns>
        public DataTable ExecuteProcDataTable(string procName)
        {
            return ExecuteProcDataSet(procName).Tables[0];
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="param">参数集合</param>
        /// <returns>返回DataTable对象</returns>
        public DataTable ExecuteProcDataTable(string procName, ICollection param)
        {
            return ExecuteProcDataSet(procName, param).Tables[0];
        }
        #endregion        

        #region FillParameterValue
        /// <summary>
        /// 填充输出型参数和返回值型参数
        /// </summary>
        /// <param name="OutPutParameters">SQL命令执行后的参数集合</param>
        /// <param name="param">SQL命令执行前的参数集合</param>
        void FillParameterValue(System.Data.IDataParameterCollection OutPutParameters, ICollection param)
        {
            if (OutPutParameters == null || param == null) return;

            ArrayList procParam = new ArrayList();
            foreach (IDbDataParameter sqlParameter in OutPutParameters) //记录经过存储过程运算后的参数值
            {
                foreach (ParamInfo p in param)
                {
                    if (p.Name == sqlParameter.ParameterName)
                    {
                        procParam.Add(new ParamInfo(p.Name, sqlParameter.Value, p.Direction, p.Size));
                    }
                }
            }

            ArrayList procOutParam = param as ArrayList;
            procOutParam.Clear();
            foreach (ParamInfo p in procParam) //填充参数值
            {
                procOutParam.Add(new ParamInfo(p.Name, p.Value, p.Direction, p.Size));
            }
        }
        #endregion
    }        
}

复制代码

using  System;
using  System.Data;
using  System.Configuration;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.HtmlControls;
using  System.Web.UI.WebControls;

namespace  VoteInfo
{
    /// <summary>
    /// SQL参数结构体
    /// </summary>
    public struct ParamInfo
    {
        /// <summary>
        /// 参数名称
        /// </summary>
        public string Name;

        /// <summary>
        /// 值
        /// </summary>
        public object Value;

        /// <summary>
        /// 参数长度
        /// </summary>
        public int Size;

        /// <summary>
        /// 参数方向
        /// </summary>
        public ParameterDirection Direction;

        /// <summary>
        /// 初始化参数对象
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="val">值</param>
        public ParamInfo(string name, object val)
        {
            Name = name;
            Value = val;
            Direction = ParameterDirection.Input;
            Size = Value == null ? 50 : Value.ToString().Length;
        }

        /// <summary>
        /// 初始化参数对象
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="val">值</param>
        /// <param name="direction"></param>
        public ParamInfo(string name, object val, ParameterDirection direction)
        {
            Name = name;
            Value = val;
            Direction = direction;
            Size = Value == null ? 50 : Value.ToString().Length;
        }

        public ParamInfo(string name, object val, ParameterDirection direction, int size)
        {
            Name = name;
            Value = val;
            Direction = direction;
            Size = size;
        }
    }
}