SqlAnalysis/AnalysisTSQL.cs

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace SqlAnalysis
{
    public class AnalysisTSQL
    {
        protected static Regex SqlMethodTagRegex = new Regex(@"(\(|\))", RegexOptions.Compiled);
        protected static Regex SqlMethodsRegex = new Regex(@"AVG\(.*\)|COUNT\(.*\)|MAX\(.*\)|MIN\(.*\)|SUM\(.*\)|ABS\(.*\)|COALESCE\(.*\)|IFNULL\(.*\)|LENGTH\(.*\)|LOWER\(.*\)|LTRIM\(.*\)|RANDOM\(.*\)|NULLIF\(.*\)|ROUND\(.*\)|RTRIM\(.*\)|SUBSTR\(.*\)|UPPER\(.*\)|TIME\(.*\)|STRFTIME\(.*\)", RegexOptions.Compiled);
        protected static Regex SqlSplitRegex = new Regex(@"(WHERE|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION|JOIN|ORDER BY|GROUP BY| AND | OR | ON |\s)", RegexOptions.Compiled);
        protected static Regex WhereCharRegex = new Regex(@"^.*\s(NOT IN|NOT EXISTS|IN|EXISTS|LIKE|BETWEEN|IS|<|>|<=|>=|=|<>|!=)\s.*$", RegexOptions.Compiled);
        protected static Regex WhereCharMatchRegex = new Regex(@"(NOT IN|NOT EXISTS|IN|EXISTS|LIKE|BETWEEN|IS|<|>|<=|>=|=|<>|!=)", RegexOptions.Compiled);
        protected static Regex whereTagCharRegex = new Regex(@"(IS NULL|NOT IN|NOT EXISTS| IN | EXISTS | LIKE | BETWEEN | IS |<=|>=|=|<>|!=|<|>| AND | OR )", RegexOptions.Compiled);
        protected static Regex TagCharRegex = new Regex(@"(WHERE|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION|JOIN|ORDER BY|GROUP BY|HAVING)", RegexOptions.Compiled);
        protected static Regex JoinCharRegex = new Regex(@"(INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION JOIN|UNION|JOIN)", RegexOptions.Compiled);
        protected static string[] JoinCharArray = "INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION JOIN|UNION|JOIN".Split('|');
        protected static string[] TagCharArray = "WHERE|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|UNION ALL|UNION|JOIN|ORDER BY|GROUP BY|HAVING".Split('|');
        protected static string[] whereTagCharArray = "IS NULL|NOT IN|NOT EXISTS| IN | EXISTS | LIKE | BETWEEN | IS |<=|>=|=|<>|!=|<|>| AND | OR ".Split('|');


        protected Dictionary<string, string> Parameters { get; set; }


        protected string MatchCharParameter(string script)
        {
            Regex reg = new Regex("'(.+?)'");

            var result = reg.Matches(script);

            if (result.Count > 0)
            {
                this.Parameters = new Dictionary<string, string>();
            }

            foreach (Match match in result)
            {
                if (!this.Parameters.ContainsValue(match.Value))
                {
                    string tag = $"@param_{this.Parameters.Count}";
                    this.Parameters.Add(tag, match.Value);
                    script = script.Replace(match.Value, tag);
                }
            }

            script =
                script
                .ToUpper()
                .Replace("SELECT*", "SELECT * ")
                .Replace("*FROM", " * FROM ")
                .Replace("JOIN(", "JOIN (")
                .Replace("IN(", "IN (")
                .Replace("ON(", "ON (");

            return script;
        }