//////////////////////////////////////////////////////////////////////////
/// 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();
}
} //end method
private void LoadSQLServerCE35SchemaTree()
{
queryBuilder.MetadataContainer.BeginUpdate();
PFDataAccessObjects.PFDatabase db = null;
string sql = string.Empty;
try
{
queryBuilder.MetadataContainer.Items.Clear();
//get list of tables and add them to the schema tree
db = new PFDataAccessObjects.PFDatabase(PFDataAccessObjects.DatabasePlatform.SQLServerCE35);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES";
DataTable datatableList = db.RunQueryDataTable(sql, CommandType.Text);
foreach (DataRow row in datatableList.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
MetadataObject table = queryBuilder.MetadataContainer.AddTable(tableName);
sql = "select * from " + tableName + " where 0=1";
Console.WriteLine(sql);
DataTable columnList = db.RunQueryDataTable(sql, CommandType.Text);
foreach (DataColumn col in columnList.Columns)
{
MetadataField metadataField = table.AddField(col.ColumnName);
// setup field
metadataField.FieldType = TypeToDbType(col.DataType);
metadataField.Nullable = col.AllowDBNull;
metadataField.ReadOnly = col.ReadOnly;
if (col.MaxLength != -1)
{
metadataField.Size = col.MaxLength;
}
// detect the field is primary key
foreach (DataColumn pkColumn in columnList.PrimaryKey)
{
if (col == pkColumn)
{
metadataField.PrimaryKey = true;
}
}
}
//close and reopen connection to free up space used by temp tables in the .NET provider
db.CloseConnection();
db.OpenConnection();
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Attempt to load SQLCE 3.5 Schema Tree failed.");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
db.Connection.Close();
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
queryBuilder.MetadataContainer.EndUpdate();
}
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
queryBuilder.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();
}
} //end method
private void LoadUltraLiteSchemaTree()
{
queryBuilder.MetadataContainer.BeginUpdate();
MetadataItem schemaRoot = null;
PFDataAccessObjects.PFDatabase db = null;
try
{
queryBuilder.MetadataContainer.Items.Clear();
queryBuilder.InitializeDatabaseSchemaTree();
if (queryBuilder.MetadataContainer.Items.Count == 1)
{
schemaRoot = queryBuilder.MetadataContainer.Items[0];
}
else
{
schemaRoot = queryBuilder.MetadataContainer.AddDatabase("UltraLiteDb");
}
//MetadataObject table = queryBuilder.MetadataContainer.AddTable("TestTable");
//MetadataObject table = schemaRoot.AddTable("TestTable");
//get list of tables and add them to the schema tree
db = new PFDataAccessObjects.PFDatabase(PFDataAccessObjects.DatabasePlatform.SQLAnywhereUltraLite);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
DataTable datatableList = db.Connection.GetSchema(ULMetaDataCollectionNames.Tables);
foreach (DataRow row in datatableList.Rows)
{
string tableName = row["table_name"].ToString();
MetadataObject table = schemaRoot.AddTable(tableName);
string sql = "select * from " + tableName + " where 0=1";
Console.WriteLine(sql);
DataTable columnList = db.RunQueryDataTable(sql, CommandType.Text);
foreach (DataColumn col in columnList.Columns)
{
MetadataField metadataField = table.AddField(col.ColumnName);
// setup field
metadataField.FieldType = TypeToDbType(col.DataType);
metadataField.Nullable = col.AllowDBNull;
metadataField.ReadOnly = col.ReadOnly;
if (col.MaxLength != -1)
{
metadataField.Size = col.MaxLength;
}
// detect the field is primary key
foreach (DataColumn pkColumn in columnList.PrimaryKey)
{
if (col == pkColumn)
{
metadataField.PrimaryKey = true;
}
}
}
//close and reopen connection to free up space used by temp tables in the UL .NET provider
db.CloseConnection();
db.OpenConnection();
}
//MetadataField field = table.AddField("Fld1");
//field.FieldTypeName = "nvarchar";
//field.Size = 30;
//field = table.AddField("Fld2");
//field.FieldTypeName = "int";
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Attempt to load UltraLite Schema Tree failed.");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
db.Connection.Close();
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
queryBuilder.MetadataContainer.EndUpdate();
}
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 1;
queryBuilder.InitializeDatabaseSchemaTree();
}
// 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");
}