protected virtual async Task <bool> CheckUpdateDelete(DbCommand cmd, ExecuteResultItem sqlResult, string sql, int changeMaxRows)
{
if (updateDeleteRegex.IsMatch(sql))
{
bool hasWhere = false;
var m = updateSqlRegex.Match(sql);
if (m.Success)
{
hasWhere = true;
sqlResult.OldData = await GetOldDataJson(cmd, m.Groups[2].Value, m.Groups[1].Value, m.Groups[3].Value, m.Groups[4].Value, changeMaxRows);
}
m = deleteSqlRegex.Match(sql);
if (m.Success)
{
hasWhere = true;
sqlResult.OldData = await GetOldDataJson(cmd, "*", m.Groups[1].Value, null, m.Groups[2].Value, changeMaxRows);
}
if (hasWhere == false)
{
sqlResult.Exception = "SQL statement has no where condition!";
return(false);
}
}
return(true);
}
public virtual async Task <ExecuteResult> ExecuteSql_InsertUpdate(DbCommand command, string sql, int readMaxRows, int changeMaxRows)
{
var m = GetInsertUpdateRegex().Match(sql);
ExecuteResult result = new ExecuteResult();
if (m.Success)
{
result.IsException = true;
result.Exception = "此SQL语句包含“" + m.Value + "”关键字！";
return(result);
}
var list = SqlStringList.SplitString(sql);
var sqlList = list.SplitBySemi();
try {
result.Result = new List <ExecuteResultItem>();
bool isRollback = false;
using (var tran = await command.Connection.BeginTransactionAsync()) {
foreach (var item in sqlList)
{
ExecuteResultItem sqlResult = new ExecuteResultItem();
result.Result.Add(sqlResult);
var b = await CheckUpdateDelete(command, sqlResult, item, changeMaxRows);
if (b)
{
b = await ExecuteSql(command, sqlResult, item, readMaxRows);
}
if (b == false)
{
result.IsException = true;
result.Exception = sqlResult.Exception;
result.Result = null;
isRollback = true;
tran.Rollback();
break;
}
}
if (isRollback == false)
{
await tran.CommitAsync();
}
}
} catch (Exception x) {
result.IsException = true;
result.Exception = x.Message;
result.Result = null;
}
return(result);
}
protected ExecuteResultItem[] BuildResult(BatchCompletionStateResponse response)
{
var detailedErrors = response.DetailedErrors.ToDictionary(x => x.Item1, x => x.Item2);
var additionalErrorsPerMessage = response.AdditionalErrorsPerMessage.ToHashSet();
var result = new ExecuteResultItem[response.ProcessedMessages];
for (var i = 0; i < result.Length; i++)
{
var recordsAffected = i < response.UpdatedRecordsPerMessage.Length
? response.UpdatedRecordsPerMessage[i]
: -1;
if (detailedErrors.TryGetValue(i, out var exception))
{
result[i] = new ExecuteResultItem()
{
RecordsAffected = recordsAffected,
IsError = true,
Exception = exception,
};
}
else if (additionalErrorsPerMessage.Contains(i))
{
result[i] = new ExecuteResultItem()
{
RecordsAffected = recordsAffected,
IsError = true,
Exception = null,
};
}
else
{
result[i] = new ExecuteResultItem()
{
RecordsAffected = recordsAffected,
IsError = false,
Exception = null,
};
}
}
return(result);
}
public virtual async Task <ExecuteResult> ExecuteSql_AllPermissions(DbCommand command, string sql)
{
var list = SqlStringList.SplitString(sql);
var sqlList = list.SplitBySemi();
ExecuteResult result = new ExecuteResult();
try {
result.Result = new List <ExecuteResultItem>();
bool isRollback = false;
using (var tran = await command.Connection.BeginTransactionAsync()) {
foreach (var item in sqlList)
{
ExecuteResultItem sqlResult = new ExecuteResultItem();
result.Result.Add(sqlResult);
var b = await ExecuteSql(command, sqlResult, item, int.MaxValue);
if (b == false)
{
result.IsException = true;
result.Exception = sqlResult.Exception;
result.Result = null;
isRollback = true;
tran.Rollback();
break;
}
}
if (isRollback == false)
{
await tran.CommitAsync();
}
}
} catch (Exception x) {
result.IsException = true;
result.Exception = x.Message;
result.Result = null;
}
return(result);
}
public virtual async Task <ExecuteResult> ExecuteSql_Select(DbCommand command, string sql, int readMaxRows)
{
var m = GetSelectRegex().Match(sql);
ExecuteResult result = new ExecuteResult();
if (m.Success)
{
result.IsException = true;
result.Exception = "此SQL语句包含“" + m.Value + "”关键字！";
return(result);
}
var list = SqlStringList.SplitString(sql);
var sqlList = list.SplitBySemi();
try {
result.Result = new List <ExecuteResultItem>();
foreach (var item in sqlList)
{
ExecuteResultItem sqlResult = new ExecuteResultItem();
result.Result.Add(sqlResult);
var b = await ExecuteSql(command, sqlResult, item, readMaxRows);
if (b == false)
{
result.IsException = true;
result.Exception = sqlResult.Exception;
result.Result = null;
break;
}
}
} catch (Exception x) {
result.IsException = true;
result.Exception = x.Message;
result.Result = null;
}
return(result);
}
protected virtual async Task <bool> ExecuteSql(DbCommand cmd, ExecuteResultItem sqlResult, string sql, int readMaxRows)
{
cmd.CommandText = sql;
sqlResult.Sql = sql;
sqlResult.StartTime = DateTime.Now;
System.Diagnostics.Stopwatch stopwatch = System.Diagnostics.Stopwatch.StartNew();
DbDataReader reader = null;
try {
reader = await cmd.ExecuteReaderAsync();
if (reader.RecordsAffected > 0)
{
sqlResult.ChangeCount = reader.RecordsAffected;
}
int fieldCount = reader.FieldCount;
if (fieldCount == 0)
{
return(true);
}
sqlResult.Columns = new List <string>();
sqlResult.Values = new List <object[]>();
for (int i = 0; i < fieldCount; i++)
{
sqlResult.Columns.Add(reader.GetName(i));
}
object[] vals = null; int rows = 0;
while (await reader.ReadAsync())
{
if (rows >= readMaxRows)
{
sqlResult.IsOverflow = true; break;
}
vals = new object[fieldCount];
reader.GetValues(vals);
sqlResult.Values.Add(vals);
rows++;
}
for (int i = 0; i < sqlResult.Columns.Count; i++)
{
var name = sqlResult.Columns[i];
if (string.IsNullOrEmpty(name))
{
if (i == 0)
{
sqlResult.Columns[i] = "Result";
}
else
{
sqlResult.Columns[i] = "Result_" + i.ToString();
}
}
}
return(true);
} catch (Exception ex) {
stopwatch.Stop();
sqlResult.RunTime = stopwatch.ElapsedMilliseconds;
sqlResult.Exception = ex.Message;
} finally {
if (reader != null && reader.IsClosed == false)
{
reader.Close();
}
}
return(false);
}
