public static async Task <DatatableEx> Query(this CTable table,
string[] operators, string[] andOrs,
bool asStar = false, params IDbDataParameter[] pars)
{
var result = new DatatableEx(new TableContainer(new DataTable(table.name), table));
var query = table.ToSelectWhereString(operators, andOrs, asStar, pars);
Common.Extensions.TraceLog.Information("Running query {query} for parent table {name}", query, table.name);
try
{
result.Root.Table = await DataSourceProvider
.Data
.Source
.Load(SelectedDatasource.ConnectionString, query);
if (result.Root.Table.Rows.Count > 0)
{
result.QueryChildren(result.Root.Table.Rows[0]);
}
}
catch (Exception e)
{
Common.Extensions.ErrorLog.Error(e, "@ Query(CTable) query: {query}, connectionString: {ConnectionString}", query, SelectedDatasource.ConnectionString.Trimmed());
}
return(result);
}
public static StringBuilder SqlInsert(this StringBuilder value, DatatableEx table, bool includeChildren = true)
{
if (null != table &&
table.Root.Table != null &&
table.Root.Table.Rows.Count > 0)
{
var hasIdentity = table.Root.ConfigTable.Column.Any(x => x.isIdentity);
if (hasIdentity)
{
value.AppendFormat("SET IDENTITY_INSERT [dbo].{0} ON{1}", table.Root.ConfigTable.name, Environment.NewLine);
}
value.SqlInsert(table.Root.Table);
if (hasIdentity)
{
value.AppendFormat("SET IDENTITY_INSERT [dbo].{0} OFF{1}", table.Root.ConfigTable.name, Environment.NewLine);
}
value.Append(Environment.NewLine);
if (includeChildren)
{
foreach (var child in table.Children)
{
value.SqlInsert(child, includeChildren);
}
}
}
return(value);
}
public static async Task <DatatableEx> Query(this CQuery query, params IDbDataParameter[] pars)
{
var result = new DatatableEx(new TableContainer(new DataTable(query.name), new CTable {
name = query.name
}));
var q = query.ToSelectWhereString(pars);
Common.Extensions.TraceLog.Information("Running query {query} for query {name}", q, query.name);
try
{
if (query.isStoreProcedure)
{
result.Root.Table = await DataSourceProvider
.Data
.Source
.LoadStoreProcedure(SelectedDatasource.ConnectionString, query, pars);
}
else
{
result.Root.Table = await DataSourceProvider
.Data
.Source
.Load(SelectedDatasource.ConnectionString, q);
}
}
catch (Exception e)
{
Common.Extensions.ErrorLog.Error(e, "@ Query(CQuery) query: {q}, connectionString: {ConnectionString}", q, SelectedDatasource.ConnectionString.Trimmed());
}
return(result);
}
public static StringBuilder ToJSon(this StringBuilder value, DatatableEx table, bool includeChidren = true)
{
if (null != table &&
table.Root.Table != null &&
table.Root.Table.Rows.Count > 0)
{
}
return(value);
}
public static async void QueryChildren(this DatatableEx parent, DataRow row)
{
var table = SelectedDataset.Table.First(t => t.name == parent.Root.Table.TableName);
var queries = table.RelatedTablesSelect(row);
if (!string.IsNullOrEmpty(queries))
{
foreach (var q in queries.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries))
{
var skipLoadingData = false;
var tableName = ParseTableName(q);
var childTable = SelectedDataset.Table.First(x => x.name == tableName);
var relationship = table.GetRelationShip(childTable);
var child = parent.Children.FirstOrDefault(x => x.Root.Table.TableName == tableName);
var appendChild = child == null;
if (null != relationship)
{
if (null != child)
{
var filter = string.Format("{0}={1}", relationship.toColumn, row.Value(relationship.fromColumn));
var rows = child.Root.Table.Select(filter);
child.Root.Table.DefaultView.RowFilter = filter;
skipLoadingData = rows != null && rows.Any();
}
}
if (!skipLoadingData)
{
Common.Extensions.TraceLog.Information("Running query {q} for child table {tableName}",
q, tableName);
var tbl = await GetDataTable(q);
if (null != tbl)
{
if (child == null)
{
child = new DatatableEx(new TableContainer(tbl, childTable));
}
else
{
Common.Extensions.TraceLog.Information("Appending data for children table {name}",
child.Root.ConfigTable.name);
tbl.CopyRows(child.Root.Table);
}
}
}
if (null != child)
{
if (child.Root.ConfigTable.Children.Count > 0 &&
child.Root.Table.Rows.Count > 0)
{
child.QueryChildren(child.Root.Table.Rows[0]);
}
if (appendChild && !parent.Children.Any(x => x.Root.Table.TableName == child.Root.Table.TableName))
{
//Dispatcher.CurrentDispatcher.Invoke(() =>
//{
parent.Children.Add(child);
//}, DispatcherPriority.SystemIdle);
}
}
}
}
}
