/// <summary>
/// Sql 语句批量执行
/// </summary>
/// <param name="SqlCmdList"></param>
/// <returns></returns>
public bool SqlBatchExcute(Dictionary <string, DbParameter[]> sqlCmdList)
{
bool result = false;
if (null == sqlCmdList || sqlCmdList.Count <= 0)
{
return(result);
}
using (DbConnection conn = DatabaseFactory.GetDbConnection(this._dbConfig))
{
conn.Open();
using (DbTransaction trans = conn.BeginTransaction())
{
try
{
var entor = sqlCmdList.GetEnumerator();
StringBuilder sbSqlCmdBagList = new StringBuilder();
var sqlParasBagList = new List <DbParameter>();
int pos = 0;
while (entor.MoveNext() || pos >= sqlCmdList.Count)
{
//到达sql参数上限或者达到命令列表边界 执行sql 包
if (sqlParasBagList.Count >= MAX_SQL_PARAS_LIMIT || pos >= sqlCmdList.Count)
{
using (DbCommand cmd = conn.CreateCommand())
{
//到达最大参数临界的时候 执行sql 批语句
cmd.Transaction = trans;
cmd.CommandText = sbSqlCmdBagList.ToString();
cmd.Parameters.AddRange(sqlParasBagList.ToArray());
cmd.ExecuteNonQuery();
}
//重置
sbSqlCmdBagList.Clear();
sqlParasBagList.Clear();
if (pos >= sqlCmdList.Count)
{
break;//超出临界 跳出循环
}
}
var cmdPair = entor.Current;
if (string.IsNullOrEmpty(cmdPair.Key))
{
continue;
}
string sqlCmd = cmdPair.Key;
DbParameter[] sqlParas = cmdPair.Value;
if (!sqlCmd.TrimEnd().EndsWith(";"))
{
sqlCmd += ";";
}
//处理sql 命令，和参数，对sql命令中的@ 和参数中的@的进行匹配识别
if (null == sqlParas || sqlParas.Length <= 0)
{
sbSqlCmdBagList.Append(sqlCmd);
}
else
{
//按照计数游标 对参数进行处理
foreach (DbParameter itemDbPara in sqlParas)
{
var paraName = itemDbPara.ParameterName;
string nameWithPos = string.Concat(paraName, pos);
itemDbPara.ParameterName = nameWithPos;
sqlCmd = sqlCmd.Replace(paraName, nameWithPos);
}
sbSqlCmdBagList.Append(sqlCmd);//追加格式化 后缀的sql 命令
sqlParasBagList.AddRange(sqlParas);
}
pos += 1;//计数后移
}
trans.Commit();
result = true;
}
catch (Exception ex)
{
result = false;
trans.Rollback();
throw ex;
}
}
}
return(result);
}
/// <summary>
/// 获取此上下文的新的db连接对象
/// </summary>
/// <returns></returns>
public IDbConnection GetCurrentDbConnection()
{
return(DatabaseFactory.GetDbConnection(this._dbConfig));
}
