private void way2ItemMetadataLoading(object sender, MetadataItem item, MetadataType types)
{
switch (item.Type)
{
case MetadataType.Root:
if (types.Contains(MetadataType.Schema))
{
// only one "dbo" schema should be at the root level
if (item.Items.FindItem <MetadataNamespace>("dbo", MetadataType.Schema) == null)
{
item.AddSchema("dbo");
}
}
break;
case MetadataType.Schema:
if (item.Name == "dbo" && types.Contains(MetadataType.Table))
{
item.AddTable("Orders");
item.AddTable("Order Details");
}
break;
case MetadataType.Table:
if (item.Name == "Orders")
{
if (types.Contains(MetadataType.Field))
{
item.AddField("OrderId");
item.AddField("CustomerId");
}
}
else if (item.Name == "Order Details")
{
if (types.Contains(MetadataType.Field))
{
item.AddField("OrderId");
item.AddField("ProductId");
}
if (types.Contains(MetadataType.ForeignKey))
{
MetadataForeignKey foreignKey = item.AddForeignKey("OrderDetailsToOrder");
foreignKey.Fields.Add("OrderId");
foreignKey.ReferencedFields.Add("OrderId");
using (MetadataQualifiedName name = new MetadataQualifiedName())
{
name.Add("Orders");
name.Add("dbo");
foreignKey.ReferencedObjectName = name;
}
}
}
break;
}
item.Items.SetLoaded(types, true);
}
private void way2ItemMetadataLoading(object sender, MetadataItem item, MetadataType types)
{
switch (item.Type)
{
case MetadataType.Root:
if ((types & MetadataType.Schema) > 0)
{
item.AddSchema("dbo");
}
break;
case MetadataType.Schema:
if ((item.Name == "dbo") && (types & MetadataType.Table) > 0)
{
item.AddTable("Orders");
item.AddTable("Order Details");
}
break;
case MetadataType.Table:
if (item.Name == "Orders")
{
if ((types & MetadataType.Field) > 0)
{
item.AddField("OrderId");
item.AddField("CustomerId");
}
}
else if (item.Name == "Order Details")
{
if ((types & MetadataType.Field) > 0)
{
item.AddField("OrderId");
item.AddField("ProductId");
}
if ((types & MetadataType.ForeignKey) > 0)
{
MetadataForeignKey foreignKey = item.AddForeignKey("OrderDetailsToOrder");
foreignKey.Fields.Add("OrderId");
foreignKey.ReferencedFields.Add("OrderId");
using (MetadataQualifiedName name = new MetadataQualifiedName())
{
name.Add("Orders");
name.Add("dbo");
foreignKey.ReferencedObjectName = name;
}
}
}
break;
}
item.Items.SetLoaded(types, true);
}
//////////////////////////////////////////////////////////////////////////
/// 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");
}
//////////////////////////////////////////////////////////////////////////
/// 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();
}
public override bool GetReferencingObjectNames(UpdatableObjectList <MetadataQualifiedName> list, MetadataObject forObject, MetadataLoadingOptions loadingOptions)
{
if (forObject.Server == null)
{
MetadataNamespace database = forObject.Database;
MetadataNamespace schema = forObject.Schema;
string[] restrictions = new string[6];
restrictions[0] = database != null ? database.Name : null;
restrictions[1] = schema != null ? schema.Name : null;
restrictions[2] = forObject.Name;
using (DataTable foreignKeys = _connection.GetSchema("ForeignKeys", restrictions))
{
int databaseField = foreignKeys.Columns.IndexOf("FKTABLE_CAT");
int schemaField = foreignKeys.Columns.IndexOf("FKTABLE_SCHEM");
int nameField = foreignKeys.Columns.IndexOf("FKTABLE_NAME");
foreach (DataRow row in foreignKeys.Rows)
{
object referencingName = nameField != -1 ? row[nameField] : null;
object referencingSchema = schemaField != -1 ? row[schemaField] : null;
object referencingDatabase = databaseField != -1 ? row[databaseField] : null;
MetadataQualifiedName name = new MetadataQualifiedName();
if (referencingName != null)
{
name.Add(referencingName.ToString(), MetadataType.Objects);
}
if (referencingSchema != null)
{
name.Add(referencingSchema.ToString(), MetadataType.Schema);
}
if (referencingDatabase != null)
{
name.Add(referencingDatabase.ToString(), MetadataType.Schema);
}
list.Add(name);
}
return(true);
}
}
return(false);
}
//////////////////////////////////////////////////////////////////////////
/// 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();
}
