/// <summary>
/// 执行SQL 语句，并返回由 T 指定的对象
/// </summary>
/// <typeparam name="T">基元类型、单实体、列表（List<T>）、DataTable、DataSet</typeparam>
/// <param name="query">查询语句</param>
/// <returns></returns>
public virtual T Execute <T>(IDbQueryable query)
{
RawCommand command = query.Resolve();
IDbCommand cmd = this.CreateCommand(command);
return(this.Execute <T>(cmd, command as IMapping));
}
/// <summary>
/// 异步执行SQL 语句，并返回由 T 指定的对象
/// </summary>
/// <typeparam name="T">基元类型、单实体、列表（List<T>）、DataTable、DataSet</typeparam>
/// <param name="query">SQL 命令</param>
/// <returns></returns>
public virtual async Task <T> ExecuteAsync <T>(IDbQueryable query)
{
RawCommand cmd = query.Resolve();
IDbCommand command = this.CreateCommand(cmd);
return(await this.ExecuteAsync <T>(command, cmd as IMapping));
}
/// <summary>
/// 解析批量 INSERT 语句
/// </summary>
/// <param name="sqlList">SQL 命令列表 </param>
/// <param name="bulkList">批量查询语义列表</param>
protected void ResolveBulk(List <RawCommand> sqlList, List <IDbQueryable> bulkList)
{
// SQL 只能接收1000个
int pageSize = 1000;
int pages = bulkList.Page(pageSize);
for (int pageIndex = 1; pageIndex <= pages; pageIndex++)
{
var dbQueryables = bulkList.Skip((pageIndex - 1) * pageSize).Take(pageSize);
int i = 0;
int count = dbQueryables.Count();
var builder = new System.Text.StringBuilder(128);
foreach (IDbQueryable query in dbQueryables)
{
i += 1;
query.Parameterized = false;
query.Bulk = new BulkInsertInfo {
OnlyValue = i != 1, IsEndPos = i == count
};
RawCommand cmd = query.Resolve();
builder.Append(cmd.CommandText);
}
if (builder.Length > 0)
{
sqlList.Add(new RawCommand(builder.ToString()));
}
}
}
/// <summary>
/// 创建 SQL 命令
/// </summary>
/// <param name="cmd">命令描述</param>
/// <returns></returns>
public IDbCommand CreateCommand(RawCommand cmd)
{
return(this.CreateCommand(cmd.CommandText, cmd.CommandType, cmd.Parameters));
}
// 拆分脚本命令
List <RawCommand> ParseCommand(string commandText, IDataParameterCollection collection, CommandType?commandType)
{
// 存储过程不需要拆分
if (commandType != null && commandType.Value == CommandType.StoredProcedure)
{
return(null);
}
bool haveBegin = false;
string methodName = string.Empty;
var myList = new List <RawCommand>();
string[] parts = commandText.Split(';');
//var regex = new Regex(@"(?<ParameterName>:p\d+)", RegexOptions.IgnoreCase | RegexOptions.Multiline);
var regex = new Regex(@"(?<ParameterName>:[0-9a-zA-Z_]+)", RegexOptions.IgnoreCase | RegexOptions.Multiline);
// 创建新命令
Func <string, bool, RawCommand> newCommand = (sql, isQuery) =>
{
RawCommand cmd = new RawCommand(string.Format("{0}{1}", sql, isQuery ? string.Empty : ";"));
if (collection != null && collection.Count > 0)
{
var myParameters = new List <IDbDataParameter>();
MatchCollection matches = regex.Matches(cmd.CommandText);
foreach (Match m in matches)
{
var p = (IDbDataParameter)collection[m.Groups["ParameterName"].Value];
myParameters.Add(p);
}
cmd.Parameters = myParameters;
}
return(cmd);
};
// 只有一条 SQL 时也不用拆分
if (parts.Length == 1)
{
return(null);
}
// 语句块不拆分
methodName = string.Empty;
if (commandText.Length > 6)
{
methodName = commandText.Substring(0, 6).Trim().ToUpper();
}
if (methodName == "BEGIN")
{
return(null);
}
for (int i = 0; i < parts.Length; i++)
{
string sql = parts[i];
methodName = string.Empty;
if (string.IsNullOrEmpty(sql))
{
continue;
}
if (sql.Length > 6)
{
methodName = sql.Substring(0, 6).Trim().ToUpper();
}
if (methodName == "SELECT")
{
// 查询单独执行
if (myList.Count > 0 && (i - 1) >= 0 && myList[myList.Count - 1] != null)
{
myList.Add(null);
}
// 创建新命令
RawCommand cmd = newCommand(sql, true);
myList.Add(cmd);
myList.Add(null);
}
else
{
// 增删改
if (!haveBegin)
{
myList.Add(new RawCommand("BEGIN"));
haveBegin = true;
}
// 创建新命令
RawCommand cmd = newCommand(sql, false);
myList.Add(cmd);
// 检查下一条是否是选择语句
bool isQuery = false;
if (i + 1 < parts.Length)
{
sql = parts[i + 1];
methodName = string.Empty;
if (!string.IsNullOrEmpty(sql) && sql.Length > 6)
{
methodName = sql.Substring(0, 6).Trim().ToUpper();
}
isQuery = methodName == "SELECT";
}
// 如果下一条是SELECT 语句，则需要结束当前语句块
if (isQuery)
{
if (haveBegin)
{
myList.Add(new RawCommand("END;"));
haveBegin = false;
myList.Add(null);
}
}
}
if (haveBegin && i == parts.Length - 1)
{
myList.Add(new RawCommand("END;"));
}
}
return(myList);
}
// 拆分脚本命令
List <RawCommand> ParseCommand(List <RawCommand> sqlList)
{
// 重新组合脚本，把 SELECT 和 UPDATE/DELETE 等分开来
bool haveBegin = false;
var myList = new List <RawCommand>();
// 创建新命令
Func <RawCommand, RawCommand> newCommand = cmd =>
{
var parameters = cmd.Parameters == null
? null
: cmd.Parameters.ToList(x => (IDbDataParameter)base.CreateParameter(x.ParameterName, x.Value, x.DbType, x.Size, x.Precision, x.Scale, x.Direction));
var result = new RawCommand(cmd.CommandText, parameters, cmd.CommandType);
return(result);
};
for (int i = 0; i < sqlList.Count; i++)
{
var cmd = sqlList[i];
if (cmd == null)
{
continue;
}
string sql = cmd.CommandText;
if (string.IsNullOrEmpty(sql))
{
continue;
}
string methodName = string.Empty;
if (sql.Length > 6)
{
methodName = sql.Substring(0, 6).Trim().ToUpper();
}
if (cmd is MappingCommand || methodName == "SELECT")
{
// 查询单独执行
if (myList.Count > 0 && (i - 1) >= 0 && myList[myList.Count - 1] != null)
{
myList.Add(null);
}
// 创建新命令
RawCommand @new = newCommand(cmd);
myList.Add(@new);
myList.Add(null);
}
else
{
// 增删改
if (!haveBegin)
{
myList.Add(new RawCommand("BEGIN"));
haveBegin = true;
}
// 创建新命令
RawCommand @new = newCommand(cmd);
myList.Add(@new);
// 检查下一条是否是选择语句
bool isQuery = false;
if (i + 1 < sqlList.Count)
{
cmd = sqlList[i + 1];
sql = cmd.CommandText;
methodName = string.Empty;
if (!string.IsNullOrEmpty(sql) && sql.Length > 6)
{
methodName = sql.Substring(0, 6).Trim().ToUpper();
}
isQuery = methodName == "SELECT";
}
// 如果下一条是SELECT 语句，则需要结束当前语句块
if (isQuery)
{
if (haveBegin)
{
myList.Add(new RawCommand("END;"));
haveBegin = false;
myList.Add(null);
}
}
}
if (haveBegin && i == sqlList.Count - 1)
{
myList.Add(new RawCommand("END;"));
}
}
return(myList);
}
/// <summary>
/// 解析 SQL 命令
/// <para>
/// 返回的已经解析语义中执行批次用 null 分开
/// </para>
/// </summary>
/// <param name="dbQueryables">查询语句</param>
/// <returns></returns>
public virtual List <RawCommand> Resolve(List <object> dbQueryables)
{
List <RawCommand> sqlList = new List <RawCommand>();
ResolveToken token = null;
foreach (var obj in dbQueryables)
{
if (obj == null)
{
continue;
}
if (obj is IDbQueryable)
{
IDbQueryable dbQueryable = (IDbQueryable)obj;
dbQueryable.Parameterized = true;
if (token == null)
{
token = new ResolveToken();
}
if (token.Parameters == null)
{
token.Parameters = new List <IDbDataParameter>(8);
}
var cmd2 = dbQueryable.Resolve(0, true, token);
sqlList.Add(cmd2);
if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
token = new ResolveToken();
token.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is RawSql)
{
RawSql rawSql = (RawSql)obj;
if (token == null)
{
token = new ResolveToken();
}
if (token.Parameters == null)
{
token.Parameters = new List <IDbDataParameter>(8);
}
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.DbValue.GetSqlValue(x, token)).ToArray();
}
string sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
var cmd2 = new RawCommand(sql, token.Parameters, CommandType.Text);
sqlList.Add(cmd2);
if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
token = new ResolveToken();
token.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is string)
{
string sql = obj.ToString();
sqlList.Add(new RawCommand(sql));
}
else
{
// 解析批量插入操作
List <IDbQueryable> bulkList = obj as List <IDbQueryable>;
if (bulkList != null && bulkList.Count > 0)
{
this.ResolveBulk(sqlList, bulkList);
}
}
}
return(sqlList);
}