Back to
Latest changes:

This is an old revision of the document!


Add columns and conditions by code

Here we suppose that our Query object is referenced as query1 and DataModel object as dataModel1.

addColumnsConditions.cs
 private void AddColumnsConditionsByCode() {
 
    query1.Clear();
 
    EntityAttr attr;
    attr = dataModel1.EntityRoot.FindAttribute(EntityAttrProp.Expression , "Customers.Country");
 
    //create columns
    Column col;
    col = new DbColumn("Country", SortDirection.Ascending);
    col.Expr = new DbEntityAttrExpr(dataModel1, attr);
    query1.Columns.Add(col);
 
    attr = dataModel1.EntityRoot.FindAttribute(EntityAttrProp.Expression, "Orders.Freight");
    col = new DbColumn("Total sum", SortDirection.None);
    col.Expr = new DbAggrFuncExpr(dataModel1, "SUM", new DbEntityAttrExpr(dataModel1, attr));
    query1.Columns.Add(col);
 
    //create conditions
    //here we create condition object
    SimpleCondition cond = new DbSimpleCondition(dataModel1);
 
    //then we search for an entity attribute which will be used in the left side of condition
    attr = dataModel1.EntityRoot.FindAttribute(EntityAttrProp.Expression, "Orders.OrderDate");
 
    //after that we add found entity attribute as first (left side) expression of our condition
    cond.BaseExpr = new DbEntityAttrExpr(dataModel1, attr);
 
    //here we set an operator used in condition. In our case it will be  "is less than" (< symbol in SQL syntax)
    cond.Operator = dataModel1.Operators.FindByID("LessThan");
 
    //finally we set the rigth side expression which is some constant value in our case.
    cond.SetValueExpr(1, new ConstExpr(DataType.Date, "2005-01-01"));
    cond.ReadOnly = true;
 
    //when all parts of our condition are ready - we add it to query
    query1.Root.Conditions.Add(cond);
 
    //here is more simple and quicker way to add a condition (same attribute, operator and value)
    query1.Root.AddSimpleCondition("Orders.OrderDate", "DateBeforePrecise", "2005-01-01");
 
 
     //generate SQL statement
     SqlQueryBuilder builder = new SqlQueryBuilder(query1);
     builder.BuildSQL();
     string sql = builder.Result.SQL;
 
 }

Discussion

, 2014/07/22 14:57
Hello!
I have some question.
My database is dynamic.
I can add new column or delete existing column. So, I want to add this column to my "Model.xml"
e.g
I have Persons table. I want add new column to this table. How to do this?

Above code does not work...
, 2014/07/22 15:26
Please don't add your comments three times. They don't appear immediately because we manually check each comment before posting to prevent spam comments.
As for your question: where exactly that code does not work?
, 2014/07/22 15:27
By the way, since it's more a support request - it's better to move our conversation to email.
, 2014/10/08 16:40
How can you access the joins (table links) from dbQquery. We need to append a column in the select based on the link.

Thanks

Chirag
, 2014/10/28 09:29
Do you need to get information about all links in your model or only those once which were used in generated query?
Please contact us with your request via contact/support form on our web-site: http://devtools.korzh.com/support/
Enter your comment: