private void ShowObjectTest(XsdDataBase.TBL_ObjectRow obj)
{
var dlg = new FormTestObject();
dlg.SetDataSource(DataBaseDataSet, SelectedConnection, obj);
dlg.ShowDialog();
}
private void RefreshObject(XsdDataBase.TBL_ObjectRow originalObject)
{
try
{
var map = new DataBaseMapper(DataBaseDataSet, SelectedConnection);
var log = new StringBuilder();
if (originalObject.Generic_Type == "StoredProcedure")
{
map.RefreshStoredProcedure(ref originalObject);
}
else
{
map.RefreshObjectTable(ref originalObject); // No aplica para modo por demanda
}
originalObject.Mapped = true;
if (log.ToString() != "")
{
var dlg = new FormLog();
dlg.AppendText(log.ToString());
dlg.ShowDialog();
}
}
catch (Exception ex)
{
if (originalObject != null)
{
originalObject.Mapped = false;
}
MessageBox.Show(ex.Message, Program.AssemblyTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public List <Parameter> GetTableColumns(XsdDataBase.TBL_ObjectRow table)
{
var param = new List <Parameter>();
var fields = table.GetTBL_FieldRows();
foreach (var field in fields)
{
var direction = (ParameterDirection)(Enum.Parse(typeof(ParameterDirection), field.Direction));
var fType = (DbType)(Enum.Parse(typeof(DbType), field.Field_Type));
param.Add(new Parameter(field.Field_Name, fType, field.Specific_Type, "", field.Is_Nullable, field.Max_Length, field.Precision, field.Scale, direction));
}
return(param);
}
private void ShowObjectConfig(XsdDataBase.TBL_ObjectRow obj)
{
if (obj.Generic_Type == "StoredProcedure")
{
var dlg = new FormConfigStoredProcedure();
dlg.SetDataSource(DataBaseDataSet, SelectedConnection, obj);
dlg.ShowDialog();
}
else
{
var dlg = new FormConfigTableFilter();
dlg.SetDataSource(DataBaseDataSet, obj.id_Object);
dlg.ShowDialog();
}
}
public void RefreshStoredProcedure(ref XsdDataBase.TBL_ObjectRow nOriginalStoredProcedure)
{
// Consultar la nueva configuracion
var newMapStoredProcedure = LoadAndRestoreStoredProcedure(nOriginalStoredProcedure, Log);
//Restaurar informacion basica
nOriginalStoredProcedure.Generic_Type = newMapStoredProcedure.Generic_Type;
nOriginalStoredProcedure.Object_Type = newMapStoredProcedure.Object_Type;
nOriginalStoredProcedure.Catalog_Name = newMapStoredProcedure.Catalog_Name;
nOriginalStoredProcedure.Schema_Name = newMapStoredProcedure.Schema_Name;
nOriginalStoredProcedure.Object_Name = newMapStoredProcedure.Object_Name;
// Restaurar parametros
var fields = nOriginalStoredProcedure.GetTBL_FieldRows();
foreach (var field in fields)
{
MapDataBase.TBL_Field.RemoveTBL_FieldRow(field);
}
try
{
fields = newMapStoredProcedure.GetTBL_FieldRows();
foreach (var parameter in fields)
{
MapDataBase.TBL_Field.AddTBL_FieldRow(nOriginalStoredProcedure,
parameter.Field_Name,
parameter.Field_Type,
parameter.Specific_Type,
parameter.Is_Nullable,
parameter.Max_Length,
parameter.Precision,
parameter.Scale,
parameter.PrimaryKey_Order,
parameter.Direction);
}
}
catch (Exception ex)
{
Log.AppendLine(ex.Message);
}
}
public List <Parameter> GetIdOnPrimaryKey(XsdDataBase.TBL_ObjectRow table)
{
var fields = table.GetTBL_FieldRows();
var KeyId = new List <Parameter>();
foreach (var field in fields)
{
//Si es una llave primaria y no es columna de una llave foranea
if (field.PrimaryKey_Order != "" && field.GetTBL_RelationRows().Length == 0)
{
var direction = (ParameterDirection)(Enum.Parse(typeof(ParameterDirection), field.Direction));
var fType = (DbType)(Enum.Parse(typeof(DbType), field.Field_Type));
KeyId.Add(new Parameter(field.Field_Name, fType, field.Specific_Type, "", field.Is_Nullable, field.Max_Length, field.Precision, field.Scale, direction));
}
}
return(KeyId);
}
public void CompareAndRestoreObjectStoredProcedure(XsdDataBase.TBL_ObjectRow originalObject, XsdDataBase.TBL_ObjectRow newObject, ref StringBuilder log)
{
var strLogObject = new StringBuilder("");
if (originalObject != null)
{
newObject.Catalog_Name = originalObject.Catalog_Name;
newObject.Schema_Name = originalObject.Schema_Name;
newObject.Object_Type = originalObject.Object_Type;
newObject.Object_Name = originalObject.Object_Name;
newObject.Selected = originalObject.Selected;
newObject.Mapped = originalObject.Mapped;
if (strLogObject.ToString() != "")
{
log.AppendLine("Se encontraron errores en el procedimiento almacenado [" + originalObject.Object_Name + "]");
log.AppendLine(strLogObject.ToString());
}
}
else
{
log.AppendLine("El procedimiento almacenado [" + newObject.Object_Name + "] ha sido agregado recientemente");
}
}
public XsdDataBase.TBL_ObjectRow LoadAndRestoreObjectTable(XsdDataBase.TBL_ObjectRow nOriginalTable, StringBuilder nLog)
{
CMData.Manager.DBManager DBM = null;
try
{
if (Connection != null)
{
if (Connection.IsConnection_TypeNull())
{
DBM = new CMData.Manager.DBManager(Connection.Connection_String);
}
else
{
var dbType = (CMData.DataBase.DataBaseType)(Enum.Parse(typeof(CMData.DataBase.DataBaseType), Connection.Connection_Type));
DBM = new CMData.Manager.DBManager(dbType, Connection.Connection_String);
}
DBM.Connection_Open();
var newDataBase = new XsdDataBase();
var newObject = newDataBase.TBL_Object.NewTBL_ObjectRow();
newObject.Generic_Type = nOriginalTable.Generic_Type;
newObject.Object_Type = nOriginalTable.Object_Type;
newObject.Catalog_Name = nOriginalTable.Catalog_Name;
newObject.Schema_Name = nOriginalTable.Schema_Name;
newObject.Object_Name = nOriginalTable.Object_Name;
newDataBase.TBL_Object.AddTBL_ObjectRow(newObject);
DBM.DataBase.FillDataTableColumns(newDataBase.TBL_Field, newDataBase.TBL_Relation, newObject);
DBM.Connection_Close();
try { newObject.Selected = nOriginalTable.Selected; }
catch { }
newObject.Mapped = true;
//Restaurar filtros
try
{
var filters = nOriginalTable.GetTBL_FilterRows();
foreach (var originalFilter in filters)
{
var newFilter = newDataBase.TBL_Filter.NewTBL_FilterRow();
newFilter.fk_Object = newObject.id_Object;
newFilter.Name = originalFilter.Name;
newDataBase.TBL_Filter.AddTBL_FilterRow(newFilter);
var filterFields = originalFilter.GetTBL_Filter_FieldRows();
bool isNewFilterFieldsComplete = true;
foreach (var originalFilterField in filterFields)
{
var fieldRows = newDataBase.TBL_Field.Select("Field_Name = '" + originalFilterField.Field_Name + "'");
if (fieldRows.Length > 0)
{
var newFilterField = newDataBase.TBL_Filter_Field.NewTBL_Filter_FieldRow();
newFilterField.fk_Filter = newFilter.id_Filter;
newFilterField.Field_Name = originalFilterField.Field_Name;
newFilterField.Filter_Order = originalFilterField.Filter_Order;
newDataBase.TBL_Filter_Field.AddTBL_Filter_FieldRow(newFilterField);
}
else
{
nLog.AppendLine(ControlChars.Tab + "El campo con nombre [" + originalFilterField.Field_Name + "] no fue encontrado en la base de datos");
isNewFilterFieldsComplete = false;
}
}
if (!isNewFilterFieldsComplete)
{
nLog.AppendLine(ControlChars.Tab + "El filtro con nombre [" + originalFilter.Name + "] no fue agregado debido a que no coinciden sus campos de filtrado");
newFilter.Delete();
}
}
}
catch { }
return(newObject);
}
throw new Exception("Se debe seleccionar una conexión");
}
catch (Exception ex)
{
if (DBM != null)
{
DBM.Connection_Close();
}
throw new Exception(ex.Message, ex);
}
}
public XsdDataBase.TBL_ObjectRow LoadAndRestoreStoredProcedure(XsdDataBase.TBL_ObjectRow nOriginalStoredProcedure, StringBuilder nLog)
{
CMData.Manager.DBManager DBM = null;
try
{
if (Connection != null)
{
if (Connection.IsConnection_TypeNull())
{
DBM = new CMData.Manager.DBManager(Connection.Connection_String);
}
else
{
var dbType = (CMData.DataBase.DataBaseType)(Enum.Parse(typeof(CMData.DataBase.DataBaseType), Connection.Connection_Type));
DBM = new CMData.Manager.DBManager(dbType, Connection.Connection_String);
}
DBM.Connection_Open();
var newDataBase = new XsdDataBase();
var newObject = newDataBase.TBL_Object.NewTBL_ObjectRow();
newObject.Generic_Type = nOriginalStoredProcedure.Generic_Type;
newObject.Object_Type = nOriginalStoredProcedure.Object_Type;
newObject.Catalog_Name = nOriginalStoredProcedure.Catalog_Name;
newObject.Schema_Name = nOriginalStoredProcedure.Schema_Name;
newObject.Object_Name = nOriginalStoredProcedure.Object_Name;
newDataBase.TBL_Object.AddTBL_ObjectRow(newObject);
DBM.DataBase.FillDataBaseParameters(newDataBase.TBL_Field, newObject);
try { newObject.Selected = nOriginalStoredProcedure.Selected; }
catch { }
newObject.Mapped = true;
try
{
var originalSpReturn = nOriginalStoredProcedure.GetTBL_SP_ReturnRows()[0];
var newSpReturn = newDataBase.TBL_SP_Return.NewTBL_SP_ReturnRow();
newSpReturn.fk_Object = newObject.id_Object;
newSpReturn.Return_Type = originalSpReturn.Return_Type;
newSpReturn.Data_Type_Returned = originalSpReturn.Data_Type_Returned;
newSpReturn.Schema_Name_Returned = originalSpReturn.Schema_Name_Returned;
newSpReturn.Object_Name_Returned = originalSpReturn.Object_Name_Returned;
newDataBase.TBL_SP_Return.AddTBL_SP_ReturnRow(newSpReturn);
}
catch { }
DBM.Connection_Close();
return(newObject);
}
throw new Exception("Se debe seleccionar una conexión");
}
catch (Exception ex)
{
if (DBM != null)
{
DBM.Connection_Close();
}
throw new Exception(ex.Message);
}
}
public void RestoreOriginalObjectStoredProcedure(XsdDataBase originalDataBase, XsdDataBase.TBL_ObjectRow oldObject, XsdDataBase.TBL_ObjectRow newObject, ref StringBuilder log, XsdDataBase dtsDataBase)
{
var strLogObject = new StringBuilder("");
if (oldObject != null)
{
newObject.Selected = oldObject.Selected;
// Recuperar las columnas
var oldColumns = (XsdDataBase.TBL_FieldRow[])(originalDataBase.TBL_Field.Select("fk_Object = " + oldObject.id_Object));
foreach (var oldColumn in oldColumns)
{
var newColumn = dtsDataBase.TBL_Field.NewTBL_FieldRow();
newColumn.fk_Object = newObject.id_Object;
newColumn.Field_Name = oldColumn.Field_Name;
newColumn.Field_Type = oldColumn.Field_Type;
newColumn.Specific_Type = oldColumn.Specific_Type;
newColumn.Is_Nullable = oldColumn.Is_Nullable;
newColumn.Max_Length = oldColumn.Max_Length;
newColumn.Precision = oldColumn.Precision;
newColumn.Scale = oldColumn.Scale;
newColumn.PrimaryKey_Order = oldColumn.PrimaryKey_Order;
newColumn.Direction = oldColumn.Direction;
dtsDataBase.TBL_Field.AddTBL_FieldRow(newColumn);
}
newObject.Mapped = true;
if (strLogObject.ToString() != "")
{
log.AppendLine("Se encontraron errores en el procedimiento almacenado [" + oldObject.Object_Name + "]");
log.AppendLine(strLogObject.ToString());
newObject.Mapped = false;
}
}
else
{
log.AppendLine("El procedimiento almacenado [" + newObject.Object_Name + "] ha sido agregado recientemente");
newObject.Mapped = false;
}
}
public void RefreshObjectTable(ref XsdDataBase.TBL_ObjectRow nOriginalTable)
{
// Consultar la nueva configuracion
var newMapTable = LoadAndRestoreObjectTable(nOriginalTable, Log);
// Restaurar informacion basica
nOriginalTable.Generic_Type = newMapTable.Generic_Type;
nOriginalTable.Object_Type = newMapTable.Object_Type;
nOriginalTable.Catalog_Name = newMapTable.Catalog_Name;
nOriginalTable.Schema_Name = newMapTable.Schema_Name;
nOriginalTable.Object_Name = newMapTable.Object_Name;
nOriginalTable.Selected = newMapTable.Selected;
nOriginalTable.Mapped = newMapTable.Mapped;
// Restaurar columnas
var fields = nOriginalTable.GetTBL_FieldRows();
foreach (var field in fields)
{
field.Delete();
}
try
{
fields = newMapTable.GetTBL_FieldRows();
foreach (var newMapField in fields)
{
var field = MapDataBase.TBL_Field.AddTBL_FieldRow(nOriginalTable,
newMapField.Field_Name,
newMapField.Field_Type,
newMapField.Specific_Type,
newMapField.Is_Nullable,
newMapField.Max_Length,
newMapField.Precision,
newMapField.Scale,
newMapField.PrimaryKey_Order,
newMapField.Direction);
try
{
var relations = newMapField.GetTBL_RelationRows();
foreach (var newMapRelation in relations)
{
MapDataBase.TBL_Relation.AddTBL_RelationRow(field,
newMapRelation.Relation_Name,
newMapRelation.Table_Name,
newMapRelation.Column_Name);
}
}
catch (Exception ex)
{
Log.AppendLine(ex.Message);
}
}
}
catch (Exception ex)
{
Log.AppendLine(ex.Message);
}
// Restaurar filtros
var filters = nOriginalTable.GetTBL_FilterRows();
foreach (var nFilter in filters)
{
nFilter.Delete();
}
try
{
filters = newMapTable.GetTBL_FilterRows();
foreach (var newMapFilter in filters)
{
var nFilter = MapDataBase.TBL_Filter.AddTBL_FilterRow(nOriginalTable, newMapFilter.Name);
try
{
var filterFields = newMapFilter.GetTBL_Filter_FieldRows();
foreach (var newFilterField in filterFields)
{
MapDataBase.TBL_Filter_Field.AddTBL_Filter_FieldRow(nFilter,
newFilterField.Filter_Order,
newFilterField.Field_Name);
}
}
catch (Exception ex)
{
Log.AppendLine(ex.Message);
}
}
}
catch (Exception ex)
{
Log.AppendLine(ex.Message);
}
}
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);
}
}
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);
}
}
