private void GetToolsByProducts() { var query = Query.GetCategoryQuery(View.GetSelectedCategory()); if (query == "one product") { query = Query.GetProdIdByProductName(View.GetChoosenProd()); } else { query = SelectFormatter.Get("prod_id", "(" + query + ")"); } var conditions = new List <string>(); conditions.Add("time_end<=to_date('" + View.GetTimeEnd() + "','dd.mm.yyyy')"); conditions.Add("time_begin>=to_date('" + View.GetTimeBegin() + "','dd.mm.yyyy')"); conditions.Add(QueryFormatter.In("prod_id", query)); query = QueryFormatter.SelectFormatter.Get("prod_id" , "products_exps" , conditions); query = Query.GetToolsByProdIdLab(query, View.GetChoosenLab()); Controller.Run <DirectorScientistsPresenter, string>(query); }
public void GetRowValuesString_DateTimeValue_ShouldHaveSqlFormatting(DateTime dateTime, string expectedFormat) { var dataColumns = new DataColumn[] { new DataColumn("Col1", typeof(DateTime)) }; var dataTable = GetDataTable(dataColumns); dataTable.Rows.Add(dateTime); IQueryFormatter queryFormatter = new QueryFormatter(); queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().BeEquivalentTo($"'{expectedFormat}'"); }
public void GetRowValuesString_StringValue_ShouldBeWrappedByQuotes() { var data = "foo"; var dataColumns = new DataColumn[] { new DataColumn("Col1", typeof(string)) }; var dataTable = GetDataTable(dataColumns); dataTable.Rows.Add(data); IQueryFormatter queryFormatter = new QueryFormatter(); queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().BeEquivalentTo($"'{data}'"); }
static internal SqlPreCommandSimple Format(Expression expression) { QueryFormatter qf = new QueryFormatter(); qf.Visit(expression); var parameters = qf.parameterExpressions.Values.Select(pi => pi.Parameter).ToList(); var sqlpc = new SqlPreCommandSimple(qf.sb.ToString(), parameters); return(PostFormatter.Value == null ? sqlpc : PostFormatter.Value.Invoke(sqlpc)); }
public void Format_Commas_OnNewLine() { var formatted = QueryFormatter.Format("select cola, colb, colc from dbo.table where cola = \"test\""); Assert.Equal(@"SELECT cola ,colb ,colc FROM dbo.table WHERE cola = ""test""", formatted); }
public void GetRowValuesString_DateTimeValue_ShouldBeWrappedByQuotes() { var data = DateTime.Now; var dataColumns = new DataColumn[] { new DataColumn("Col1", typeof(DateTime)) }; var dataTable = GetDataTable(dataColumns); dataTable.Rows.Add(data); IQueryFormatter queryFormatter = new QueryFormatter(); queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().StartWith("'"); queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().EndWith("'"); }
public void GetRowValuesString_DecimalValue_ShouldNotBeWrappedByQuotes() { var data = 1.1m; var dataColumns = new DataColumn[] { new DataColumn("Col1", typeof(decimal)) }; var dataTable = GetDataTable(dataColumns); dataTable.Rows.Add(data); IQueryFormatter queryFormatter = new QueryFormatter(); queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().BeEquivalentTo($"{data}"); queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().NotContain("'"); }
public void ColumnListString_ShouldReturnColumnsList() { var col1 = new DataColumn("Col1", typeof(string)); col1.AllowDBNull = true; var col2 = new DataColumn("Col2", typeof(DateTime)); col2.AllowDBNull = true; var col3 = new DataColumn("Col3", typeof(int)); col3.AllowDBNull = true; var dataColumns = new DataColumn[] { col1, col2, col3 }; var dataTable = GetDataTable(dataColumns); IQueryFormatter queryFormatter = new QueryFormatter(); queryFormatter.GetColumnsListString(dataTable.Columns.ToList()).Should().BeEquivalentTo("Col1, Col2, Col3"); }
public void GetRowValuesString_ShouldProduceNull_IfColumnIsNullableAndValueIsNull() { var data = new object[] { null, null, null }; var col1 = new DataColumn("Col1", typeof(string)); col1.AllowDBNull = true; var col2 = new DataColumn("Col2", typeof(DateTime)); col2.AllowDBNull = true; var col3 = new DataColumn("Col3", typeof(int)); col3.AllowDBNull = true; var dataColumns = new DataColumn[] { col1, col2, col3 }; var dataTable = GetDataTable(dataColumns); dataTable.Rows.Add(data); IQueryFormatter queryFormatter = new QueryFormatter(); queryFormatter.GetRowValuesString(dataTable.Rows[0]).Should().BeEquivalentTo($"NULL, NULL, NULL"); }
private TranslateResult Translate(Expression expression) { ProjectionExpression projection = expression as ProjectionExpression; if (projection == null) { Expression modified = Evaluator.PartialEval(expression, CanBeEvaluatedLocally); modified = new QueryBinder(this).Bind(modified); //expression = new OrderByRewriter().Rewrite(expression); modified = new UnusedColumnRemover().Remove(modified); modified = new RedundantSubqueryRemover().Remove(modified); projection = (ProjectionExpression)modified; } string commandText = new QueryFormatter().Format(projection.Source); //LambdaExpression projector = new ProjectionBuilder().Build(projection); LambdaExpression projector = ((UnaryExpression)((MethodCallExpression)expression).Arguments[1]).Operand as LambdaExpression; return(new TranslateResult { CommandText = commandText, Projector = projector }); }
/// <summary> /// Deletes every row in the table that satisfies the given predicate. /// </summary> /// <returns>The number of rows deleted.</returns> public int Delete(Expression <Func <T, bool> > predicate) { if (predicate == null) { throw new ArgumentNullException(nameof(predicate)); } var pred = PropertyReplacer.Replace(predicate.Body, predicate.Parameters[0]); pred = ((SQLiteQueryProvider)Provider).Translate(pred); if (pred is ProjectionExpression projection) { pred = projection.Source; } var args = new List <object>(); var cmdText = $"DELETE FROM [{Name}] WHERE {QueryFormatter.Format(pred, args)};"; _db.Execute(cmdText, args.ToArray()); return(_db.Changes); }
internal IEnumerable <T> ExecuteQuery <T>(ProjectionExpression projection) { List <object> args = new List <object>(); string sql = QueryFormatter.Format(projection.Source, args); Database.Log?.Invoke(sql); using (SQLiteQuery query = Database.ExecuteQuery(sql, args.ToArray())) { LambdaExpression projectorExpr = ProjectionBuilder.Build( projection.Projector, projection.Source.Alias, name => string.IsNullOrEmpty(name) ? 0 : query.GetColumnIndex(name) ); var projector = (Func <SQLiteQueryProvider, SQLiteQuery, T>)projectorExpr.Compile(); while (query.Step()) { yield return(projector(this, query)); } } }
public override string ToString() { return(QueryFormatter.Format(this)); }
public static string GetExperimentsByScientist(string nameScientist) { var name = nameScientist.Split(' '); var expIdQuery = QueryFormatter.SelectFormatter.Get("exp_id" , "investigators" , new List <string> { "first_name='" + name[0] + "'" , "last_name='" + name[1] + "'" , "patronymic='" + name[2] + "'" }); var prodExpQuery = QueryFormatter.SelectFormatter.Get(new List <string> { "time_begin" , "time_end" , "prod_id" , "exp_id" } , "products_exps" , expIdQuery , "exp_id"); var expNameQuery = QueryFormatter.SelectFormatter .Get(new List <string> { "id", "name" }, "experiments", expIdQuery, "id"); var joinQuery = QueryFormatter.Join(prodExpQuery , expNameQuery , new List <string> { "exp_id=id" } , "t1" , "t2"); joinQuery = QueryFormatter.SelectFormatter.Get("*", joinQuery); var unionQuery = QueryFormatter.SelectFormatter .Union(new List <string> { "name", "prod_id" } , new List <string> { "rockets" , "gliders" , "hang_gliders" , "helicopters" , "planes" , "other_prods" }); joinQuery = QueryFormatter.Join(joinQuery , unionQuery , new List <string> { "prod_id=prod_id" } , "t3" , "t4"); return(QueryFormatter.SelectFormatter.Get(new List <string> { "t3.name" , "t4.name" , "time_begin" , "time_end" } , joinQuery)); }