예제 #1
0
        /// <summary>
        /// 解析SQL语句中的Select 字段信息,并返回 From 谓词在SQL语句中的位置索引
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="fromIndex">From 谓词在SQL语句中的位置索引</param>
        /// <returns>Select 字段信息</returns>
        public List <SqlField> ParseSelect(string sql, out int fromIndex)
        {
            Point ps = TextSearchUtil.SearchWordsIndex(sql, "select");

            if (ps.A == -1)
            {
                throw new ArgumentException("未找到期望的谓词select ,不是合法的SQL:\r\n" + sql);
            }
            Point pf = TextSearchUtil.SearchWordsIndex(sql, "from");

            if (pf.A < ps.A)
            {
                throw new ArgumentException("在select谓词之后未找到期望的from谓词,不支持此种类型的SQL分页:\r\n" + sql);
            }
            fromIndex = pf.A;

            string          selectFieldsBlock = sql.Substring(ps.B, pf.A - ps.B).Trim();
            List <SqlField> sqlFields         = new List <SqlField>();

            if (selectFieldsBlock == "*")
            {
                sqlFields.Add(new SqlField()
                {
                    Field = "*"
                });
                return(sqlFields);
            }
            else
            {
                string[] selectFieldsArr = selectFieldsBlock.Split(new string[] { ",", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string fieldItem in selectFieldsArr)
                {
                    int    targetIndex = 0;
                    string field       = TextSearchUtil.FindNearWords(fieldItem, 0, 100, out targetIndex);
                    if (targetIndex >= 0)
                    {
                        //寻找临近的单词,可能没有,可能是AS,也可能直接就是字段别名
                        string fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + field.Length, 50, out targetIndex);
                        if (fieldAsName.ToLower() == "as")
                        {
                            fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + 2, 50, out targetIndex);
                        }
                        sqlFields.Add(new SqlField()
                        {
                            Field = field, Alias = fieldAsName
                        });
                    }
                }
            }
            return(sqlFields);
        }
예제 #2
0
        /// <summary>
        /// 解析SQL语句中的Select 字段信息,并返回 From 谓词在SQL语句中的位置索引
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="fromIndex">From 谓词在SQL语句中的位置索引</param>
        /// <returns>Select 字段信息</returns>
        public List <SqlField> ParseSelect(string sql, out int fromIndex)
        {
            Point ps = TextSearchUtil.SearchWordsIndex(sql, "select");

            if (ps.A == -1)
            {
                throw new ArgumentException("未找到期望的谓词select ,不是合法的SQL:\r\n" + sql);
            }
            Point pd = TextSearchUtil.SearchWordsIndex(sql, "DISTINCT");

            if (pd.A != -1)
            {
                ps.B = pd.B;
            }

            Point pf = TextSearchUtil.SearchWordsIndex(sql, "from");

            if (pf.A < ps.A)
            {
                throw new ArgumentException("在select谓词之后未找到期望的from谓词,不支持此种类型的SQL分页:\r\n" + sql);
            }
            fromIndex = pf.A;

            string          selectFieldsBlock = sql.Substring(ps.B, pf.A - ps.B).Trim();
            List <SqlField> sqlFields         = new List <SqlField>();

            if (selectFieldsBlock == "*")
            {
                sqlFields.Add(new SqlField()
                {
                    Field = "*"
                });
                return(sqlFields);
            }
            else
            {
                string[] selectFieldsArr = selectFieldsBlock.Split(new string[] { ",", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string fieldItem in selectFieldsArr)
                {
                    int    targetIndex = 0;
                    string field       = TextSearchUtil.FindNearWords(fieldItem, 0, 100, out targetIndex);
                    if (targetIndex >= 0)
                    {
                        //select 字段可能是函数调用,感谢网友 芜湖-大枕头 发现此Bug 2017.4.14
                        string fieldUp = field.ToUpper();
                        if (fieldUp == "SUM" || fieldUp == "AVG" || fieldUp == "MIN" || fieldUp == "MAX" || fieldUp == "COUNT" || fieldUp.IndexOf('(') > 0)
                        {
                            int    atas        = fieldItem.ToUpper().IndexOf(" AS ");
                            string fieldAsName = fieldItem.Substring(atas + 3);
                            sqlFields.Add(new SqlField()
                            {
                                Field = fieldItem.Substring(0, atas),
                                Alias = fieldAsName
                            });
                        }
                        else
                        {
                            //寻找临近的单词,可能没有,可能是AS,也可能直接就是字段别名
                            string fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + field.Length, 50, out targetIndex);
                            if (fieldAsName.ToLower() == "as")
                            {
                                fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + 2, 50, out targetIndex);
                            }
                            sqlFields.Add(new SqlField()
                            {
                                Field = field, Alias = fieldAsName
                            });
                        }
                    }
                }
            }
            return(sqlFields);
        }
예제 #3
0
        /// <summary>
        /// 构造可分页的排序SQL语句,如果topCount小于0,源SQL必须带排序语句
        /// </summary>
        /// <param name="topCount">如果指定大于0的值,将生成Top子句,如果小于0,则是限制逆向排序的记录数</param>
        /// <returns></returns>
        public string Build(int topCount)
        {
            int             fromIndex;
            Point           orderBlockPoint;
            bool            isSelectStart = false;
            List <SqlField> sqlFields     = ParseSelect(this.sourceSql, out fromIndex);

            this.OrderFields = ParseOrder(this.sourceSql, out orderBlockPoint);
            if (sqlFields[0].Field == "*")
            {
                isSelectStart = true;
            }
            else if (this.OrderFields != null)
            {
                //检查参与排序的字段是否在SELECT中,如果不在,添加进去
                foreach (OrderField orderItem in this.OrderFields)
                {
                    var target = sqlFields.Find(p => string.Equals(p.Field, orderItem.Field, StringComparison.OrdinalIgnoreCase) ||
                                                string.Equals(p.Alias, orderItem.Field, StringComparison.OrdinalIgnoreCase));
                    if (target == null)
                    {
                        orderItem.CreateAlias("");
                        var tempField = new SqlField()
                        {
                            Field = orderItem.Field, Alias = orderItem.Alias
                        };
                        sqlFields.Add(tempField);
                        orderItem.InSelect = false;
                    }
                    else
                    {
                        orderItem.Field = target.Field;
                        orderItem.Alias = target.Alias;

                        orderItem.InSelect = true;
                    }
                }
            }
            this.SelectFields = sqlFields;
            //重新构造Select语句块
            System.Text.StringBuilder sb = new StringBuilder();
            //DISTINCT 查询问题,感谢网友 @深圳-光头佬 发现此问题 2017.6.21
            Point psd = TextSearchUtil.SearchWordsIndex(sourceSql, "select DISTINCT");

            if (psd.A != -1)
            {
                sb.Append("SELECT DISTINCT ");
            }
            else
            {
                sb.Append("SELECT ");
            }

            if (topCount > 0)
            {
                sb.Append("Top " + topCount + "\r\n");
            }
            else if (topCount < 0)
            {
                sb.Append("Top " + (-topCount) + "\r\n");
            }
            else
            {
                sb.Append(" \r\n");
            }
            if (isSelectStart)
            {
                sb.Append(" * \r\n");
            }
            else
            {
                int count = this.SelectFields.Count;
                int index = 0;
                foreach (SqlField item in this.SelectFields)
                {
                    index++;
                    sb.Append('\t');
                    sb.Append(item.Field);
                    if (!string.IsNullOrEmpty(item.Alias))
                    {
                        sb.Append(" AS ");
                        sb.Append(item.Alias);
                    }
                    if (index != count)
                    {
                        sb.Append(',');
                    }
                    sb.Append("\r\n");
                }
            }
            if (topCount < 0)
            {
                //逆排序,用于获取最后几页的数据,包括最后一页
                if (orderBlockPoint.A == -1)
                {
                    throw new Exception("当参数 topCount小于0(逆排序),必须指定排序信息");
                }
                //改变Order by 子句,生成逆向排序查询
                string beforOrder         = this.sourceSql.Substring(fromIndex, orderBlockPoint.A - fromIndex);
                string afterOrder         = this.sourceSql.Substring(orderBlockPoint.B);
                string InverseOrderString = "\r\n ORDER BY " + this.GetLastInverseOrderExpString() + "\r\n";
                sb.Append(beforOrder);
                sb.Append(InverseOrderString);
                sb.Append(afterOrder);
            }
            else
            {
                sb.Append(this.sourceSql.Substring(fromIndex));
            }
            return(sb.ToString());
        }