/// <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)
{
DbRawCommand command = query.Translate();
IDbCommand cmd = this.CreateCommand(command);
return(this.Execute <T>(cmd, command as IMapDescriptor));
}
/// <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)
{
DbRawCommand cmd = query.Translate();
IDbCommand command = this.CreateCommand(cmd);
return(await this.ExecuteAsync <T>(command, cmd as IMapDescriptor));
}
/// <summary>
/// 解析批量 INSERT 语句
/// </summary>
/// <param name="sqlList">SQL 命令列表 </param>
/// <param name="bulkQueryables">批量查询语义列表</param>
protected void TranslateBulk(List <DbRawCommand> sqlList, List <IDbQueryable> bulkQueryables)
{
// SQL 只能接收1000个
int pageSize = 1000;
int pages = bulkQueryables.Page(pageSize);
for (int pageIndex = 1; pageIndex <= pages; pageIndex++)
{
var dbQueryables = bulkQueryables.Skip((pageIndex - 1) * pageSize).Take(pageSize);
int i = 0;
int count = dbQueryables.Count();
var builder = new System.Text.StringBuilder(128);
foreach (DbQueryable query in dbQueryables)
{
i += 1;
query.Parameterized = false;
query.Bulk = new BulkInsertInfo {
OnlyValue = i != 1, IsEndPos = i == count
};
DbRawCommand cmd = query.Translate();
builder.Append(cmd.CommandText);
}
if (builder.Length > 0)
{
sqlList.Add(new DbRawCommand(builder.ToString()));
}
}
}
/// <summary>
/// 解析左关联
/// </summary>
protected virtual void AppendLfInJoin(DbExpression dbExpression)
{
_builder.Append(' ');
DbQueryable dbQueryable = (DbQueryable)((dbExpression.Expressions[0] as ConstantExpression).Value);
dbQueryable.Parameterized = _builder.Parameterized;
if (dbQueryable.DbExpressions.Count == 1 && dbQueryable.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable)
{
// 区别 GetTable 有三个重载
var expressions = dbQueryable.DbExpressions[0].Expressions;
if (expressions.Length == 2 && ((expressions[0] as ConstantExpression).Value as string) != null)
{
string text = (expressions[0] as ConstantExpression).Value as string;
object[] @params = (expressions[1] as ConstantExpression).Value as object[];
var provider = ((DbQueryProvider)_builder.Provider);
var context = _builder.TranslateContext;
// 解析参数
object[] args = null;
if (@params != null)
{
args = @params.Select(x => provider.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = text;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
_builder.Append("(");
_builder.Append(cmd.CommandText);
_builder.AppendNewLine();
_builder.Append(')');
}
else
{
Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
_builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
}
}
else
{
// 嵌套
var cmd = dbQueryable.Translate(_builder.Indent + 1, false, _builder.TranslateContext);
_builder.Append("(");
_builder.Append(cmd.CommandText);
_builder.AppendNewLine();
_builder.Append(')');
}
var left = dbExpression.Expressions[1] as LambdaExpression;
var right = dbExpression.Expressions[2] as LambdaExpression;
// t0(t1)
string alias = !(left.Body.NodeType == ExpressionType.New || left.Body.NodeType == ExpressionType.MemberInit)
? _ag.GetTableAlias(dbExpression.Expressions[2])
: _ag.GetTableAlias(right.Parameters[0]);
_builder.Append(' ');
_builder.Append(alias);
_builder.Append(' ');
// ON a.Name = b.Name AND a.Id = b.Id
_builder.Append("ON ");
if (left.Body.NodeType == ExpressionType.New)
{
var body1 = left.Body as NewExpression;
var body2 = right.Body as NewExpression;
for (int index = 0; index < body1.Arguments.Count; ++index)
{
base.Visit(body1.Arguments[index]);
_builder.Append(" = ");
base.Visit(body2.Arguments[index]);
if (index < body1.Arguments.Count - 1)
{
_builder.Append(" AND ");
}
}
}
else if (left.Body.NodeType == ExpressionType.MemberInit)
{
var body1 = left.Body as MemberInitExpression;
var body2 = right.Body as MemberInitExpression;
for (int index = 0; index < body1.Bindings.Count; ++index)
{
base.Visit((body1.Bindings[index] as MemberAssignment).Expression);
_builder.Append(" = ");
base.Visit((body2.Bindings[index] as MemberAssignment).Expression);
if (index < body1.Bindings.Count - 1)
{
_builder.Append(" AND ");
}
}
}
else
{
base.Visit(left.Body.ReduceUnary());
_builder.Append(" = ");
base.Visit(right.Body.ReduceUnary());
}
}
// 拆分脚本命令
List <DbRawCommand> 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 <DbRawCommand>();
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, DbRawCommand> newCommand = (sql, isQuery) =>
{
List <IDbDataParameter> parameters = null;
if (collection != null && collection.Count > 0)
{
parameters = new List <IDbDataParameter>();
MatchCollection matches = regex.Matches(sql);
foreach (Match m in matches)
{
var p = (IDbDataParameter)collection[m.Groups["ParameterName"].Value];
parameters.Add(p);
}
}
DbRawCommand cmd = new DbRawCommand(string.Format("{0}{1}", sql, isQuery ? string.Empty : ";"), parameters);
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);
}
// 创建新命令
DbRawCommand cmd = newCommand(sql, true);
myList.Add(cmd);
myList.Add(null);
}
else
{
// 增删改
if (!haveBegin)
{
myList.Add(new DbRawCommand("BEGIN"));
haveBegin = true;
}
// 创建新命令
DbRawCommand 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 DbRawCommand("END;"));
haveBegin = false;
myList.Add(null);
}
}
}
if (haveBegin && i == parts.Length - 1)
{
myList.Add(new DbRawCommand("END;"));
}
}
return(myList);
}
// 拆分脚本命令
List <DbRawCommand> ParseCommand(List <DbRawCommand> sqlList)
{
// 重新组合脚本，把 SELECT 和 UPDATE/DELETE 等分开来
bool haveBegin = false;
var myList = new List <DbRawCommand>();
// 创建新命令
Func <DbRawCommand, DbRawCommand> newCommand = cmd =>
{
var parameters = cmd.Parameters == null
? null
: cmd.Parameters.ToList(a => (IDbDataParameter)_provider.CreateParameter(a.ParameterName, a.Value, a.DbType, a.Size, a.Precision, a.Scale, a.Direction));
var result = new DbRawCommand(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 DbSelectCommand || methodName == "SELECT")
{
// 查询单独执行
if (myList.Count > 0 && (i - 1) >= 0 && myList[myList.Count - 1] != null)
{
myList.Add(null);
}
// 创建新命令
DbRawCommand @new = newCommand(cmd);
myList.Add(@new);
myList.Add(null);
}
else
{
// 增删改
if (!haveBegin)
{
myList.Add(new DbRawCommand("BEGIN"));
haveBegin = true;
}
// 创建新命令
DbRawCommand @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 DbRawCommand("END;"));
haveBegin = false;
myList.Add(null);
}
}
}
if (haveBegin && i == sqlList.Count - 1)
{
myList.Add(new DbRawCommand("END;"));
}
}
return(myList);
}
/// <summary> /// 创建 SQL 命令 /// </summary> /// <param name="cmd">命令描述</param> /// <returns></returns> public IDbCommand CreateCommand(DbRawCommand cmd) => this.CreateCommand(cmd.CommandText, cmd.CommandType, cmd.Parameters);
/// <summary>
/// 解析 SQL 命令
/// <para>
/// 返回的已经解析语义中执行批次用 null 分开
/// </para>
/// </summary>
/// <param name="dbQueryables">查询语句</param>
/// <returns></returns>
public virtual List <DbRawCommand> Translate(List <object> dbQueryables)
{
ITranslateContext context = null;
var sqlList = new List <DbRawCommand>();
foreach (var obj in dbQueryables)
{
if (obj == null)
{
continue;
}
if (obj is IDbQueryable)
{
DbQueryable dbQuery = (DbQueryable)obj;
dbQuery.Parameterized = true;
if (context == null)
{
context = this.CreateTranslateContext(dbQuery.DbContext);
}
if (context.Parameters == null)
{
context.Parameters = new List <IDbDataParameter>(8);
}
var cmd = dbQuery.Translate(0, true, context);
sqlList.Add(cmd);
if (cmd.Parameters != null && cmd.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
context = this.CreateTranslateContext(dbQuery.DbContext);
context.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is DbRawSql)
{
DbRawSql rawSql = (DbRawSql)obj;
if (context == null)
{
context = this.CreateTranslateContext(rawSql.DbContext);
}
if (context.Parameters == null)
{
context.Parameters = new List <IDbDataParameter>(8);
}
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
sqlList.Add(cmd);
if (cmd.Parameters != null && cmd.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
context = this.CreateTranslateContext(rawSql.DbContext);
context.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is string)
{
string sql = obj.ToString();
sqlList.Add(new DbRawCommand(sql));
}
else
{
// 解析批量插入操作
List <IDbQueryable> bulkList = obj as List <IDbQueryable>;
if (bulkList != null && bulkList.Count > 0)
{
this.TranslateBulk(sqlList, bulkList);
}
}
}
return(sqlList);
}
// LEFT OR INNER JOIN
private void AppendLfInJoin(ISqlBuilder jf, ISqlBuilder on, DbExpression dbExpression)
{
DbQueryable dbQueryable = (DbQueryable)((dbExpression.Expressions[0] as ConstantExpression).Value);
dbQueryable.Parameterized = jf.Parameterized;
if (!usedKeyword)
{
jf.AppendNewLine();
jf.Append(_keywordName);
usedKeyword = true;
}
else
{
jf.Append(',');
jf.AppendNewLine();
jf.Append(_pad);
}
if (dbQueryable.DbExpressions.Count == 1 && dbQueryable.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable)
{
// 区别 GetTable 有三个重载
var expressions = dbQueryable.DbExpressions[0].Expressions;
if (expressions.Length == 2 && ((expressions[0] as ConstantExpression).Value as string) != null)
{
string text = (expressions[0] as ConstantExpression).Value as string;
object[] @params = (expressions[1] as ConstantExpression).Value as object[];
var provider = ((DbQueryProvider)jf.Provider);
var context = jf.TranslateContext;
// 解析参数
object[] args = null;
if (@params != null)
{
args = @params.Select(x => provider.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = text;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
jf.Append("( ");
jf.Append(_dbExpressionType == DbExpressionType.Delete ? cmd.CommandText.TrimStart() : cmd.CommandText);
jf.Append(')');
}
else
{
Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
jf.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
}
}
else
{
// 嵌套
var cmd = dbQueryable.Translate(jf.Indent + _dbExpressionType == DbExpressionType.Delete ? 2 : 1, false, jf.TranslateContext);
jf.Append("( ");
jf.Append(_dbExpressionType == DbExpressionType.Delete ? cmd.CommandText.TrimStart() : cmd.CommandText);
jf.Append(')');
}
LambdaExpression left = dbExpression.Expressions[1] as LambdaExpression;
LambdaExpression right = dbExpression.Expressions[2] as LambdaExpression;
// t0(t1)
string alias = !(left.Body.NodeType == ExpressionType.New || left.Body.NodeType == ExpressionType.MemberInit)
? _ag.GetTableAlias(dbExpression.Expressions[2])
: _ag.GetTableAlias(right.Parameters[0]);
jf.Append(' ');
jf.Append(alias);
if (on.Length > 0)
{
on.Append(" AND ");
}
if (left.Body.NodeType == ExpressionType.New)
{
NewExpression body1 = left.Body as NewExpression;
NewExpression body2 = right.Body as NewExpression;
for (int index = 0; index < body1.Arguments.Count; ++index)
{
on.AppendMember(_ag, body1.Arguments[index]);
on.Append(" = ");
on.AppendMember(_ag, body2.Arguments[index]);
if (index < body1.Arguments.Count - 1)
{
on.Append(" AND ");
}
}
}
else if (left.Body.NodeType == ExpressionType.MemberInit)
{
MemberInitExpression body1 = left.Body as MemberInitExpression;
MemberInitExpression body2 = right.Body as MemberInitExpression;
for (int index = 0; index < body1.Bindings.Count; ++index)
{
on.AppendMember(_ag, (body1.Bindings[index] as MemberAssignment).Expression);
on.Append(" = ");
on.AppendMember(_ag, (body2.Bindings[index] as MemberAssignment).Expression);
if (index < body1.Bindings.Count - 1)
{
on.Append(" AND ");
}
}
}
else
{
on.AppendMember(_ag, left.Body.ReduceUnary());
on.Append(" = ");
on.AppendMember(_ag, right.Body.ReduceUnary());
}
}
