예제 #1
0
        /// <summary>
        /// Merges multiple parameters into this list. <br />
        /// Checks for name clashes, and will rename parameters if necessary. <br />
        /// If some parameter is renamed the returned Sql statement will containg the original sql replaced with new names, else (if nothing changed) returns null. <br />
        /// </summary>
        public string MergeParameters(ParameterInfos parameters, string sql)
        {
            Dictionary <string, string> renamedParameters = new Dictionary <string, string>();

            foreach (var parameter in parameters.Values)
            {
                string newParameterName = MergeParameter(parameter);
                if (newParameterName != null && parameter.Name != newParameterName)
                {
                    renamedParameters.Add(DapperQueryBuilderOptions.DatabaseParameterSymbol + parameter.Name, DapperQueryBuilderOptions.DatabaseParameterSymbol + newParameterName);
                }
            }
            if (renamedParameters.Any())
            {
                Regex matchParametersRegex = new Regex("(?:[,~=<>*/%+&|^-]|\\s|\\b|^)* (" + string.Join("|", renamedParameters.Select(p => p.Key)) + ") (?:[,~=<>*/%+&|^-]|\\s|\\b|$)",
                                                       RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled);
                string newSql = matchParametersRegex.Replace(sql, match => {
                    Group group    = match.Groups[match.Groups.Count - 1]; // last match is the inner parameter
                    string replace = renamedParameters[group.Value];
                    return(String.Format("{0}{1}{2}", match.Value.Substring(0, group.Index - match.Index), replace, match.Value.Substring(group.Index - match.Index + group.Length)));
                });
                return(newSql);
            }
            return(null);
        }
예제 #2
0
 /// <inheritdoc/>
 public void MergeParameters(ParameterInfos target)
 {
     foreach (IFilter filter in this)
     {
         filter.MergeParameters(target);
     }
 }
예제 #3
0
        /// <summary>
        /// New Filter statement. <br />
        /// Example: $"[CategoryId] = {categoryId}" <br />
        /// Example: $"[Name] LIKE {productName}"
        /// </summary>
        public Filter(FormattableString filter)
        {
            var parsedStatement = new InterpolatedStatementParser(filter);

            Sql            = parsedStatement.Sql;
            Parameters     = parsedStatement.Parameters;
            _parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
        }
예제 #4
0
        /// <summary>
        /// New Filter statement. <br />
        /// Example: $"[CategoryId] = {categoryId}" <br />
        /// Example: $"[Name] LIKE {productName}"
        /// </summary>
        public Filter(FormattableString filter)
        {
            var parsedStatement = new InterpolatedStatementParser(filter);

            Sql            = parsedStatement.Sql;
            Parameters     = parsedStatement.Parameters;
            _parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => DapperQueryBuilderOptions.DatabaseParameterSymbol + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
        }
        private InterpolatedStatementParser(string format, params object[] arguments)
        {
            Parameters = new ParameterInfos();

            StringBuilder sb = new StringBuilder();

            if (string.IsNullOrEmpty(format))
            {
                return;
            }
            var matches = _formattableArgumentRegex.Matches(format);
            int lastPos = 0;

            for (int i = 0; i < matches.Count; i++)
            {
                // unescape escaped curly braces
                string sql = format.Substring(lastPos, matches[i].Index - lastPos).Replace("{{", "{").Replace("}}", "}");
                lastPos = matches[i].Index + matches[i].Length;

                // arguments[i] may not work because same argument can be used multiple times
                int    argPos    = int.Parse(matches[i].Groups["ArgPos"].Value);
                string argFormat = matches[i].Groups["Format"].Value;
                object arg       = arguments[argPos];

                if (arg is string && argFormat == "raw") // example: {nameof(Product.Name):raw}  -> won't be parametrized, we just emit raw string!
                {
                    sb.Append(sql);
                    sb.Append(arg);
                    continue;
                }

                // If user passes " column LIKE '{variable}' ", we assume that he used single quotes incorrectly as if interpolated string was a sql literal
                if (quotedVariableStart.IsMatch(sql) && quotedVariableEnd.IsMatch(format.Substring(lastPos)))
                {
                    sql = sql.Substring(0, sql.Length - 1); // skip starting quote
                    lastPos++;                              // skip closing quote
                }

                sb.Append(sql);

                string parmName       = AutoGeneratedParameterPrefix + _autoNamedParametersCount.ToString();
                string parmObjectName = AutoGeneratedParameterObjectPrefix + _autoNamedParametersCount.ToString();
                _autoNamedParametersCount++;
                //var direction = System.Data.ParameterDirection.Input;
                //if (argFormat == "out")
                //    direction = System.Data.ParameterDirection.Output;
                Parameters.Add(new ParameterInfo(parmObjectName, arg));
                sb.Append(parmName);
            }
            string lastPart = format.Substring(lastPos).Replace("{{", "{").Replace("}}", "}");

            sb.Append(lastPart);
            Sql            = sb.ToString();
            _parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
        }
 /// <summary>
 /// Merges parameters from this query/statement into a CommandBuilder. <br />
 /// Checks for name clashes, and will rename parameters (in CommandBuilder) if necessary. <br />
 /// If some parameter is renamed the underlying Sql statement will have the new parameter names replaced by their new names.<br />
 /// This method does NOT append Parser SQL to CommandBuilder SQL (you may want to save this SQL statement elsewhere)
 /// </summary>
 public void MergeParameters(ParameterInfos target)
 {
     string newSql = target.MergeParameters(Parameters, Sql);
     if (newSql != null)
     {
         Sql = newSql;
         _parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get<dynamic>(n)) + "'"));
         // filter parameters in Sql were renamed and won't match the previous passed filters - discard original parameters to avoid reusing wrong values
         Parameters = null;
     }
 }
예제 #7
0
        /// <summary>
        /// Merges multiple parameters into this list. <br />
        /// Checks for name clashes, and will rename parameters if necessary. <br />
        /// If some parameter is renamed the returned Sql statement will containg the original sql replaced with new names, else (if nothing changed) returns null. <br />
        /// </summary>
        public string MergeParameters(ParameterInfos parameters, string sql)
        {
            string newSql = sql;

            foreach (var parameter in parameters.Values)
            {
                string newParameterName = MergeParameter(parameter);
                if (newParameterName != null)
                {
                    newSql = newSql.Replace("@" + parameter.Name, "@" + newParameterName);
                }
            }
            if (newSql != sql)
            {
                return(newSql);
            }
            return(null);
        }
예제 #8
0
        /// <summary>
        /// If you're using Filters in standalone structure (without QueryBuilder), <br />
        /// you can just "build" the filters over a ParameterInfos and get the string for the filters (with leading WHERE)
        /// </summary>
        /// <param name="target"></param>
        /// <returns></returns>
        public string BuildFilters(DynamicParameters target)
        {
            ParameterInfos parameters = new ParameterInfos();

            foreach (IFilter filter in this)
            {
                filter.MergeParameters(parameters);
            }
            foreach (var parameter in parameters.Values)
            {
                target.Add(parameter.Name, parameter.Value, parameter.DbType, parameter.ParameterDirection, parameter.Size);
            }
            StringBuilder sb = new StringBuilder();

            WriteFilter(sb);
            if (sb.Length > 0)
            {
                return("WHERE " + sb.ToString());
            }
            return("");
        }
예제 #9
0
 /// <summary>
 /// New CommandBuilder. 
 /// </summary>
 /// <param name="cnn"></param>
 public CommandBuilder(IDbConnection cnn)
 {
     _cnn = cnn;
     _command = new StringBuilder();
     _parameters = new ParameterInfos();
 }
        private InterpolatedStatementParser(string format, params object[] arguments)
        {
            Parameters = new ParameterInfos();

            StringBuilder sb = new StringBuilder();

            if (string.IsNullOrEmpty(format))
            {
                return;
            }
            var matches = _formattableArgumentRegex.Matches(format);
            int lastPos = 0;

            for (int i = 0; i < matches.Count; i++)
            {
                // unescape escaped curly braces
                string sql = format.Substring(lastPos, matches[i].Index - lastPos).Replace("{{", "{").Replace("}}", "}");
                lastPos = matches[i].Index + matches[i].Length;

                // arguments[i] may not work because same argument can be used multiple times
                int           argPos     = int.Parse(matches[i].Groups["ArgPos"].Value);
                string        argFormat  = matches[i].Groups["Format"].Value;
                List <string> argFormats = argFormat.Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries).Select(f => f.Trim()).ToList();
                object        arg        = arguments[argPos];

                if (argFormats.Contains("raw")) // example: {nameof(Product.Name):raw}  -> won't be parametrized, we just emit raw string!
                {
                    sb.Append(sql);
                    sb.Append(arg);
                    continue;
                }
                else if (arg is FormattableString fsArg) //Support nested FormattableString
                {
                    sb.Append(sql);
                    var    nestedStatement = new InterpolatedStatementParser(fsArg);
                    string subSql          = nestedStatement.Sql;
                    if (nestedStatement.Parameters.Any())
                    {
                        subSql = (Parameters.MergeParameters(nestedStatement.Parameters, nestedStatement.Sql) ?? subSql);
                    }
                    sb.Append(nestedStatement.Sql);
                    continue;
                }
                else if (arg is ICompleteCommand innerQuery) //Support nested QueryBuilder, CommandBuilder or FluentQueryBuilder
                {
                    sb.Append(sql);
                    string innerSql = innerQuery.Sql;
                    // Convert back from @p0 @p1 etc to {0} {1} etc (FormattableString standard)
                    Regex matchParametersRegex = new Regex("(?:[,~=<>*/%+&|^-]|\\s|\\b|^)* " + "(" + DapperQueryBuilderOptions.DatabaseParameterSymbol + DapperQueryBuilderOptions.AutoGeneratedParameterName + "(?<Name>\\d*)" + ")" + " (?:[,~=<>*/%+&|^-]|\\s|\\b|$)",
                                                           RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled);
                    innerSql = matchParametersRegex.Replace(innerSql, match => {
                        Group parm     = match.Groups[1];
                        int parmNum    = int.Parse(match.Groups[2].Value);
                        string replace = "{" + parmNum + "}";
                        string ret     = string.Format("{0}{1}{2}", match.Value.Substring(0, parm.Index - match.Index), replace, match.Value.Substring(parm.Index - match.Index + parm.Length));
                        return(ret);
                    });


                    var    nestedStatement = new InterpolatedStatementParser(innerSql, innerQuery.Parameters.Select(p => p.Value.Value).ToArray());
                    string subSql          = nestedStatement.Sql;
                    if (nestedStatement.Parameters.Any())
                    {
                        subSql = (Parameters.MergeParameters(nestedStatement.Parameters, nestedStatement.Sql) ?? subSql);
                    }
                    sb.Append(nestedStatement.Sql);
                    continue;
                }
                // If user passes " column LIKE '{variable}' ", we assume that he used single quotes incorrectly as if interpolated string was a sql literal
                if (quotedVariableStart.IsMatch(sql) && quotedVariableEnd.IsMatch(format.Substring(lastPos)))
                {
                    sql = sql.Substring(0, sql.Length - 1); // skip starting quote
                    lastPos++;                              // skip closing quote
                }

                sb.Append(sql);

                var direction             = System.Data.ParameterDirection.Input;
                System.Data.DbType?dbType = null;
                if (argFormats.Contains("out"))
                {
                    direction = System.Data.ParameterDirection.Output;
                }

                System.Data.DbType parsedDbType;
                Match m;
                foreach (var f in argFormats)
                {
                    if (arg is string && (m = regexDbTypeString.Match(f)) != null && m.Success) // String(maxlength) / nvarchar(maxlength) / String / nvarchar
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = false,
                            IsFixedLength = false,
                            Value         = (string)arg,
                            Length        = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeAnsiString.Match(f)) != null && m.Success) // AnsiString(maxlength) / varchar(maxlength) / AnsiString / varchar
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = true,
                            IsFixedLength = false,
                            Value         = (string)arg,
                            Length        = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeStringFixedLength.Match(f)) != null && m.Success) // StringFixedLength(length) / nchar(length) / StringFixedLength / nchar
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = false,
                            IsFixedLength = true,
                            Value         = (string)arg,
                            Length        = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeAnsiStringFixedLength.Match(f)) != null && m.Success) // AnsiStringFixedLength(length) / char(length) / AnsiStringFixedLength / char
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = true,
                            IsFixedLength = true,
                            Value         = (string)arg,
                            Length        = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeText.Match(f)) != null && m.Success) // text / varchar(MAX) / varchar(-1)
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = false,
                            IsFixedLength = true,
                            Value         = (string)arg,
                            Length        = int.MaxValue
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeNText.Match(f)) != null && m.Success) // ntext / nvarchar(MAX) / nvarchar(-1)
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = true,
                            IsFixedLength = true,
                            Value         = (string)arg,
                            Length        = int.MaxValue
                        }
                    }
                    ;

                    else if (!(arg is DbString) && dbType == null && Enum.TryParse <System.Data.DbType>(value: f, ignoreCase: true, result: out parsedDbType))
                    {
                        dbType = parsedDbType;
                    }

                    //TODO: parse SqlDbTypes?
                    // https://stackoverflow.com/questions/35745226/net-system-type-to-sqldbtype
                    // https://gist.github.com/tecmaverick/858392/53ddaaa6418b943fa3a230eac49a9efe05c2d0ba
                }
                sb.Append(DapperQueryBuilderOptions.DatabaseParameterSymbol + Parameters.Add(arg, dbType, direction));
            }
            string lastPart = format.Substring(lastPos).Replace("{{", "{").Replace("}}", "}");

            sb.Append(lastPart);
            Sql            = sb.ToString();
            _parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => DapperQueryBuilderOptions.DatabaseParameterSymbol + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
        }
        private InterpolatedStatementParser(string format, params object[] arguments)
        {
            Parameters = new ParameterInfos();

            StringBuilder sb = new StringBuilder();

            if (string.IsNullOrEmpty(format))
            {
                return;
            }
            var matches = _formattableArgumentRegex.Matches(format);
            int lastPos = 0;

            for (int i = 0; i < matches.Count; i++)
            {
                // unescape escaped curly braces
                string sql = format.Substring(lastPos, matches[i].Index - lastPos).Replace("{{", "{").Replace("}}", "}");
                lastPos = matches[i].Index + matches[i].Length;

                // arguments[i] may not work because same argument can be used multiple times
                int           argPos     = int.Parse(matches[i].Groups["ArgPos"].Value);
                string        argFormat  = matches[i].Groups["Format"].Value;
                List <string> argFormats = argFormat.Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries).Select(f => f.Trim()).ToList();
                object        arg        = arguments[argPos];

                if (argFormats.Contains("raw")) // example: {nameof(Product.Name):raw}  -> won't be parametrized, we just emit raw string!
                {
                    sb.Append(sql);
                    sb.Append(arg);
                    continue;
                }

                // If user passes " column LIKE '{variable}' ", we assume that he used single quotes incorrectly as if interpolated string was a sql literal
                if (quotedVariableStart.IsMatch(sql) && quotedVariableEnd.IsMatch(format.Substring(lastPos)))
                {
                    sql = sql.Substring(0, sql.Length - 1); // skip starting quote
                    lastPos++;                              // skip closing quote
                }

                sb.Append(sql);

                var direction             = System.Data.ParameterDirection.Input;
                System.Data.DbType?dbType = null;
                if (argFormats.Contains("out"))
                {
                    direction = System.Data.ParameterDirection.Output;
                }

                System.Data.DbType parsedDbType;
                Match m;
                foreach (var f in argFormats)
                {
                    if (arg is string && (m = regexDbTypeString.Match(f)) != null && m.Success) // String(maxlength) / nvarchar(maxlength) / String / nvarchar
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = false,
                            IsFixedLength = false,
                            Value         = (string)arg,
                            Length        = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeAnsiString.Match(f)) != null && m.Success) // AnsiString(maxlength) / varchar(maxlength) / AnsiString / varchar
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = true,
                            IsFixedLength = false,
                            Value         = (string)arg,
                            Length        = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeStringFixedLength.Match(f)) != null && m.Success) // StringFixedLength(length) / nchar(length) / StringFixedLength / nchar
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = false,
                            IsFixedLength = true,
                            Value         = (string)arg,
                            Length        = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeAnsiStringFixedLength.Match(f)) != null && m.Success) // AnsiStringFixedLength(length) / char(length) / AnsiStringFixedLength / char
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = true,
                            IsFixedLength = true,
                            Value         = (string)arg,
                            Length        = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeText.Match(f)) != null && m.Success) // text / varchar(MAX) / varchar(-1)
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = false,
                            IsFixedLength = true,
                            Value         = (string)arg,
                            Length        = int.MaxValue
                        }
                    }
                    ;
                    else if (arg is string && (m = regexDbTypeNText.Match(f)) != null && m.Success) // ntext / nvarchar(MAX) / nvarchar(-1)
                    {
                        arg = new DbString()
                        {
                            IsAnsi        = true,
                            IsFixedLength = true,
                            Value         = (string)arg,
                            Length        = int.MaxValue
                        }
                    }
                    ;

                    else if (!(arg is DbString) && dbType == null && Enum.TryParse <System.Data.DbType>(value: f, ignoreCase: true, result: out parsedDbType))
                    {
                        dbType = parsedDbType;
                    }

                    //TODO: parse SqlDbTypes?
                    // https://stackoverflow.com/questions/35745226/net-system-type-to-sqldbtype
                    // https://gist.github.com/tecmaverick/858392/53ddaaa6418b943fa3a230eac49a9efe05c2d0ba
                }
                sb.Append(Parameters.Add(arg, dbType, direction));
            }
            string lastPart = format.Substring(lastPos).Replace("{{", "{").Replace("}}", "}");

            sb.Append(lastPart);
            Sql            = sb.ToString();
            _parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
        }