To accomplish the described task, we need to perform several steps in consecutive order. First, we should add the tables our model will be based on and links between those tables. Then we add entities and attributes and connect our attributes to the tables we added before.
In result of the execution of the code below, we will get a data model which consists of two tables (Customers and Orders), two entities which correspond to these tables and few attributes for each entity.
Below we suppose that we already have correctly initialized DbModel and DbQuery objects (referenced as “model” and “query” correspondingly).
//clear current model
model.Clear();
//add default operators
model.AddDefaultOperators();
//add two tables
DbTable table1 = new DbTable();
table1.Name = "Customers";
table1.Alias = "Cust";
DbTable table2 = new DbTable();
table2.Name = "Orders";
table2.Alias = "Ord";
model.Tables.Add(table1);
model.Tables.Add(table2);
//then we add a link between our tables
TableLink link1 = new TableLink();
link1.Table1 = table1;
link1.Table2 = table2;
//add a condition of linking (Cust.CustNo = Ord.CustNo)
link1.AddCondition(LinkCondType.FieldField, "CustNo", "CustNo", "=");
model.Links.Add(link1);
//now we proceed with adding entities and their attributes
Entity ent1 = model.CreateEntity();
ent1.Name = "Customer";
Entity ent2 = model.CreateEntity();
ent2.Name = "Order";
model.EntityRoot.SubEntities.Add(ent1);
model.EntityRoot.SubEntities.Add(ent2);
DbEntityAttr attr = new DbEntityAttr();
attr.Caption = "Company Name";
attr.Tables.Add(table1);
attr.Expr = "CompanyName";
attr.DataType = DataType.String;
attr.Size = 50;
attr.FillOperatorsWithDefaults(model);
model.AssignEntityAttrID(attr);
ent1.Attributes.Add(attr);
attr = new DbEntityAttr();
attr.Caption = "Company Address";
attr.Tables.Add(table1);
attr.Expr = "Address";
attr.DataType = DataType.String;
attr.Size = 80;
attr.FillOperatorsWithDefaults(model);
model.AssignEntityAttrID(attr);
ent1.Attributes.Add(attr);
attr = new DbEntityAttr();
attr.Caption = "Country";
attr.Tables.Add(table1);
attr.Expr = "CountryID";
attr.DataType = DataType.String;
attr.Size = 3;
//Assign SQLLIST value editor for Country attribute
SqlListValueEditor sqlValueEditor = new SqlListValueEditor();
sqlValueEditor.SQL = "SELECT CountryID, CountryName FROM Countries";
attr.DefaultEditor = sqlValueEditor;
attr.FillOperatorsWithDefaults(model);
model.AssignEntityAttrID(attr);
ent1.Attributes.Add(attr);
attr = new DbEntityAttr();
attr.Kind = EntAttrKind.Virtual;
attr.Caption = "Full Region Code";
attr.Tables.Add(table1);
attr.UseInConditions = true;
attr.Expr = "CountryID + ' ' + RegionCode";
attr.Size = 40;
attr.DataType = DataType.String;
//Assign CUSTOMLIST value editor for Region attribute
CustomListValueEditor regionValueEditor = new CustomListValueEditor();
regionValueEditor.ListName = "RegionList";
attr.DefaultEditor = regionValueEditor;
attr.FillOperatorsWithDefaults(model);
model.AssignEntityAttrID(attr);
ent1.Attributes.Add(attr);
attr = new DbEntityAttr();
attr.Caption = "Order Date";
attr.Tables.Add(table2);
attr.Expr = "OrderDate";
attr.DataType = DataType.Date;
attr.FillOperatorsWithDefaults(model);
model.AssignEntityAttrID(attr);
ent2.Attributes.Add(attr);
attr = new DbEntityAttr();
attr.Caption = "Order Freight";
attr.Tables.Add(table2);
attr.Expr = "Freight";
attr.DataType = DataType.Currency;
attr.FillOperatorsWithDefaults(model);
model.AssignEntityAttrID(attr);
ent2.Attributes.Add(attr);
attr = new DbEntityAttr();
attr.Caption = "Shipping Address";
attr.Tables.Add(table2);
attr.Expr = "ShipAddress";
attr.DataType = DataType.String;
attr.FillOperatorsWithDefaults(model);
model.AssignEntityAttrID(attr);
ent2.Attributes.Add(attr);
//Refresh all model-related information in visual controls
QPanel.UpdateModelInfo();
QCPanel.UpdateModelInfo();
//Finally, try to build some query to check if our model is a correct
query.Clear();
//create one simple condition: Customer Address contains "str"
//here we create condition object
attr = (DbEntityAttr)model.EntityRoot.FindAttribute(EntityAttrProp.Expression, "Cust.Address");
SimpleCondition cond = query.CreateSimpleCondition(model);
cond = query.CreateSimpleCondition(model);
cond.BaseExpr = new DbEntityAttrExpr(model, attr);
cond.Operator = model.Operators.FindByID("Contains");
cond.SetValueExpr(1, new ConstExpr(DataType.String, "str"));
query.Root.Conditions.Add(cond);
//build query to check if it works
SqlQueryBuilder sqlBuilder = new SqlQueryBuilder(query);
sqlBuilder.BuildSQL();
MessageBox.Show(sqlBuilder.Result.SQL);