【DM版本】:dmdbms.DmProvider v1.1.3
【问题描述】*:
/// <summary>
/// 表批量写入,根据行数据 RowState 状态新增、修改
/// 需要开启连接池,https://eco.dameng.com/community/question/3f1acf7922e5fd27bed66f0edeb28c1f
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="sqlEmpty">查询空表脚本,默认*,可选列,会影响数据更新的列</param>
/// <param name="dataAdapter">执行前修改(命令行脚本、超时等信息)</param>
/// <param name="openTransaction">开启事务,默认 True</param>
/// <returns></returns>
public async Task<int> BulkBatchDm(DataTable dt, string sqlEmpty = null, Action<DmDataAdapter> dataAdapter = null, bool openTransaction = true)
{
return await SafeConn(async () =>
{
var connection = (DmConnection)ConnOption.Connection;
//强制使用连接池
if (!connection.ConnectionString.Contains("conn_pooling=True"))
{
await Console.Out.WriteLineAsync("Reconnect using connection pool");
var csb = new DmConnectionStringBuilder(connection.ConnectionString)
{
ConnPooling = true
};
connection = new DmConnection(csb.ConnectionString);
await connection.OpenAsync();
ConnOption.Connection = connection;
}
var transaction = openTransaction ? (DmTransaction)(await connection.BeginTransactionAsync()) : null;
var cb = new DmCommandBuilder();
if (string.IsNullOrWhiteSpace(sqlEmpty))
{
var sntn = DbKitExtensions.SqlSNTN(dt.TableName, dt.Namespace, DBTypes.Dm);
sqlEmpty = DbKitExtensions.SqlEmpty(sntn);
}
cb.DataAdapter = new DmDataAdapter
{
SelectCommand = new DmCommand(sqlEmpty, connection, transaction)
};
cb.ConflictOption = ConflictOption.OverwriteChanges;
var da = new DmDataAdapter
{
InsertCommand = (DmCommand)cb.GetInsertCommand(true),
UpdateCommand = (DmCommand)cb.GetUpdateCommand(true)
};
da.InsertCommand.CommandText = da.InsertCommand.CommandText.Replace(":TYPE,", ":\"TYPE\",");
da.UpdateCommand.CommandText = da.UpdateCommand.CommandText.Replace(":TYPE,", ":\"TYPE\",");
da.InsertCommand.CommandTimeout = ConnOption.Timeout * 10;
da.UpdateCommand.CommandTimeout = ConnOption.Timeout * 10;
//执行前修改
dataAdapter?.Invoke(da);
var num = da.Update(dt);
if (transaction != null)
{
await transaction.CommitAsync();
}
return num;
});
}
GetInsertCommand、GetUpdateCommand 生成的新增、修改,还有删除,关键字需要加上双引号,不然报错:第 1 行, 第 745 列[,]附近出现错误: 语法分析出错
-- 生成的脚本,报错
INSERT INTO "sch"."table" ("ID", "NAME", "TYPE", "op") VALUES (:ID, :NAME, :TYPE, :op)
-- 关键字加双引号的脚本,写入成功
INSERT INTO "sch"."table" ("ID", "NAME", "TYPE", "op") VALUES (:ID, :NAME, :"TYPE", :op)
[GeneratedRegex(@":(\w+)", RegexOptions.Multiline)] private static partial Regex MatchVarKeyword(); da.InsertCommand.CommandText = MatchVarKeyword().Replace(da.InsertCommand.CommandText, match => ":\"" + match.Groups[1].Value + "\"");
这是我当前的修补方式,最好还得列举关键字进行判断