public ImageRecord(DbString dbstring) { string[] parts = dbstring.Split(_DELIM); HasBeenUsed = Boolean.Parse(parts[0]); Name = parts[1]; Url = parts[2]; }
private static DbString AsDbString([CanBeNull] this string value, int maxLength, [CanBeNull] string defaultValue = "", bool?isAnsi = null, bool?isFixedLength = null) { var s = new DbString { Length = maxLength, Value = value.ToMaxLength(maxLength) ?? defaultValue }; if (isAnsi.HasValue) { s.IsAnsi = isAnsi.Value; } if (isFixedLength.HasValue) { s.IsFixedLength = isFixedLength.Value; } return(s); }
public override string BuildLimits(string query, int limit, int offset) { if (offset == 0) { return(query.Insert(6, string.Concat(" TOP ", limit))); } int index = DbString.IndexOf(query, "ORDER BY"); string select = (index >= 0) ? query.Substring(0, index) : query; string fields = select.Substring(7, select.IndexOf("FROM", StringComparison.OrdinalIgnoreCase) - 7); string orderby = (index >= 0) ? query.Substring(index) : "ORDER BY Id"; select = select.Insert(6, " ROW_NUMBER() OVER(" + orderby + ") AS RowNum,"); return(string.Format( @"WITH Buffer AS ({0}) SELECT {4} FROM Buffer WHERE RowNum BETWEEN {3} AND {3}+{2}-1 {1};", select, orderby, limit, offset + 1, fields)); }
public void TestDefaultDbStringDbType() { var origDefaultStringDbType = DbString.IsAnsiDefault; try { DbString.IsAnsiDefault = true; var a = new DbString { Value = "abcde" }; var b = new DbString { Value = "abcde", IsAnsi = false }; Assert.True(a.IsAnsi); Assert.False(b.IsAnsi); } finally { DbString.IsAnsiDefault = origDefaultStringDbType; } }
public static IEnumerable <Commit <byte[]> > Execute(DocumentTransaction tx, ReadStream command) { if (tx.SqlTransaction.IsolationLevel != IsolationLevel.Snapshot) { throw new InvalidOperationException("Reads from event store is best done in snapshot isolation so they don't block writes."); } var sql = $@" SELECT Position, EventId, CommitId, @Id AS [StreamId], SequenceNumber, Name, Generation, Metadata, Data FROM {tx.Store.Database.FormatTableNameAndEscape(command.Table.Name)} WHERE StreamId = @Id AND SequenceNumber >= @fromStreamSeq AND Position <= @toPosition ORDER BY SequenceNumber {(command.Direction == Direction.Forward ? "ASC" : "DESC")}"; // Using DbString over just string as a important performance optimization, // see https://github.com/StackExchange/dapper-dot-net/issues/288 var idParameter = new DbString { Value = command.StreamId, IsAnsi = false, IsFixedLength = false, Length = 850 }; return(tx.SqlConnection.Query <Row>(sql, new { Id = idParameter, command.FromStreamSeq, command.ToPosition }, tx.SqlTransaction, buffered: false).Batch()); }
public static DbString Coalesce(this DbFunctions dbFunctions, DbString value, params IDbFragment[] other) { return(new DbStringFunctionValue("coalesce", new[] { value }.Concat(other))); }
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)) + "'")); }
public static DbString IIf(this DbFunctions dbFunctions, DbBool condition, DbString trueValue, DbString falseValue) => new DbStringFunctionValue("iif", new IDbFragment[] { condition, trueValue, falseValue });
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)) + "'")); }
private List <OrderReport> GetReportData(string jtSorting, List <Criterias> criteria) { List <OrderReport> lst = new List <OrderReport>(); os = new OrderSummary(); string userRestriction = UserRestriction.GetUserRestriction(User.Identity.GetUserName(), "OrderReport"); if (userRestriction == "") { return(lst); } string conStr = ConnectionStringManager.ProdConnectionString; using (con = new NpgsqlConnection(conStr)) { string query = "salesreport"; con.Open(); //ToDo : sorgu dinamik hale getirilecek. string prQuery = ""; if (criteria.Count > 0) { prQuery = prQuery + "(1 = 0 "; } foreach (var item in criteria) { prQuery = prQuery + " OR (1=1"; if (item.startdate != null) { prQuery = prQuery + " AND f.PostedDate >= '" + item.startdate + " 12:00:00 AM'"; } if (item.enddate != null) { prQuery = prQuery + " AND f.PostedDate <= '" + item.enddate + " 11:59:59 PM'"; } if (item.asin != null) { prQuery = prQuery + " AND s.asin = '" + item.asin + "'"; } if (item.sku != null) { prQuery = prQuery + " AND s.sku = '" + item.sku + "'"; } if (item.vendor != null) { prQuery = prQuery + " AND s.vendor = '" + item.vendor + "'"; } if (item.skutitle != null) { prQuery = prQuery + " AND s.title like '%" + item.skutitle + "%'"; } if (item.fulfill != null) { prQuery = prQuery + " AND s.FulfillmentBy = '" + item.fulfill + "'"; } prQuery = prQuery + ") "; } prQuery = prQuery + ")"; if (userRestriction != "") { prQuery = "(" + userRestriction + " AND " + prQuery + ")"; } DbString str = new DbString(); str.Value = prQuery; lst = con.Query <OrderReport>(query, new { parameterquery = str }, commandType: CommandType.StoredProcedure).ToList <OrderReport>(); long orderItems = 0; long unitsOrdered = 0; decimal orderProductSales = 0; decimal avgUnitsOrderedItem = 0; decimal avgSalesOrderedItem = 0; decimal refundAmount = 0; decimal profit = 0; foreach (var item in lst) { orderItems = orderItems + item.OrderItem; unitsOrdered = unitsOrdered + item.UnitsOrdered; orderProductSales = orderProductSales + item.OrderUAmount; refundAmount = refundAmount + item.RefundUAmount; profit = profit + item.Profit; } if (lst.Count > 0) { avgUnitsOrderedItem = Convert.ToDecimal(orderItems) / Convert.ToDecimal(unitsOrdered); avgSalesOrderedItem = Math.Round(orderProductSales / orderItems, 3); os.AvgSalesOrderItem = Math.Round(avgSalesOrderedItem, 3); os.AvgUnitsOrderItem = Math.Round(avgUnitsOrderedItem, 3); os.OrderedProductSales = orderProductSales; os.TotalOrderItems = orderItems; os.UnitsOrdered = unitsOrdered; os.RefundAmount = refundAmount; os.Profit = profit; } else { os.AvgSalesOrderItem = 0; os.AvgUnitsOrderItem = 0; os.OrderedProductSales = 0; os.Profit = 0; os.RefundAmount = 0; os.TotalOrderItems = 0; os.UnitsOrdered = 0; } //TODO : diğer alanlarda konacak. if (jtSorting != null && jtSorting != "") { lst.Sort(jtSorting); } } return(lst); }
public static DbString ToUpper(this DbString value) { return(new DbStringFunctionValue("upper", new[] { value })); }
public static DbString Average(this DbFunctions dbFunctions, DbString value) { return(new DbStringFunctionValue("avg", new[] { value })); }
//public static DbExpression RowNumber(this DbFunctions dbFunctions,IEnumerable<Column> partitionBy = null, IEnumerable<(Column column, Sort sort)> orderBy = null) //{ // return new RowNumberFunctionDbExpression(partitionBy ?? Enumerable.Empty<Column>(), orderBy ?? Enumerable.Empty<(Column column, Sort sort)>()); //} public static DbString Sum(this DbFunctions dbFunctions, DbString value) { return(new DbStringFunctionValue("sum", new[] { value })); }
public static DbString Right(this DbFunctions dbFunctions, DbString dbString, int length) => new DbStringFunctionValue("right", new IDbFragment[] { dbString, length.DbValue() });
public static DbString Max(this DbFunctions dbFunctions, DbString value) { return(new DbStringFunctionValue("max", new[] { value })); }
public static DbInt Len(this DbFunctions dbFunctions, DbString dbString) => new DbIntFunctionValue("len", new[] { dbString });
//public static TResult Cast<T, TResult>(this DbFunctions dbFunctions, T value, ColumnType<TResult> column) //{ // throw new InvalidOperationException(Exceptions.DontInvokeDirectly); //} public static DbBool Like(this DbFunctions dbFunctions, DbString field, string pattern) { return(new DbBoolBinaryOperator(field, "like", pattern.DbValue())); }
public static DbString Replicate(this DbFunctions dbFunctions, DbString dbString, DbInt length) => new DbStringFunctionValue("replicate", new IDbFragment[] { dbString, length });