Beispiel #1
0
        public override void FillDataTableColumns(XsdDataBase.TBL_FieldDataTable nFieldTable, XsdDataBase.TBL_RelationDataTable nRelationTable, XsdDataBase.TBL_ObjectRow nTable)
        {
            try
            {
                string sql = @"Select * From (" + ControlChars.CrLf +
                             "SELECT col.column_name , col.is_nullable , col.data_type , col.character_maximum_length, col.numeric_precision , col.numeric_scale , col.ordinal_position , col.table_catalog , col.table_schema , col.table_name , k.ordinal_position pk_position " + ControlChars.CrLf +
                             "FROM information_schema.key_column_usage k INNER JOIN information_schema.table_constraints tc ON tc.constraint_type = 'PRIMARY KEY' AND tc.constraint_name = k.constraint_name" + ControlChars.CrLf +
                             "RIGHT JOIN information_schema.columns col ON col.column_name = k.column_name  AND tc.table_name = col.table_name" + ControlChars.CrLf +
                             "WHERE col.table_catalog = '" + nTable.Catalog_Name + "' AND col.table_schema = '" + nTable.Schema_Name + "' AND col.table_name = '" + nTable.Object_Name + "'" + ControlChars.CrLf +
                             ") Tab Left Join (" + ControlChars.CrLf +
                             "SELECT    FRG_TBL.CONSTRAINT_NAME AS FRG_CONSTRAINT_NAME , FRG_TBL.TABLE_CATALOG AS FRG_TABLE_CATALOG , FRG_TBL.TABLE_SCHEMA AS FRG_TABLE_SCHEMA" + ControlChars.CrLf +
                             ", FRG_TBL.TABLE_NAME AS FRG_TABLE_NAME , FRG_TBL.COLUMN_NAME AS FRG_COLUMN_NAME , FRG_TBL.ORDINAL_POSITION AS FRG_ORDINAL_POSITION" + ControlChars.CrLf +
                             ", PRI_TBL.CONSTRAINT_NAME AS PRI_CONSTRAINT_NAME , PRI_TBL.CONSTRAINT_CATALOG AS PRI_CONSTRAINT_CATALOG , PRI_TBL.CONSTRAINT_SCHEMA AS PRI_CONSTRAINT_SCHEMA" + ControlChars.CrLf +
                             ", PRI_TBL.TABLE_NAME AS PRI_TABLE_NAME , PRI_TBL.COLUMN_NAME AS PRI_COLUMN_NAME , PRI_TBL.ORDINAL_POSITION AS PRI_ORDINAL_POSITION" + ControlChars.CrLf +
                             "FROM	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC"+ ControlChars.CrLf +
                             "JOIN	INFORMATION_SCHEMA.KEY_COLUMN_USAGE FRG_TBL "+ ControlChars.CrLf +
                             "ON	FRG_TBL.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND FRG_TBL.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND FRG_TBL.CONSTRAINT_NAME = RC.CONSTRAINT_NAME"+ ControlChars.CrLf +
                             "JOIN	INFORMATION_SCHEMA.KEY_COLUMN_USAGE PRI_TBL"+ ControlChars.CrLf +
                             "ON	PRI_TBL.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG "+ ControlChars.CrLf +
                             "AND	PRI_TBL.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA"+ ControlChars.CrLf +
                             "AND	PRI_TBL.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME"+ ControlChars.CrLf +
                             "AND	PRI_TBL.ORDINAL_POSITION = FRG_TBL.ORDINAL_POSITION"+ ControlChars.CrLf +
                             "WHERE       FRG_TBL.CONSTRAINT_CATALOG = '" + nTable.Catalog_Name + "' AND FRG_TBL.CONSTRAINT_SCHEMA = '" + nTable.Schema_Name + "' AND" + ControlChars.CrLf +
                             "FRG_TBL.TABLE_NAME = '" + nTable.Object_Name + "' " + ControlChars.CrLf +
                             ") Rel" + ControlChars.CrLf +
                             "On Rel.FRG_COLUMN_NAME = Tab.column_name" + ControlChars.CrLf +
                             "Order by table_catalog , table_schema , table_name , ordinal_position";

                DataTable table = ExecuteQueryGet(sql);

                string col = "";

                XsdDataBase.TBL_FieldRow field = null;

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow row = table.Rows[i];

                    if (col != row["column_name"].ToString())
                    {
                        field = nFieldTable.AddTBL_FieldRow(nTable, row["column_name"].ToString(), GetGenericParameterType(row["data_type"].ToString()).ToString(), row["data_type"].ToString(), (row["is_nullable"].ToString().ToUpper() != "NO"), ToInt(row["character_maximum_length"]), ToByte(row["numeric_precision"]), ToByte(row["numeric_scale"]), row["pk_position"].ToString(), GetGenericParameterDirection("In"));
                        col   = row["column_name"].ToString();
                    }

                    if (!row.IsNull("FRG_CONSTRAINT_NAME"))
                    {
                        nRelationTable.AddTBL_RelationRow(field, row["FRG_CONSTRAINT_NAME"].ToString(), row["PRI_TABLE_NAME"].ToString(), row["PRI_COLUMN_NAME"].ToString());
                    }
                }
                //}
            }
            catch (Exception ex)
            {
                throw new Exception("No fue posible obtener el listado de columnas " + base.Connection.DataSource + " Tabla = " + nTable.Object_Name + ", " + ex.Message, ex);
            }
        }
Beispiel #2
0
        public override void FillDataBaseParameters(XsdDataBase.TBL_FieldDataTable nFieldTable, XsdDataBase.TBL_ObjectRow nTable)
        {
            try
            {
                string sql = @"SELECT p.parameter_name ,  'NO' is_nullable , p.data_type , p.character_maximum_length, p.numeric_precision , p.numeric_scale , p.ordinal_position , p.parameter_mode" + ControlChars.CrLf +
                             "FROM information_schema.routines r INNER JOIN information_schema.parameters p ON p.specific_catalog = r.specific_catalog AND p.specific_schema = r.specific_schema AND p.specific_name = r.specific_name" + ControlChars.CrLf +
                             "WHERE r.routine_type = 'PROCEDURE' AND r.specific_schema NOT IN('information_schema', 'pg_catalog')" + ControlChars.CrLf +
                             "AND r.routine_catalog = '" + nTable.Catalog_Name + "' AND r.routine_schema = '" + nTable.Schema_Name + "' AND r.routine_name = '" + nTable.Object_Name + "'" + ControlChars.CrLf +
                             "ORDER BY p.ordinal_position";

                DataTable table = ExecuteQueryGet(sql);

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow row = table.Rows[i];
                    nFieldTable.AddTBL_FieldRow(nTable, row["parameter_name"].ToString(), GetGenericParameterType(row["data_type"].ToString()).ToString(), row["data_type"].ToString(), (row["is_nullable"].ToString().ToUpper() != "NO"), ConvertToInt(row["character_maximum_length"]), ConvertToByte(row["numeric_precision"]), ConvertToByte(row["numeric_scale"]), row["ordinal_position"].ToString(), GetGenericParameterDirection(row["parameter_mode"].ToString()));
                }
            }
            catch (Exception ex)
            {
                throw new Exception("No fue posible obtener el listado de columnas " + base.Connection.DataSource + " Tabla = " + nTable.Object_Name + ", " + ex.Message, ex);
            }
        }