Exemplo n.º 1
0
/// <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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        /// <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);
        }
Exemplo n.º 4
0
/// <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);
        }
Exemplo n.º 5
0
        /// <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);
        }