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