Пример #1
0
        public static TableMapper GetTableSchema(String TableName, EDataBase DataBaseType)
        {
            try
            {
                var Retorno = new TableMapper();
                var Query = String.Empty;

                switch (DataBaseType)
                {
                    case EDataBase.MySql:
                        Retorno = LoadMySql(TableName);
                        break;
                    case EDataBase.SqlServer:
                        Retorno = LoadSQLServer(TableName);
                        break;
                    case EDataBase.Oracle:
                        Retorno = LoadOracle(TableName);
                        break;
                }

                return (Retorno);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
Пример #2
0
        private static TableMapper LoadOracle(String TableName)
        {
            var TableMap = new TableMapper();
            var Query = String.Format(@"SELECT a.column_name column_name, 
                                               CASE a.nullable         
                                                    WHEN 'Y' then 'yes' 
                                                    WHEN 'N' then 'no' 
                                                    ELSE ''
                                               END is_nullable,       
                                               LOWER(a.data_type) data_type,        
                                               a.data_length maximum_length,
                                               CASE b.constraint_type 
                                                    WHEN 'P' THEN 'pk' 
                                                    ELSE ''
                                               END column_key  
                                          FROM all_tab_columns a,       
                                               (SELECT w.column_name, 
                                                       g.constraint_type,
                                                       w.table_name
                                                  FROM all_cons_columns w, all_constraints g  
                                                 WHERE w.constraint_name = g.constraint_name(+) 
                                                   AND g.constraint_type = 'P' ORDER BY 1) b  
                                         WHERE a.table_name  = b.table_name(+) 
                                           AND a.column_name = b.column_name(+) 
                                           AND a.table_name  = '{0}'", TableName);

            using (var Conexao = new ConnectionOracle(Query, string.Empty))
            {
                if (Conexao.Open())
                {
                    var Collection = new List<ColumnMapper>();
                    TableMap.TableName = TableName;

                    while (Conexao.LerRegistro())
                    {
                        var Column = new ColumnMapper();

                        Column.ColumnName = Conexao.Ler("COLUMN_NAME").ToString();
                        Column.IsNullable = Conexao.Ler("IS_NULLABLE").ToString();
                        Column.DataType = Conexao.Ler("DATA_TYPE").ToString();
                        Column.MaxLenght = Conexao.Ler("MAXIMUM_LENGTH").ToString();
                        Column.ColumnKey = Conexao.Ler("COLUMN_KEY").ToString();

                        Collection.Add(Column);
                    }

                    TableMap.CollectionColumn = Collection;
                }
            }
            return (TableMap);
        }
Пример #3
0
        private static TableMapper LoadMySql(String TableName)
        {
            var TableMapper = new TableMapper();

            var Query = string.Format(@"SELECT UPPER(COLUMN_NAME) COLUMN_NAME,
		                                       LOWER(IS_NULLABLE) IS_NULLABLE,
		                                       LOWER(DATA_TYPE)   DATA_TYPE,
		                                       LOWER(CHARACTER_MAXIMUM_LENGTH) MAXIMUM_LENGTH, 
		                                       CASE WHEN LOWER(COLUMN_KEY) = 'pri' THEN 'pk' 
                                                    WHEN LOWER(COLUMN_KEY) = 'mul' THEN 'fk'    
				                                    WHEN LOWER(COLUMN_KEY) = NULL THEN NULL 
                                               END AS COLUMN_KEY 
	                                      FROM INFORMATION_SCHEMA.COLUMNS A
	                                     WHERE A.TABLE_NAME = '{0}' 
	                                     ORDER BY A.ORDINAL_POSITION ASC", TableName);

            using (var Conexao = new ConnectionMySql(Query))
            {
                if (Conexao.Open())
                {
                    TableMapper.TableName = TableName;
                    var CollectionColumnMapper = new List<ColumnMapper>();

                    while (Conexao.LerRegistro())
                    {
                        var Column = new ColumnMapper();

                        Column.ColumnName = Conexao.Ler("COLUMN_NAME").ToString();
                        Column.IsNullable = Conexao.Ler("IS_NULLABLE").ToString();
                        Column.DataType = Conexao.Ler("DATA_TYPE").ToString();
                        Column.MaxLenght = Conexao.Ler("MAXIMUM_LENGTH").ToString();
                        Column.ColumnKey = Conexao.Ler("COLUMN_KEY").ToString();

                        CollectionColumnMapper.Add(Column);
                    }

                    TableMapper.CollectionColumn = CollectionColumnMapper;
                }
            }
            return (TableMapper);
        }
Пример #4
0
        private static TableMapper LoadSQLServer(String TableName)
        {
            var TableMapper = new TableMapper();

            var Query = "SELECT " +
                                "UPPER(A.COLUMN_NAME) COLUMN_NAME, " +
                                "LOWER(A.IS_NULLABLE) IS_NULLABLE, " +
                                "LOWER(A.DATA_TYPE) DATA_TYPE, " +
                                "LOWER(A.CHARACTER_MAXIMUM_LENGTH) MAXIMUM_LENGTH, " +
                                "B.COLUMN_KEY " +
                        "FROM " +
                                "INFORMATION_SCHEMA.COLUMNS A LEFT JOIN " +
                                "(SELECT UPPER(KCU.TABLE_NAME) AS TABLE_NAME, " +
                                "      UPPER(KCU.COLUMN_NAME) AS COLUMN_NAME, " +
                                "      CASE WHEN LOWER(TC.CONSTRAINT_TYPE) = 'PRIMARY KEY' THEN 'pk' " +
                                "           WHEN LOWER(TC.CONSTRAINT_TYPE) = 'FOREIGN KEY' THEN 'fk' " +
                                "           WHEN LOWER(TC.CONSTRAINT_TYPE) = 'UNIQUE' THEN NULL " +
                                "           WHEN LOWER(TC.CONSTRAINT_TYPE) = NULL THEN NULL " +
                                "      END AS COLUMN_KEY " +
                                " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU " +
                                "      LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC " +
                                "      ON KCU.TABLE_NAME = TC.TABLE_NAME " +
                                "      AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA " +
                                "      AND KCU.TABLE_CATALOG = TC.TABLE_CATALOG " +
                                "      AND KCU.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG " +
                                "      AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME " +
                                "      LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC " +
                                "      ON RC.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA " +
                                "      AND RC.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG " +
                                "      AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME " +
                                "      LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU " +
                                "      ON RC.UNIQUE_CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA " +
                                "      AND RC.UNIQUE_CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG " +
                                "      AND RC.UNIQUE_CONSTRAINT_NAME = CCU.CONSTRAINT_NAME) B " +
                                "      ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME " +
                        "WHERE " +
                                "A.TABLE_NAME = '" + TableName + "'";

            using (var Conexao = new ConnectionSqlServer(Query))
            {
                if (Conexao.Open())
                {
                    TableMapper.TableName = TableName;
                    var CollectionColumnMapper = new List<ColumnMapper>();

                    while (Conexao.LerRegistro())
                    {
                        var Column = new ColumnMapper();

                        Column.ColumnName = Conexao.Ler("COLUMN_NAME").ToString();
                        Column.IsNullable = Conexao.Ler("IS_NULLABLE").ToString();
                        Column.DataType = Conexao.Ler("DATA_TYPE").ToString();
                        Column.MaxLenght = Conexao.Ler("MAXIMUM_LENGTH").ToString();
                        Column.ColumnKey = Conexao.Ler("COLUMN_KEY").ToString();

                        CollectionColumnMapper.Add(Column);
                    }

                    TableMapper.CollectionColumn = CollectionColumnMapper;
                }
            }
            return (TableMapper);
        }