/// <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); }