Creating data model dynamically with code

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);

var attr = model.AddEntityAttr(new DbEntityAttrDescriptor(ent1, "CompanyName", table1) { 
    Caption = "Company Name",
    DataType = DataType.String,
    Size = 50
});

attr = model.AddEntityAttr(new DbEntityAttrDescriptor(ent1, "Address", table1) {
    Caption = "Company Address",
    DataType = DataType.String,
    Size = 80
});


attr = model.AddEntityAttr(new DbEntityAttrDescriptor(ent1, "CountryID", table1) {
    Caption = "Country",
    DataType = DataType.String,
    Size = 3
});

//Assign SQLLIST value editor for Country attribute
string sql = "SELECT CountryID, CountryName FROM Countries";
SqlListValueEditor sqlValueEditor = new SqlListValueEditor("sql_list", sql);
attr.DefaultEditor = sqlValueEditor;


attr = model.AddEntityAttr(new DbEntityAttrDescriptor(ent1, EntityAttrKind.Virtual, 
                                            "CountryID + ' ' + RegionCode", table1) 
{
    Caption = "Full Region Code",
    DataType = DataType.String,
    Size = 40
});          

//Assign CUSTOMLIST value editor for Region attribute
CustomListValueEditor regionValueEditor = new CustomListValueEditor("region_list", "RegionList");
attr.DefaultEditor = regionValueEditor;

attr = model.AddEntityAttr(new DbEntityAttrDescriptor(ent2, "OrderDate", table2) {
    Caption = "Order Date",
    DataType = DataType.Date
});

attr = model.AddEntityAttr(new DbEntityAttrDescriptor(ent2, "Freight", table2) {
    Caption = "Order Freight",
    DataType = DataType.Currency
});

attr = model.AddEntityAttr(new DbEntityAttrDescriptor(ent2, "ShipAddress", table2) {
    Caption = "Shipping Address",
    DataType = DataType.String
});

//If we have a WinForms or WPF project 
//it's better to refresh visual controls
//(just remove the following two lines if it a ASP.NET (Core) project)
QPanel.UpdateModelInfo();
QCPanel.UpdateModelInfo();

//Finally, try to build some query to check if our model is a correct
query.Clear();

//create a simple condition: Customer Address contains "str"
query.Root.AddSimpleCondition("Cust.Address", "Contains", "str");

//build query to check if it works
SqlQueryBuilder sqlBuilder = new SqlQueryBuilder(query);
sqlBuilder.Formats.SetDefaultFormats(FormatType.SqlServer);
sqlBuilder.BuildSQL();
var sqlStatement = sqlBuilder.Result.SQL;