public static SqlBuilder ToSqlBuilder(this MetadataColumn ForeignKeyColumn) { MetadataForeignKey FK = ForeignKeyColumn.Parent.FindForeignKeys(ForeignKeyColumn).First(); MetadataTable PK = SqlBuilder.DefaultMetadata.FindTable(FK.ReferencedSchema + "." + FK.ReferencedTable); string namecol = PK.GuessTitleColumn(); string[] valuecols = FK.ColumnReferences.Where(x => !x.Column.IsComputed).Select(x => x.ReferencedColumn.Name).ToArray(); SqlBuilder Builder = SqlBuilder.Select() .From(PK.Name, null, PK.Schema) .Column(namecol) .Columns(valuecols) .Builder(); List <MetadataColumnReference> mcrs = FK.ColumnReferences.Where(x => x.Column.IsComputed).ToList(); if (mcrs.Count > 0) { MetadataColumnReference first = mcrs.First(); Builder.From(PK.Name, null, PK.Schema) .Where(PK.Name, first.ReferencedColumn.Name, SqlOperators.Equal, (object)first.Column.Name.Trim('\"')) .Builder(); foreach (MetadataColumnReference mcr in mcrs.Skip(1)) { Builder.From(PK.Name, null, PK.Schema) .Where(PK.Name, mcr.ReferencedColumn.Name, SqlOperators.Equal, (object)mcr.Column.Name.Trim('\"')) .Builder(); } } return(Builder); }
// // LinkCreating event allows to prevent link creation // private void QBuilder_LinkCreating(DataSource fromDataSource, MetadataField fromField, DataSource toDataSource, MetadataField toField, MetadataForeignKey correspondingMetadataForeignKey, ref bool abort) { if (!CbLinkCreating.Checked) { return; } var fromFieldText = fromField == null ? fromDataSource.NameInQuery + ".*" : fromDataSource.MetadataObject.Name + "." + fromField.Name; var toFieldText = fromField == null ? toDataSource.NameInQuery + ".*" : toDataSource.MetadataObject.Name + "." + toField.Name; var value = $"LinkCreating. Creating the link between {fromFieldText} and {toFieldText}"; AddRowToReport(value); var msg = $"Do you want to create the link between {fromFieldText} and {toFieldText}?"; if (MessageBox.Show(msg, "LinkCreating event handler", MessageBoxButtons.YesNo) == DialogResult.No) { abort = true; } }
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"); }
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(); }
private static Table JoinInternal(MetadataHelper helper, string ForeignKeyField, Join.JoinTypes JoinType) { MetadataColumn FromField = null; if (!helper.Model.Columns.TryGetValue(ForeignKeyField, out FromField)) { throw new ArgumentException("The Field " + ForeignKeyField + " was not found", "FromField"); } List <MetadataForeignKey> Fks = new List <MetadataForeignKey>(helper.Model.FindForeignKeys(FromField)); if (Fks.Count != 1) { throw new ArgumentException("The Field " + ForeignKeyField + " points to more than one table", "FromField"); } MetadataForeignKey FK = Fks.First(); string table = FK.ReferencedSchema + "." + FK.ReferencedTable; MetadataTable ToTable = helper.Table.Builder.Metadata.FindTable(table); if (ToTable == null) { throw new InvalidOperationException("The table '" + table + "' was not found in metadata"); } JoinConditionGroup jcg = To(helper.Table.Builder, helper.Table, helper.Model, FromField, ToTable, JoinType, true); Table toTable = jcg.ToTable(); if (FromField.IncludeColumns != null) { foreach (string include in FromField.IncludeColumns) { string iName = include; string iAlias = null; if (include.IndexOf('=') > 0) { iName = include.Split('=')[0]; iAlias = include.Split('=')[1]; } else { iAlias = FromField.Name + "_" + iName; } toTable.Column(iName, iAlias); } } return(toTable); }
private static void CreateColumn(MetadataColumn mc, MetadataTable mt, StringBuilder sb) { string format = "\t\tpublic {0}{1}{2} {3}{4}\r\n\r\n"; string FKformat = "\t\t[FK(\"{0}\",\"{1}\",\"{2}\",\"{3}\")]\r\n"; if (mc.IsForeignKey) { List <MetadataForeignKey> FKs = new List <MetadataForeignKey>(); try { IEnumerable <MetadataForeignKey> fks = mt.FindForeignKeys(mc); FKs.AddRange(fks); } catch (Exception ex) { throw ex; } if (FKs.Count == 0) { throw new InvalidOperationException(string.Format("The foreign key for Column {0} in table {1} cannot be resolved to one key", mc.Name, mt.Fullname)); } MetadataForeignKey FK = FKs.First(); sb.AppendFormat(FKformat, FK.ReferencedTable, FK.ColumnReferences.First().ReferencedColumn.Name, FK.ReferencedSchema, FK.Name); } if (mc.IsPrimaryKey) { sb.AppendLine("\t\t[PK]"); } bool nullable = mc.Nullable && (mc.DataType != typeof(string) && !mc.DataType.IsByRef && !mc.DataType.IsArray); if (ClassCreationOptions.ColumnAsProperty) { sb.AppendFormat(format, (nullable ? "Nullable<" : ""), mc.DataType.Name, nullable ? "> " : " ", mc.Name, " { get; set; }"); } else { sb.AppendFormat(format, nullable ? "Nullable<" : "", mc.DataType.Name, nullable ? "> " : " ", mc.Name, ";"); } }
/// <summary> /// Checking the feasibility of creating a link between the given fields. /// </summary> private void QBuilder_LinkDragOver(DataSource fromDataSource, MetadataField fromField, DataSource toDataSource, MetadataField toField, MetadataForeignKey correspondingMetadataForeignKey, ref bool abort) { if (CheckBoxLinkDragOver.Checked != true) { return; } // Allow creation of links between fields of the same data type. if (fromField.FieldType == toField.FieldType) { TextBoxReport.Text = "OnLinkDragOver from field \"" + fromField.Name + "\" to field \"" + toField.Name + "\" allow" + Environment.NewLine + TextBoxReport.Text; return; } TextBoxReport.Text = "OnLinkDragOver from field \"" + fromField.Name + "\" to field \"" + toField.Name + "\" deny" + Environment.NewLine + TextBoxReport.Text; abort = true; }
// 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"); }
private void BuildVirtualKeys(List<VirtualForeignKey> keys, MetadataTable mt, MetadataDatabase mdb, bool PrimaryKeyIndexOnly) { foreach (VirtualForeignKey vfk in keys) { MetadataForeignKey mfk = new MetadataForeignKey() { ID = 0, Name = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[0], ReferencedKey = "", ReferencedTable = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[1], ReferencedSchema = vfk.values[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[2], Parent = mt, IsVirtual = true }; MetadataTable mtref = null; if (!mdb.Tables.TryGetValue(mfk.ReferencedSchema + "." + mfk.ReferencedTable, out mtref)) { bool self = false; if (mfk.ReferencedSchema == mt.Schema && mfk.ReferencedTable == mt.Name) { self = true; } mtref = BuildMetadata(mdb, mfk.ReferencedTable, mfk.ReferencedSchema, PrimaryKeyIndexOnly, self); } for (int i = 1; i < vfk.values.Length; i++) { MetadataColumnReference mcf = new MetadataColumnReference() { ReferencedColumn = mtref[vfk.values[i].Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[1]], }; string from = vfk.values[i].Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[0]; if (from.StartsWith("\"")) { MetadataColumn mcVirtual = new MetadataColumn() { Name = from, IsForeignKey = true, ID = 0, SqlDataType = SqlDbType.NVarChar, Nullable = false, Length = 0, IsComputed = true, DataType = typeof(string), }; mcf.Column = mcVirtual; mcf.Name = from; } else { mcf.Column = mt[from]; mcf.Name = mcf.Column.Name; } mfk.ColumnReferences.Add(mcf); } mt.ForeignKeys.AddOrUpdate(mfk.Name, mfk, (k, v) => { return mfk; }); } }
private MetadataTable BuildMetadata(MetadataDatabase mdb, Microsoft.SqlServer.Management.Smo.Table table, bool PrimaryKeyIndexOnly = true, bool SelfJoin = false) { MetadataTable mt = null; List<VirtualForeignKey> VirtualKeys = new List<VirtualForeignKey>(); table.Refresh(); if (mdb.Tables.TryGetValue(table.Name, out mt)) { return mt; } mt = new MetadataTable() { ID = table.ID, Schema = table.Schema, Name = table.Name, //Parent = mdb }; mt.TitleColumn = GetExtendedProperty("TitleColumn", table.ExtendedProperties); string[] values = GetExtendedProperty("DisplayName", table.ExtendedProperties, new char[] { '\r', '\n' }); if (values != null) { foreach (string value in values) { string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries); mt.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]); } } values = GetExtendedProperty("Lists", table.ExtendedProperties, new char[] { '\r', '\n' }); if (values != null) { foreach (string value in values) { string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries); List<string> v2 = v[1].Split(',').ToList(); if (!mt.ListDefinitions.TryAdd(v[0].Trim(), v2)) { throw new InvalidOperationException(string.Format("The TinySql.Lists extended property is invalid for the table '{0}'", table.Name)); } } } foreach (Microsoft.SqlServer.Management.Smo.Column column in table.Columns) { try { MetadataColumn col = new MetadataColumn() { ID = column.ID, Parent = mt, //Database = mdb, Name = column.Name, Collation = column.Collation, Default = column.Default, IsComputed = column.Computed, ComputedText = column.ComputedText, IsPrimaryKey = column.InPrimaryKey, IsIdentity = column.Identity, IsForeignKey = column.IsForeignKey, IdentityIncrement = column.IdentityIncrement, IdentitySeed = column.IdentitySeed, Nullable = column.Nullable, IsRowGuid = column.RowGuidCol }; BuildColumnDataType(col, column); values = GetExtendedProperty("DisplayName", column.ExtendedProperties, new char[] { '\r', '\n' }); if (values != null) { foreach (string value in values) { if (!value.Contains("=")) { col.DisplayNames.TryAdd(SqlBuilder.DefaultCulture.LCID, value); } else { string[] v = value.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries); col.DisplayNames.TryAdd(Convert.ToInt32(v[0]), v[1]); } } } col.IncludeColumns = GetExtendedProperty("IncludeColumns", column.ExtendedProperties, new char[] { ',' }); values = GetExtendedProperty("FK", column.ExtendedProperties, new char[] { '\r', '\n' }); if (values != null) { VirtualKeys.Add(new VirtualForeignKey() { Column = col, values = values }); col.IsForeignKey = true; } mt.Columns.AddOrUpdate(col.Name, col, (k, v) => { return col; }); } catch (Exception exColumn) { throw new InvalidOperationException(string.Format("Unable to generate the column {0}", column.Name), exColumn); } } foreach (Index idx in table.Indexes) { if (!PrimaryKeyIndexOnly || idx.IndexKeyType == IndexKeyType.DriPrimaryKey) { Key key = new Key() { ID = idx.ID, Parent = mt, Database = mdb, Name = idx.Name, IsUnique = idx.IsUnique, IsPrimaryKey = idx.IndexKeyType == IndexKeyType.DriPrimaryKey }; foreach (IndexedColumn c in idx.IndexedColumns) { key.Columns.Add(mt[c.Name]); } mt.Indexes.AddOrUpdate(key.Name, key, (k, v) => { return key; }); } } if (!SelfJoin) { foreach (ForeignKey FK in table.ForeignKeys) { MetadataForeignKey mfk = new MetadataForeignKey() { ID = FK.ID, Parent = mt, Database = mdb, Name = FK.Name, ReferencedKey = FK.ReferencedKey, ReferencedSchema = FK.ReferencedTableSchema, ReferencedTable = FK.ReferencedTable }; MetadataTable mtref = null; if (!mdb.Tables.TryGetValue(mfk.ReferencedSchema + "." + mfk.ReferencedTable, out mtref)) { bool self = false; if ((mfk.ReferencedSchema == mt.Schema && mfk.ReferencedTable == mt.Name) || TablesInProgress.Contains(mfk.ReferencedSchema + "." + mfk.ReferencedTable)) { self = true; } TablesInProgress.Add(mfk.ReferencedSchema + "." + mfk.ReferencedTable); mtref = BuildMetadata(mdb, mfk.ReferencedTable, mfk.ReferencedSchema, PrimaryKeyIndexOnly, self); } foreach (ForeignKeyColumn cc in FK.Columns) { mfk.ColumnReferences.Add(new MetadataColumnReference() { Name = cc.Name, Column = mt[cc.Name], ReferencedColumn = mtref[cc.ReferencedColumn] }); } mt.ForeignKeys.AddOrUpdate(mfk.Name, mfk, (key, existing) => { return mfk; }); } } if (VirtualKeys.Count > 0) { BuildVirtualKeys(VirtualKeys, mt, mdb, PrimaryKeyIndexOnly); } mdb.Tables.AddOrUpdate(mt.Schema + "." + mt.Name, mt, (key, existing) => { return mt; }); return mt; }
////////////////////////////////////////////////////////////////////////// /// 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"); }
// // LinkCreating event allows to prevent link creation // private void QBuilder_OnLinkCreating(DataSource fromDatasource, MetadataField fromField, DataSource toDatasource, MetadataField toField, MetadataForeignKey correspondingmetadataforeignkey, ref bool abort) { if (CbLinkCreating.IsChecked != true) { return; } if (fromField == null || toField == null) { return; } BoxLogEvents.Text = "LinkCreating" + Environment.NewLine + BoxLogEvents.Text; var msg = string.Format("Do you want to create the link between {0}.{1} and {2}.{3}?", fromDatasource.MetadataObject.Name, fromField.Name, toDatasource.MetadataObject.Name, toField.Name); if (MessageBox.Show(msg, "LinkCreating event handler", MessageBoxButton.YesNo) == MessageBoxResult.No) { abort = true; } }
////////////////////////////////////////////////////////////////////////// /// 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(); }
private static JoinConditionGroup To(SqlBuilder Builder, Table FromSqlTable, MetadataTable FromTable, MetadataColumn FromField, MetadataTable ToTable, Join.JoinTypes JoinType, bool PreferForeignKeyOverPrimaryKey = true) { MetadataDatabase mdb = Builder.Metadata; List <MetadataForeignKey> Fks = null; MetadataForeignKey FK = null; Join j = null; MetadataColumnReference mcr = null; JoinConditionGroup jcg = null; if (FromField.IsPrimaryKey) { if (!FromField.IsForeignKey || !PreferForeignKeyOverPrimaryKey) { Fks = ToTable.ForeignKeys.Values.Where(x => x.ReferencedTable.Equals(FromTable.Name) && x.ReferencedSchema.Equals(FromTable.Schema) && x.ColumnReferences.Any(y => y.ReferencedColumn.Equals(FromField))).ToList(); if (Fks.Count != 1) { throw new InvalidOperationException(string.Format("The column '{0}' is referenced by {1} keys in the table {2}. Expected 1. Make the join manually", FromField.Name, Fks.Count, ToTable.Fullname)); } FK = Fks.First(); j = SqlStatementExtensions.MakeJoin(JoinType, FromSqlTable, ToTable.Name, null, ToTable.Schema.Equals("dbo") ? null : ToTable.Schema); mcr = FK.ColumnReferences.First(); jcg = j.On(FromField.Name, SqlOperators.Equal, mcr.Name); return(jcg); } } if (FromField.IsForeignKey) { Fks = new List <MetadataForeignKey>(FromTable.FindForeignKeys(FromField, ToTable.Name)); if (Fks.Count != 1) { throw new InvalidOperationException(string.Format("The column '{0}' resolves to {1} keys in the table {2}. Expected 1. Make the join manually", FromField.Name, Fks.Count, ToTable.Fullname)); } FK = Fks.First(); j = SqlStatementExtensions.MakeJoin(JoinType, FromSqlTable, ToTable.Name, null, ToTable.Schema.Equals("dbo") ? null : ToTable.Schema); mcr = FK.ColumnReferences.First(); jcg = j.On(FromField.Name, SqlOperators.Equal, mcr.ReferencedColumn.Name); if (FK.ColumnReferences.Count > 1) { foreach (MetadataColumnReference mcr2 in FK.ColumnReferences.Skip(1)) { if (mcr2.Name.StartsWith("\"")) { // its a value reference // jcg.And(FK.ReferencedTable, mcr2.ReferencedColumn.Name, SqlOperators.Equal, mcr2.Name.Trim('\"'),null); decimal d; object o; if (decimal.TryParse(mcr2.Name.Trim('\"'), out d)) { o = d; } else { o = (object)mcr2.Name.Trim('\"'); } jcg.And(mcr2.ReferencedColumn.Name, SqlOperators.Equal, o, null); } else { jcg.And(mcr2.Column.Name, SqlOperators.Equal, mcr2.ReferencedColumn.Name); } } } return(jcg); } throw new ArgumentException(string.Format("The Column '{0}' in the table '{1}' must be a foreign key or primary key", FromField.Name, FromTable.Fullname), "FromField"); }