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