public static Collection <DBSchemaInfo> GetTargetSchemas(dbOrigen who)
{
string schemas = null;
string[] aSchemas = {};
Collection <DBSchemaInfo> schemaList = new Collection <DBSchemaInfo>();
switch (who)
{
case dbOrigen.ORA:
schemas = AdministradorPreferencias.Read(PrefsBD.ORATargetSchemas);
break;
case dbOrigen.PGSQL:
schemas = AdministradorPreferencias.Read(PrefsBD.PGSQLTargetSchemas);
break;
}
if (schemas != null && schemas.Trim() != string.Empty)
{
aSchemas = schemas.Split(new char[] { ',', ';' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string s in aSchemas)
{
schemaList.Add(new DBSchemaInfo(who, s.Trim()));
}
}
return(schemaList);
}
private static DBColumnInfo ColInfoFromName(dbOrigen source, string qualifiedName)
{
string[] names = qualifiedName.Split(new char[] { '.' });
string schemaName = string.Empty;
string relationName = string.Empty;
string columnName = string.Empty;
switch (source)
{
case dbOrigen.PGSQL:
//En PostgreSQL el esquema por defecto es 'public'
schemaName = (names.Length > 2) ? names[0] : "public";
break;
case dbOrigen.ORA:
//En ORACLE el nombre debe ser completamente calificado
//puesto que no existe el concepto de ESQUEMA como categoría.
//En ORACLE ESQUEMA = NOMBRE_PROPIETARIO. Por tanto, si no
//conocemos el nombre del propietario, la estructura DBColumnInfo
//quedará incompleta.
schemaName = (names.Length > 2) ? names[0] : string.Empty;
break;
}
relationName = (names.Length > 2) ? names[1] : names[0];
columnName = (names.Length > 2) ? names[2] : names[1];
DBSchemaInfo schema = new DBSchemaInfo(source, schemaName);
DBRelationInfo relation = new DBRelationInfo(schema, relationName, dbRelationType.Unknown);
return(new DBColumnInfo(relation, columnName, string.Empty, string.Empty));
}
private void CrearRamaEsquemas(TreeNode raiz)
{
if (raiz.GetNodeCount(false) == 0)
{
dbOrigen who = (dbOrigen)raiz.Parent.Tag;
foreach (DBSchemaInfo s in DBUtils.GetTargetSchemas(who))
{
TreeNode RT = new TreeNode("Tablas");
RT.Tag = TipoRaiz.RaizTablas;
TreeNode RV = new TreeNode("Vistas");
RV.Tag = TipoRaiz.RaizVistas;
TreeNode E = new TreeNode(s.Name);
E.Tag = s;
E.Nodes.AddRange(new TreeNode[] { RT, RV });
raiz.Nodes.Add(E);
}
}
}
public static void ExceptionBox(DataTable dt, dbOrigen who)
{
if (dt.TableName == "Excepcion")
{
string msg = (string)dt.Rows[0]["Mensaje"];
string caption = "Error";
switch (who)
{
case dbOrigen.ORA:
caption = "Mensaje de error del servidor ORACLE";
break;
case dbOrigen.PGSQL:
caption = "Mensaje de error del servidor PostgreSQL";
break;
}
System.Windows.Forms.MessageBox.Show(msg, caption, System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
}
public static bool TestConnection(dbOrigen source)
{
bool r = false;
switch (source)
{
case dbOrigen.Ninguno:
r = false;
System.Windows.Forms.MessageBox.Show("No se ha especificado origen de datos.",
"Error al conectar a la base de datos",
System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Error);
break;
case dbOrigen.ORA:
OracleConnection oraCn = new OracleConnection(GetORACnString());
try
{
oraCn.Open();
oraCn.Close();
r = true;
}
catch (OracleException e)
{
r = false;
System.Windows.Forms.MessageBox.Show(e.Message,
"Error al conectar a la base de datos",
System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Error);
}
catch (Exception e)
{
r = false;
System.Windows.Forms.MessageBox.Show(e.Message,
"Error al conectar a la base de datos",
System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Error);
}
finally
{
if (oraCn.State != 0)
{
oraCn.Close();
}
}
break;
case dbOrigen.PGSQL:
NpgsqlConnection pgCn = new NpgsqlConnection(GetPGSQLCnString());
try
{
pgCn.Open();
pgCn.Close();
r = true;
}
catch (NpgsqlException e)
{
r = false;
System.Windows.Forms.MessageBox.Show(e.Message,
"Error al conectar a la base de datos",
System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Error);
}
catch (Exception e)
{
r = false;
System.Windows.Forms.MessageBox.Show(e.Message,
"Error al conectar a la base de datos",
System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Error);
}
finally
{
if (pgCn.State != 0)
{
pgCn.Close();
}
}
break;
}
return(r);
}
public static DataSet ExecuteNonQuery(dbOrigen source, string sSQL, object[] aParam)
{
int i = 1;
int numRows = 0;
DataSet oDs = new DataSet();
switch (source)
{
case dbOrigen.Ninguno:
FillExceptionDataSet(oDs, "No se ha especificado origen de datos.");
break;
case dbOrigen.ORA:
OracleConnection oraCn = new OracleConnection(GetORACnString());
try
{
oraCn.Open();
OracleCommand oraCmd = new OracleCommand(RemoveSemiColon(sSQL), oraCn);
if (aParam != null)
{
foreach (object param in aParam)
{
oraCmd.Parameters.Add(new OracleParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
numRows = oraCmd.ExecuteNonQuery();
FillInfoDataSet(oDs, string.Format("El comando se completó con {0} registro/s afectado/s", numRows.ToString()));
oraCmd.Dispose();
oraCn.Close();
}
catch (OracleException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (oraCn.State != 0)
{
oraCn.Close();
}
}
break;
case dbOrigen.PGSQL:
NpgsqlConnection pgCn = new NpgsqlConnection(GetPGSQLCnString());
try
{
pgCn.Open();
NpgsqlCommand pgCmd = new NpgsqlCommand(sSQL, pgCn);
if (aParam != null)
{
foreach (object param in aParam)
{
pgCmd.Parameters.Add(new NpgsqlParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
numRows = pgCmd.ExecuteNonQuery();
FillInfoDataSet(oDs, string.Format("El comando se completó con {0} registro/s afectado/s", numRows.ToString()));
pgCmd.Dispose();
pgCn.Close();
}
catch (NpgsqlException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (pgCn.State != 0)
{
pgCn.Close();
}
}
break;
}
return(oDs);
}
public static object GetScalar(dbOrigen source, string sSQL, object[] aParam)
{
object r = null;
int i = 1;
switch (source)
{
case dbOrigen.Ninguno:
r = new DataSet();
FillExceptionDataSet((DataSet)r, "No se ha especificado origen de datos.");
break;
case dbOrigen.ORA:
OracleConnection oraCn = new OracleConnection(GetORACnString());
try
{
oraCn.Open();
OracleCommand oraCmd = new OracleCommand(RemoveSemiColon(sSQL), oraCn);
if (aParam != null)
{
foreach (object param in aParam)
{
oraCmd.Parameters.Add(new OracleParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
r = oraCmd.ExecuteScalar();
oraCmd.Dispose();
oraCn.Close();
}
catch (OracleException e)
{
r = new DataSet();
FillExceptionDataSet((DataSet)r, e.ToString());
}
finally
{
if (oraCn.State != 0)
{
oraCn.Close();
}
}
break;
case dbOrigen.PGSQL:
NpgsqlConnection pgCn = new NpgsqlConnection(GetPGSQLCnString());
try
{
pgCn.Open();
NpgsqlCommand pgCmd = new NpgsqlCommand(sSQL, pgCn);
if (aParam != null)
{
foreach (object param in aParam)
{
pgCmd.Parameters.Add(new NpgsqlParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
r = pgCmd.ExecuteScalar();
pgCmd.Dispose();
pgCn.Close();
}
catch (NpgsqlException e)
{
r = new DataSet();
FillExceptionDataSet((DataSet)r, e.ToString());
}
finally
{
if (pgCn.State != 0)
{
pgCn.Close();
}
}
break;
}
return(r);
}
public static DataSet GetDataSet(dbOrigen source, string sSQL, object[] aParam)
{
int i = 1;
DataSet oDs = new DataSet();
switch (source)
{
case dbOrigen.Ninguno:
FillExceptionDataSet(oDs, "No se ha especificado origen de datos.");
break;
case dbOrigen.ORA:
OracleConnection oraCn = new OracleConnection(GetORACnString());
try
{
oraCn.Open();
OracleCommand oraCmd = new OracleCommand(RemoveSemiColon(sSQL), oraCn);
if (aParam != null)
{
foreach (object param in aParam)
{
oraCmd.Parameters.Add(new OracleParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
OracleDataAdapter oraAdaptador = new OracleDataAdapter(oraCmd);
oraAdaptador.Fill(oDs);
oraCmd.Dispose();
oraCn.Close();
}
catch (OracleException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (oraCn.State != 0)
{
oraCn.Close();
}
}
break;
case dbOrigen.PGSQL:
NpgsqlConnection pgCn = new NpgsqlConnection(GetPGSQLCnString());
try
{
pgCn.Open();
NpgsqlCommand pgCmd = new NpgsqlCommand(sSQL, pgCn);
pgCmd.CommandTimeout = DBSettings.PGSQLCmdTimeOut;
if (aParam != null)
{
foreach (object param in aParam)
{
pgCmd.Parameters.Add(new NpgsqlParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
NpgsqlDataAdapter pgAdaptador = new NpgsqlDataAdapter(pgCmd);
pgAdaptador.Fill(oDs);
pgCmd.Dispose();
pgCn.Close();
}
catch (NpgsqlException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (pgCn.State != 0)
{
pgCn.Close();
}
}
break;
}
return(oDs);
}
public static DataSet GetSchemaFromQuery(dbOrigen source, string sSQL, object[] aParam)
{
int i = 1;
DataSet oDs = new DataSet();
switch (source)
{
case dbOrigen.Ninguno:
FillExceptionDataSet(oDs, "No se ha especificado origen de datos.");
break;
case dbOrigen.ORA:
OracleConnection oraCn = new OracleConnection(GetORACnString());
try
{
oraCn.Open();
OracleCommand oraCmd = new OracleCommand(RemoveSemiColon(sSQL), oraCn);
if (aParam != null)
{
foreach (object param in aParam)
{
oraCmd.Parameters.Add(new OracleParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
OracleDataReader oraReader = oraCmd.ExecuteReader(CommandBehavior.SchemaOnly);
oDs.Tables.Add(oraReader.GetSchemaTable());
oraCmd.Dispose();
oraCn.Close();
}
catch (OracleException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (oraCn.State != 0)
{
oraCn.Close();
}
}
break;
case dbOrigen.PGSQL:
NpgsqlConnection pgCn = new NpgsqlConnection(GetPGSQLCnString());
try
{
pgCn.Open();
NpgsqlCommand pgCmd = new NpgsqlCommand(sSQL, pgCn);
if (aParam != null)
{
foreach (object param in aParam)
{
pgCmd.Parameters.Add(new NpgsqlParameter(string.Format("param{0}", i.ToString()), param));
i += 1;
}
}
NpgsqlDataReader pgReader = pgCmd.ExecuteReader(CommandBehavior.SchemaOnly);
oDs.Tables.Add(pgReader.GetSchemaTable());
pgCmd.Dispose();
pgCn.Close();
}
catch (NpgsqlException e)
{
FillExceptionDataSet(oDs, e.ToString());
}
finally
{
if (pgCn.State != 0)
{
pgCn.Close();
}
}
break;
}
return(oDs);
}
public static bool IsColumn(dbOrigen source, string qualifiedName)
{
return(SchemaObjectExists(ColInfoFromName(source, qualifiedName)));
}
public DBSchemaInfo(dbOrigen source, string name)
{
this.Source = source;
this.Name = name;
}
