/// <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)) + "'")); }
private InterpolatedStatementParser(string format, params object[] arguments) { Parameters = new ParameterInfos(); if (string.IsNullOrEmpty(format)) { return; } StringBuilder sb = new StringBuilder(); var matches = _formattableArgumentRegex.Matches(format); int currentBlockEndPos = 0; int previousBlockEndPos = 0; for (int i = 0; i < matches.Count; i++) { previousBlockEndPos = currentBlockEndPos; currentBlockEndPos = matches[i].Index + matches[i].Length; // unescape escaped curly braces string sql = format.Substring(previousBlockEndPos, matches[i].Index - previousBlockEndPos).Replace("{{", "{").Replace("}}", "}"); // 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); if (nestedStatement.Parameters.Any()) { sb.Append(Parameters.MergeParameters(nestedStatement.Parameters, nestedStatement.Sql)); } else { 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(currentBlockEndPos))) { sql = sql.Substring(0, sql.Length - 1); // skip starting quote currentBlockEndPos++; // 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; * } */ if (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(currentBlockEndPos).Replace("{{", "{").Replace("}}", "}"); sb.Append(lastPart); Sql = sb.ToString(); _parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'")); }