public static void realizarRestore(String directorio)
{
SqlConnection connection = ConexionSingleton.obtenerConexion();
connection.Open();
directorio = directorio.Replace("//", "\\");
StringBuilder queryText = new StringBuilder();
queryText.Append(" USE MASTER ");
queryText.Append(" alter database " + ConexionSingleton.dbName);
queryText.Append(" set offline with rollback immediate ");
queryText.Append(" RESTORE DATABASE " + ConexionSingleton.dbName);
queryText.Append(" FROM DISK = '" + directorio + "'");
queryText.Append(" WITH REPLACE ");
queryText.Append(" alter database " + ConexionSingleton.dbName);
queryText.Append(" set online with rollback immediate ");
SqlCommand query = new SqlCommand(queryText.ToString(), connection);
try
{
query.ExecuteNonQuery();
connection.Close();
}
catch (Exception e)
{
connection.Close();
BitacoraDAO.grabarBitacora(0, "Falló un restore");
throw e;
}
BitacoraDAO.grabarBitacora(0, "Se realizó un restore");
}
public static User login(String username, String password)
{
SqlConnection cn = ConexionSingleton.obtenerConexion();
cn.Open();
password = CryptoUtils.encriptarMD5(password);
SqlTransaction tx = cn.BeginTransaction();
SqlCommand cmd = new SqlCommand("SELECT * from Usuario where Alias = @id and Password = @password");
cmd.Parameters.Add(new SqlParameter("id", username));
cmd.Parameters.Add(new SqlParameter("password", password));
cmd.Connection = cn;
cmd.Transaction = tx;
SqlDataReader reader = cmd.ExecuteReader();
User user = null;
if (reader.Read())
{
user = new User(reader.GetString(5), reader.GetString(4).Trim(), reader.GetInt32(0), reader.GetInt32(7));
}
reader.Close();
tx.Commit();
cn.Close();
return(user);
}
public static void recalcularDigitoVertical(String tabla)
{
SqlConnection connection = ConexionSingleton.obtenerConexion();
connection.Open();
SqlTransaction tx = connection.BeginTransaction();
String updateCommand = " UPDATE DIGITO_VERTICAL SET DV_DIGITO_CALCULADO = @HASH WHERE DV_NOMBRE_TABLA = @NOMBRE_TABLA ";
SqlCommand query = new SqlCommand("", connection, tx);
StringBuilder sb = new StringBuilder();
sb.Append(" SELECT dvh FROM " + tabla);
query.CommandText = sb.ToString();
SqlDataReader reader;
String sumaDeDVH = "";
try
{
reader = query.ExecuteReader();
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
sumaDeDVH += reader.GetString(0);
}
}
reader.Close();
query.Parameters.Clear();
query.CommandText = updateCommand;
query.Parameters.Add(new SqlParameter("@HASH", System.Data.SqlDbType.VarChar)).Value = CryptoUtils.encriptarMD5(sumaDeDVH);
query.Parameters.Add(new SqlParameter("@NOMBRE_TABLA", System.Data.SqlDbType.VarChar)).Value = tabla;
query.ExecuteNonQuery();
tx.Commit();
connection.Close();
}
catch (Exception ex)
{
try
{
tx.Rollback();
}
catch (Exception ex2)
{
connection.Close();
throw ex2;
}
connection.Close();
throw ex;
}
finally
{
connection.Close();
}
}
protected override void UpdateMethod()
{
command = new MySqlCommand("sp_actualizarCliente", ConexionSingleton.GetConnection());
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@nombre", nombre);
command.Parameters.AddWithValue("@direccion", direccion);
command.Parameters.AddWithValue("@telefono", telefono);
command.Parameters.AddWithValue("@email", email);
command.Parameters.AddWithValue("@domiciliofiscal", domiciliofiscal);
}
protected override void RetriveMethod()
{
dataset.Clear();
command = new MySqlCommand("sp_buscarClientes", ConexionSingleton.GetConnection());
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@nombre", nombre);
command.Parameters.AddWithValue("@tel", telefono);
dataadapter = new MySqlDataAdapter(command);
dataadapter.Fill(dataset);
}
public static void wrongLogin(String username)
{
SqlConnection cn = ConexionSingleton.obtenerConexion();
cn.Open();
SqlTransaction tx = cn.BeginTransaction();
SqlCommand cmd = new SqlCommand("UPDATE USUARIO SET INTENTOS = INTENTOS + 1 WHERE ALIAS = @ALIAS");
cmd.Parameters.Add(new SqlParameter("ALIAS", username));
cmd.Connection = cn;
cmd.Transaction = tx; cmd.ExecuteNonQuery();
tx.Commit();
cn.Close();
}
public static void grabarBitacora(int usuId, String mensaje)
{
SqlConnection connection = ConexionSingleton.obtenerConexion();
connection.Open();
SqlTransaction tx = connection.BeginTransaction();
StringBuilder builder = new StringBuilder(" INSERT INTO BITACORA (");
builder.Append("idUsuario,");
builder.Append("mensaje,");
builder.Append("dvh )");
builder.Append(" VALUES (");
builder.Append(" @USUARIO,");
builder.Append(" @MENSAJE,");
builder.Append(" @DVH");
builder.Append(" ) ");
SqlCommand cmd = new SqlCommand(builder.ToString(), connection, tx);
DateTime fecha = DateTime.Now;
cmd.Parameters.Add(new SqlParameter("@MENSAJE", System.Data.SqlDbType.Text)).Value = mensaje;
cmd.Parameters.Add(new SqlParameter("@DVH", System.Data.SqlDbType.VarChar)).Value = DigitosDAO.recalcularDigitoHorizontal(new string[] { fecha.ToString(), mensaje });
cmd.Parameters.Add(new SqlParameter("@USUARIO", System.Data.SqlDbType.BigInt)).Value = usuId;
try
{
cmd.ExecuteNonQuery();
tx.Commit();
connection.Close();
DigitosDAO.recalcularDigitoVertical("BITACORA");
}
catch (Exception ex)
{
try
{
tx.Rollback();
}
catch (Exception)
{
}
connection.Close();
throw ex;
}
}
public static List <Bitacora> execute(String desde, String hasta)
{
SqlConnection cn = ConexionSingleton.obtenerConexion();
cn.Open();
SqlTransaction tx = cn.BeginTransaction();
StringBuilder sb = new StringBuilder();
SqlCommand cmd = new SqlCommand();
List <Bitacora> registros = new List <Bitacora>();
sb.Append(" SELECT * from vistaBitacora ");
sb.Append(!String.IsNullOrEmpty(desde) || !String.IsNullOrEmpty(hasta) ? " where " : "");
if (!String.IsNullOrEmpty(desde))
{
sb.Append(" fecha >= @desde");
cmd.Parameters.Add(new SqlParameter("desde", desde));
}
if (!String.IsNullOrEmpty(desde) && !String.IsNullOrEmpty(hasta))
{
sb.Append(" and ");
}
if (!String.IsNullOrEmpty(hasta))
{
sb.Append(" fecha <= @hasta");
cmd.Parameters.Add(new SqlParameter("hasta", hasta));
}
cmd.CommandText = sb.ToString();
cmd.Connection = cn;
cmd.Transaction = tx;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
registros.Add(new Bitacora(reader.GetDateTime(0), reader.GetString(2), reader.GetString(1)));
}
reader.Close();
tx.Commit();
cn.Close();
return(registros);
}
public static void realizarBackup(int partes, String directorio)
{
SqlConnection connection = ConexionSingleton.obtenerConexion();
connection.Open();
StringBuilder queryText = new StringBuilder();
directorio = directorio.Replace("//", "\\");
queryText.Append(" USE MASTER ");
queryText.Append(" BACKUP DATABASE " + ConexionSingleton.dbName);
for (int i = 0; i < partes; i++)
{
if (i == 0)
{
queryText.Append(" TO DISK = '" + directorio + (i + 1) + ".bak '");
}
else
{
queryText.Append(" , DISK = '" + directorio + (i + 1) + ".bak '");
}
}
queryText.Append(" WITH init ");
SqlCommand query = new SqlCommand(queryText.ToString(), connection);
try
{
query.ExecuteNonQuery();
connection.Close();
BitacoraDAO.grabarBitacora(0, "Se realizó un backup");
}
catch (Exception e)
{
connection.Close();
BitacoraDAO.grabarBitacora(0, "Falló un backup");
throw e;
}
}
public static void verificarDigitosVerificadores()
{
SqlConnection connection = ConexionSingleton.obtenerConexion();
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
connection.Open();
SqlTransaction tr = connection.BeginTransaction();
SqlDataReader reader = null;
SqlCommand cmd = new SqlCommand("", connection, tr);
Dictionary <String, String> digitoVerticalCalculado = new Dictionary <string, string>();
StringBuilder stringParaDVH = new StringBuilder();
StringBuilder builder = new StringBuilder();
List <String> mensajesDeError = new List <string>(); // la lista en donde voy a cargar todos los mensajes de error
String query = "SELECT id, fecha, mensaje, dvh FROM BITACORA ";
cmd.CommandText = query;
builder = new StringBuilder();
reader = cmd.ExecuteReader();
while (reader.Read())
{
builder.Clear();
builder.Append(reader.GetValue(1).ToString());
builder.Append(reader.GetValue(2).ToString());
String md5 = CryptoUtils.encriptarMD5(builder.ToString());
String patDVH = reader.GetString(3);
if (!md5.Equals(patDVH))
{
int id = (int)reader.GetValue(0);
mensajesDeError.Add("Falló la integridad de datos en bitácora en el id " + id.ToString());
}
stringParaDVH.Append(md5);
}
digitoVerticalCalculado.Add("BITACORA", stringParaDVH.ToString());
reader.Close();
stringParaDVH.Clear();
query = " SELECT DV_NOMBRE_TABLA,DV_DIGITO_CALCULADO,dv_id FROM DIGITO_VERTICAL ";
cmd.CommandText = query;
reader = cmd.ExecuteReader();
while (reader.Read())
{
builder.Clear();
string tabla = reader.GetValue(0).ToString();
string md5Base = reader.GetValue(1).ToString();
string md5Calculado = digitoVerticalCalculado[tabla];
md5Calculado = CryptoUtils.encriptarMD5(md5Calculado);
if (!md5Base.Equals(md5Calculado))
{
long id = (long)reader.GetValue(2);
mensajesDeError.Add("Falló la integridad de datos en digito vertical en el row " + id.ToString());
}
}
reader.Close();
connection.Close();
if (mensajesDeError.Count > 0)
{
foreach (String item in mensajesDeError)
{
BitacoraDAO.grabarBitacora(0, item);
}
throw new Exception("Falló la integridad de datos.");
}
}
public static void insertsDePatentes()
{
SqlConnection connection = ConexionSingleton.obtenerConexion();
StringBuilder sb = new StringBuilder();
sb.Append("insert into patente(pat_desc,pat_dvh) values('Crear Alumno',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Modificar Alumno',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Ver el listado de alumnos',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Borrar un Alumno',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Registrar Inasistencia',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Registrar Amonestación',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Crear Tutor',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Modificar Tutor',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Borrar Tutor',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Listar Tutores',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Crear Usuario',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Modificar Usuario',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Borrar un Usuario',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Listar Usuarios',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Regenerar Contraseña',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Generar Backups',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Restaurar desde Backup',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Recalcular Dígitos Verificadores',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Ver Bitácora',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Bloquear Usuario',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Modificar Familias',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Listar Familias',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Crear Familia',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Borrar Familia',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Crear Horario',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Listar Horarios',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Modificar Horario',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Borrar Horario',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Crear Curso',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Borrar Curso',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Modificar Curso',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Listar Cursos',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Promocionar Alumnos',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Crear Materia',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Modificar Materia',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Listar Materia',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Borrar Materia',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Asignar Materia a Nivel',0)");
sb.Append("insert into patente(pat_desc,pat_dvh) values('Generar Reportes',0)");
SqlCommand query = new SqlCommand("", connection);
query.CommandText = sb.ToString();
connection.Open();
query.ExecuteNonQuery();
List <Patente> patentes = new List <Patente>();
SqlDataReader reader;
query.CommandText = " SELECT * FROM PATENTE";
reader = query.ExecuteReader();
while (reader.Read())
{
Patente pat = new Patente();
pat.descripcion = reader["PAT_DESC"].ToString();
pat.id = (long)reader["PAT_ID"];
patentes.Add(pat);
}
reader.Close();
foreach (Patente item in patentes)
{
SqlCommand query2 = new SqlCommand("UPDATE PATENTE SET PAT_DESC = @DESC WHERE PAT_ID = @ID", connection);
query2.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.BigInt)).Value = item.id;
query2.Parameters.Add(new SqlParameter("@DESC", System.Data.SqlDbType.NVarChar)).Value = SeguridadUtiles.encriptarAES(item.descripcion);
query2.ExecuteNonQuery();
}
connection.Close();
}
protected override void DeleteMethod()
{
command = new MySqlCommand("sp_eliminarCliente", ConexionSingleton.GetConnection());
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@id", id);
}