/// <summary>
/// Fill a list of columns from a table catalog
/// </summary>
public void AddColumnsFromCatalog(List <MetaColumn> columns, DbConnection connection, MetaTable table)
{
if (table.Name == null)
{
throw new Exception("No table name has been defined...");
}
//handle if table name = dbname.owner.tablename
string name = table.Name.Replace("[", "").Replace("]", "");
string[] names = name.Split('.');
DataTable schemaColumns = null;
Helper.ExecutePrePostSQL(connection, ReportModel.ClearCommonRestrictions(table.PreSQL), table, table.IgnorePrePostError);
if (names.Length == 3)
{
schemaColumns = connection.GetSchema("Columns", names);
}
else if (names.Length == 2)
{
schemaColumns = connection.GetSchema("Columns", new string[] { null, names[0], names[1] });
}
else
{
schemaColumns = connection.GetSchema("Columns", new string[] { null, null, name });
}
Helper.ExecutePrePostSQL(connection, ReportModel.ClearCommonRestrictions(table.PostSQL), table, table.IgnorePrePostError);
foreach (DataRow row in schemaColumns.Rows)
{
try
{
string tableName = (!string.IsNullOrEmpty(table.AliasName) ? table.AliasName : Helper.DBNameToDisplayName(row["TABLE_NAME"].ToString().Trim()));
MetaColumn column = MetaColumn.Create(tableName + "." + GetColumnName(row["COLUMN_NAME"].ToString()));
column.DisplayName = table.KeepColumnNames ? row["COLUMN_NAME"].ToString().Trim() : Helper.DBNameToDisplayName(row["COLUMN_NAME"].ToString().Trim());
column.DisplayOrder = table.GetLastDisplayOrder();
MetaColumn col = table.Columns.FirstOrDefault();
if (col != null)
{
column.Category = col.Category;
}
else
{
column.Category = !string.IsNullOrEmpty(table.AliasName) ? table.AliasName : Helper.DBNameToDisplayName(table.Name.Trim());
}
column.Source = this;
string dbType = "";
if (row.Table.Columns.Contains("TYPE_NAME"))
{
dbType = row["TYPE_NAME"].ToString();
}
if (connection is OdbcConnection)
{
column.Type = Helper.ODBCToNetTypeConverter(dbType);
}
else if (connection is SqlConnection)
{
column.Type = Helper.ODBCToNetTypeConverter(row["DATA_TYPE"].ToString());
}
else
{
column.Type = Helper.DatabaseToNetTypeConverter(row["DATA_TYPE"]);
}
column.SetStandardFormat();
if (!columns.Exists(i => i.Name == column.Name))
{
columns.Add(column);
}
}
catch { }
}
}
/// <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 if (IsSQL)
{
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) + "." : "") + (IsSQL ? Source.GetColumnName(column.ColumnName) : 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 = 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);
UpdateEditorAttributes(); //!NETCore
}
