一个DataAccess类的实现。实现了Commed builder，异构数据库，和事务控制等功能。...

如果你在系统中使用了此架构，请标注出处，谢谢。

/**/ /**/ /**/ ////
///
///    Copyright (C), 2002-2008, Murphy Corporation.
///    
///    FileName:        DBAccess.cs
///    Author:            胡晓伟
///    Version:          Beta 
///    Description:    DataAccess Foundation Class : Basic DB Function Class ..
///   
///                    
////
using  System;
using  System.Data;
using  System.Data.SqlClient;
using  System.Data.OleDb;
using  System.Data.OracleClient;

using  COM.Makinfo.DataEntity;
namespace  COM.Makinfo.DataAccess
{
    public abstract class DBAccess:IDataAccess
    {
        /**//**//**//// <summary>
        /// SQL变量的前缀
        /// </summary>
        private const string        SQL_PARA_PREFIX = "@";
        private const string        ACE_PARA_PREFIX = "@";
        private const string        ORA_PARA_PREFIX = ":";

        /**//**//**//// <summary>
        /// 初始化函数。
        /// </summary>
        public DBAccess()
        {
        }


        PropertiesProperties#region Properties
        /**//**//**//// <summary>
        /// 得到数据库信息。
        /// </summary>
        private DBInfoData dbInfoData
        {
            get
            {
                return DBInfoData.dbInfoData;
            }
        }
    
        private string    _ConnectionString = "";    
        /**//**//**//// <summary>
        ///  得到连接数据库的字符串。
        /// </summary>
        public string ConnectionString
        {
            get
            {
                if(this._ConnectionString == "")
                {
                    switch(DBType)
                    {
                        case DatabaseType.Access:
                            this._ConnectionString = 
                                "Provider=Microsoft.Jet.OLEDB.4.0;"+
                                "Data Source="+dbInfoData.Source+";"+
                                "User Id="+dbInfoData.UserID+";"+
                                "Password="+dbInfoData.Password +";" ; 
                            break;
                        case DatabaseType.SQLServer:
                            this._ConnectionString = 
                                " Data Source="+dbInfoData.Server + "; "+
                                " Initial Catalog="+dbInfoData.Source+"; " +
                                " user id="+dbInfoData.UserID + "; " +
                                " Password="+dbInfoData.Password + "; "+
                                " persist security info=False;"+ 
                                " Connect Timeout="+dbInfoData.ConnectTimeOut.ToString()+ "; "; 
                            break;
                        case DatabaseType.Oracle:
                            this._ConnectionString = 
                                " Data source=" + dbInfoData.Source + ";" +
                                " User ID=" + dbInfoData.UserID + ";"+
                                " password=" + dbInfoData.Password;
                            break;
                        default:
                            this._ConnectionString = 
                                " Data Source="+dbInfoData.Server + "; "+
                                " Initial Catalog="+dbInfoData.Source+"; " +
                                " user id="+dbInfoData.UserID + "; " +
                                " persist security info=False;"+ 
                                " Connect Timeout="+dbInfoData.ConnectTimeOut.ToString()+ "; "; 
                            break;               
                    }
                }
                return this._ConnectionString;
            }

        }
        /**//**//**//// <summary>
        /// 得到数据库类型
        /// </summary>
        protected DatabaseType DBType
        {
            get
            {    
                return this.dbInfoData.DBType;
            }
        }
        private IDbConnection    _Conn;
        /**//**//**//// <summary>
        /// 得到一个数据库的实例，这个实例只在此类中使用。
        /// </summary>
        private IDbConnection Conn
        {
            get
            {
                switch(DBType)
                {
                    case DatabaseType.Access:
                        this._Conn = new OleDbConnection
                            (ConnectionString); 
                        break;
                    case DatabaseType.SQLServer:
                        this._Conn = new SqlConnection
                            (ConnectionString); 
                        break;
                    case DatabaseType.Oracle:
                        this._Conn = new OracleConnection
                            (ConnectionString);
                        break;
                    default:
                        this._Conn = new SqlConnection
                            (ConnectionString); 
                        break;               
                }
                return this._Conn;
            }
        }
        /**//**//**//// <summary>
        /// 获得数据库变量的前缀。
        /// </summary>
        /// <returns></returns>
        protected string GetParaPreFix
        {
            get
            {
                string strRet = "";
                switch(DBType)
                {
                    case DatabaseType.Access:
                        strRet = ACE_PARA_PREFIX; 
                        break;

                    case DatabaseType.SQLServer:
                        strRet = SQL_PARA_PREFIX; 
                        break;

                    case DatabaseType.Oracle:
                        strRet = ORA_PARA_PREFIX; 
                        break;

                    default:
                        strRet = SQL_PARA_PREFIX; 
                        break;
                }

                strRet =  SQL_PARA_PREFIX  ;             
                return strRet;
            }
        }
        private IDbConnection    _TempConn;
        /**//**//**//// <summary>
        /// 得到或设置一个临时的数据库连接的实例，他在事务处理中使用。
        /// （用于交互式方法ExcuteCmdListCooperation）。
        /// </summary>
        protected IDbConnection TempConn
        {
            get
            {
                return this._TempConn;
            }
            set
            {
                this._TempConn = value;
            }
        }
        /**//**//**//// <summary>
        /// 得到一个DataAdapter实例，其数据库连接和SelectCommand已经设置。
        /// </summary>
        protected IDbDataAdapter InstanceAdap
        {
            get
            {
                IDbDataAdapter da;
                switch(DBType)
                {
                    case DatabaseType.Access:
                        da = new OleDbDataAdapter((OleDbCommand)InstanceComm); 
                        break;

                    case DatabaseType.SQLServer:
                        da = new SqlDataAdapter((SqlCommand)InstanceComm); 
                        break;

                    case DatabaseType.Oracle:
                        da = new OracleDataAdapter((OracleCommand)InstanceComm); 
                        break;

                    default:
                        da = new SqlDataAdapter((SqlCommand)InstanceComm); 
                        break;
                }

                return da;

            }
        }

        /**//**//**//// <summary>
        /// 得到参数实例.
        /// </summary>
        protected IDbDataParameter InstanceParam
        {
            get
            {
                IDbDataParameter dp;
                switch(DBType)
                {
                    case DatabaseType.Access:
                        dp = new OleDbParameter() ; 
                        break;

                    case DatabaseType.SQLServer:
                        dp = new  SqlParameter(); 
                        break;

                    case DatabaseType.Oracle:
                        dp = new  OracleParameter(); 
                        break;

                    default:
                        dp = new SqlParameter(); 
                        break;
                }

                return dp;

            }
        }
        /**//**//**//// <summary>
        /// 得到一个Command实例，其数据库连接已经设置。
        /// 如果有事务，则事务也会自动设置完成（用于交互式方法ExcuteCmdListCooperation）。
        /// </summary>
        protected IDbCommand InstanceComm
        {
            get
            {    
                IDbCommand cmd;
                if(!this.IsCooperation)
                {
                    switch(DBType)
                    {
                        case DatabaseType.Access:
                            cmd = new OleDbCommand
                                ("",(OleDbConnection)Conn);
                            break;

                        case DatabaseType.SQLServer:
                            cmd = new SqlCommand
                                ("",(SqlConnection)Conn); 
                            break;

                        case DatabaseType.Oracle:
                            cmd = new OracleCommand
                                ("",(OracleConnection)Conn);
                            break;
                        default:
                            cmd = new SqlCommand
                                ("",(SqlConnection)Conn); 
                            break;
                    }
                }
                else
                {
                    switch(DBType)
                    {
                        case DatabaseType.Access:
                            cmd = new OleDbCommand
                                ("",(OleDbConnection)Conn,(OleDbTransaction)this.TempTransaction);
                            break;

                        case DatabaseType.SQLServer:
                            cmd = new SqlCommand
                                ("",(SqlConnection)Conn,(SqlTransaction)this.TempTransaction); 
                            break;

                        case DatabaseType.Oracle:
                            cmd = new OracleCommand
                                ("",(OracleConnection)Conn,(OracleTransaction)this.TempTransaction);
                            break;
                        default:
                            cmd = new SqlCommand
                                ("",(SqlConnection)Conn,(SqlTransaction)this.TempTransaction); 
                            break;
                    }
                }
                return cmd;
            }
        }

        private IDbTransaction _Transaction;
        /**//**//**//// <summary>
        /// 得到或设置一个事务。
        /// </summary>
        protected IDbTransaction Transaction
        {
            get
            {
                return this._Transaction;
            }
            set
            {
                this._Transaction = value;
            }
        }

        private IDbTransaction _TempTransaction;
        /**//**//**//// <summary>
        /// 得到或设置一个临时事务。
        /// （用于交互式方法ExcuteCmdListCooperation）。
        /// </summary>
        protected IDbTransaction TempTransaction
        {
            get
            {
                return this._TempTransaction;
            }
            set
            {
                this._TempTransaction = value;
            }
        }

        private bool _IsCooperation;
        /**//**//**//// <summary>
        /// 得到或设置是否交互式方法。
        /// （用于交互式方法ExcuteCmdListCooperation）。
        /// </summary>
        private bool IsCooperation
        {
            get
            {
                return this._IsCooperation;
            }
            set
            {
                this._IsCooperation = value;
            }
        }

        #endregion Properties

        FunctionsFunctions#region Functions

        /**//**//**//// <summary>
        /// 从数据库中向数据表对象填充数据。
        /// </summary>
        /// <param name="dsRet">要填充数据的数据表类。</param>
        /// <param name="sql">Sql语句</param>
        protected void    InitDataSetTableBySQL(ref DataTable dsRet, string sql)
        {        
            IDbCommand cmd    = this.InstanceComm;
            cmd.CommandText = sql;
            this.InitDataSetTableByCmd(ref dsRet,cmd);
        }
        /**//**//**//// <summary>
        /// 从数据库中向数据表对象填充数据。
        /// </summary>
        /// <param name="dsRet">要填充数据的数据表类。</param>
        /// <param name="sql">Sql语句</param>
        protected void    InitDataSetTableByCmd(ref DataTable dsRet, IDbCommand cmd)
        {        
            IDbDataAdapter myAdapter        = this.InstanceAdap;
            string tableName                = dsRet.TableName;
            myAdapter.MissingSchemaAction    = System.Data.MissingSchemaAction.Add;
            myAdapter.TableMappings.Add("Table" ,tableName);
            myAdapter.SelectCommand            = cmd;
            if(dsRet.DataSet == null)
            {
                DataSet dtsTemp                    = new DataSet();
                dtsTemp.Tables.Add(dsRet);
            }
            if(this.IsCooperation)
            {
                myAdapter.SelectCommand.Connection        = this.TempConn;
                myAdapter.SelectCommand.Transaction        = this.TempTransaction;
            }

            myAdapter.Fill(dsRet.DataSet);
        }
        /**//**//**//// <summary>
        /// 执行Command List。
        /// 如果之前调用过BeginCooperation方法，则所有Command都在一个事务中。
        /// </summary>
        /// <param name="aryIDbCommand"></param>
        protected void ExcuteCmdList(IDbCommand[] aryIDbCommand)
        {    
            if(this.IsCooperation)
                this.ExcuteCmdListCooperation(aryIDbCommand);
            else
                this.ExcuteCmdListNoCooperation(aryIDbCommand);
        }
        /**//**//**//// <summary>
        /// 执行Command List。所有Command都在一个事务中。
        /// </summary>
        /// <param name="aryIDbCommand"></param>
        protected void ExcuteCmdListCooperation(IDbCommand[] aryIDbCommand)
        {    
            if(aryIDbCommand == null || aryIDbCommand.Length ==0)
            {
                return;
            }
            //open connection.
            IDbConnection conn    = this.TempConn;
            //begin transaction.
            
            IDbTransaction tran    = this.TempTransaction;
            try
            {
                //excute commands.
                foreach(IDbCommand anyIDbCommand in aryIDbCommand)
                {
                    anyIDbCommand.Connection    = conn;
                    anyIDbCommand.Transaction    = tran;
                    anyIDbCommand.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        /**//**//**//// <summary>
        /// 执行Command List。没有事务。
        /// </summary>
        /// <param name="aryIDbCommand"></param>
        protected void ExcuteCmdListNoCooperation (IDbCommand[] aryIDbCommand)
        {    
            if(aryIDbCommand == null || aryIDbCommand.Length ==0)
            {
                return;
            }
            //excute commands.
            foreach(IDbCommand anyIDbCommand in aryIDbCommand)
            {
                anyIDbCommand.Connection.Open();
                try
                {
                    //执行
                    anyIDbCommand.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if(anyIDbCommand.Connection.State == ConnectionState.Open)
                    {
                        anyIDbCommand.Connection.Close();
                    }

                }

            }
            
        }
        /**//**//**//// <summary>
        /// 开始事务。
        /// </summary>
        /// <param name="aryDBAccess"></param>
        public static void BeginCooperation(DBAccess[] aryDBAccess)
        {
            IDbConnection conn        = aryDBAccess[0].Conn;
            conn.Open();
            //begin transaction.
            IDbTransaction tran        = conn.BeginTransaction();
            //set the same connection and transaction for every DBAccess instance.
            foreach(DBAccess anyDBAccess in aryDBAccess)
            {
                anyDBAccess.TempConn        = conn;
                anyDBAccess.TempTransaction = tran;
                anyDBAccess.IsCooperation    = true;
            }
            
        }
        /**//**//**//// <summary>
        /// 提交事务。
        /// </summary>
        public static void CommitCooperation(DBAccess[] aryDBAccess)
        {
            foreach(DBAccess anyDBAccess in aryDBAccess)
            {
                anyDBAccess.IsCooperation = false;
            }
            aryDBAccess[0].TempTransaction.Commit();
            aryDBAccess[0].TempConn.Close();
        }
        /**//**//**//// <summary>
        /// 回滚事务。
        /// </summary>
        public static void RollbackCooperation(DBAccess[] aryDBAccess)
        {
            foreach(DBAccess anyDBAccess in aryDBAccess)
            {
                anyDBAccess.IsCooperation = false;
            }
            aryDBAccess[0].TempTransaction.Rollback();
            aryDBAccess[0].TempConn.Close();
        }
        
        #endregion Functions

        Generate Command FuncionsGenerate Command Funcions#region Generate Command Funcions

        Get Insert CommandGet Insert Command#region Get Insert Command
        /**//**//**//// <summary>
        /// 生成InsertCommand。
        /// </summary>
        /// <param name="aTable">插入数据库的数据。</param>
        protected  IDbCommand[] GetInsertCommand (DataTable aTable)
        {
            DataColumn[] InsertColumn = new DataColumn[aTable.Columns.Count];
            aTable.Columns.CopyTo(InsertColumn,0);
            return GetInsertCommand(aTable,InsertColumn);
        }
        /**//**//**//// <summary>
        /// 生成InsertCommand。
        /// </summary>
        /// <param name="InsertRow">插入数据库的数据。</param>
        /// <param name="InsertColumn">插入数据库的字段。</param>
        /// <returns></returns>
        protected IDbCommand[] GetInsertCommand (DataTable aTable,DataColumn[] InsertColumn)
        {    
            DataRow[] InsertRow = this.GetRow(aTable);

            return  this.GetInsertCommand(aTable.TableName,InsertRow,InsertColumn);
        }
    
        /**//**//**//// <summary>
        /// 生成InsertCommand。
        /// </summary>
        /// <param name="aTable">插入数据库的数据。</param>
        /// <param name="colName">插入数据库的字段。</param>
        /// <returns></returns>
        protected  IDbCommand[] GetInsertCommand (DataTable aTable,string[] colName)
        {
            DataColumn[] dcCol = this.GetColumnByStr(aTable,colName);            
            return GetInsertCommand(aTable,dcCol);
        }

        /**//**//**//// <summary>
        /// 生成InsertCommand。
        /// </summary>
        /// <param name="InsertRow">插入数据库的数据。</param>
        /// <param name="colName">插入数据库的字段。</param>
        /// <returns></returns>
        protected IDbCommand GetInsertCommand (DataRow InsertRow,string[] colName)
        {
            if ( InsertRow == null  )
                return null;
            DataColumn[] dcCol = this.GetColumnByStr(InsertRow.Table,colName);
            IDbCommand[] cmdList = this.GetInsertCommand(InsertRow.Table.TableName,
                new DataRow[]{InsertRow},
                dcCol);
            if ( cmdList.Length > 0)
                return cmdList[0];
            else
                return null;
        }

    
        /**//**//**//// <summary>
        /// 生成InsertCommand。        
        /// </summary>
        /// <param name="InsertRow">插入数据库的数据。</param>
        /// <param name="colName">插入数据库的字段。</param>
        /// <returns></returns>
        protected IDbCommand[] GetInsertCommand (string TableName,DataRow[] InsertRow,string[] colName)
        {
            if ( InsertRow == null || InsertRow.Length <= 0 )
                return null;
            DataColumn[] dcCol = this.GetColumnByStr(InsertRow[0].Table,colName);
            return this.GetInsertCommand(TableName,InsertRow,dcCol);
        }
            
    
        /**//**//**//// <summary>
        /// 生成InsertCommand。
        /// </summary>
        /// <param name="InsertRow">插入数据库的数据。</param>
        /// <param name="InsertColumn">插入数据库的字段。</param>
        /// <returns></returns>
        protected IDbCommand[] GetInsertCommand (string TableName,DataRow[] InsertRow,DataColumn[] InsertColumn)
        {
            IDbCommand[] cmdList  = new IDbCommand[InsertRow.Length];
            IDbDataParameter [] ParaArray = new IDbDataParameter[InsertColumn.Length ];
            if ( InsertColumn.Length <=0  || InsertRow.Length <= 0)
                return cmdList;
            string strCol = "";
            string strValue = "";
            for(int i=0;i< InsertColumn.Length  ;i++)
            {
                DataColumn dc = InsertColumn[i];
                //Generate Sql ParaMeter
                ParaArray[i] = this.GetParameter(dc);
                
                //Generate column
                strCol += "," + dc.ColumnName ;
                strValue += "," + ParaArray[i].ParameterName ;
            }
            if ( strCol.Trim() != "" )
                strCol = " ( " + strCol.Remove(0,1) + " )";
            if ( strValue.Trim() != "")
                strValue = " values ( " + strValue.Remove(0,1) + " )";
            //Generate sql command
            string strSql =  " INSERT INTO " + TableName + strCol + strValue;
            //Add para to sql Command
            for(int i = 0 ;i< InsertRow.Length ; i++)
            {
                cmdList[i]                    = this.InstanceComm;    
                cmdList[i].CommandText        = strSql;
                foreach( IDbDataParameter para in ParaArray)
                {
                    para.Value = InsertRow[i][para.SourceColumn];
                    IDbDataParameter tempPara = cmdList[i].CreateParameter();
                    this.ClonePara(para,tempPara);
                    cmdList[i].Parameters.Add(tempPara);
                }
                if(this.dbInfoData.IsSqlText)
                {
                    cmdList[i].CommandText    = this.GetSQLByCommand(cmdList[i]);
                }
            }
            return     cmdList;
        }
        #endregion 

        Get Update CommandGet Update Command#region Get Update Command
        /**//**//**//// <summary>    
        /// 生成更新Command。
        /// 根据数据表的PrimaryKey，和UpdateColName生成Command。
        /// </summary>
        /// <param name="updateTable">需要更新的数据。</param>
        /// <param name="updateColName">需要更新的字段。</param>
        /// <returns></returns>
        protected  IDbCommand[] GetUpdateCommand (DataTable updateTable,string[] UpdateColName)
        {    
            DataColumn[] PKCol        = updateTable.PrimaryKey;        
            string[] PKColName        = new string[PKCol.Length];
            for(int iIndex = 0;iIndex < PKColName.Length;iIndex ++)
            {
                PKColName[iIndex]    = PKCol[iIndex].ColumnName;
            }

            return  this.GetUpdateCommand(updateTable,UpdateColName,PKColName);
        }    
    
        /**//**//**//// <summary>
        /// 生成更新Command。
        /// 根据指定的PKColName字段，和UpdateCol生成Command。
        /// </summary>
        /// <param name="updateTable">需要更新的数据。</param>
        /// <param name="UpdateColName">需要更新的字段.</param>
        /// <param name="PKColName">更新条件字段。</param>
        /// <returns></returns>
        protected IDbCommand[] GetUpdateCommand (DataTable updateTable,string[] UpdateColName,string[]PKColName)
        {
            DataColumn[] PkCol                = this.GetColumnByStr(updateTable,PKColName);
            
            //Generate Sql where.
            IDbDataParameter [] ParaArray    = new IDbDataParameter[PkCol.Length];
            string strWhere = "";            
            for(int i = 0; i< PkCol.Length ;i++)
            {
                DataColumn dc = PkCol[i];

                //Generate Sql ParaMeter
                ParaArray[i] = this.GetParameter(dc);

                strWhere += " AND " + dc.ColumnName + " = " + ParaArray[i].ParameterName;
            }
            if ( strWhere.Trim() != "" )
                strWhere = " Where " + strWhere.Remove(0,4);
            //Generate
            DataRow[] UpdateRow     = this.GetRow(updateTable);
            return this.GetUpdateCommand(updateTable.TableName,UpdateRow,UpdateColName,strWhere);
        }
    
        
        /**//**//**//// <summary>
        /// 生成更新Command。
        /// 根据要更新的数据，字段和更新条件生成Command。
        /// </summary>
        /// <param name="updateTable">需要更新的数据。</param>
        /// <param name="UpdateColName">需要更新的字段。</param>
        /// <param name="strWhere">更新条件。</param>
        /// <returns></returns>
        protected IDbCommand[] GetUpdateCommand (DataTable updateTable,string[] UpdateColName,string strWhere)
        {
            DataRow[] UpdateRow     = this.GetRow(updateTable);
            return  this.GetUpdateCommand(updateTable.TableName,UpdateRow,UpdateColName,strWhere);
        }

    
        /**//**//**//// <summary>
        /// 生成更新Command。
        /// 根据要更新的数据，字段和更新条件生成Command。
        /// </summary>
        /// <param name="tableName">要更新的数据表名。</param>
        /// <param name="UpdateRow">要更新的数据。</param>
        /// <param name="UpDataColumn">要更新的数据表字段名。</param>
        /// <param name="strWhere">Where语句。（例如 where leng(@Nam}>4 or Desc is not null )，字段用{GetParaPreFix+字段名}组成。）</param>
        /// <returns></returns>
        protected IDbCommand[] GetUpdateCommand (string tableName,DataRow[] UpdateRow,string[] UpdateColName,string strWhere)
        {    
            DataColumn[] UpDataColumn            = this.GetColumnByStr(UpdateRow[0].Table,UpdateColName);

            IDbCommand[] cmdList  = new IDbCommand[UpdateRow.Length];
            if ( UpdateRow.Length <= 0)
                return cmdList;
            
            IDbDataParameter [] ParaArray    = new IDbDataParameter[UpDataColumn.Length];

            string strUpdateSet = "";            
            for(int i = 0; i< UpDataColumn.Length ;i++)
            {
                DataColumn dc = UpDataColumn[i];
                //Generate Sql ParaMeter
                ParaArray[i] = this.GetParameter(dc);                
                strUpdateSet += " , " + dc.ColumnName + " = " + ParaArray[i].ParameterName;
            }
            if ( strUpdateSet.Trim() != "" )
                strUpdateSet = " Set " + strUpdateSet.Remove(0,3);
            //Generate sql command
            string strSql =  " Update " + tableName + strUpdateSet + strWhere;
            //Add para to sql Command
            for(int i = 0 ;i< UpdateRow.Length ;i ++)
            {
                cmdList[i]                    = this.InstanceComm;    
                cmdList[i].CommandText        = strSql;    
                foreach( IDbDataParameter para in ParaArray)
                {
                    para.Value = UpdateRow[i][para.SourceColumn];
                    IDbDataParameter tempPara = cmdList[i].CreateParameter();
                    this.ClonePara(para,tempPara);
                    cmdList[i].Parameters.Add(tempPara);
                }
                if(this.dbInfoData.IsSqlText)
                {
                    cmdList[i].CommandText    = this.GetSQLByCommand(cmdList[i]);
                }
            }
            return     cmdList;
        }

        #endregion Get Update Command

        Get Select CommandGet Select Command#region Get Select Command

        /**//**//**//// <summary>
        /// 生成SelectCommand。
        /// </summary>
        /// <param name="data">读取数据条件数据。</param>
        /// <param name="CheckCol">要读取数据表的字段名</param>
        /// <param name="PkCol">读取数据条件字段</param>
        /// <returns></returns>
        protected IDbCommand GetSelectCommand(DataTable data,string[] CheckCol,string[] PkCol)
        {
            return this.GetSelectCommand(data.TableName,data.Select(),CheckCol,PkCol);
        }
        /**//**//**//// <summary>
        /// 生成SelectCommand。
        /// </summary>
        /// <param name="tableName">读取数据的数据表名。</param>
        /// <param name="rowList">读取数据条件数据。</param>
        /// <param name="CheckCol">要读取数据表的字段名。</param>
        /// <param name="PkCol">读取数据条件字段。</param>
        /// <returns></returns>
        protected IDbCommand GetSelectCommand(string tableName,DataRow[] rowList,string[] CheckCol,string[] PkCol)
        {
            IDbCommand cmdRet        = this.InstanceComm;    
            cmdRet.CommandText        = "";    
            if ( rowList.Length <= 0 )
                return cmdRet;

            string strCol = "";
            for ( int i = 0 ; i < PkCol.Length ; i++)
                strCol += "," + PkCol[i] ;
            for ( int i = 0 ; i < CheckCol.Length ; i++)
                strCol += "," + CheckCol[i] ;
            if ( strCol.Length > 0)
                strCol = strCol.Remove(0,1);
            
            //Generate Sql ParaMeter
            IDbDataParameter [] ParaArray = new IDbDataParameter[PkCol.Length];        
            for(int i = 0; i< ParaArray.Length ;i++)
            {
                DataColumn dc = rowList[0].Table.Columns[PkCol[i]];
                ParaArray[i] = this.GetParameter(dc);    
            }

            string strSQL = "";                
            for(int i=0 ;i< rowList.Length ;i++)
            {
                strSQL = "union all select " + strCol + " from " + tableName  ;
                string strWhere = "";
                DataRow aRow =    rowList[i];    
                foreach( IDbDataParameter para in ParaArray)
                {
                    para.Value = aRow[para.SourceColumn];
                    IDbDataParameter tempPara = cmdRet.CreateParameter();
                    this.ClonePara(para,tempPara);    
                    tempPara.ParameterName = tempPara.ParameterName + i.ToString();
                    cmdRet.Parameters.Add(tempPara);
                    strWhere += " AND " + tempPara.SourceColumn  + " =" + tempPara.ParameterName;
                }                
                if ( strWhere.Trim().Length > 0)
                    strWhere = " Where " + strWhere.Remove(0,4);
                strSQL += strWhere;
            }
            if ( strSQL.Trim().Length > 0)
                strSQL = strSQL.Remove(0,9);
            cmdRet.CommandText = strSQL;
            if(this.dbInfoData.IsSqlText)
            {
                cmdRet.CommandText    = this.GetSQLByCommand(cmdRet);
            }
            return cmdRet;
        }
        #endregion

        Get Delete CommandGet Delete Command#region Get Delete Command

        /**//**//**//// <summary>
        /// 生成DeleteCommand。
        /// </summary>
        /// <param name="deleteTable">条件数据.</param>
        /// <param name="strWhere">Where语句。（例如 len(@Name}>4 or @Desc is not null )，字段用{GetParaPreFix+字段名}组成。）</param>
        /// <param name="PKColumn">条件字段。</param>
        /// <returns></returns>
        protected IDbCommand[] GetDeleteCommand (DataTable deleteTable ,string strWhere,string[] PKColumn)
        {    
            DataColumn[] dcCol = this.GetColumnByStr(deleteTable,PKColumn);    

            return  this.GetDeleteCommand(deleteTable.Select(),strWhere,dcCol);
        }
        /**//**//**//// <summary>
        /// 生成DeleteCommand。
        /// </summary>
        /// <param name="DeleteRow">条件数据。</param>
        /// <param name="PKColumn">条件字段。</param>
        /// <returns></returns>
        protected IDbCommand[] GetDeleteCommand (DataTable data,string[] PKColumn)
        {    
            DataColumn[] dcCol = this.GetColumnByStr(data,PKColumn);    

            return GetDeleteCommand(data.Select(),dcCol);
        }
        /**//**//**//// <summary>
        /// 生成DeleteCommand。
        /// </summary>
        /// <param name="DeleteRow">条件数据。</param>
        /// <param name="PKColumn">条件字段。</param>
        /// <returns></returns>
        protected IDbCommand[] GetDeleteCommand (DataRow[] DeleteRow,DataColumn[] PKColumn)
        {    
            return GetDeleteCommand(DeleteRow,null,PKColumn);
        }
        /**//**//**//// <summary>
        /// 生成DeleteCommand。
        /// </summary>
        /// <param name="DeleteRow">条件数据。</param>
        /// <param name="strWhere">Where语句。（例如 where leng(@Nam}>4 or Desc is not null )，字段用{GetParaPreFix+字段名}组成。）</param>
        /// <param name="intCount">参数个数。</param>
        /// <returns></returns>
        protected IDbCommand[] GetDeleteCommand (DataRow[] DeleteRow,string Where,DataColumn[] PKColumn)
        {    
            //实例化Command和Parameter
            IDbCommand[] cmdList  = new IDbCommand[DeleteRow.Length];
            if ( DeleteRow.Length <= 0 )
                return cmdList;
            IDbDataParameter [] ParaArray = new IDbDataParameter[PKColumn.Length];
            for(int i = 0; i< ParaArray.Length ;i++)
            {
                DataColumn dc = PKColumn[i];

                //Generate Sql ParaMeter
                ParaArray[i] = this.GetParameter(dc);                
            }

            //生成Where语句。
            string strWhere = "";
            if(null != Where && Where != "")
            {
                for(int i = 0; i< ParaArray.Length ;i++)
                {        
                    strWhere += " AND " + ParaArray[i].SourceColumn + " =" + ParaArray[i].ParameterName ;
                }
                if ( strWhere.Trim() != "" )
                    strWhere = " Where " + strWhere.Remove(0,4);
            }
            else
            {
                strWhere = " Where " + Where;
            }
            
            //Generate sql command
            string strSql =  " Delete From " + DeleteRow[0].Table.TableName + strWhere;
            //Add para to sql Command
            for(int i=0 ;i<DeleteRow.Length ;i++)
            {
                DataRow aRow                = DeleteRow[i];
                cmdList[i]                    = this.InstanceComm;    
                cmdList[i].CommandText        = strSql;
                foreach( IDbDataParameter para in ParaArray)
                {
                    object oValue = null;
                    if (aRow.RowState == DataRowState.Deleted)
                        oValue = aRow[para.SourceColumn,DataRowVersion.Original];
                    else
                        oValue = aRow[para.SourceColumn];
                    para.Value = oValue;
                    IDbDataParameter tempPara = cmdList[i].CreateParameter();
                    this.ClonePara(para,tempPara);                    
                    cmdList[i].Parameters.Add(tempPara);
                }
                if(this.dbInfoData.IsSqlText)
                {
                    cmdList[i].CommandText    = this.GetSQLByCommand(cmdList[i]);
                }
            }
            return     cmdList;
        }


    

        #endregion Get Delete Command

        Public FunctionPublic Function#region Public Function
        /**//**//**//// <summary>
        /// 得到DbDataParameter
        /// </summary>
        /// <param name="pramObj">生成Parameters的参数</param>
        /// <returns></returns>
        protected IDbDataParameter[] GetParameter(ParaObject[] pramObj)
        {
            IDbDataParameter[] paras = new IDbDataParameter[pramObj.Length];

            for(int iIndex =0;iIndex < paras.Length ; iIndex++)
            {
                paras[iIndex]                = this.InstanceParam;
                paras[iIndex].DbType        = this.GetDataType(pramObj[iIndex].PaType,0);
                paras[iIndex].Direction        = pramObj[iIndex].Direct;
                paras[iIndex].Value            = pramObj[iIndex].Value;
                paras[iIndex].ParameterName    = this.GetParaPreFix+pramObj[iIndex].Name;
            }

            return paras;
        }

        /**//**//**//// <summary>
        /// 根据数据库类型获取参数接口
        /// </summary>
        /// <returns>数据适配器接口</returns>
        private IDbDataParameter  GetParameter(DataColumn aColumn)
        {
            const int MAX_SIZE    = 4000; 
            
            IDbDataParameter para = this.InstanceParam;
            
        
            para.ParameterName = this.GetParaPreFix  + aColumn.ColumnName;    
            para.SourceColumn = aColumn.ColumnName ;
            para.DbType  = this.GetDataType(aColumn.DataType,aColumn.MaxLength);    
             
            if ( para.DbType == DbType.String )
            {
                int size  = aColumn.MaxLength ;
                if ( size <= 0)
                    size = MAX_SIZE;
                para.Size = size;                
            }
            return para;
        }
        /**//**//**//// <summary>
        /// 根据Command拼SQL Text.
        /// </summary>
        /// <param name="comm"></param>
        /// <returns></returns>
        public string GetSQLByCommand(IDbCommand comm)
        {
            string strSQL = comm.CommandText;
            foreach(IDbDataParameter para in comm.Parameters)
            {
                switch(para.DbType)
                {
                    case DbType.Decimal:
                        strSQL = strSQL.Replace( para.ParameterName,    para.Value.ToString() );
                        break;
                    case DbType.DateTime:
                        strSQL = strSQL.Replace( para.ParameterName,    "'" + para.Value.ToString() + "'" );    
                        break;
                    case DbType.Int16:
                        strSQL = strSQL.Replace( para.ParameterName,    para.Value.ToString());        
                        break;
                    case DbType.Int32:
                        strSQL = strSQL.Replace( para.ParameterName,    para.Value.ToString());        
                        break;
                    case DbType.Int64:
                        strSQL = strSQL.Replace( para.ParameterName,    para.Value.ToString());        
                        break;
                    case DbType.Byte:
                        strSQL = strSQL.Replace( para.ParameterName,    para.Value.ToString());    
                        break;
                    case DbType.AnsiStringFixedLength:
                        strSQL = strSQL.Replace( para.ParameterName,    "'"+para.Value.ToString()+"'");    
                        break;
                    case DbType.String:
                        strSQL = strSQL.Replace( para.ParameterName,    "'"+para.Value.ToString()+"'");    
                        break;
                    case DbType.Binary:
                        strSQL = strSQL.Replace( para.ParameterName,    para.Value.ToString());
                        break;
                    case DbType.Boolean:
                        strSQL = strSQL.Replace( para.ParameterName,    Convert.ToInt32(para.Value).ToString());
                        break;
                    default:
                        strSQL = strSQL.Replace( para.ParameterName,    "'"+para.Value.ToString()+"'");    
                        break;
                }
            }
            return strSQL;
        }
        /**//**//**//// <summary>
        /// 根据C# 数据类型返回数据库类型。
        /// </summary>
        /// <param name="dataType">C#数据类型。</param>
        /// <param name="maxLength">最大长度。</param>
        /// <returns></returns>
        private DbType GetDataType(System.Type dataType,int maxLength)
        {
            DbType typeRet = DbType.String ;

            if (dataType == typeof(decimal) )
            {
                typeRet =    DbType.Decimal;                                    
            }
            else if (dataType == typeof(DateTime) )
            {
                typeRet = DbType.DateTime ;    
                    
            }
            else if (dataType == typeof(string) )
            {
                if ( maxLength > 0)
                    typeRet = DbType.AnsiStringFixedLength ;    
                else
                    typeRet = DbType.String ;                
            }
            else if (dataType == typeof(int))
            {
                typeRet = DbType.Int64;
            }
            else if( dataType == typeof(bool))
            {
                typeRet = DbType.Boolean ;
            }
            if( dataType == typeof(byte[]))
            {
                typeRet = DbType.Binary ;
            }
            return typeRet;
        }
        /**//**//**//// <summary>
        /// 根据数据表和字段名称，得到DataColumn。
        /// </summary>
        /// <param name="aTable">数据表。</param>
        /// <param name="colName">字段名称。</param>
        /// <returns></returns>
        private  DataColumn[] GetColumnByStr(DataTable aTable,string[] colName)
        {
            DataColumn[] dcCol = new DataColumn[colName.Length];
            for( int i = 0 ; i < dcCol.Length  ; i++)
            {
                string columnName = colName[i].Trim();
                int index = aTable.Columns.IndexOf(columnName);
                if ( index >= 0 )
                    dcCol[i] = aTable.Columns[index];
            }
            return dcCol;
        }
    
        /**//**//**//// <summary>
        /// 根据记录的状态（新增，修改，删除等）来得到相应的数据记录。
        /// </summary>
        /// <param name="aTable">数据表。</param>
        /// <param name="rowState">数据状态。</param>
        /// <returns></returns>
        private DataRow[] GetRow(DataTable aTable,DataRowState rowState)
        {
            DataTable dtChanged = aTable.GetChanges(rowState);
            DataRow[] rowArray = new DataRow[dtChanged.Rows.Count];
            for( int i = 0 ; i < rowArray.Length ; i++)
            {
                rowArray[i] = dtChanged.Rows[i];
            }
            return rowArray;
        }
        /**//**//**//// <summary>
        /// 得到数据表中所有记录。
        /// </summary>
        /// <param name="aTable">数据表。</param>
        /// <returns></returns>
        private DataRow[] GetRow(DataTable aTable)
        {
            DataRow[] rowArray = new DataRow[aTable.Rows.Count];
            for( int i = 0 ; i < rowArray.Length ; i++)
            {
                rowArray[i] = aTable.Rows[i];
            }
            return rowArray;
        }
        /**//**//**//// <summary>
        /// 获得源参数的克隆参数
        /// </summary>
        /// <param name="paraSource">原始参数</param>
        /// <param name="paraSource">克隆参数<</param>
        /// 
        private void ClonePara(IDbDataParameter paraSource,IDbDataParameter paraDest)
        {            
            paraDest.ParameterName = paraSource.ParameterName ;
            paraDest.SourceColumn = paraSource.SourceColumn ;
            paraDest.DbType  = paraSource.DbType ;
            paraDest.Value = paraSource.Value;
            paraDest.Size = paraSource.Size;
        }
    
        #endregion

        #endregion
    }
    /**//**//**//// <summary>
    /// 生成Parameters的参数类.
    /// </summary>
    public class ParaObject
    {
        
        public  ParaObject(object Value,ParameterDirection direct,string Name,System.Type PaType)
        {
            this.Value = Value;
            this.Direct = direct;
            this.Name = Name;
            this.PaType = PaType;
        }

        private object _Value;
        /**//**//**//// <summary>
        /// 参数值
        /// </summary>
        public object Value
        {
            get
            {
                return this._Value;
            }
            set
            {
                this._Value = value;
            }
        }

        private ParameterDirection _Direct;
        /**//**//**//// <summary>
        /// 方向
        /// </summary>
        public ParameterDirection Direct
        {
            get
            {
                return this._Direct;
            }
            set
            {
                this._Direct = value;
            }
        }
        private string _Name;
        /**//**//**//// <summary>
        /// 参数名.
        /// </summary>
        public string Name
        {
            get
            {
                return this._Name;
            }
            set
            {
                this._Name = value;
            }
        }
        
        private System.Type _PaType;
        /**//**//**//// <summary>
        /// 参数类型.
        /// </summary>
        public System.Type PaType
        {
            get
            {
                return this._PaType;
            }
            set
            {
                this._PaType = value;
            }
        }

    }

}

转载于:https://www.cnblogs.com/richardhu/archive/2006/07/25/459598.html