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