数据访问逻辑类 clsSQLDB

///////////////////////////////////////////////////////////////////////////////
///
///数据访问逻辑类操作说明：
///    1.在执行任何数据操作之前，首先执行Open(),否则返回null或-1;
///    2.在执行任何操作之后，执行Close()，或Dispose()
///    3.类扑获所有数据库操作的异常。
///    4.ExecuteDataAdapter不支持事务处理。需要在操作前手工操作
///    5.执行带参数的Sql语句的格式必须符合Sql的格式要求，即：@parametername;
///    6.对于大量的数据操作请直接构造Sql语句，对少量的数据操作可以采用本类提供的数据操作方法。
///    7.在DataReader操作之后，请执行Close()命令。
///    
///    示例：
///            String[] strColumn = new String[]{"TableName","TableDescribe","TitleName","FlagEstablish","FlagDel"};
///            object[] objValue = new Object[] {"asdf","xxx","",0,0};
///            String[] strKey = new String[] {"TableID"};
///            object[] objKey = new Object[]{333};
///            db.RecordAddNew("_RHOA_Table",strColumn,objValue);
///            db.RecordSave("_RHOA_Table",strColumn,objValue,strKey,objKey);
///            db.RecordDelete("_RHOA_Table",strKey,objKey,"");
///
///
///////////////////////////////////////////////////////////////////////////////

using System;
using System.Data.OleDb;
using System.Xml;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;
using System.Text;

namespace BLLV2.DBAccess
{
    /// <summary>
    /// clsDB 的摘要说明。
    /// </summary>
    public class clsSQLDB:IDatabase
    {
        private SqlConnection mConn;
        private SqlTransaction mTrans;
        private bool mblnInTrans;
        private bool mblnOpened;

        public clsSQLDB(String strConnectionString)
        {
            try
            {
                mConn = new SqlConnection(strConnectionString);
                this.mTrans = null;
                this.mblnInTrans = false;
                mblnOpened = false;
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"clsSQLDB构造函数错误：" + strConnectionString);
                mConn = null;
                this.mTrans = null;
                this.mblnInTrans = false;
                mblnOpened = false;
            }
        }


        #region 数据库操作：打开、关闭、释放资源

        /// <summary>
        /// 是否成功打开数据库连接
        /// </summary>
        /// <returns></returns>
        public override bool  Open()
        {
            try
            {
                if(mConn.State == ConnectionState.Closed )
                {
                    mConn.Open();
                    this.mblnOpened = true;
                    this.mblnInTrans = false;
                }
                return true;
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"数据库打开错误；");
                return false;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public override void Close()
        {
            try
            {
                if(mConn.State != ConnectionState.Closed)
                {
                    mConn.Close();
                    this.mblnOpened = false;
                    this.mblnInTrans = false;
                }
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"数据库关闭错误；");
            }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public override void Dispose()
        {
            this.Close();
            if(mConn!=null)    mConn.Dispose();
        }

        
        #endregion

        #region 数据操作：添加记录、修改记录、保存记录、删除记录

        /// <summary>
        /// 保存一条记录
        /// </summary>
        /// <param name="strTableName">表名称</param>
        /// <param name="strColumns">字段名称</param>
        /// <param name="objValues">字段值</param>
        /// <param name="strKeys">主键字段</param>
        /// <param name="objKeys">主键字段值</param>
        /// <returns>是否成功</returns>
        public override bool RecordSave(String strTableName,
            String[] strColumns,
            object[] objValues,
            String[] strKeys,
            object[] objKeys)
        {
            String strSql = "";
            Object objExistNum;
            String[] strColumns_;
            object[] objValues_;

            try
            {
                if(!this.mblnOpened)
                {
                    this.ErrorLog("RecordAddNew","数据库尚未打开连接","");
                    return false;
                }
                else if(strColumns==null || objValues==null )
                {
                    this.ErrorLog("RecordAddNew","参数不可以为空引用","");
                    return false;
                }
                else if(strColumns.Length != objValues.Length )
                {
                    this.ErrorLog("RecordAddNew","参数匹配错误","");
                    return false;
                }
                else if(strColumns.Length < 1)
                {
                    this.ErrorLog("RecordAddNew","缺少参数","");
                    return false;
                }
                else if(strKeys!=null && objKeys!=null && strKeys.Length != objKeys.Length )
                {
                    this.ErrorLog("RecordUpdate","参数匹配错误","");
                    return false;
                }
                else
                {
                    if(strKeys!=null && objKeys!=null)
                    {
                        //如果存在主键
                        //根据主键生成条件
                        for(Int32 i=0;i<strKeys.Length;i++)
                        {
                            if(objKeys[i].GetType() == typeof(String))
                            {
                                strSql = strSql + " AND " + strKeys[i] + "='"+Convert.ToString(objKeys[i])+"'";
                            }
                            else
                            {
                                strSql = strSql + " AND " + strKeys[i] + "=" + objKeys[i];
                            }
                        }
                        strSql = "SELECT COUNT(*) FROM " + strTableName + " WHERE " + strSql.Trim().TrimStart("AND".ToCharArray());
                        objExistNum = this.ExecuteScalar(strSql);
                        if(Convert.ToInt32(objExistNum) == 0)
                        {
                            //不存在相同的主键记录
                            strColumns_ = new String[strKeys.Length + strColumns.Length];
                            objValues_ = new object[strKeys.Length + strColumns.Length];
                            strKeys.CopyTo(strColumns_,0);
                            strColumns.CopyTo(strColumns_,strKeys.Length);
                            objKeys.CopyTo(objValues_,0);
                            objValues.CopyTo(objValues_,objKeys.Length );
                            return this.RecordAddNew(strTableName,strColumns_,objValues_);
                        }
                        else
                        {
                            //存在相同的主键记录
                            return this.RecordUpdate(strTableName,strColumns,objValues,strKeys,objKeys,"");
                        }
                    }
                    else
                    {
                        return this.RecordAddNew(strTableName,strColumns,objValues);
                    }

                }

            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"RecordSave;操作错误" + strTableName);
                return false;
            }
        }
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="strTableName">表名称</param>
        /// <param name="strColumns">字段名称</param>
        /// <param name="objValues">字段值</param>
        /// <returns>是否成功</returns>
        public override bool RecordAddNew(String strTableName,
            String[] strColumns,
            object[] objValues)
        {
            String strSql = "";
            
            try
            {
                if(!this.mblnOpened)
                {
                    this.ErrorLog("RecordAddNew","数据库尚未打开连接","");
                    return false;
                }
                else if(strColumns==null || objValues==null )
                {
                    this.ErrorLog("RecordAddNew","参数不可以为空引用","");
                    return false;
                }
                else if(strColumns.Length != objValues.Length )
                {
                    this.ErrorLog("RecordAddNew","参数匹配错误","");
                    return false;
                }
                else if(strColumns.Length < 1)
                {
                    this.ErrorLog("RecordAddNew","缺少参数","");
                    return false;
                }
                else
                {
                    foreach(String strFieldName in strColumns)
                    {
                        strSql = strSql +",@" + strFieldName.Trim();
                    }
                    strSql = "INSERT INTO " + strTableName + " (" + strSql.Replace("@","").TrimStart(',') + ") VALUES("+strSql.TrimStart(',')+")";
                    if(this.ExecuteNonQuery(strSql,  strColumns,  objValues)!=-1)
                        return true;
                    else
                        return false;
                }
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"RecordAddNew；无法执行：" + strTableName);
                return false;
            }
        }
        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="strTableName">表名称</param>
        /// <param name="strColumns">字段名称</param>
        /// <param name="objValues">字段值</param>
        /// <param name="strKeys">主键字段</param>
        /// <param name="objKeys">主键字段值</param>
        /// <param name="strCustomCondition">其他条件</param>
        /// <returns>是否成功</returns>
        public override bool RecordUpdate(String strTableName,
            String[] strColumns,
            object[] objValues,
            String[] strKeys,
            object[] objKeys,
            String strCustomCondition)
        {
            String strSql = "";
            String strSqlWhere = "";

            try
            {
                if(!this.mblnOpened)
                {
                    this.ErrorLog("RecordUpdate","数据库尚未打开连接","");
                    return false;
                }
                else if(strColumns==null || objValues==null )
                {
                    this.ErrorLog("RecordAddNew","参数不可以为空引用","");
                    return false;
                }
                else if(strColumns.Length != objValues.Length )
                {
                    this.ErrorLog("RecordUpdate","参数匹配错误","");
                    return false;
                }
                else if(strColumns.Length < 1)
                {
                    this.ErrorLog("RecordUpdate","缺少参数","");
                    return false;
                }
                else if(strKeys!=null && objKeys!=null && strKeys.Length != objKeys.Length )
                {
                    this.ErrorLog("RecordUpdate","参数匹配错误","");
                    return false;
                }
                else
                {
                    //根据主键生成条件
                    if(strKeys!=null && objKeys!=null)
                    {
                        for(Int32 i=0;i<strKeys.Length;i++)
                        {
                            if(objKeys[i].GetType() == typeof(String))
                            {
                                strSqlWhere = strSqlWhere + " AND " + strKeys[i] + "='"+Convert.ToString(objKeys[i])+"'";
                            }
                            else
                            {
                                strSqlWhere = strSqlWhere + " AND " + strKeys[i] + "=" + objKeys[i];
                            }
                        }
                    }
                    if(strCustomCondition!=null && strCustomCondition!="")
                    {
                        strSqlWhere = strSqlWhere + " AND " + strCustomCondition;
                    }
                    //strSqlWhere = strSqlWhere.Trim().TrimStart().TrimStart("AND".ToCharArray());
                    if(strSqlWhere.IndexOf(" AND") == 0)
                        strSqlWhere = strSqlWhere.Remove(0,4);
                    
                    if(strSqlWhere!="")
                        strSqlWhere = " WHERE " + strSqlWhere;
                    foreach(String strField in strColumns)
                    {
                        strSql = strSql + "," + strField.Trim() +"=@" + strField.Trim();
                    }
                    strSql = "UPDATE " + strTableName + " SET " + strSql.TrimStart(',') + strSqlWhere;
                    if(this.ExecuteNonQuery(strSql,  strColumns,  objValues)!=-1)
                        return true;
                    else
                        return false;
                }

            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"RecordUpdate；无法执行：" + strTableName);
                return false;
                
            }
        }

        /// <summary>
        /// 删除表记录
        /// </summary>
        /// <param name="strTableName">表名称</param>
        /// <param name="strKeys">主键字段</param>
        /// <param name="objKeys">主键字段值</param>
        /// <param name="strCustomCondition">其他条件</param>
        /// <returns>是否成功</returns>
        /// <returns></returns>
        public override bool RecordDelete(String strTableName,
            String[] strKeys,
            object[] objKeys,
            String strCustomCondition)
        {
            String strSql = "";

            try
            {
                if(!this.mblnOpened)
                {
                    this.ErrorLog("RecordDelete","数据库尚未打开连接","");
                    return false;
                }
                else if(strKeys!=null && objKeys!=null && strKeys.Length != objKeys.Length )
                {
                    this.ErrorLog("RecordDelete","参数匹配错误","");
                    return false;
                }
                else
                {
                    //根据主键生成条件
                    if(strKeys!=null && objKeys!=null)
                    {
                        for(Int32 i=0;i<strKeys.Length;i++)
                        {
                            if(objKeys[i].GetType() == typeof(String))
                            {
                                strSql = strSql + " AND " + strKeys[i] + "='"+Convert.ToString(objKeys[i])+"'";
                            }
                            else
                            {
                                strSql = strSql + " AND " + strKeys[i] + "=" + objKeys[i];
                            }
                        }
                    }
                    if(strCustomCondition!=null && strCustomCondition!="")
                    {
                        strSql = strSql + " AND " + strCustomCondition;
                    }
                    strSql = strSql.Trim().TrimStart("AND".ToCharArray());
                    if(strSql!="")
                        strSql = " WHERE " + strSql;
                    strSql = "DELETE FROM " + strTableName + strSql;
                    if(this.ExecuteNonQuery(strSql)!=-1)
                        return true;
                    else
                        return false;

                }

            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"RecordDelete；无法执行：" + strTableName);
                return false;
            }
        }


        #endregion

        #region 数据读取：Reader、DataSet、DataView、DataTable、ExecuteScalar

        public override System.Data.IDataReader ExecuteReader(string strCommand,bool blnHaveBomb )
        {
            SqlCommand cmd=null;
            SqlDataReader da=null;

            try
            {
                if(!this.mblnOpened) return null;
                cmd = new SqlCommand(strCommand,this.mConn);
                if(this.mblnInTrans) cmd.Transaction = this.mTrans;
                if(blnHaveBomb)
                    da = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection );
                else
                    da = cmd.ExecuteReader(CommandBehavior.CloseConnection );
                return (IDataReader)da;
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"ExecuteReader()；无法执行：" + strCommand);
                return null;
            }
            finally
            {
                if(cmd!=null)cmd.Dispose();
            }
        }
        /// <summary>
        /// 执行Reader
        /// </summary>
        /// <param name="strCommand"></param>
        /// <returns></returns>
        public override System.Data.IDataReader ExecuteReader(string strCommand )
        {
            SqlCommand cmd=null;
            SqlDataReader da=null;

            try
            {
                if(!this.mblnOpened) return null;
                cmd = new SqlCommand(strCommand,this.mConn);
                if(this.mblnInTrans) cmd.Transaction = this.mTrans;
                da = cmd.ExecuteReader(CommandBehavior.CloseConnection );
                return (IDataReader)da;
                
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"ExecuteReader()；无法执行：" + strCommand);
                return null;
            }
            finally
            {
                if(cmd!=null)cmd.Dispose();
            }
        }

        public override System.Data.DataSet ExecuteDataset(string strCommand)
        {
            SqlCommand cmd=null;
            SqlDataAdapter da=null;
            DataSet ds=null;

            try
            {
                if(!this.mblnOpened) return null;
                cmd = new SqlCommand(strCommand,this.mConn);
                if(this.mblnInTrans) cmd.Transaction = this.mTrans;
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"ExecuteDataset()；无法执行：" + strCommand);
                return null;
            }
            finally
            {
                if(cmd!=null)cmd.Dispose();
                if(ds!=null)da.Dispose();
            }

        }

        public override System.Data.DataView ExecuteDataView(string strCommand)
        {
            SqlDataAdapter da=null;
            SqlCommand cmd=null;
            DataSet ds=null;
            DataView dv=null;

            try
            {
                if(!this.mblnOpened) return null;
                cmd = new SqlCommand(strCommand,this.mConn);
                if(this.mblnInTrans) cmd.Transaction = this.mTrans;
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                if(ds.Tables.Count > 0)
                {
                    dv = new DataView(ds.Tables[0]);
                }
                return dv;
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"ExecuteDataView()；无法执行：" + strCommand);
                return null;
            }
            finally
            {
                if(cmd!=null)cmd.Dispose();
                if(da!=null)da.Dispose();
                if(ds!=null)ds.Dispose();
            }

        }
        public override System.Data.DataTable ExecuteDataTable(string strCommand)
        {
            SqlDataAdapter da=null;
            SqlCommand cmd=null;
            DataSet ds=null;
            DataTable dt=null;

            try
            {
                if(!this.mblnOpened) return null;
                cmd = new SqlCommand(strCommand,this.mConn);
                if(this.mblnInTrans) cmd.Transaction = this.mTrans;
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                if(ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0].Copy();
                }
                return dt;
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"ExecuteDataTable()；无法执行：" + strCommand);
                return null;
            }
            finally
            {
                if(cmd!=null)cmd.Dispose();
                if(da!=null)da.Dispose();
                if(ds!=null) ds.Dispose();
            }
        }

        public override object ExecuteScalar(string strCommand)
        {
            SqlCommand cmd=null;

            try
            {
                if(this.mblnOpened)
                {
                    cmd = new SqlCommand(strCommand,this.mConn);
                    if(this.mblnInTrans) cmd.Transaction = this.mTrans;
                    return cmd.ExecuteScalar();
                }
                else
                {
                    return null;
                }
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"clsSQLDB.strCommand()；无法执行：" + strCommand);
                return null;
            }
            finally
            {
                if(cmd!=null)cmd.Dispose();
            }
        }


        #endregion

        #region 其他操作

        //不支持事务处理
        public override System.Data.IDataAdapter ExecuteDataAdapter(string strCommand)
        {
            SqlDataAdapter da = null;
            
            try
            {
                if(!this.mblnOpened) return null;
                da = new SqlDataAdapter(strCommand,this.mConn);
                return da;
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"ExecuteDataAdapter;无法执行：" + strCommand);
                return null;
            }
        }
        
        public override int ExecuteNonQuery(string strCommand)
        {
            SqlCommand cmd = null;

            try
            {
                if(!this.mblnOpened) return -1;
                else
                {
                    cmd  = new SqlCommand(strCommand,this.mConn);
                    if(this.mblnInTrans)
                    {
                        cmd.Transaction = this.mTrans;
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"ExecuteNonQuery；无法执行："+strCommand);
                return -1;
            }
            finally
            {
                if(cmd!=null)cmd.Dispose();
            }
        }

        public override int ExecuteNonQuery(string strSql, string[] strParameterNames,Object[] objObjects)
        {
            SqlCommand cmd = null;
            SqlParameter p = null;

            try
            {
                if(!this.mblnOpened) return -1;
                if(strParameterNames !=null && objObjects != null  && strParameterNames.Length != objObjects.Length)
                {
                    this.ErrorLog("clsSQLDB.ExecuteNonQuery()","传递的参数不匹配",strSql);
                    return -1;
                }
                else
                {
                    cmd = new SqlCommand(strSql,this.mConn);
                    if(this.mblnInTrans)
                    {
                        cmd.Transaction = this.mTrans;
                    }

                    if(strParameterNames !=null && objObjects != null)
                    {
                        for(Int32 i=0;i<strParameterNames.Length ;i++)
                        {
                            p = new SqlParameter("@" + strParameterNames[i],objObjects[i]);
                            cmd.Parameters.Add(p);
                        }
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"ExecuteNonQuery；无法执行："+strSql);
                return -1;
            }
            finally
            {
                if(cmd!=null)cmd.Dispose();
            }

        }

        public override System.Xml.XmlReader ExecuteXmlReader(string strSqlXml)
        {
            SqlCommand cmd = null;

            try
            {
                if(!this.mblnOpened) return null;
                cmd = new SqlCommand(strSqlXml,this.mConn);
                if(this.mblnOpened)
                {
                    if(this.mblnInTrans) cmd.Transaction = this.mTrans;
                    return cmd.ExecuteXmlReader();
                }
                else
                {
                    return null;
                }
            }
            catch(Exception e)
            {
                this.ErrorLog(e.Source,e.Message,"ExecuteXmlReader；无法执行：" + strSqlXml);
                return null;
            }
            finally
            {
                if(cmd!=null) cmd.Dispose();
            }

        }


        #endregion

        #region 事务处理

        /// <summary>
        /// 开始事务处理
        /// </summary>
        /// <returns>是否成功开始事务处理</returns>
        public override bool BeginTrans()
        {
            if(!this.mblnInTrans && this.mblnOpened)
            {
                this.mTrans = this.mConn.BeginTransaction();
                this.mblnInTrans = true;
                return true;
            }
            else
            {
                return false;
            }
        }

        //提交
        public override void Commit()
        {
            if(this.mblnInTrans)
            {
                this.mTrans.Commit();
                this.mblnInTrans = false;
            }

        }

        //回退
        public override void Rollback()
        {
            if(this.mblnInTrans)
            {
                this.mTrans.Rollback();
                this.mblnInTrans = false;
            }
        
        }


        #endregion

        #region 属性

        /// <summary>
        /// 数据库状态
        /// </summary>
        public override System.Data.ConnectionState State
        {
            get
            {
                return this.mConn.State;
            }
        }

        //数据库类型
        public override DataBaseType DBType
        {
            get
            {
                return DataBaseType.SQLServer;
            }
        }


        #endregion

    }
}