/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="sourceName"></param>
/// <param name="schemaName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetColumnMetadataFromSql(
string sql,
DbConnectionMx conn)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata md;
List <DbColumnMetadata> mdList = new List <DbColumnMetadata>();
string sql2 = sql + " where 1=2"; // make execution fast
DbCommandMx cmd = new DbCommandMx();
cmd.MxConn = conn;
cmd.PrepareUsingDefinedConnection(sql2);
OracleDataReader rdr = cmd.ExecuteReader() as OracleDataReader;
for (int fi = 0; fi < rdr.FieldCount; fi++)
{
md = new DbColumnMetadata();
md.Name = rdr.GetName(fi);
md.Type = rdr.GetDataTypeName(fi); //
mdList.Add(md);
}
rdr.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(mdList);
}
public static List <DbColumnMetadata> GetTableMetadataFromMySqlDictionary(
DbConnectionMx conn,
string schema,
string tableName)
{
string sql = String.Format(
@"SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
table_schema = '{0}'
AND table_name = '{1}'
ORDER BY ORDINAL_POSITION" ,
schema, tableName);
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
drd.PrepareUsingDefinedConnection(sql);
DbDataReader rdr = drd.ExecuteReader();
List <DbColumnMetadata> md = new List <DbColumnMetadata>();
while (rdr.Read())
{
DbColumnMetadata cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetLongByName("character_maximum_length");
cmd.Precision = drd.GetIntByName("numeric_precision");
cmd.Scale = drd.GetIntByName("numeric_scale");
cmd.Nullable = drd.GetStringByName("is_nullable");
cmd.Comment = drd.GetStringByName("column_comment");
md.Add(cmd);
}
rdr.Close();
return(md);
}
/// <summary>
/// Get a metatable from Oracle catalog
/// </summary>
/// <param name="tableName">schema.table</param>
/// <returns></returns>
public static MetaTable GetMetaTableFromDatabaseDictionary(
DbConnectionMx conn,
string schemaName,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
MetaTable mt = new MetaTable();
mt.MetaBrokerType = MetaBrokerType.Generic;
mt.Name = tableName;
mt.Label = MetaTable.IdToLabel(tableName);
mt.TableMap = schemaName + "." + tableName;
List <DbColumnMetadata> cmdList = GetTableMetadataFromOracleDictionary(conn, schemaName, tableName);
for (int ci = 0; ci < cmdList.Count; ci++)
{
DbColumnMetadata cmd = cmdList[ci];
MetaColumn mc = new MetaColumn();
mc.Name = cmd.Name;
mc.ColumnMap = mc.Name;
mc.Label = MetaTable.IdToLabel(cmd.Name);
if (cmd.Type == "VARCHAR" ||
cmd.Type == "VARCHAR2" ||
cmd.Type == "NVARCHAR2" ||
cmd.Type == "CHAR" ||
cmd.Type == "CHARACTER" ||
cmd.Type == "LONG")
{
mc.DataType = MetaColumnType.String;
}
else if (cmd.Type == "INTEGER")
{
mc.DataType = MetaColumnType.Integer;
}
else if (cmd.Type == "NUMBER" ||
cmd.Type == "FLOAT")
{
mc.DataType = MetaColumnType.Number;
mc.Format = ColumnFormatEnum.SigDigits; // display with 3 sig figures by default
mc.Decimals = 3;
}
else if (cmd.Type == "DATE" || cmd.Type.StartsWith("TIMESTAMP"))
{
mc.DataType = MetaColumnType.Date;
}
else
{
continue; // unrecognized
}
mc.InitialSelection = ColumnSelectionEnum.Selected;
mc.Width = 12;
mc.MetaTable = mt;
mt.AddMetaColumn(mc);
}
t0 = TimeOfDay.Milliseconds() - t0;
return(mt);
}
/// <summary>
/// GetTableMetadataFromOracleDictionary
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetTableMetadataFromOracleDictionary(
DbConnectionMx conn,
string schemaName,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata cmd;
List <DbColumnMetadata> cmdList = new List <DbColumnMetadata>();
string sql = "select column_name,data_type,data_length,data_precision,data_scale,nullable " +
"from sys.all_tab_columns where owner=:0 " +
"and table_name=:1 order by column_id";
if (conn == null)
{
throw new Exception("Connection not found for tableName: " + tableName);
}
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
int parmCount = 2;
drd.PrepareMultipleParameter(sql, parmCount);
string[] sa = tableName.Split('.');
if (sa.Length != 2)
{
throw new Exception("TableName not in owner.tableName form: " + tableName);
}
string creator = sa[0];
string tname = sa[1];
if (Lex.Eq(creator, "mbs_user")) // workaround to allow tables owned by dev mbs_owner
{
creator = "mbs_owner"; // to be accessed via synonyms defined on dev mbs_user
}
object[] p = new object[2];
p[0] = creator.ToUpper();
p[1] = tname.ToUpper();
drd.ExecuteReader(p);
while (drd.Read())
{
cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetIntByName("data_length");
cmd.Precision = drd.GetIntByName("data_precision");
cmd.Scale = drd.GetIntByName("data_scale");
cmd.Nullable = drd.GetStringByName("nullable");
cmdList.Add(cmd);
}
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(cmdList);
}
/// <summary>
/// Get a metatable from MySql catalog
/// </summary>
/// <param name="tableName">schema.table</param>
/// <returns></returns>
public static MetaTable GetMetaTableFromDatabaseDictionary(
DbConnectionMx conn,
string schema,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
MetaTable mt = new MetaTable();
mt.MetaBrokerType = MetaBrokerType.Generic;
mt.Name = tableName;
mt.Label = MetaTable.IdToLabel(tableName);
mt.TableMap = schema + "." + tableName;
List <DbColumnMetadata> cmdList = GetTableMetadataFromMySqlDictionary(conn, schema, tableName);
for (int ci = 0; ci < cmdList.Count; ci++)
{
DbColumnMetadata cmd = cmdList[ci];
MetaColumn mc = new MetaColumn();
mc.Name = cmd.Name;
mc.ColumnMap = mc.Name;
mc.Label = MetaTable.IdToLabel(cmd.Name);
if (Lex.Contains(cmd.Type, "CHAR") ||
Lex.Contains(cmd.Type, "TEXT"))
{
mc.DataType = MetaColumnType.String;
}
else if (Lex.Contains(cmd.Type, "INT") ||
Lex.Contains(cmd.Type, "ENUM"))
{
mc.DataType = MetaColumnType.Integer;
}
else if (cmd.Type == "FLOAT" ||
cmd.Type == "REAL" ||
cmd.Type == "DOUBLE" ||
cmd.Type == "DECIMAL" ||
cmd.Type == "NUMERIC")
{
mc.DataType = MetaColumnType.Number;
mc.Format = ColumnFormatEnum.Decimal;
mc.Decimals = cmd.Scale;
}
else if (cmd.Type == "DATE" ||
cmd.Type == "DATETIME" ||
cmd.Type == "TIMESTAMP")
{
mc.DataType = MetaColumnType.Date;
}
else
{
continue; // unrecognized
}
mc.InitialSelection = ColumnSelectionEnum.Selected;
mc.Width = 12;
mc.MetaTable = mt;
mc.Description = cmd.Comment;
mt.AddMetaColumn(mc);
}
t0 = TimeOfDay.Milliseconds() - t0;
return(mt);
}
