public void Refresh()
{
if (_source == null || !DynamicColumns)
{
return;
}
try
{
_information = "";
_error = "";
MustRefresh = true;
//Build table def from SQL or table name
DataTable defTable = GetDefinitionTable(IsForSQLModel ? Sql : string.Format("SELECT * FROM {0} WHERE 1=0", FullSQLName));
foreach (DataColumn column in defTable.Columns)
{
string fullColumnName = (IsSQL && !IsForSQLModel ? Source.GetTableName(AliasName) + "." : "") + Source.GetColumnName(column.ColumnName);
MetaColumn newColumn = Columns.FirstOrDefault(i => i.Name == fullColumnName);
ColumnType type = Helper.NetTypeConverter(column.DataType);
if (newColumn == null)
{
newColumn = MetaColumn.Create(fullColumnName);
newColumn.Source = _source;
newColumn.DisplayName = (KeepColumnNames ? column.ColumnName.Trim() : Helper.DBNameToDisplayName(column.ColumnName.Trim()));
newColumn.Category = (Alias == MetaData.MasterTableName ? "Master" : AliasName);
newColumn.DisplayOrder = GetLastDisplayOrder();
Columns.Add(newColumn);
newColumn.Type = type;
newColumn.SetStandardFormat();
}
newColumn.Source = _source;
if (type != newColumn.Type)
{
newColumn.Type = type;
newColumn.SetStandardFormat();
}
}
//Clear columns for No SQL or SQL Model
if (!IsSQL || IsForSQLModel)
{
Columns.RemoveAll(i => !defTable.Columns.Contains(i.Name));
}
MustRefresh = false;
_information = "Dynamic columns have been refreshed successfully";
}
catch (Exception ex)
{
_error = ex.Message;
_information = "Error got when refreshing dynamic columns.";
}
_information = Helper.FormatMessage(_information);
UpdateEditorAttributes();
}
/// <summary>
/// Refresh the dynamic columns
/// </summary>
public void Refresh()
{
if (_source == null || !DynamicColumns)
{
return;
}
try
{
Information = "";
Error = "";
MustRefresh = true;
//Build table def from SQL or table name
var sql = "";
if (IsForSQLModel)
{
if (Model.UseRawSQL)
{
sql = Sql;
}
else
{
sql = string.Format("SELECT * FROM ({0}) a WHERE 1=0", Sql);
}
}
else
{
string CTE = "", name = "";
GetExecSQLName(ref CTE, ref name);
sql = string.Format("{0}SELECT * FROM {1} WHERE 1=0", CTE, name);
}
DataTable defTable = GetDefinitionTable(sql);
int position = 1;
foreach (DataColumn column in defTable.Columns)
{
string fullColumnName = (IsSQL && !IsForSQLModel ? Source.GetTableName(AliasName) + "." : "") + Source.GetColumnName(column.ColumnName);
MetaColumn newColumn = Columns.FirstOrDefault(i => i.Name.ToLower() == fullColumnName.ToLower());
column.ColumnName = fullColumnName; //Set it here to clear the columns later
ColumnType type = Helper.NetTypeConverter(column.DataType);
if (newColumn == null)
{
newColumn = MetaColumn.Create(fullColumnName);
newColumn.Source = _source;
newColumn.DisplayName = (KeepColumnNames ? column.ColumnName.Trim() : Helper.DBNameToDisplayName(column.ColumnName.Trim()));
newColumn.Category = (Alias == MetaData.MasterTableName ? "Master" : AliasName);
newColumn.DisplayOrder = GetLastDisplayOrder();
Columns.Add(newColumn);
newColumn.Type = type;
newColumn.SetStandardFormat();
}
newColumn.Source = _source;
if (type != newColumn.Type)
{
newColumn.Type = type;
newColumn.SetStandardFormat();
}
newColumn.DisplayOrder = position++;
}
//Clear columns for No SQL or SQL Model
if (!IsSQL || IsForSQLModel)
{
Columns.RemoveAll(i => !defTable.Columns.Contains(i.Name));
}
MustRefresh = false;
Information = "Dynamic columns have been refreshed successfully";
}
catch (Exception ex)
{
Error = ex.Message;
Information = "Error got when refreshing dynamic columns.";
}
Information = Helper.FormatMessage(Information);
}
void addSchemaTables(DataTable schemaTables, List<MetaTable> tables, MetaSource source)
{
//Helper.DisplayDataTable(schemaTables);
foreach (DataRow row in schemaTables.Rows)
{
//if (row["TABLE_TYPE"].ToString() == "SYSTEM TABLE" || row["TABLE_TYPE"].ToString() == "SYSTEM VIEW") continue;
MetaTable table = MetaTable.Create();
string schema = "";
if (schemaTables.Columns.Contains("TABLE_SCHEMA")) schema = row["TABLE_SCHEMA"].ToString();
else if (schemaTables.Columns.Contains("TABLE_SCHEM")) schema = row["TABLE_SCHEM"].ToString();
table.Name = (!string.IsNullOrEmpty(schema) ? source.GetTableName(schema) + "." : "") + source.GetTableName(row["TABLE_NAME"].ToString());
table.Type = row["TABLE_TYPE"].ToString();
table.Source = source;
tables.Add(table);
}
}
List<MetaJoin> GetJoins(DbConnection connection, MetaSource source)
{
List<MetaJoin> joins = new List<MetaJoin>();
if (!(connection is OleDbConnection)) return joins;
DataTable schemaTables = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
foreach (DataRow row in schemaTables.Rows)
{
string table1Name = source.GetTableName(row["PK_TABLE_NAME"].ToString());
string table2Name = source.GetTableName(row["FK_TABLE_NAME"].ToString());
MetaTable table1 = source.MetaData.Tables.FirstOrDefault(i => i.Name == source.GetTableName(table1Name));
MetaTable table2 = source.MetaData.Tables.FirstOrDefault(i => i.Name == source.GetTableName(table2Name));
if (table1 == null)
{
string pkschema = "";
if (schemaTables.Columns.Contains("PK_TABLE_SCHEMA")) pkschema = row["PK_TABLE_SCHEMA"].ToString();
else if (schemaTables.Columns.Contains("PK_TABLE_SCHEM")) pkschema = row["PK_TABLE_SCHEM"].ToString();
if (!string.IsNullOrEmpty(pkschema)) table1 = source.MetaData.Tables.FirstOrDefault(i => i.Name == pkschema + "." + table1Name);
}
if (table2 == null)
{
string fkschema = "";
if (schemaTables.Columns.Contains("FK_TABLE_SCHEMA")) fkschema = row["FK_TABLE_SCHEMA"].ToString();
else if (schemaTables.Columns.Contains("FK_TABLE_SCHEM")) fkschema = row["FK_TABLE_SCHEM"].ToString();
if (!string.IsNullOrEmpty(fkschema)) table2 = source.MetaData.Tables.FirstOrDefault(i => i.Name == fkschema + "." + table2Name);
}
if (table1 != null && table2 != null && table1.Name != table2.Name && !source.MetaData.Joins.Exists(i => i.LeftTableGUID == table1.GUID && i.RightTableGUID == table2.GUID))
{
MetaJoin join = joins.FirstOrDefault(i => i.LeftTableGUID == table1.GUID && i.RightTableGUID == table2.GUID);
if (join == null)
{
join = MetaJoin.Create();
join.Name = table1.Name + " - " + table2.Name;
join.LeftTableGUID = table1.GUID;
join.RightTableGUID = table2.GUID;
join.Source = source;
joins.Add(join);
}
if (!string.IsNullOrEmpty(join.Clause)) join.Clause += " AND ";
join.Clause += string.Format("{0}.{1} = {2}.{3}\r\n", table1.Name, source.GetColumnName(row["PK_COLUMN_NAME"].ToString()), table2.Name, source.GetColumnName(row["FK_COLUMN_NAME"].ToString()));
join.JoinType = JoinType.Inner;
}
}
return joins;
}