为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM Database Server 64 V8
【操作系统】:win11
【CPU】:(英特尔)Intel(R) Core(TM) i5-10400 CPU @ 2.90GHz(2901 MHz)
【问题描述】*:新增临时表并对临时表批量写入,执行不报错临时表空数据
#region 批量写入临时表
//临时表名称
var tmpTabName = "PBXC.##" + tableName + "_" + Guid.NewGuid().ToString("N");
//字段集合
var tmpTabFeds = feds.Select(item =>
{
var f = item.ParameterName.Replace(":", "");
if (item.DmSqlType == DmDbType.Int64) { f += " BIGINT"; }
else if (item.DmSqlType == DmDbType.Int32) { f += " INT"; }
else if (item.DmSqlType == DmDbType.Decimal) { f += " DECIMAL(18,4)"; }
else if (item.DmSqlType == DmDbType.Date) { f += " DATE"; }
else if (item.DmSqlType == DmDbType.Time) { f += " TIME"; }
else if (item.DmSqlType == DmDbType.DateTime) { f += " DATETIME"; }
else if (item.DmSqlType == DmDbType.Bit) { f += " BIT"; }
else if (item.DmSqlType == DmDbType.VarChar)
{
//若为不限大小则启用TEXT类型
if (item.Size == -1)
{
f += " TEXT";
}
else
{
f += " NVARCHAR(" + item.Size + " CHAR)";
}
}
return f;
});
DmConnection conn = DbHelperDM.Connection();
//创建临时表
DbHelperDM.ExecuteSql("CREATE GLOBAL TEMPORARY TABLE " + tmpTabName + "(" + string.Join(",", tmpTabFeds) + ") ON COMMIT PRESERVE ROWS;", conn);
//待更新字段集合
var upTabFeds = new List<string>();
//获取数据库链接字符串
//var conn = PubConstant.GetConnectionString("ConnectionString");
//指定更新主键范围
var upPkParams = new List<DmParameter>();
var upPkNames = new List<string>();
//链接数据库
using (var bulkCopy = new DmBulkCopy(conn))
{
bulkCopy.BatchSize = list.Count;
bulkCopy.DestinationTableName = tmpTabName;
bulkCopy.BulkCopyTimeout = 30000;
var table = new DataTable(tmpTabName);
var props = list[0].GetType().GetProperties();
int clrprops = 0;
foreach (var propertyInfo in props)
{
var fedName = propertyInfo.Name;
bulkCopy.ColumnMappings.Add(fedName, fedName);
table.Columns.Add(fedName, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
if (fedName != pkFed)
{
upTabFeds.Add(fedName + "=(SELECT " + fedName + " FROM " + tmpTabName + " WHERE " + pkFed + "=" + tableName + "." + pkFed + ")");
//Photo=(SELECT Photo FROM T_DVPM_DvPm_20210607210838789 WHERE DvPmID=T_DVPM_DvPm.DvPmID)
}
}
var values = new object[props.Length - clrprops];
foreach (var item in list)
{
for (var i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item)!;
if (props[i].Name == pkFed)
{
//目前支持三种类型主键
var sqlDbType = DmDbType.Int64;
if (values[i].GetType() == typeof(long)) { sqlDbType = DmDbType.Int64; }
else if (values[i].GetType() == typeof(int)) { sqlDbType = DmDbType.Int32; }
else if (values[i].GetType() == typeof(string)) { sqlDbType = DmDbType.VarChar; }
var pName = ":" + pkFed + "_" + values[i].ToString()!.Replace(".", "_");
upPkNames.Add(pName);
upPkParams.Add(new DmParameter() { ParameterName = pName, DmSqlType = sqlDbType, Value = values[i] });
}
}
table.Rows.Add(values);
}
bulkCopy.WriteToServer(table);
}