/// <summary> /// Create database command using provided sql and by /// replacing ? with parameter arguments. /// </summary> /// <param name="query">ADO.NET driver</param> /// <param name="sql">sql to execute</param> /// <param name="parameters">parameters to bind</param> /// <returns></returns> public static IDbCommand CreateCommand( this IDatabaseQuery query, string sql, params object[] parameters) { Contract.Requires(query != null); Contract.Requires(!string.IsNullOrWhiteSpace(sql)); var com = query.NewCommand(); com.CommandText = sql; if (parameters != null) { foreach (var p in parameters) { var cp = com.CreateParameter(); cp.Value = p; if (string.IsNullOrEmpty(cp.ParameterName)) { cp.ParameterName = ":p" + (com.Parameters.Count + 1); } com.Parameters.Add(cp); var index = sql.IndexOf('?'); sql = sql.Substring(0, index) + cp.ParameterName + sql.Substring(index + 1); } } com.CommandText = sql; return(com); }
public DataTable Analyze( IEnumerable <string> dimensions, IEnumerable <string> facts, IEnumerable <KeyValuePair <string, bool> > order, ISpecification <TSource> filter, int?limit, int?offset) { var usedDimensions = new List <string>(); var usedFacts = new List <string>(); var customOrder = new List <KeyValuePair <string, bool> >(); if (dimensions != null) { usedDimensions.AddRange(dimensions); } if (facts != null) { usedFacts.AddRange(facts); } if (order != null) { foreach (var o in order) { if (o.Key != null) { customOrder.Add(new KeyValuePair <string, bool>(o.Key, o.Value)); } } } ValidateInput(usedDimensions, usedFacts, customOrder.Select(it => it.Key)); var sb = new StringBuilder(); var alias = filter != null?filter.IsSatisfied.Parameters.First().Name : "it"; if (offset != null) { sb.Append("SELECT "); sb.Append(string.Join(", ", usedDimensions.UnionAll(usedFacts).Select(it => "\"" + it + "\""))); sb.AppendLine(" FROM ("); } if (limit != null || offset != null) { sb.Append("SELECT /*+ FIRST_ROWS(n) */ "); sb.Append(string.Join(", ", usedDimensions.UnionAll(usedFacts).Select(it => "\"" + it + "\""))); if (offset != null) { sb.Append(", RowNum rn$"); } sb.AppendLine(" FROM ("); } sb.Append("SELECT "); foreach (var d in usedDimensions) { sb.AppendFormat("{0} AS \"{1}\", ", CubeDimensions[d](alias), d); } foreach (var f in usedFacts) { sb.AppendFormat("{0} AS \"{1}\", ", CubeFacts[f](alias), f); } sb.Length -= 2; sb.AppendLine(); sb.AppendFormat("FROM {0} \"{1}\"", Source, alias); sb.AppendLine(); var command = DatabaseQuery.NewCommand(); if (filter != null) { var cf = Locator.Resolve <IOracleConverterFactory>(); var ep = Locator.Resolve <IExtensibilityProvider>(); var qp = new MainQueryParts( Locator, cf, ep.ResolvePlugins <IQuerySimplification>(), ep.ResolvePlugins <IExpressionMatcher>(), ep.ResolvePlugins <IMemberMatcher>(), new IProjectionMatcher[0]); var linq = new Queryable <TSource>(new QueryExecutor(DatabaseQuery, Locator, cf, ep)).Filter(filter); var parser = QueryParser.CreateDefault(); var model = parser.GetParsedQuery(linq.Expression); if (model.BodyClauses.Count > 0) { sb.AppendLine("WHERE"); for (int i = 0; i < model.BodyClauses.Count; i++) { var wc = model.BodyClauses[i] as WhereClause; sb.Append(" "); if (i > 0) { sb.Append("AND "); } sb.Append(qp.GetSqlExpression(wc.Predicate)); } } foreach (var p in qp.Parameters.Parameters) { command.Parameters.Add(p); } } sb.AppendLine(); if (usedDimensions.Count > 0) { sb.Append("GROUP BY "); sb.AppendLine(string.Join(", ", usedDimensions.Select(it => CubeDimensions[it](alias)))); } if (customOrder.Count > 0) { sb.Append("ORDER BY "); sb.AppendLine(string.Join(", ", customOrder.Select(it => "\"{0}\" {1}".With(it.Key, it.Value ? string.Empty : "DESC")))); } if (limit != null || offset != null) { sb.AppendLine(") sq$"); if (limit != null) { sb.Append("WHERE RowNum <= "); sb.Append(limit.Value + (offset != null ? offset.Value : 0)); } } if (offset != null) { sb.AppendLine(") sq$"); sb.Append("WHERE sq$.rn$ > "); sb.Append(offset.Value); } command.CommandText = sb.ToString(); return(DatabaseQuery.Fill(command)); }