//调用 System.Data.SqlClient.SqlBulkCopy 进行数据批量处理。 // ML 文件中 参数 MappingName 必须和表中字段的名称一致。 //特殊说明:目前只支持INSERT 的情况。 private void writeToServer(string xmlFileName, string sqlName, IList lstData, IDbTransaction transaction) { Database db = MB.Orm.Persistence.DatabaseHelper.CreateDatabase(); if ((transaction.Connection as SqlConnection) == null) { throw new MB.Util.APPException(string.Format("SqlServer批量处理中提供的{0}不是有效的SqlConnection", transaction.Connection.GetType().FullName), MB.Util.APPMessageType.SysErrInfo); } if ((transaction as SqlTransaction) == null) { throw new MB.Util.APPException(string.Format("SqlServer批量处理中提供的{0}不是有效的SqlTransaction", transaction.GetType().FullName), MB.Util.APPMessageType.SysErrInfo); } using (SqlBulkCopy bulkCopy = new SqlBulkCopy(transaction.Connection as SqlConnection, SqlBulkCopyOptions.Default, transaction as SqlTransaction)) { bulkCopy.BatchSize = this.BatchSize; bulkCopy.BulkCopyTimeout = this.BulkCopyTimeout; bulkCopy.NotifyAfter = this.NotifyAfter; bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied); MB.Orm.DbSql.SqlString sqlStr = this.GetXmlSqlString(db, xmlFileName, sqlName); string tableName = getTableNameFromSql(sqlStr.SqlStr); bulkCopy.DestinationTableName = tableName.IndexOf('#') == 0 ? TEMP_TABLE_PREX + tableName : tableName; DataTable data = convertToDataTable(db, tableName, sqlStr, lstData); bulkCopy.WriteToServer(data); } }
//把ListData 中的所有实体对象 转换为 可执行的DataTable 格式 private DataTable convertToDataTable(Database db, string tableName, MB.Orm.DbSql.SqlString sqlStr, IList lstData) { IList <SqlParamInfo> tPars = sqlStr.ParamFields; DataTable dTable = createTable(tableName); Dictionary <string, MB.Util.Emit.DynamicPropertyAccessor> dynamicAcc = new Dictionary <string, MB.Util.Emit.DynamicPropertyAccessor>(); object entityData = lstData[0]; Type objType = entityData.GetType(); //edit by cdc 2011-3-13 if (objType.IsValueType || string.Compare(objType.Name, "String", true) == 0) { if (tPars.Count != 1) { throw new MB.Util.APPException(string.Format("批量处理中,值只有一个而参数有{0}个", tPars.Count), Util.APPMessageType.SysErrInfo); } var sp = tPars[0]; foreach (var d in lstData) { DataRow newDr = dTable.NewRow(); newDr[sp.MappingName] = d; dTable.Rows.Add(newDr); } } else if ((entityData as DataRow) != null) { foreach (DataRow dr in lstData) { DataRow newDr = dTable.NewRow(); foreach (SqlParamInfo info in tPars) { newDr[info.MappingName] = dr[info.MappingName]; } dTable.Rows.Add(newDr); } } else { foreach (SqlParamInfo info in tPars) { MB.Util.Emit.DynamicPropertyAccessor acc = new MB.Util.Emit.DynamicPropertyAccessor(entityData.GetType(), info.MappingName); dynamicAcc.Add(info.MappingName, acc); } foreach (object entity in lstData) { DataRow newDr = dTable.NewRow(); foreach (string fieldName in dynamicAcc.Keys) { //MB.Util.MyReflection.Instance.ConvertValueType( newDr[fieldName] = dynamicAcc[fieldName].Get(entity); } dTable.Rows.Add(newDr); } } return(dTable); }
/// <summary> /// 获取指定的SQL 字符窜。 /// </summary> /// <param name="xmlFileName"></param> /// <param name="sqlName"></param> /// <returns></returns> public MB.Orm.DbSql.SqlString[] GetSqlString(string xmlFileName, string sqlName) { // string xmlFileFullName = XML_FILE_PATH + xmlFileName + ".xml"; //统一在方法 getSqlNode 进行判断 //if (!System.IO.File.Exists(xmlFileFullName)) // throw new MB.Util.APPException(string.Format("XML配置文件{0},不存在!", xmlFileFullName), MB.Util.APPMessageType.SysErrInfo); System.Xml.XmlNode sqlNode = getSqlNode(xmlFileName, sqlName); if (sqlNode == null || sqlNode.ChildNodes.Count == 0) { TraceEx.Write("在XML 文件:" + xmlFileName + " 中获取相应SQL 语句:" + sqlName + "出错,请检查资源文件或者对应SQL 语句是否存在!"); return(null); } List <MB.Orm.DbSql.SqlString> sqlStrings = new List <SqlString>(); foreach (System.Xml.XmlNode sqlStringNode in sqlNode.ChildNodes) { if (sqlStringNode.NodeType != System.Xml.XmlNodeType.Element) { continue; } if (string.Compare(sqlStringNode.Name, XML_SQL_STRING_NAME, true) != 0) { continue; } string str = string.Empty; foreach (System.Xml.XmlNode node in sqlStringNode.ChildNodes) { if (node.NodeType != System.Xml.XmlNodeType.Element) { continue; } if (string.Compare(node.Name, XML_SQL_STRING_TEXT_NAME, true) != 0) { continue; } TraceEx.Write("成功发现SQL 语句:" + sqlName); str = formatSqlString(node.InnerText.Trim()); break; } List <SqlParamInfo> pars = getSqlParams(sqlStringNode); MB.Orm.DbSql.SqlString sqlStr = new MB.Orm.DbSql.SqlString(str, pars); sqlStrings.Add(sqlStr); } return(sqlStrings.ToArray()); }
//创建DBCommand 对象. public System.Data.Common.DbCommand createDbCommandByXml(Database db, string xmlFileName, string sqlName, IList lstData) { SimulatedOracleHelper oh = new SimulatedOracleHelper(); MB.Orm.Persistence.PersistenceManagerHelper pm = new PersistenceManagerHelper(); var dbType = MB.Orm.Persistence.DatabaseHelper.GetDatabaseType(db); List <System.Data.Common.DbCommand> dbCmds = new List <System.Data.Common.DbCommand>(); MB.Orm.DbSql.SqlString sqlStr = this.GetXmlSqlString(db, xmlFileName, sqlName); IList <SqlParamInfo> tPars = sqlStr.ParamFields; if (_PersistenceManagerHelper == null) { _PersistenceManagerHelper = new PersistenceManagerHelper(); } var dbCmd = oh.CreateOracleCommand(_PersistenceManagerHelper.ReplaceSqlParamsByDatabaseType(db, sqlStr.SqlStr));// new OracleCommand(_PersistenceManagerHelper.ReplaceSqlParamsByDatabaseType(db, sqlStr.SqlStr)); //dbCmd.ArrayBindCount = lstData.Count; List <SqlParamInfo> overcastPars = new List <SqlParamInfo>(); oh.SetCommandArrayBindCount(dbCmd, lstData.Count); Type oracleDbType = oh.GetOracleDbType(); Dictionary <string, ArrayList> pValues = paramValues(db, tPars, lstData); for (int i = 0; i < tPars.Count; i++) { SqlParamInfo parInfo = tPars[i]; if (parInfo.Overcast) { dbCmd.CommandText = pm.ReaplaceSpecString(dbCmd.CommandText, MB.Orm.DbSql.SqlShareHelper.ORACLE_PARAM_PREFIX + pm.CreateParName(db, parInfo.Name), pValues[parInfo.Name][0] == null ? null : pValues[parInfo.Name][0].ToString()); } else { var oraInfo = oh.CreateOracleParameter(parInfo.Name, convertToOracleType(oh, oracleDbType, parInfo.DbType));// new OracleParameter(parInfo.Name, convertToOracleType(parInfo.DbType)); oraInfo.Direction = System.Data.ParameterDirection.Input; oraInfo.Value = pValues[parInfo.Name].ToArray(); dbCmd.Parameters.Add(oraInfo); } } return(dbCmd); }