/// <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); }