public override void FillDataTableColumns(XsdDataBase.TBL_FieldDataTable nFieldTable, XsdDataBase.TBL_RelationDataTable nRelationTable, XsdDataBase.TBL_ObjectRow nTable)
{
try
{
if (nTable.Generic_Type == "View")
{
string sql = @"select column_name , nullable is_nullable , data_type , data_length character_maximum_length , data_precision numeric_precision , data_scale numeric_scale , data_type udt_name , column_id ordinal_position " + ControlChars.CrLf +
"from all_tab_columns av where owner = '" + nTable.Schema_Name + "' and table_name = '" + nTable.Object_Name + "'";
DataTable table = ExecuteQueryGet(sql);
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
nFieldTable.AddTBL_FieldRow(nTable, row["column_name"].ToString(), GetGenericParameterType(row["udt_name"].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("In"));
}
}
else // Table
{
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 , col.udt_name" + 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["udt_name"].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["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 FillDataTableColumns(XsdDataBase.TBL_FieldDataTable nFieldTable, XsdDataBase.TBL_RelationDataTable nRelationTable, XsdDataBase.TBL_ObjectRow nTable)
{
try
{
if (nTable.Generic_Type == "View")
{
string sql = @"SELECT column_name , nullable is_nullable , data_type , data_length character_maximum_length , data_precision numeric_precision , data_scale numeric_scale , data_type udt_name , column_id ordinal_position " + ControlChars.CrLf +
"FROM all_tab_columns av WHERE owner = '" + nTable.Schema_Name + "' AND table_name = '" + nTable.Object_Name + "'";
DataTable table = ExecuteQueryGet(sql);
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
nFieldTable.AddTBL_FieldRow(nTable, row["column_name"].ToString(), GetGenericParameterType(row["udt_name"].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("In"));
}
}
else // Table
{
string sql = @"SELECT *" + ControlChars.CrLf +
" FROM (SELECT column_name, nullable is_nullable, data_type, data_length character_maximum_length, data_precision numeric_precision, data_scale numeric_scale, data_type udt_name, column_id ordinal_position" + ControlChars.CrLf +
" FROM all_tab_columns av WHERE owner = '" + nTable.Schema_Name + "' AND table_name = '" + nTable.Object_Name + "') tab" + ControlChars.CrLf +
" LEFT JOIN" + ControlChars.CrLf +
" (SELECT UNIQUE a.constraint_name, '' AS primary_catalog_name, b.owner AS primary_schema_name, b.table_name AS primary_table_name, b.column_name AS primary_column_name, b.POSITION AS primary_ordinal_position, '' AS foreign_catalog_name, c.owner AS foreign_schema_name, a.table_name AS foreign_table_name, c.column_name AS foreign_column_name, c.POSITION AS foreign_ordinal_position" + ControlChars.CrLf +
" FROM all_constraints a, all_cons_columns b, all_cons_columns c" + ControlChars.CrLf +
" WHERE ( a.r_constraint_name = b.constraint_name AND a.constraint_name = c.constraint_name AND a.owner = c.owner AND a.owner = b.owner AND a.table_name = c.table_name AND b.POSITION = c.POSITION )" + ControlChars.CrLf +
" AND c.owner = '" + nTable.Schema_Name + "' AND a.table_name = '" + nTable.Object_Name + "') rel" + ControlChars.CrLf +
" ON rel.foreign_column_name = tab.column_name" + ControlChars.CrLf +
" LEFT JOIN" + ControlChars.CrLf +
" (SELECT cols.column_name AS pk_column_name, cols.POSITION AS pk_position, cons.status AS pk_status" + ControlChars.CrLf +
" FROM all_constraints cons, all_cons_columns cols" + ControlChars.CrLf +
" WHERE cons.owner = '" + nTable.Schema_Name + "' AND cols.table_name = '" + nTable.Object_Name + "' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner) pri" + ControlChars.CrLf +
" ON pri.pk_column_name = tab.column_name" + ControlChars.CrLf +
"ORDER BY primary_catalog_name, primary_schema_name, foreign_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["udt_name"].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["pk_position"].ToString(), GetGenericParameterDirection("In"));
col = row["column_name"].ToString();
}
if (!row.IsNull("CONSTRAINT_NAME"))
{
nRelationTable.AddTBL_RelationRow(field, row["constraint_name"].ToString(), row["primary_table_name"].ToString(), row["primary_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.udt_name , 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 = 'FUNCTION' 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["udt_name"].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);
}
}
public override void FillDataBaseParameters(XsdDataBase.TBL_FieldDataTable nFieldTable, XsdDataBase.TBL_ObjectRow nTable)
{
try
{
string sql = "";
if (nTable.Object_Name.IndexOf(".") > 0)
{
string package = nTable.Object_Name.Split('.')[0];
string proc = nTable.Object_Name.Split('.')[1];
sql = @"SELECT argument_name AS parameter_name , 'YES' is_nullable , data_type , data_length AS character_maximum_length," + ControlChars.CrLf +
"data_precision AS numeric_precision , data_scale AS numeric_scale , pls_type udt_name , sequence ordinal_position , in_out parameter_mode" + ControlChars.CrLf +
"FROM all_arguments WHERE owner = '" + nTable.Schema_Name + "' AND package_name = '" + package + "' AND object_name = '" + proc + "'" + ControlChars.CrLf +
"ORDER BY sequence";
}
else
{
sql = @"SELECT argument_name AS parameter_name , 'YES' is_nullable , data_type , data_length AS character_maximum_length," + ControlChars.CrLf +
"data_precision AS numeric_precision , data_scale AS numeric_scale , pls_type udt_name , sequence ordinal_position , in_out parameter_mode" + ControlChars.CrLf +
"FROM all_arguments WHERE owner = '" + nTable.Schema_Name + "' AND object_name = '" + nTable.Object_Name + "'" + ControlChars.CrLf +
"ORDER BY sequence";
}
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"), GetParameterSize(ConvertToInt(row["character_maximum_length"]), row["data_type"].ToString()), GetParameterPresicion(ConvertToByte(row["numeric_precision"]), row["data_type"].ToString()), GetParameterScale(ConvertToByte(row["numeric_scale"]), row["data_type"].ToString()), 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);
}
}