public ArrayList RequestGlobalAsCollection(string sql)
{
ArrayList result = null;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
DB2DataAdapter adapter = new DB2DataAdapter(sql, connection);
DataSet ds = new DataSet();
adapter.Fill(ds);
if (ds.Tables.Count > 1)
{
throw new Exception("Consulta inválida");
}
ArrayList list = new ArrayList(ds.Tables[0].Rows.Count);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Hashtable table = new Hashtable();
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
DataColumn col = ds.Tables[0].Columns[j];
Object data = ds.Tables[0].Rows[i][j];
table.Add(col.ColumnName, data);
}
list.Add(table);
}
result = list;
return(result);
}
public static void CrearCarpetaRegistro(CarpetaRegistro carpetaRegistro)
{
if (!AdministradorCarpetasRegistro.ExisteCarpetaRegistro(carpetaRegistro))
{
Directory.CreateDirectory(carpetaRegistro.CarpetaRaizCompleto);
}
}
public DataSet Request(DataSet ds, IncludeSchema isEnum, string sql)
{
SqlConnection lc = OpenConnection();
try
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
sql = esquemaDB(sql);
SqlDataAdapter adapter = new SqlDataAdapter(sql, lc);
if (isEnum == IncludeSchema.YES)
{
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
}
}
catch { }
CloseConnection(lc);
return(ds);
}
public DataSet RequestGlobal(DataSet ds, IncludeSchema isEnum, string dataBase, string sql)
{
DB2Connection lc;
if (dataBase == "")
{
lc = OpenConnectionGlobal();
}
else
{
lc = OpenConnectionGlobal(dataBase);
}
try
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
DB2DataAdapter adapter = new DB2DataAdapter(sql, lc);
if (isEnum == IncludeSchema.YES)
{
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
}
}
catch { }
CloseConnection(lc);
return(ds);
}
public DataSet Request(DataSet ds, IncludeSchema isEnum, string nombreProcedimiento, IDictionaryEnumerator parametros)
{
DB2Connection lc = OpenConnection();
try
{
DB2Command comm = new DB2Command(nombreProcedimiento, lc);
comm.CommandType = System.Data.CommandType.StoredProcedure;
if (parametros != null)
{
while (parametros.MoveNext())
{
comm.Parameters.Add(parametros.Key.ToString(), parametros.Value);
}
}
DB2DataAdapter adapter = new DB2DataAdapter(comm);
if (isEnum == IncludeSchema.YES)
{
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
}
}
catch (Exception e)
{
exceptions = "Error ejecutando SQL." + cambioLinea + cambioLinea;
exceptions += e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, nombreProcedimiento, e, exceptions);
}
CloseConnection(lc);
return(ds);
}
public SqlDataReader DataReader(string sql)
{
SqlCommand oc = new SqlCommand();
try
{
sql = esquemaDB(sql);
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
oc.CommandText = sql;
SqlConnection con = OpenConnection();
connection = con;
oc.Connection = con;
SqlDataReader dr = oc.ExecuteReader();
CloseConnection(con);
return(dr);
}
catch (Exception e)
{
exceptions = "Error ejecutando SQL." + cambioLinea + cambioLinea;
exceptions += e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, exceptions);
return(null);
}
}
private OleDbConnection OpenConnection()
{
OleDbConnection con;
if (dataSource.EndsWith("X") || dataSource.EndsWith("x")) //Microsoft.ACE.OLEDB.12.0
{
con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dataSource + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"");
}
else
{
con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataSource + ";Extended Properties=Excel 8.0;");
}
try
{
con.Open();
exceptions += cambioLinea + "Se ha abierto el archivo " + dataSource;
}
catch (Exception e)
{
exceptions += cambioLinea + "Error al abrir el archivo " + dataSource;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, string.Empty, e, exceptions);
}
return(con);
}
public int UpdateHashtable(String tableName, Hashtable hashData, Hashtable hashPk)
{
DB2Connection ncc = OpenConnection();
DB2Command com = null;
String sql = "UPDATE {0} SET {1} WHERE {2};";
int affectedRows = 0;
try
{
string set = "";
string where = "";
foreach (string key in hashData.Keys)
{
string col = key;
string value = hashData[key].ToString();
if (!set.Equals(""))
{
set += ",";
}
set += String.Format("{0} = {1}", col, value);
}
foreach (string key in hashPk.Keys)
{
string col = key;
string value = hashPk[key].ToString();
if (!where.Equals(""))
{
where += " AND ";
}
where += String.Format("{0} = {1}", col, value);
}
sql = string.Format(sql, tableName, set, where);
com = new DB2Command(sql, ncc);
affectedRows = com.ExecuteNonQuery();
QueryCache.removeData(tableName); //limpiando caché de la tabla...
HistorialSeguimientoTabla(TABLA_SEGUIR, sql); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, e.Message);
return(-1);
}
finally
{
CloseConnection(ncc);
}
return(affectedRows);
}
public static ArchivoRegistro CargarArchivoRegistro(CarpetaRegistro carpetaRegistro)
{
if (AdministradorCarpetasRegistro.ExisteArchivoRegistro(carpetaRegistro))
{
return((ArchivoRegistro)SerializacionXML.DeserializarObjetoArchivo(typeof(ArchivoRegistro), carpetaRegistro.NombreArchivoCompleto));
}
else
{
return(new ArchivoRegistro());
}
}
private void HistorialSeguimientoTabla(String tablaSeguir, String sqlRevision)
{
sqlRevision = sqlRevision.Replace("'", "");
sqlRevision = sqlRevision.ToUpper();
tablaSeguir = tablaSeguir.ToUpper();
RegexOptions options = RegexOptions.None;
Regex regex = new Regex(@"[ ]{2,}", options);
sqlRevision = regex.Replace(sqlRevision, @" ");
if ((sqlRevision.Contains("UPDATE " + tablaSeguir) == true || sqlRevision.Contains("INSERT INTO " + tablaSeguir) == true || sqlRevision.Contains("DELETE FROM " + tablaSeguir) == true) &&
sqlRevision.Contains("MHISTORIAL_CAMBIOS") == false)
{
string usuario = HttpContext.Current.User.Identity.Name.ToLower();
string operacion = "";
if (sqlRevision.Contains("UPDATE"))
{
operacion = "U";
}
else if (sqlRevision.Contains("DELETE"))
{
operacion = "D";
}
else if (sqlRevision.Contains("INSERT"))
{
operacion = "I";
}
DB2Command command = new DB2Command();
DB2Connection con = OpenConnection();
command.Connection = con;
DB2Transaction trans = con.BeginTransaction();
command.Transaction = trans;
string sqlHistorial = "INSERT INTO MHISTORIAL_CAMBIOS VALUES (DEFAULT,'" + tablaSeguir + "','" + operacion + "','" + sqlRevision + "','" + usuario + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "');";
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sqlHistorial, null, string.Empty);
command.CommandText = sqlHistorial;
try
{
command.ExecuteNonQuery();
trans.Commit();
exceptions += "ejecutando: " + tablaSeguir + cambioLinea;
}
catch (Exception ex)
{
exceptions += String.Format("error ejecutando: {0} \n {1} \n", tablaSeguir, ex.Message);
}
}
}
public string SingleDataGlobal(string sql)
{
string val;
DB2Command command = new DB2Command();
command.Connection = connection;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
command.CommandText = sql;
val = Convert.ToString(command.ExecuteScalar());
return(val);
}
public int DeleteHashtable(string tableName, Hashtable hashPk)
{
SqlConnection ncc = OpenConnection();
SqlCommand com = null;
String sql = "DELETE FROM {0} WHERE {1};";
int affectedRows = 0;
try
{
string where = "";
foreach (string key in hashPk.Keys)
{
string col = key;
string value = hashPk[key].ToString();
if (!where.Equals(""))
{
where += " AND ";
}
int n = 0;
if (int.TryParse(value, out n))
{
where += String.Format("{0} = {1}", col, value);
}
else
{
where += String.Format("{0} = '{1}'", col, value);
}
}
sql = string.Format(sql, tableName, where);
com = new SqlCommand(sql, ncc);
affectedRows = com.ExecuteNonQuery();
QueryCache.removeData(tableName); //limpiando caché de la tabla...
HistorialSeguimientoTabla(TABLA_SEGUIR, sql); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, e.Message);
return(-1);
}
finally
{
CloseConnection(ncc);
}
return(affectedRows);
}
public bool SaveHashtable(String tableName, Hashtable hash)
{
DB2Connection ncc = OpenConnection();
DB2Command com = null;
String sql = "INSERT INTO {0} ({1}) VALUES ({2});";
int affectedRows = 0;
try
{
string cols = "";
string values = "";
foreach (string key in hash.Keys)
{
string col = key;
string value = hash[key].ToString();
if (!cols.Equals(""))
{
cols += ",";
}
if (!values.Equals(""))
{
values += ",";
}
cols += col;
values += value;
}
sql = string.Format(sql, tableName, cols, values);
com = new DB2Command(sql, ncc);
affectedRows = com.ExecuteNonQuery();
QueryCache.removeData(tableName); //limpiando caché de la tabla...
HistorialSeguimientoTabla(TABLA_SEGUIR, sql); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, e.Message);
//exceptions += e.ToString() + cambioLinea;
}
finally
{
CloseConnection(ncc);
}
return(affectedRows == 1);
}
public SqlConnection OpenConnection()
{
//if (connection != null && connection.IsOpen) return connection;
string algo = HttpContext.Current.Request.Url.AbsolutePath;
string algo2 = HttpContext.Current.Request.Url.AbsoluteUri;
string host = HttpContext.Current.Request.Url.Host;
string servidor = ConfigurationManager.AppSettings["Server" + GlobalData.getEMPRESA()];
string database = ConfigurationManager.AppSettings["DataBase" + GlobalData.getEMPRESA()];
string usuario = ConfigurationManager.AppSettings["UID"];
string schema = ConfigurationManager.AppSettings["SCHEMA"];
string password = ConfigurationManager.AppSettings["PWD" + GlobalData.getEMPRESA()];
string timeout = ConfigurationManager.AppSettings["ConnectionTimeout"];
string port = ConfigurationManager.AppSettings["DataBasePort"];
AMS.CriptoServiceProvider.Crypto miCripto = new Crypto(AMS.CriptoServiceProvider.Crypto.CryptoProvider.TripleDES);
miCripto.IV = ConfigurationManager.AppSettings["VectorInicialEncriptacion"];
miCripto.Key = ConfigurationManager.AppSettings["ValorConcatClavePrivada"];
string newPwd = miCripto.DescifrarCadena(password);
//connectionString = "data source=ECASMIN\\MSSQLSERVER2;initial catalog=MAZKOP;user id=sa;password=.ecas2010.;";
//connectionString = "data source=LAPTOP1\\caldana2,1433;initial catalog=SQLAKORE;user id=sa;password=.ecas2010.;";
//connectionString = "data source=" + servidor + "\\caldana2,1433;initial catalog=SQLAKORE;user id=sa;password=.ecas2010.;";
//connectionString = "data source=50.23.209.233\\db2admin,1433; initial catalog=SQLAKORE"+
// ";Integrated Security=False;user id=sa;password=.ecas2010.; ";
//connectionString = "data source=192.168.0.5\\caldana2,1433; initial catalog=SQLAKORE" +
// ";Integrated Security=False;user id=sa;password=.ecas2010.; ";
connectionString = "data source=" + servidor + "\\" + schema + "," + port + "; initial catalog=" + database +
";Integrated Security=False;user id=" + usuario + ";password=.ecas2010.; ";
string aaa = HttpContext.Current.User.Identity.Name.ToLower();
SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
//SetSchema(connection);
exceptions = "Se ha abierto una conexión con la base de datos:";
exceptions += connection.Database + " en UDB SQL SERVER " + connection.ServerVersion + cambioLinea;
}
catch (Exception e)
{
exceptions = "Error al conectar con la base de datos especificada." + cambioLinea + cambioLinea;
exceptions += e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, string.Empty, e, connectionString);
}
return(connection);
}
public DB2Connection OpenConnection()
{
string servidor;
string database;
string usuario;
string password;
//para 2015
servidor = ConfigurationManager.AppSettings["Server" + GlobalData.getEMPRESA()];
database = ConfigurationManager.AppSettings["DataBase" + GlobalData.getEMPRESA()];
usuario = ConfigurationManager.AppSettings["UID"];
password = ConfigurationManager.AppSettings["PWD" + GlobalData.getEMPRESA()];
string timeout = ConfigurationManager.AppSettings["ConnectionTimeout"];
string port = ConfigurationManager.AppSettings["DataBasePort"];
AMS.CriptoServiceProvider.Crypto miCripto = new Crypto(AMS.CriptoServiceProvider.Crypto.CryptoProvider.TripleDES);
miCripto.IV = ConfigurationManager.AppSettings["VectorInicialEncriptacion"];
miCripto.Key = ConfigurationManager.AppSettings["ValorConcatClavePrivada"];
string newPwd = miCripto.DescifrarCadena(password);
connectionString = "Server=" + servidor + ":" + port + ";DataBase=" + database + ";UID=" + usuario + ";PWD=" + newPwd + ";QueryTimeout=3600";
if (timeout != null)
{
connectionString += ";Connection Timeout=" + timeout;
}
string aaa = HttpContext.Current.User.Identity.Name.ToLower();
DB2Connection connection = new DB2Connection(connectionString);
try
{
connection.Open();
SetSchema(connection);
exceptions = "Se ha abierto una conexión con la base de datos:";
exceptions += connection.Database + " en UDB DB2 " + connection.ServerVersion + cambioLinea;
}
catch (Exception e)
{
exceptions = "Error al conectar con la base de datos especificada." + cambioLinea + cambioLinea;
exceptions += e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, string.Empty, e, connectionString);
}
return(connection);
}
public DataSet Request(DataSet ds, IncludeSchema isEnum, string sql)
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
DB2DataAdapter adapter = new DB2DataAdapter(sql, connection);
if (isEnum == IncludeSchema.YES)
{
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
}
return(ds);
}
public void CloseConnection()
{
try
{
connection.Close();
exceptions += "se ha cerrado la conexión" + cambioLinea;
}
catch (Exception e)
{
exceptions = "Error al cerrar la conexión con la base de datos" + cambioLinea + cambioLinea;
exceptions += e.ToString();
if (connectionString == null)
{
connectionString = "null connectionString";
}
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, string.Empty, e, connectionString);
}
}
public string SingleDataGlobal(string sql)
{
string val = "";
SqlCommand command = new SqlCommand();
SqlConnection con = OpenConnection();
command.Connection = con;
sql = esquemaDB(sql);
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
command.CommandText = sql;
try
{
val = Convert.ToString(command.ExecuteScalar());
}
catch { }
CloseConnection(con);
return(val);
}
public static void GrabarLogs(TipoRegistro tipoRegistro, string sql, Exception excepcion, string observaciones)
{
string pathlogs = ConfigurationManager.AppSettings["PathToLogs"];
string aplicalogs = ConfigurationManager.AppSettings["AplicaLogs"];
string usuario;
string nombreArchivo = "Registro";
if (HttpContext.Current != null)
{
usuario = HttpContext.Current.User.Identity.Name.ToString().ToLower();
}
else
{
usuario = "automatico";
}
bool registrarLog = false;
if (tipoRegistro != TipoRegistro.Actividad || aplicalogs == "true")
{
registrarLog = true;
}
if (registrarLog)
{
if (pathlogs == "")
{
throw new Exception(excepcion.ToString()); //return;
}
CarpetaRegistro carpetaRegistro = new CarpetaRegistro(pathlogs, usuario, nombreArchivo);
ArchivoRegistro archivoRegistro = AdministradorCarpetasRegistro.CargarArchivoRegistro(carpetaRegistro);
Registro registro = new Registro(usuario, tipoRegistro, sql, excepcion, observaciones);
archivoRegistro.AgregarRegistro(registro);
AdministradorCarpetasRegistro.GuardarArchivoRegistro(carpetaRegistro, archivoRegistro);
}
}
public bool SetSchema(DB2Connection con)
{
bool status = false;
DB2Command sql = new DB2Command();
sql.Connection = con;
sql.CommandText = "SET SCHEMA " + schema;
try
{
sql.ExecuteNonQuery();
status = true;
}
catch (Exception e)
{
exceptions = "Error al ejecutar: " + cambioLinea + cambioLinea;
exceptions += e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql.CommandText, e, exceptions);
status = false;
}
return(status);
}
public bool RecordExist(string sql)
{
bool exist = false;
DB2Command command = new DB2Command();
DB2Connection con = OpenConnection();
command.Connection = con;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
command.CommandText = sql;
try
{
DB2DataReader db2dr = command.ExecuteReader();
exist = db2dr.Read();
db2dr.Close(); // <Observacion>
}
catch (Exception e)
{
exceptions += e.ToString() + cambioLinea;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, sql, e, exceptions);
}
CloseConnection(con);
return(exist);
}
public DataSet Request(DataSet ds, IncludeSchema isEnum, string sql)
{
DB2Connection lc = OpenConnection();
try
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, sql, null, string.Empty);
DB2DataAdapter adapter = new DB2DataAdapter(sql, lc);
if (isEnum == IncludeSchema.YES)
{
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, "result_ " + ds.Tables.Count.ToString());
}
}
catch (Exception z) { this.exceptions = z.Message; }
CloseConnection(lc);
return(ds);
}
public bool Transaction(ArrayList sql)
{
int numQueries = 0, i = 0;
bool status = false;
DB2Command command = new DB2Command();
DB2Connection con = OpenConnection();
command.Connection = con;
DB2Transaction trans = con.BeginTransaction();
command.Transaction = trans;
for (i = 0; i < sql.Count; i++)
{
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, (string)sql[i], null, string.Empty);
command.CommandText = sql[i].ToString();
try
{
command.ExecuteNonQuery();
exceptions += "ejecutando: " + sql[i] + cambioLinea;
numQueries++;
HistorialSeguimientoTabla(TABLA_SEGUIR, sql[i].ToString()); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception ex)
{
exceptions += String.Format("error ejecutando: {0} \n {1} \n", sql[i], ex.Message);
status = false;
}
}
if (numQueries == sql.Count)
{
try
{
trans.Commit();
exceptions += "ejecutando Commit -- " + cambioLinea;
status = true;
}
catch (Exception e)
{
exceptions += "Error ejecutando Commit: " + e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, string.Empty, e, exceptions);
status = false;
}
}
else
{
try
{
trans.Rollback();
}
catch (Exception e)
{
exceptions += "Error ejecutando RollBack: " + e.ToString();
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, string.Empty, e, exceptions);
}
numQueries = 0;
}
CloseConnection(con);
return(status);
}
public DataSet Update(DataSet ds, int index)
{
int i = 0;
string table = "Table";
string fieldsString = " SET ";
string whereString = " WHERE ";
string[] sqlWords = sqlSelect.Split(' ');
for (i = 0; i < sqlWords.Length; i++)
{
if (sqlWords[i] == "FROM")
{
table = sqlWords[i + 1];
break;
}
}
for (i = 0; i < ds.Tables[index].Columns.Count; i++)
{
fieldsString += ds.Tables[index].Columns[i].ColumnName + " = ?";
if (i != ds.Tables[index].Columns.Count - 1)
{
fieldsString += ", ";
}
}
for (i = 0; i < ds.Tables[index].PrimaryKey.Length; i++)
{
whereString += ds.Tables[index].PrimaryKey[i] + " = ?";
if (i != ds.Tables[index].PrimaryKey.Length - 1)
{
whereString += " AND ";
}
}
DB2Connection dc = OpenConnection();
DB2DataAdapter adapter = new DB2DataAdapter(sqlSelect, dc);
DB2Command updateCommand = new DB2Command("UPDATE " + table + fieldsString + whereString, adapter.SelectCommand.Connection);
exceptions += "UPDATE " + table + fieldsString + whereString + cambioLinea;
for (i = 0; i < ds.Tables[index].Columns.Count; i++)
{
if (ds.Tables[index].Columns[i].DataType.ToString() == "System.String")
{
updateCommand.Parameters.Add("@" + ds.Tables[index].Columns[i] + "", (DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType], ds.Tables[index].Columns[i].MaxLength, ds.Tables[index].Columns[i].ColumnName);
}
else
{
updateCommand.Parameters.Add("@" + ds.Tables[index].Columns[i] + "", (DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType], (int)lengthsRelation[(DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType]], ds.Tables[index].Columns[i].ColumnName);
}
}
for (i = 0; i < ds.Tables[index].PrimaryKey.Length; i++)
{
if (ds.Tables[index].PrimaryKey[i].DataType.ToString() == "System.String")
{
parm = updateCommand.Parameters.Add("@old" + ds.Tables[index].PrimaryKey[i] + "", (DB2Type)typesRelation[ds.Tables[index].PrimaryKey[i].DataType], ds.Tables[index].PrimaryKey[i].MaxLength, ds.Tables[index].PrimaryKey[i].ColumnName);
}
else
{
parm = updateCommand.Parameters.Add("@old" + ds.Tables[index].PrimaryKey[i] + "", (DB2Type)typesRelation[ds.Tables[index].PrimaryKey[i].DataType], (int)lengthsRelation[(DB2Type)typesRelation[ds.Tables[index].PrimaryKey[i].DataType]], ds.Tables[index].PrimaryKey[i].ColumnName);
}
parm.SourceVersion = DataRowVersion.Original;
}
exceptions += updateCommand.Parameters.Count.ToString() + " Parameters" + cambioLinea;
adapter.UpdateCommand = updateCommand;
updateStatus = false;
try
{
affectedRows = adapter.Update(ds.Tables[index]);
updateStatus = true;
HistorialSeguimientoTabla(TABLA_SEGUIR, sqlSelect); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
exceptions += e.ToString() + cambioLinea;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, Sql, e, exceptions);
}
CloseConnection(dc);
return(ds);
}
public DataSet Delete(DataSet ds, int index)
{
int i = 0;
string table = "Table";
string whereString = " WHERE ";
string[] sqlWords = sqlSelect.Split(' ');
for (i = 0; i < sqlWords.Length; i++)
{
if (sqlWords[i] == "FROM")
{
table = sqlWords[i + 1];
break;
}
}
for (i = 0; i < ds.Tables[0].PrimaryKey.Length; i++)
{
whereString += ds.Tables[0].PrimaryKey[i] + " = ?";
if (i != ds.Tables[0].PrimaryKey.Length - 1)
{
whereString += " AND ";
}
}
DB2Connection dc = OpenConnection();
DB2DataAdapter adapter = new DB2DataAdapter(sqlSelect, dc);
Sql = "DELETE FROM " + table + " " + whereString + "";
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, Sql, null, String.Empty);
DB2Command deleteCommand = new DB2Command(Sql, adapter.SelectCommand.Connection);
for (i = 0; i < ds.Tables[0].PrimaryKey.Length; i++)
{
if (ds.Tables[0].PrimaryKey[i].DataType.ToString() == "System.String")
{
deleteCommand.Parameters.Add("@" + ds.Tables[0].PrimaryKey[i] + "", (DB2Type)typesRelation[ds.Tables[0].PrimaryKey[i].DataType], ds.Tables[0].PrimaryKey[i].MaxLength, ds.Tables[0].PrimaryKey[i].ColumnName);
}
else
{
deleteCommand.Parameters.Add("@" + ds.Tables[0].PrimaryKey[i] + "", (DB2Type)typesRelation[ds.Tables[0].PrimaryKey[i].DataType], (int)lengthsRelation[(DB2Type)typesRelation[ds.Tables[0].PrimaryKey[i].DataType]], ds.Tables[0].PrimaryKey[i].ColumnName);
}
}
adapter.DeleteCommand = deleteCommand;
deleteStatus = false;
try
{
affectedRows = adapter.Update(ds.Tables[index]);
deleteStatus = true;
HistorialSeguimientoTabla(TABLA_SEGUIR, sqlSelect); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
exceptions += e.ToString() + cambioLinea;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, Sql, e, exceptions);
}
CloseConnection(dc);
return(ds);
}
public DataSet Insert(DataSet ds, int index)
{
int i = 0;
string table = "Table";
string fieldsString = "(";
string valuesString = "(";
string[] sqlWords = sqlSelect.Split(' ');
for (i = 0; i < sqlWords.Length; i++)
{
if (sqlWords[i] == "FROM")
{
table = sqlWords[i + 1];
break;
}
}
for (i = 0; i < ds.Tables[index].Columns.Count; i++)
{
fieldsString += ds.Tables[index].Columns[i].ColumnName;
valuesString += "?";
if (i != ds.Tables[index].Columns.Count - 1)
{
fieldsString += ", ";
valuesString += ", ";
}
}
fieldsString += ")";
valuesString += ")";
DB2Connection dc = OpenConnection();
DB2DataAdapter adapter = new DB2DataAdapter(sqlSelect, dc);
Sql = "INSERT INTO " + table + " " + fieldsString + " VALUES " + valuesString + "";
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Actividad, Sql, null, string.Empty);
DB2Command insertCommand = new DB2Command(Sql, adapter.SelectCommand.Connection);
//exceptions += ds.Tables[0].Columns[0].DataType.ToString() + ":::" + cambioLinea;
for (i = 0; i < ds.Tables[index].Columns.Count; i++)
{
if (ds.Tables[index].Columns[i].DataType.ToString() == "System.String")
{
insertCommand.Parameters.Add("@" + ds.Tables[index].Columns[i] + "", (DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType], ds.Tables[index].Columns[i].MaxLength, ds.Tables[index].Columns[i].ColumnName);
}
else
{
insertCommand.Parameters.Add("@" + ds.Tables[index].Columns[i] + "", (DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType], (int)lengthsRelation[(DB2Type)typesRelation[ds.Tables[index].Columns[i].DataType]], ds.Tables[index].Columns[i].ColumnName);
}
}
adapter.InsertCommand = insertCommand;
insertStatus = false;
try
{
affectedRows = adapter.Update(ds.Tables[index]);
insertStatus = true;
HistorialSeguimientoTabla(TABLA_SEGUIR, sqlSelect); //Almacenamiento historial de seguimiento a tabla.
}
catch (Exception e)
{
exceptions += e.ToString() + "INSERT INTO " + table + " " + fieldsString + " VALUES " + valuesString + "" + cambioLinea;
exceptions += "parameters: " + insertCommand.Parameters.Count.ToString() + cambioLinea;
AdministradorCarpetasRegistro.GrabarLogs(TipoRegistro.Error, Sql, e, exceptions);
}
CloseConnection(dc);
return(ds);
}
public static void GuardarArchivoRegistro(CarpetaRegistro carpetaRegistro, ArchivoRegistro archivoRegistro)
{
AdministradorCarpetasRegistro.CrearCarpetaRegistro(carpetaRegistro);
SerializacionXML.SerializarObjetoArchivo(archivoRegistro, carpetaRegistro.NombreArchivoCompleto);
}
