public static SqlBuilder ToSqlBuilder(this MetadataTable table, string ListName)
{
List <string> columns = new List <string>(table.PrimaryKey.Columns.Select(x => x.Name));
List <string> columnDef = null;
List <MetadataColumn> mcs = new List <MetadataColumn>();
if (table.ListDefinitions.TryGetValue(ListName, out columnDef))
{
mcs = table.Columns.Values.Where(x => columnDef.Contains(x.Name)).ToList();
}
else
{
mcs = table.Columns.Values.Where(x => x.IsRowGuid == false && x.IsPrimaryKey == false).ToList();
}
columns.AddRange(mcs.Select(x => x.Name));
SqlBuilder Builder = SqlBuilder.Select()
.From(table.Name, null, table.Schema).Builder();
foreach (MetadataColumn mc in mcs.Where(x => x.IsForeignKey))
{
Builder.BaseTable().WithMetadata().AutoJoin(mc.Name);
}
Builder.From(table.Name, null, table.Schema).Columns(columns.ToArray());
return(Builder);
}
private static ResultTable ExecuteRelatedInternal(SqlBuilder builder, Dictionary <string, RowData> results)
{
if (results.Count > 0)
{
MetadataTable mt = builder.BaseTable().WithMetadata().Model;
foreach (string key in results.Keys)
{
foreach (MetadataForeignKey fk in mt.ForeignKeys.Values.Where(x => (x.ReferencedSchema + "." + x.ReferencedTable).Equals(key, StringComparison.OrdinalIgnoreCase)))
{
RowData row = results[key];
foreach (MetadataColumnReference mcr in fk.ColumnReferences)
{
if (row.Columns.Contains(mcr.Column.Name))
{
Field f = builder.BaseTable().FindField(mcr.Column.Name);
if (f != null)
{
f.Value = row.Column(mcr.Column.Name);
}
else
{
(builder.BaseTable() as InsertIntoTable).Value(mcr.Column.Name, row.Column(mcr.Column.Name), SqlDbType.VarChar);
}
}
}
}
}
}
DataTable dt = new DataTable();
ResultTable table = new ResultTable();
using (SqlConnection context = new SqlConnection(builder.ConnectionString))
{
context.Open();
SqlCommand cmd = new SqlCommand(builder.ToSql(), context);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.AcceptChangesDuringFill = false;
adapter.Fill(dt);
context.Close();
}
if (builder.SubQueries.Count > 0)
{
Dictionary <string, RowData> subresults = new System.Collections.Generic.Dictionary <string, RowData>(results);
if (dt.Rows.Count > 0)
{
MetadataTable mt = builder.BaseTable().WithMetadata().Model;
if (!subresults.ContainsKey(mt.Fullname))
{
ResultTable rt = new ResultTable(dt, ResultTable.DateHandlingEnum.None);
RowData row = rt.First();
table.Add(row);
subresults.Add(mt.Fullname, row);
}
}
foreach (SqlBuilder Builder in builder.SubQueries.Values)
{
ResultTable sub = ExecuteRelatedInternal(Builder, subresults);
foreach (RowData row in sub)
{
table.Add(row);
}
}
}
return(table);
}