private static int ExecuteBatchStoreCommand(DatabaseEngine db, StoreCommand[] storeCommandList, int maxExecuteCount = 100)
{
int totalCount = 0;//总影响记录数
if (maxExecuteCount <= 1)
{
foreach (var sc in storeCommandList)
{
DbCommand dbCmd = ConvertStoreCommandToDbCommand(db, sc);
totalCount += db.ExecuteNonQuery(dbCmd);
}
}
else
{
List <StoreCommand> storeCommandListTemp = new List <StoreCommand>();
int paramerCount = 0;//参数数量
for (int i = 0; i < storeCommandList.Length; i++)
{
storeCommandListTemp.Add(storeCommandList[i]);
paramerCount += storeCommandList[i].Parameters.Length;
if (i == (storeCommandList.Length - 1) || storeCommandListTemp.Count == maxExecuteCount || paramerCount >= MaxParameterCount)
{
StoreCommand sc = StoreCommandHelper.UnitStoreCommand(storeCommandListTemp);
DbCommand dbCmd = ConvertStoreCommandToDbCommand(db, sc);
totalCount += db.ExecuteNonQuery(dbCmd);
storeCommandListTemp.Clear();
paramerCount = 0;
}
}
}
return(totalCount);
}
/// <summary>
/// 将多个StoreCommand合并成一个StoreCommand
/// </summary>
/// <param name="storeCommandList"></param>
/// <returns></returns>
private static StoreCommand UnitStoreCommand(List <StoreCommand> storeCommandList)
{
StoreCommand scTotal = new StoreCommand();
StringBuilder sbCommand = new StringBuilder();
List <object> paramterList = new List <object>();
//Regex regex = new Regex(@"(?<=\{)(\d+)(?=\})");//"{"用的是反向正声明，"}"用的是正向声明
Regex regex = new Regex(@"(?<=\{)(\d+)(?=\})");//"{"用的是反向正声明，"}"用的是正向声明
int currentIndex = 0;
Dictionary <int, int> indexDic = new Dictionary <int, int>();
foreach (StoreCommand sc in storeCommandList)
{
indexDic.Clear();
Match match = regex.Match(sc.CommandText);
while (match.Success)
{
int index = int.Parse(sc.CommandText.Substring(match.Index, match.Length));
if (!indexDic.ContainsKey(index))
{
indexDic.Add(index, index + currentIndex);
}
match = match.NextMatch();
}
UnitSbCommand(sbCommand, sc.CommandText, indexDic);
paramterList.AddRange(sc.Parameters);
currentIndex += indexDic.Count;
}
scTotal.CommandText = sbCommand.ToString();
scTotal.Parameters = paramterList.ToArray();
return(scTotal);
}
public static List <T> ExecuteQuery <T>(DatabaseEngine db, StoreCommand storeCommand)
{
DbCommand dbCmd = ConvertStoreCommandToDbCommand(db, storeCommand);
List <T> list = new List <T>();
using (IDataReader dr = db.ExecuteReader(dbCmd))
{
db.Load(typeof(T), list, dr);
}
return(list);
}
public static DbCommand ConvertStoreCommandToDbCommand(DatabaseEngine db, StoreCommand storeCommand)
{
Regex regex = new Regex(@"\{(\d+)\}");
string cmdText = regex.Replace(storeCommand.CommandText, db.GetParameterPrefix() + "Para$1");
DbCommand dbCmd = db.DatabaseSession.Database.GetSqlStringCommand(cmdText);
for (int i = 0; i < storeCommand.Parameters.Length; i++)
{
SqlParameter par = new SqlParameter("Para" + i.ToString(), storeCommand.Parameters[i]);
db.AddParameter(dbCmd, par);
}
return(dbCmd);
}
public static StoreCommand GetStoreCommand(DatabaseEngine db, string strSQL, SqlParameterCollection paras)
{
StoreCommand storecommand = new StoreCommand();
storecommand.Parameters = new object[paras.Count];
List <SqlParameter> tempparas = paras.OrderByDescending(o => o.Name.Length).ToList();
for (int i = 0; i < tempparas.Count; i++)
{
strSQL = strSQL.Replace(string.Format("@{0}", tempparas[i].Name), string.Format("{{{0}}}", i));
storecommand.Parameters[i] = tempparas[i].Value;
}
storecommand.CommandText = strSQL;
return(storecommand);
}