Regex.Replace替换字符串问题

Question

我的目的是替换伪SQL中 @开头的参数名为"?"，当@前不是空格，则会将“REP.f_getId(@GroupId)” 替换为“REP.f_getId?)”string sql = @"select d.UserId
                                 ,d.UserName 
                           from table(REP.f_getId(@GroupId)) a
                                left join BIR.Tb_Group b
                                on a.id=b.GroupId
                                left join REP.Tb_User_Group c
                                on a.id=c.GroupId
                                left join REP.Tb_User d
                                on c.UserId=d.UserId
                          where b.Status=@Status and d.Status<=@Status ";
            List<string> result = new List<string>();
            Regex paramReg = new Regex(@"[^@@](?<p>@\w+)", RegexOptions.IgnoreCase | RegexOptions.Multiline);
            MatchCollection matches = paramReg.Matches(String.Concat(sql, ""));
            foreach (Match m in matches)
            {
                result.Add(m.Groups["p"].Value);
            }
            foreach (string s in result)
            {
                Console.WriteLine(s);
            }
            if (paramReg.IsMatch(sql))
            {
                sql = paramReg.Replace(sql, "?");
            }
            Console.WriteLine(sql);
            Console.Read();执行结果为:select d.UserId
                                 ,d.UserName 
                           from table(BIR.f_getId?)) a
                                left join BIR.Tb_Group b
                                on a.id=b.GroupId
                                left join BIR.Tb_User_Group c
                                on a.id=c.GroupId
                                left join BIR.Tb_User d
                                on c.UserId=d.UserId
                          where b.Status? and d.Status<? 各位帮我看看 ，正则表达式那里处理问题 ！ 

Answer 1

而我想要的结果为：select d.UserId
                                 ,d.UserName 
                           from table(BIR.f_getId(?)) a
                                left join BIR.Tb_Group b
                                on a.id=b.GroupId
                                left join BIR.Tb_User_Group c
                                on a.id=c.GroupId
                                left join BIR.Tb_User d
                                on c.UserId=d.UserId
                          where b.Status=? and d.Status<=? 

Answer 2

sql = Regex.Replace(sql,@"@\w+","?");

Answer 3

主要目的是排除 对 "SELECT @@INDENTITY" 的替换!

Answer 4

如果SQL语句里有 SELECT @@INDENTITY  会被替换.这个会替换为 "SELECT @?"

Answer 5

这里我用Regex paramReg = new Regex(@"[^@@](?<p>@\w+)", RegexOptions.IgnoreCase | RegexOptions.Multiline);
MatchCollection matches = paramReg.Matches(String.Concat(sql, ""));
            foreach (Match m in matches)
            {
                result.Add(m.Groups["p"].Value);
            }提取需要替换的字符串时没有问题的.就是在使用替换 方法是出问题! 

Answer 6

sql = Regex.Replace(sql,@"(?<!@)@\w+","?");

Answer 7

private static List<DbParameter> GetOleDbParameters(List<DbParameter> sourceParameters, ref string sourceSqlText)
        {
            List<DbParameter> outParameters = new List<DbParameter>();
            List<string> paraNameList = GetParameterNameList(ref sourceSqlText);
            foreach (string paraName in paraNameList)
            {
                GetOleDbParameter(paraName, sourceParameters, ref outParameters);
            }
            return outParameters;
        }
        private static List<string> GetParameterNameList(ref string sourceSqlText)
        {
            //sourceSqlText = sourceSqlText.Replace("(@", "( @");
            sourceSqlText = sourceSqlText.Replace("(@", "( @").Replace("=@", "= @").Replace(">@", "> @");
            List<string> result = new List<string>();
            Regex paramReg = new Regex(@"[^@@](?<p>@\w+)");
            //Regex paramReg = new Regex(@"[^@@](?<p>@\[\w+\])");
            MatchCollection matches = paramReg.Matches(String.Concat(sourceSqlText, " "));
            foreach (Match m in matches)
            {
                result.Add(m.Groups["p"].Value);
            }
            foreach (string s in result)
            {
                Console.WriteLine(s);
            }
            if (paramReg.IsMatch(sourceSqlText))
            {
                sourceSqlText = paramReg.Replace(sourceSqlText, "?");
            }
            return result;
        }
        private static void GetOleDbParameter(string paraName, List<DbParameter> sourceParameters, ref List<DbParameter> parameters)
        {
            bool isNothing = false;
            foreach (DbParameter para in sourceParameters)
            {
                if (para.ParameterName.ToUpper().Equals(paraName.ToUpper()))
                {
                    isNothing = true;
                    parameters.Add(para);
                    break;
                }
            }
            if (isNothing == false)
            {
                throw new Exception(string.Format("{0}: 参数未提供!", paraName));
            }
        }

Answer 8

private static List<DbParameter> GetOleDbParameters(List<DbParameter> sourceParameters, ref string sourceSqlText)
        {
            List<DbParameter> outParameters = new List<DbParameter>();
            List<string> paraNameList = GetParameterNameList(ref sourceSqlText);
            foreach (string paraName in paraNameList)
            {
                GetOleDbParameter(paraName, sourceParameters, ref outParameters);
            }
            return outParameters;
        }
        private static List<string> GetParameterNameList(ref string sourceSqlText)
        {
            //sourceSqlText = sourceSqlText.Replace("(@", "( @");
            sourceSqlText = sourceSqlText.Replace("(@", "( @").Replace("=@", "= @").Replace(">@", "> @");
            List<string> result = new List<string>();
            Regex paramReg = new Regex(@"[^@@](?<p>@\w+)");
            //Regex paramReg = new Regex(@"[^@@](?<p>\[\w+\])");
            MatchCollection matches = paramReg.Matches(String.Concat(sourceSqlText, " "));
            foreach (Match m in matches)
            {
                result.Add(m.Groups["p"].Value);
            }
            foreach (string s in result)
            {
                Console.WriteLine(s);
            }
            if (paramReg.IsMatch(sourceSqlText))
            {
                sourceSqlText = paramReg.Replace(sourceSqlText, "?");
            }
            return result;
        }
        private static void GetOleDbParameter(string paraName, List<DbParameter> sourceParameters, ref List<DbParameter> parameters)
        {
            bool isNothing = false;
            foreach (DbParameter para in sourceParameters)
            {
                if (para.ParameterName.ToUpper().Equals(paraName.ToUpper()))
                {
                    isNothing = true;
                    parameters.Add(para);
                    break;
                }
            }
            if (isNothing == false)
            {
                throw new Exception(string.Format("{0}: 参数未提供!", paraName));
            }
        }