Esempio n. 1
0
        /// <summary>
        /// 执行SQL 语句,并返回由 T 指定的对象
        /// </summary>
        /// <typeparam name="T">基元类型、单实体、列表(List&lt;T&gt;)、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));
        }
Esempio n. 2
0
        /// <summary>
        /// 异步执行SQL 语句,并返回由 T 指定的对象
        /// </summary>
        /// <typeparam name="T">基元类型、单实体、列表(List&lt;T&gt;)、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));
        }
Esempio n. 3
0
        /// <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()));
                }
            }
        }
Esempio n. 4
0
 /// <summary>
 /// 创建 SQL 命令
 /// </summary>
 /// <param name="cmd">命令描述</param>
 /// <returns></returns>
 public IDbCommand CreateCommand(RawCommand cmd)
 {
     return(this.CreateCommand(cmd.CommandText, cmd.CommandType, cmd.Parameters));
 }
Esempio n. 5
0
        // 拆分脚本命令
        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);
        }
Esempio n. 6
0
        // 拆分脚本命令
        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);
        }
Esempio n. 7
0
        /// <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);
        }