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