如果你在系统中使用了此架构，请标注出处，谢谢。
/**/////////////////////////////////////////////////////////////////////////////////////////////
////// 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() { } Properties#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 Functions#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 Funcions#region Generate Command Funcions Get 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 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 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 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 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; } } }}