//////////////////////////////////////////////////////////////////////////
/// 1st way:
/// This method demonstrates the direct access to the internal metadata
/// objects collection (MetadataContainer).
//////////////////////////////////////////////////////////////////////////
public void Way1(object sender, EventArgs e)
{
ActiveTabs = 0;
var queryBuilder1 = QueryBuilderStore.Get("LoadMetadata");
ResetQueryBuilderMetadata(queryBuilder1);
queryBuilder1.SyntaxProvider = new GenericSyntaxProvider();
// prevent QueryBuilder to request metadata
queryBuilder1.MetadataLoadingOptions.OfflineMode = true;
queryBuilder1.MetadataProvider = null;
MetadataContainer metadataContainer = queryBuilder1.MetadataContainer;
metadataContainer.BeginUpdate();
try
{
metadataContainer.Clear();
MetadataNamespace schemaDbo = metadataContainer.AddSchema("dbo");
// prepare metadata for table "Orders"
MetadataObject orders = schemaDbo.AddTable("Orders");
// fields
orders.AddField("OrderId");
orders.AddField("CustomerId");
// prepare metadata for table "Order Details"
MetadataObject orderDetails = schemaDbo.AddTable("Order Details");
// fields
orderDetails.AddField("OrderId");
orderDetails.AddField("ProductId");
// foreign keys
MetadataForeignKey foreignKey = orderDetails.AddForeignKey("OrderDetailsToOrders");
using (MetadataQualifiedName referencedName = new MetadataQualifiedName())
{
referencedName.Add("Orders");
referencedName.Add("dbo");
foreignKey.ReferencedObjectName = referencedName;
}
foreignKey.Fields.Add("OrderId");
foreignKey.ReferencedFields.Add("OrderId");
}
finally
{
metadataContainer.EndUpdate();
}
queryBuilder1.MetadataStructure.Refresh();
}
protected void btn1_Click(object sender, EventArgs e)
{
var queryBuilder1 = QueryBuilderControl1.QueryBuilder;
// prevent QueryBuilder to request metadata
queryBuilder1.OfflineMode = true;
queryBuilder1.MetadataProvider = null;
MetadataContainer metadataContainer = queryBuilder1.MetadataContainer;
metadataContainer.BeginUpdate();
try
{
metadataContainer.Items.Clear();
MetadataNamespace schemaDbo = metadataContainer.AddSchema("dbo");
// prepare metadata for table "Orders"
MetadataObject orders = schemaDbo.AddTable("Orders");
// fields
orders.AddField("OrderId");
orders.AddField("CustomerId");
// prepare metadata for table "Order Details"
MetadataObject orderDetails = schemaDbo.AddTable("Order Details");
// fields
orderDetails.AddField("OrderId");
orderDetails.AddField("ProductId");
// foreign keys
MetadataForeignKey foreignKey = orderDetails.AddForeignKey("OrderDetailsToOrders");
using (MetadataQualifiedName referencedName = new MetadataQualifiedName())
{
referencedName.Add("Orders");
referencedName.Add("dbo");
foreignKey.ReferencedObjectName = referencedName;
}
foreignKey.Fields.Add("OrderId");
foreignKey.ReferencedFields.Add("OrderId");
}
finally
{
metadataContainer.EndUpdate();
}
queryBuilder1.MetadataStructure.Refresh();
StatusBar1.Message.Information("Metadata loaded");
}
private void fillProgrammaticallyMenuItem_Click(object sender, EventArgs e)
{
ResetQueryBuilder();
// Fill the query builder metadata programmatically
// setup the query builder with metadata and syntax providers
queryBuilder1.SyntaxProvider = genericSyntaxProvider1;
queryBuilder1.MetadataLoadingOptions.OfflineMode = true; // prevent querying obejects from database
// create database and schema
MetadataNamespace database = queryBuilder1.MetadataContainer.AddDatabase("MyDB");
database.Default = true;
MetadataNamespace schema = database.AddSchema("MySchema");
schema.Default = true;
// create table
MetadataObject tableOrders = schema.AddTable("Orders");
tableOrders.AddField("OrderID");
tableOrders.AddField("OrderDate");
tableOrders.AddField("CustomerID");
tableOrders.AddField("ResellerID");
// create another table
MetadataObject tableCustomers = schema.AddTable("Customers");
tableCustomers.AddField("CustomerID");
tableCustomers.AddField("CustomerName");
tableCustomers.AddField("CustomerAddress");
// add a relation between these two tables
MetadataForeignKey relation = tableCustomers.AddForeignKey("FK_CustomerID");
relation.Fields.Add("CustomerID");
relation.ReferencedObjectName = tableOrders.GetQualifiedName();
relation.ReferencedFields.Add("CustomerID");
//create view
MetadataObject viewResellers = schema.AddView("Resellers");
viewResellers.AddField("ResellerID");
viewResellers.AddField("ResellerName");
// kick the query builder to fill metadata tree
queryBuilder1.InitializeDatabaseSchemaTree();
WarnAboutGenericSyntaxProvider(); // show warning (just for demonstration purposes)
}
//////////////////////////////////////////////////////////////////////////
/// 1st way:
/// This method demonstrates the direct access to the internal metadata
/// objects collection (MetadataContainer).
//////////////////////////////////////////////////////////////////////////
private void btn1Way_Click(object sender, EventArgs e)
{
// prevent QueryBuilder to request metadata
QBuilder.MetadataLoadingOptions.OfflineMode = true;
QBuilder.MetadataProvider = null;
MetadataContainer metadataContainer = QBuilder.MetadataContainer;
metadataContainer.BeginUpdate();
try
{
metadataContainer.Clear();
MetadataNamespace schemaDbo = metadataContainer.AddSchema("dbo");
// prepare metadata for table "Orders"
MetadataObject orders = schemaDbo.AddTable("Orders");
// fields
orders.AddField("OrderId");
orders.AddField("CustomerId");
// prepare metadata for table "Order Details"
MetadataObject orderDetails = schemaDbo.AddTable("Order Details");
// fields
orderDetails.AddField("OrderId");
orderDetails.AddField("ProductId");
// foreign keys
MetadataForeignKey foreignKey = orderDetails.AddForeignKey("OrderDetailsToOrders");
using (MetadataQualifiedName referencedName = new MetadataQualifiedName())
{
referencedName.Add("Orders");
referencedName.Add("dbo");
foreignKey.ReferencedObjectName = referencedName;
}
foreignKey.Fields.Add("OrderId");
foreignKey.ReferencedFields.Add("OrderId");
}
finally
{
metadataContainer.EndUpdate();
}
QBuilder.InitializeDatabaseSchemaTree();
}
//////////////////////////////////////////////////////////////////////////
/// 4th way:
/// This method demonstrates manual filling of metadata structure from
/// stored DataSet.
//////////////////////////////////////////////////////////////////////////
private void btn4Way_Click(object sender, EventArgs e)
{
QBuilder.MetadataProvider = null;
QBuilder.MetadataLoadingOptions.OfflineMode = true; // prevent QueryBuilder to request metadata from connection
DataSet dataSet = new DataSet();
// Load sample dataset created in the Visual Studio with Dataset Designer
// and exported to XML using WriteXmlSchema() method.
dataSet.ReadXmlSchema(@"StoredDataSetSchema.xml");
QBuilder.MetadataContainer.BeginUpdate();
try
{
QBuilder.ClearMetadata();
// add tables
foreach (DataTable table in dataSet.Tables)
{
// add new metadata table
MetadataObject metadataTable = QBuilder.MetadataContainer.AddTable(table.TableName);
// add metadata fields (columns)
foreach (DataColumn column in table.Columns)
{
// create new field
MetadataField metadataField = metadataTable.AddField(column.ColumnName);
// setup field
metadataField.FieldType = TypeToDbType(column.DataType);
metadataField.Nullable = column.AllowDBNull;
metadataField.ReadOnly = column.ReadOnly;
if (column.MaxLength != -1)
{
metadataField.Size = column.MaxLength;
}
// detect the field is primary key
foreach (DataColumn pkColumn in table.PrimaryKey)
{
if (column == pkColumn)
{
metadataField.PrimaryKey = true;
}
}
}
// add relations
foreach (DataRelation relation in table.ParentRelations)
{
// create new relation on the parent table
MetadataForeignKey metadataRelation = metadataTable.AddForeignKey(relation.RelationName);
// set referenced table
using (MetadataQualifiedName referencedName = new MetadataQualifiedName())
{
referencedName.Add(relation.ParentTable.TableName);
metadataRelation.ReferencedObjectName = referencedName;
}
// set referenced fields
foreach (DataColumn parentColumn in relation.ParentColumns)
{
metadataRelation.ReferencedFields.Add(parentColumn.ColumnName);
}
// set fields
foreach (DataColumn childColumn in relation.ChildColumns)
{
metadataRelation.Fields.Add(childColumn.ColumnName);
}
}
}
}
finally
{
QBuilder.MetadataContainer.EndUpdate();
}
QBuilder.InitializeDatabaseSchemaTree();
}
protected void btn4_Click(object sender, EventArgs e)
{
var queryBuilder1 = QueryBuilderControl1.QueryBuilder;
queryBuilder1.MetadataProvider = null;
queryBuilder1.OfflineMode = true; // prevent QueryBuilder to request metadata from connection
DataSet dataSet = new DataSet();
// Load sample dataset created in the Visual Studio with Dataset Designer
// and exported to XML using WriteXmlSchema() method.
dataSet.ReadXmlSchema(Path.Combine(Server.MapPath("~/"), "StoredDataSetSchema.xml"));
queryBuilder1.MetadataContainer.BeginUpdate();
try
{
queryBuilder1.MetadataContainer.Items.Clear();
// add tables
foreach (DataTable table in dataSet.Tables)
{
// add new metadata table
MetadataObject metadataTable = queryBuilder1.MetadataContainer.AddTable(table.TableName);
// add metadata fields (columns)
foreach (DataColumn column in table.Columns)
{
// create new field
MetadataField metadataField = metadataTable.AddField(column.ColumnName);
// setup field
metadataField.FieldType = TypeToDbType(column.DataType);
metadataField.Nullable = column.AllowDBNull;
metadataField.ReadOnly = column.ReadOnly;
if (column.MaxLength != -1)
{
metadataField.Size = column.MaxLength;
}
// detect the field is primary key
foreach (DataColumn pkColumn in table.PrimaryKey)
{
if (column == pkColumn)
{
metadataField.PrimaryKey = true;
}
}
}
// add relations
foreach (DataRelation relation in table.ParentRelations)
{
// create new relation on the parent table
MetadataForeignKey metadataRelation = metadataTable.AddForeignKey(relation.RelationName);
// set referenced table
using (MetadataQualifiedName referencedName = new MetadataQualifiedName())
{
referencedName.Add(relation.ParentTable.TableName);
metadataRelation.ReferencedObjectName = referencedName;
}
// set referenced fields
foreach (DataColumn parentColumn in relation.ParentColumns)
{
metadataRelation.ReferencedFields.Add(parentColumn.ColumnName);
}
// set fields
foreach (DataColumn childColumn in relation.ChildColumns)
{
metadataRelation.Fields.Add(childColumn.ColumnName);
}
}
}
}
finally
{
queryBuilder1.MetadataContainer.EndUpdate();
}
queryBuilder1.MetadataStructure.Refresh();
StatusBar1.Message.Information("Metadata loaded");
}
//////////////////////////////////////////////////////////////////////////
/// 4th way:
/// This method demonstrates manual filling of metadata structure from
/// stored DataSet.
//////////////////////////////////////////////////////////////////////////
public void Way4(object sender, EventArgs e)
{
ActiveTabs = 3;
var queryBuilder1 = QueryBuilderStore.Get("LoadMetadata");
ResetQueryBuilderMetadata(queryBuilder1);
queryBuilder1.MetadataLoadingOptions.OfflineMode = true; // prevent QueryBuilder to request metadata from connection
DataSet dataSet = new DataSet();
// Load sample dataset created in the Visual Studio with Dataset Designer
// and exported to XML using WriteXmlSchema() method.
var xml = Path.Combine(Server.MapPath("~"), "../Sample databases/StoredDataSetSchema.xml");
dataSet.ReadXmlSchema(xml);
queryBuilder1.MetadataContainer.BeginUpdate();
try
{
queryBuilder1.ClearMetadata();
// add tables
foreach (DataTable table in dataSet.Tables)
{
// add new metadata table
MetadataObject metadataTable = queryBuilder1.MetadataContainer.AddTable(table.TableName);
// add metadata fields (columns)
foreach (DataColumn column in table.Columns)
{
// create new field
MetadataField metadataField = metadataTable.AddField(column.ColumnName);
// setup field
metadataField.FieldType = TypeToDbType(column.DataType);
metadataField.Nullable = column.AllowDBNull;
metadataField.ReadOnly = column.ReadOnly;
if (column.MaxLength != -1)
{
metadataField.Size = column.MaxLength;
}
// detect the field is primary key
foreach (DataColumn pkColumn in table.PrimaryKey)
{
if (column == pkColumn)
{
metadataField.PrimaryKey = true;
}
}
}
// add relations
foreach (DataRelation relation in table.ParentRelations)
{
// create new relation on the parent table
MetadataForeignKey metadataRelation = metadataTable.AddForeignKey(relation.RelationName);
// set referenced table
using (MetadataQualifiedName referencedName = new MetadataQualifiedName())
{
referencedName.Add(relation.ParentTable.TableName);
metadataRelation.ReferencedObjectName = referencedName;
}
// set referenced fields
foreach (DataColumn parentColumn in relation.ParentColumns)
{
metadataRelation.ReferencedFields.Add(parentColumn.ColumnName);
}
// set fields
foreach (DataColumn childColumn in relation.ChildColumns)
{
metadataRelation.Fields.Add(childColumn.ColumnName);
}
}
}
}
finally
{
queryBuilder1.MetadataContainer.EndUpdate();
}
queryBuilder1.MetadataStructure.Refresh();
}
// HOWTO: Fill metadata container with custom objects
public void FillMetadataContainer()
{
MetadataNamespace database = _query.SQLContext.MetadataContainer.AddDatabase("MyDB");
database.Default = true; // hides the default database prefix from object names
MetadataNamespace schema = database.AddSchema("MySchema");
schema.Default = true; // hides the default schema prefix from object names
// create table
MetadataObject tableOrders = schema.AddTable("Orders");
tableOrders.AddField("OrderID");
tableOrders.AddField("OrderDate");
tableOrders.AddField("City");
tableOrders.AddField("CustomerID");
tableOrders.AddField("ResellerID");
// create another table
MetadataObject tableCustomers = schema.AddTable("Customers");
tableCustomers.AddField("CustomerID");
tableCustomers.AddField("CustomerName");
tableCustomers.AddField("CustomerAddress");
MetadataField fieldCustValue = tableCustomers.AddField("CustomerValue");
fieldCustValue.FieldType = System.Data.DbType.Double;
MetadataField fieldCustBirthDate = tableCustomers.AddField("CustomerBirthDay");
fieldCustBirthDate.FieldType = System.Data.DbType.DateTime;
MetadataField fieldCustCity = tableCustomers.AddField("City");
fieldCustCity.FieldType = System.Data.DbType.String;
fieldCustCity.Size = 50;
// add a relation between these two tables
MetadataForeignKey relation = tableCustomers.AddForeignKey("FK_CustomerID");
relation.Fields.Add("CustomerID");
relation.ReferencedObjectName = tableOrders.GetQualifiedName();
relation.ReferencedFields.Add("CustomerID");
// create another table
MetadataObject salesOrderHeader = schema.AddTable("SalesOrderHeader");
salesOrderHeader.AddField("SalesOrderID");
salesOrderHeader.AddField("OrderDate");
// create another table
MetadataObject salesOrderDetail = schema.AddTable("SalesOrderDetail");
salesOrderDetail.AddField("SalesOrderID");
salesOrderDetail.AddField("UnitPrice");
//create a view
MetadataObject viewResellers = schema.AddView("Resellers");
viewResellers.AddField("ResellerID");
viewResellers.AddField("ResellerName");
}