//调用 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);
}
