DBObject数据库操作底层类

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Diagnostics;
using System.Xml;

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace SqlDataProvider
{
    #region DBObject数据库操作底层类
    public sealed class DbObject : IDisposable
    {
        #region 定义变量
        System.Data.SqlClient.SqlConnection __SqlConnection;
        System.Data.SqlClient.SqlCommand __SqlCommand;
        System.Data.SqlClient.SqlDataAdapter __SqlDataAdapter;
        #endregion

        #region 构造函数
        public DbObject()
        {
            __SqlConnection = new SqlConnection(SqlHelper.ConnectionString);
        }

        public DbObject(string _ConnectionString)
        {
            __SqlConnection = new SqlConnection(_ConnectionString);
        }
        #endregion

        #region 检测是否打开连接
        private static bool OpenConnection(System.Data.SqlClient.SqlConnection __SqlConnection)
        {
            bool result = false;
            try
            {
                if (__SqlConnection.State != ConnectionState.Open)
                {
                    __SqlConnection.Open();
                    result = true;
                }
                else
                {
                    result = true;
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                ApplicationLog.WriteError("打开数据库连接错误:" + ex.Message.Trim());
                result = false;
            }

            return result;
        }
        #endregion

        #region 执行sql语句，返回bool型执行结果
        /// <summary>
        ///  执行sql语句，返回bool型执行结果
        /// </summary>
        /// <param name="Sqlcomm">sql语句</param>
        /// <returns>bool型变量</returns>
        public bool Exesqlcomm(string Sqlcomm)
        {
            if (!OpenConnection(this.__SqlConnection)) return false;

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.CommandType = CommandType.Text;
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandText = Sqlcomm;
                this.__SqlCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行sql语句: " + Sqlcomm + "错误信息为: " + ex.Message.Trim());
                return false;
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return true;
        }
        #endregion

        #region 执行sql语句，返回int型第一行第一列数值
        /// <summary>
        /// 执行sql语句，返回第一行第一列
        /// </summary>
        /// <param name="Sqlcomm">sql语句</param>
        /// <returns>返回的第一行第一列的数值</returns>
        public int GetRecordCount(string Sqlcomm)
        {
            int retval = 0;

            if (!OpenConnection(this.__SqlConnection))
            {
                retval = 0;
            }
            else
            {
                try
                {
                    this.__SqlCommand = new SqlCommand();
                    this.__SqlCommand.Connection = this.__SqlConnection;
                    this.__SqlCommand.CommandType = CommandType.Text;
                    this.__SqlCommand.CommandText = Sqlcomm;

                    if (this.__SqlCommand.ExecuteScalar() == null) { retval = 0; }
                    else { retval = (int)this.__SqlCommand.ExecuteScalar(); }
                }
                catch (SqlException ex)
                {
                    ApplicationLog.WriteError("执行sql语句: " + Sqlcomm + "错误信息为: " + ex.Message.Trim());
                }
                finally
                {
                    this.__SqlConnection.Close();
                    this.Dispose(true);
                }
            }

            return retval;
        }
        #endregion

        #region 执行sql语句，返回DataTable类型数据集
        /// <summary>
        /// 执行sql语句。返回DataTable类型数据集
        /// </summary>
        /// <param name="TableName">数据表名</param>
        /// <param name="Sqlcomm">sql语句</param>
        /// <returns>返回DataTable类型数据集</returns>
        public DataTable GetDataTableBySqlcomm(string TableName, string Sqlcomm)
        {
            System.Data.DataTable ResultTable = new DataTable(TableName);

            if (!OpenConnection(this.__SqlConnection)) return ResultTable;

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.Text;
                this.__SqlCommand.CommandText = Sqlcomm;

                this.__SqlDataAdapter = new SqlDataAdapter();
                this.__SqlDataAdapter.SelectCommand = this.__SqlCommand;

                this.__SqlDataAdapter.Fill(ResultTable);
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行sql语句: " + Sqlcomm + "错误信息为: " + ex.Message.Trim());
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return ResultTable;
        }
        #endregion

        #region 执行sql语句，返回DataTable类型数据集，提供分页
        /// <summary>
        /// 执行sql语句，返回DataTable类型数据集，提供分页
        /// </summary>
        /// <param name="TableName">数据表名</param>
        /// <param name="ProcedureName">sql语句</param>
        /// <param name="StartRecordNo">开始行数</param>
        /// <param name="PageSize">一页的大小</param>
        /// <returns>返回分页DataTable类型数据集</returns>
        public DataTable GetDataTableBySqlcomm(string TableName, string Sqlcomm, int StartRecordNo, int PageSize)
        {
            DataTable RetTable = new DataTable(TableName);

            if (!OpenConnection(this.__SqlConnection))
            {
                RetTable.Dispose();
                this.Dispose(true);
                return RetTable;
            }

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.Text;
                this.__SqlCommand.CommandText = Sqlcomm;

                this.__SqlDataAdapter = new SqlDataAdapter();
                this.__SqlDataAdapter.SelectCommand = this.__SqlCommand;

                DataSet ds = new DataSet();
                ds.Tables.Add(RetTable);

                this.__SqlDataAdapter.Fill(ds, StartRecordNo, PageSize, TableName);
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行sql语句: " + Sqlcomm + "错误信息为: " + ex.Message.Trim());
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return RetTable;
        }
        #endregion

        #region 执行带参数存储过程，返回执行的bool型结果
        /// <summary>
        /// 执行带参数存储过程，返回执行的bool型结果
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="SqlParameters">参数数组</param>
        /// <returns>执行的bool型结果</returns>
        public bool RunProcedure(string ProcedureName, SqlParameter[] SqlParameters)
        {
            if (!OpenConnection(this.__SqlConnection)) return false;

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                foreach (SqlParameter parameter in SqlParameters)
                {
                    this.__SqlCommand.Parameters.Add(parameter);
                }

                this.__SqlCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
                return false;
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return true;
        }
        #endregion

        #region 执行不带参数存储过程，返回执行的bool型结果
        /// <summary>
        /// 执行不带参数存储过程，返回执行的bool型结果
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>
        /// <returns>执行的bool型结果</returns>
        public bool RunProcedure(string ProcedureName)
        {
            if (!OpenConnection(this.__SqlConnection)) return false;

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                this.__SqlCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
                return false;
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return true;
        }
        #endregion

        #region 执行无参数存储过程，得到SqlDataReader类型数据集，返回bool型执行结果
        /// <summary>
        /// 执行无参数存储过程，得到SqlDataReader类型数据集，返回bool型执行结果 
        /// </summary>
        /// <param name="ResultDataReader">SqlDataReader类型数据集名称</param>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <returns>返回bool型执行结果</returns>
        public bool GetReader(ref SqlDataReader ResultDataReader, string ProcedureName)
        {
            if (!OpenConnection(this.__SqlConnection)) return false;

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                ResultDataReader = this.__SqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
                return false;
            }

            return true;
        }
        #endregion

        #region 执行有参数存储过程，得到SqlDataReader类型数据集，返回bool型执行结果
        /// <summary>
        /// 执行有参数存储过程，得到SqlDataReader类型数据集，返回bool型执行结果
        /// </summary>
        /// <param name="ResultDataReader">SqlDataReader类型数据集名称</param>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="SqlParameters">存储过程参数数组</param>
        /// <returns>返回bool型执行结果</returns>
        public bool GetReader(ref SqlDataReader ResultDataReader, string ProcedureName, SqlParameter[] SqlParameters)
        {
            if (!OpenConnection(this.__SqlConnection)) return false;

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                foreach (SqlParameter parameter in SqlParameters)
                {
                    this.__SqlCommand.Parameters.Add(parameter);
                }

                ResultDataReader = this.__SqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
                return false;
            }

            return true;
        }
        #endregion

        #region 执行有参数存储过程，返回DataTable类型数据集
        /// <summary>
        /// 执行有参数存储过程，返回DataTable类型数据集
        /// </summary>
        /// <param name="TableName">数据表名称</param>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="SqlParameters">参数数组</param>
        /// <returns>返回DataTable类型数据集</returns>
        public DataTable GetDataTable(string TableName, string ProcedureName, SqlParameter[] SqlParameters)
        {
            System.Data.DataTable FullTable = new DataTable(TableName);

            if (!OpenConnection(this.__SqlConnection))
            {
                FullTable.Dispose();
                this.Dispose(true);
                return FullTable;
            }

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                foreach (SqlParameter parameter in SqlParameters)
                {
                    this.__SqlCommand.Parameters.Add(parameter);
                }

                this.__SqlDataAdapter = new SqlDataAdapter();
                this.__SqlDataAdapter.SelectCommand = this.__SqlCommand;
                this.__SqlDataAdapter.Fill(FullTable);
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return FullTable;
        }
        #endregion

        #region 执行无参数存储过程，返回DataTable类型数据集
        /// <summary>
        /// 执行无参数存储过程，返回DataTable类型数据集
        /// </summary>
        /// <param name="TableName">数据表名称</param>
        /// <param name="ProcedureName">存储过程名</param>
        /// <returns>返回DataTable类型数据集</returns>
        public DataTable GetDataTable(string TableName, string ProcedureName)
        {

            System.Data.DataTable FullTable = new DataTable(TableName);

            if (!OpenConnection(this.__SqlConnection))
            {
                FullTable.Dispose();
                this.Dispose(true);
                return FullTable;
            }

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                this.__SqlDataAdapter = new SqlDataAdapter();
                this.__SqlDataAdapter.SelectCommand = this.__SqlCommand;

                this.__SqlDataAdapter.Fill(FullTable);
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return FullTable;
        }
        #endregion

        #region 执行无参数存储过程，返回DataTable类型数据集，提供分页
        /// <summary>
        /// 执行无参数存储过程，返回DataTable类型数据集，提供分页
        /// </summary>
        /// <param name="TableName">数据表名</param>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="StartRecordNo">开始行数</param>
        /// <param name="PageSize">一页的大小</param>
        /// <returns>返回分页DataTable类型数据集</returns>
        public DataTable GetDataTable(string TableName, string ProcedureName, int StartRecordNo, int PageSize)
        {
            DataTable RetTable = new DataTable(TableName);

            if (!OpenConnection(this.__SqlConnection))
            {
                RetTable.Dispose();
                this.Dispose(true);
                return RetTable;
            }

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                this.__SqlDataAdapter = new SqlDataAdapter();
                this.__SqlDataAdapter.SelectCommand = this.__SqlCommand;

                DataSet ds = new DataSet();
                ds.Tables.Add(RetTable);

                this.__SqlDataAdapter.Fill(ds, StartRecordNo, PageSize, TableName);
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return RetTable;
        }
        #endregion

        #region 执行有参数存储过程，返回DataTable类型数据集，提供分页
        /// <summary>
        /// 执行有参数存储过程，返回DataTable类型数据集，提供分页
        /// </summary>
        /// <param name="TableName">数据表名</param>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="SqlParameters">参数数组</param>
        /// <param name="StartRecordNo">开始行数</param>
        /// <param name="PageSize">一页的大小</param>
        /// <returns>返回分页DataTable类型数据集</returns>
        public DataTable GetDataTable(string TableName, string ProcedureName, SqlParameter[] SqlParameters, int StartRecordNo, int PageSize)
        {

            DataTable RetTable = new DataTable(TableName);

            if (!OpenConnection(this.__SqlConnection))
            {
                RetTable.Dispose();
                this.Dispose(true);
                return RetTable;
            }

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                foreach (SqlParameter parameter in SqlParameters)
                {
                    this.__SqlCommand.Parameters.Add(parameter);
                }

                this.__SqlDataAdapter = new SqlDataAdapter();
                this.__SqlDataAdapter.SelectCommand = this.__SqlCommand;

                DataSet ds = new DataSet();
                ds.Tables.Add(RetTable);

                this.__SqlDataAdapter.Fill(ds, StartRecordNo, PageSize, TableName);
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return RetTable;
        }
        #endregion

        #region 执行无参数存储过程，得到分页DataTable类型数据集，返回bool型执行结果
        /// <summary>
        /// 执行无参数存储过程，得到分页DataTable类型数据集，返回bool型执行结果
        /// </summary>
        /// <param name="ResultTable">分页DataTable类型数据集</param>
        /// <param name="TableName">数据表名</param>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="StartRecordNo">开始行数</param>
        /// <param name="PageSize">一页的大小</param>
        /// <returns>返回bool型执行结果</returns>
        public bool GetDataTable(ref DataTable ResultTable, string TableName, string ProcedureName, int StartRecordNo, int PageSize)
        {
            ResultTable = null;

            if (!OpenConnection(this.__SqlConnection)) return false;

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                this.__SqlDataAdapter = new SqlDataAdapter();
                this.__SqlDataAdapter.SelectCommand = this.__SqlCommand;

                DataSet ds = new DataSet();
                ds.Tables.Add(ResultTable);

                this.__SqlDataAdapter.Fill(ds, StartRecordNo, PageSize, TableName);
                ResultTable = ds.Tables[TableName];
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
                return false;
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return true;
        }
        #endregion

        #region 执行有参数存储过程，得到分页DataTable类型数据集，返回bool型执行结果
        /// <summary>
        /// 执行有参数存储过程，得到分页DataTable类型数据集，返回bool型执行结果
        /// </summary>
        /// <param name="ResultTable">分页DataTable类型数据集</param>
        /// <param name="TableName">数据表名</param>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="StartRecordNo">开始行数</param>
        /// <param name="PageSize">一页的大小</param>
        /// <param name="SqlParameters">参数数组</param>
        /// <returns>返回bool型执行结果</returns>
        public bool GetDataTable(ref DataTable ResultTable, string TableName, string ProcedureName, int StartRecordNo, int PageSize, SqlParameter[] SqlParameters)
        {
            if (!OpenConnection(this.__SqlConnection)) return false;

            try
            {
                this.__SqlCommand = new SqlCommand();
                this.__SqlCommand.Connection = this.__SqlConnection;
                this.__SqlCommand.CommandType = CommandType.StoredProcedure;
                this.__SqlCommand.CommandText = ProcedureName;

                foreach (SqlParameter parameter in SqlParameters)
                {
                    this.__SqlCommand.Parameters.Add(parameter);
                }

                this.__SqlDataAdapter = new SqlDataAdapter();
                this.__SqlDataAdapter.SelectCommand = this.__SqlCommand;

                DataSet ds = new DataSet();
                ds.Tables.Add(ResultTable);

                this.__SqlDataAdapter.Fill(ds, StartRecordNo, PageSize, TableName);
                ResultTable = ds.Tables[TableName];
            }
            catch (SqlException ex)
            {
                ApplicationLog.WriteError("执行存储过程: " + ProcedureName + "错误信息为: " + ex.Message.Trim());
                return false;
            }
            finally
            {
                this.__SqlConnection.Close();
                this.Dispose(true);
            }

            return true;
        }
        #endregion

        #region 析构函数
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(true);
        }

        private void Dispose(bool disposing)
        {
            if (!disposing)
                return;

            if (this.__SqlDataAdapter != null)
            {
                if (this.__SqlDataAdapter.SelectCommand != null)
                {
                    if (this.__SqlCommand.Connection != null)
                        this.__SqlDataAdapter.SelectCommand.Connection.Dispose();
                    this.__SqlDataAdapter.SelectCommand.Dispose();
                }
                this.__SqlDataAdapter.Dispose();
                this.__SqlDataAdapter = null;
            }
        }
        #endregion
    }
    #endregion   

    #region 添加日志静态方法
    public static class ApplicationLog
    {
        #region
        public static void WriteError(String message)
        {
            WriteLog(TraceLevel.Error, message);
        }

        private static void WriteLog(TraceLevel level, String messageText)
        {
            try
            {
                EventLogEntryType LogEntryType;
                switch (level)
                {
                    case TraceLevel.Error:
                        LogEntryType = EventLogEntryType.Error;
                        break;
                    default:
                        LogEntryType = EventLogEntryType.Error;
                        break;
                }

                String LogName = "Application";
                if (!EventLog.SourceExists(LogName))
                {
                    EventLog.CreateEventSource(LogName, "BIZ");
                }

                EventLog eventLog = new EventLog(LogName, ".", LogName);
                eventLog.WriteEntry(messageText, LogEntryType);
            }
            catch
            {

            }
        }
        #endregion
    }
    #endregion
}