/// <summary> /// Los contactos no mantiene relaciones por lo que se utiliza directamente un drop en ellos /// </summary> public void DropContacto(Modelo.Contacto c, int Lista) { SqlConnection connection = null; try { connection = GetConnection(); connection.Open(); SqlCommand cmd = connection.CreateCommand(); string insertContacto = "DELETE FROM DesgloseContactos WHERE Persona = @IdPersona " + "AND ListaContactos = @IdLista"; cmd.CommandText = insertContacto; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@IdPersona", c.IdPersona); cmd.Parameters.AddWithValue("@IdLista", Lista); cmd.ExecuteNonQuery(); connection.Close(); } catch (Exception ex) { if (connection != null) { connection.Close(); } throw ex; } }
/// <summary> /// Actualiza los datos de un contacto en la base de datos /// </summary> /// <param name="c">Contacto a actualizar con los datos nuevos que tendrá</param> public void UpdateContacto(Modelo.Contacto c) { SqlConnection connection = null; SqlTransaction transaction = null; try { connection = GetConnection(); connection.Open(); SqlCommand cmd = connection.CreateCommand(); string insertContacto = "UPDATE Personas SET " + "Nombre = @Nombre," + "ApellidoPaterno = @ApellidoPaterno, " + "ApellidoMaterno = @ApellidoMaterno, " + "Telefono = @Telefono, " + "CorreoElectronico = @CorreoElectronico, " + "Calle = @Calle, " + "Numero = @Numero, " + "Colonia = @Colonia " + "WHERE IdPersona = @IdPersona"; cmd.CommandText = insertContacto; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Nombre", c.Nombre); cmd.Parameters.AddWithValue("@ApellidoPaterno", c.ApellidoPaterno); cmd.Parameters.AddWithValue("@ApellidoMaterno", c.ApellidoMaterno); cmd.Parameters.AddWithValue("@Telefono", c.Telefono); cmd.Parameters.AddWithValue("@CorreoElectronico", c.CorreoElectronico); cmd.Parameters.AddWithValue("@Calle", c.Calle); cmd.Parameters.AddWithValue("@Numero", c.Numero); cmd.Parameters.AddWithValue("@Colonia", c.Colonia); cmd.Parameters.AddWithValue("@IdPersona", c.IdPersona); Utils.ClearNullParameterValues(cmd.Parameters); cmd.ExecuteNonQuery(); connection.Close(); } catch (Exception ex) { if (transaction != null) { transaction.Rollback(); } if (connection != null) { connection.Close(); } throw ex; } }
/// <summary> /// Inserta un contacto en la lista especificada usando un comando ya otorgado /// </summary> /// <param name="c">Objeto de la clase <see cref="Contacto"/> a insertar</param> /// <param name="Lista">Lista de usuarios destino</param> /// <param name="cmd">Comando <see cref="SqlCommand"/> con transaccion y conexion /// ya configuradas</param> /// <remarks> /// Se utiliza este metodo principalmente para la primera insercion de un proveedor o /// cliente. Esto permitirá mantener todo el proceso dentro de la misma transaccion /// </remarks> public static void InsertarContacto(Modelo.Contacto c, int Lista, SqlCommand cmd) { string insertContacto = "INSERT INTO Personas VALUES(" + "@Nombre," + "@ApellidoPaterno," + "@ApellidoMaterno," + "@Telefono," + "@CorreoElectronico," + "@Calle," + "@Numero," + "@Colonia" + ")" + Environment.NewLine + "SELECT CAST(SCOPE_IDENTITY() as int)"; cmd.CommandText = insertContacto; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Nombre", c.Nombre); cmd.Parameters.AddWithValue("@ApellidoPaterno", c.ApellidoPaterno); cmd.Parameters.AddWithValue("@ApellidoMaterno", c.ApellidoMaterno); cmd.Parameters.AddWithValue("@Telefono", c.Telefono); cmd.Parameters.AddWithValue("@CorreoElectronico", c.CorreoElectronico); cmd.Parameters.AddWithValue("@Calle", c.Calle); cmd.Parameters.AddWithValue("@Numero", c.Numero); cmd.Parameters.AddWithValue("@Colonia", c.Colonia); Utils.ClearNullParameterValues(cmd.Parameters); int idPersona = (int)cmd.ExecuteScalar(); insertContacto = "INSERT INTO Contactos VALUES (@IdPersona)" + Environment.NewLine + "SELECT CAST(SCOPE_IDENTITY() as int)"; cmd.CommandText = insertContacto; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@IdPersona", idPersona); int idContacto = (int)cmd.ExecuteScalar(); insertContacto = "INSERT INTO DesgloseContactos VALUES(" + "@IdLista," + "@IdContacto" + ")"; cmd.CommandText = insertContacto; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@IdLista", Lista); cmd.Parameters.AddWithValue("@IdContacto", idContacto); Utils.ClearNullParameterValues(cmd.Parameters); cmd.ExecuteNonQuery(); }
public Cliente GetById(int idCliente) { Cliente c = null; SqlConnection connection = null; SqlCommand cmd = null; SqlDataReader reader = null; try { connection = GetConnection(); connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = "SELECT * FROM [Ventas].[Clientes] WHERE IdCliente = @IdCliente"; cmd.Parameters.AddWithValue("@IdCliente", idCliente); reader = cmd.ExecuteReader(); if (reader.Read()) { c = new Cliente(); c.IdCliente = reader.GetInt32(0); c.Nombre = reader.GetString(1); c.Telefono = reader.GetString(2); c.CorreoElectronico = reader.GetString(3); c.Calle = reader.GetString(4); c.Numero = reader.GetString(5); c.Colonia = reader.GetString(6); if (reader.GetInt32(7) == 1) { c.RegimenFiscal = RegimenesFiscales.PersonaMoral; } else if (reader.GetInt32(7) == 2) { c.RegimenFiscal = RegimenesFiscales.PersonaFisica; } c.RFC = reader.GetString(8); c.Estatus = (bool)reader[9]; c.ListaContactos = new Modelo.ListaContactos { IdListaContactos = reader.GetInt32(10) }; ControladorContacto contContacto = new ControladorContacto(); DataTable dt = contContacto.GetAll(c.ListaContactos.IdListaContactos); foreach (DataRow row in dt.Rows) { Modelo.Contacto contacto = new Modelo.Contacto() { IdContacto = (int)row["IdContacto"], IdPersona = (int)row["IdPersona"], Nombre = row["Nombre"] != DBNull.Value ? (string)row["Nombre"] : null, ApellidoPaterno = row["ApellidoPaterno"] != DBNull.Value ? (string)row["ApellidoPaterno"] : null, ApellidoMaterno = row["ApellidoMaterno"] != DBNull.Value ? (string)row["ApellidoMaterno"] : null, Telefono = row["Telefono"] != DBNull.Value ? (string)row["Telefono"] : null, CorreoElectronico = row["CorreoElectronico"] != DBNull.Value ? (string)row["CorreoElectronico"] : null, Calle = row["Calle"] != DBNull.Value ? (string)row["Calle"] : null, Numero = row["Numero"] != DBNull.Value ? (string)row["Numero"] : null, Colonia = row["Colonia"] != DBNull.Value ? (string)row["Colonia"] : null }; c.ListaContactos.Add(contacto); } } return(c); } catch (Exception ex) { if (connection != null) { connection.Close(); connection.Dispose(); } throw ex; } }
public Proveedor GetById(int idProveedor) { Proveedor p = null; SqlConnection connection = null; SqlCommand cmd = null; SqlDataReader reader = null; try { connection = GetConnection(); connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = "SELECT * FROM [Compras].[Proveedores] WHERE IdProveedor = @IdProveedor"; cmd.Parameters.AddWithValue("@IdProveedor", idProveedor); reader = cmd.ExecuteReader(); if (reader.Read()) { p = new Proveedor() { IdProveedor = (int)reader["IdProveedor"], Nombre = (string)reader["Nombre"], Telefono = (string)reader["Telefono"], CorreoElectronico = (string)reader["CorreoElectronico"], Calle = (string)reader["Calle"], Numero = (string)reader["Numero"], Colonia = (string)reader["Colonia"], Contactos = new Modelo.ListaContactos { IdListaContactos = (int)reader["ListaContactos"] } }; ControladorContacto contContacto = new ControladorContacto(); DataTable dt = contContacto.GetAll(p.Contactos.IdListaContactos); foreach (DataRow row in dt.Rows) { Modelo.Contacto contacto = new Modelo.Contacto() { IdContacto = (int)row["IdContacto"], IdPersona = (int)row["IdPersona"], Nombre = row["Nombre"] != DBNull.Value ? (string)row["Nombre"] : null, ApellidoPaterno = row["ApellidoPaterno"] != DBNull.Value ? (string)row["ApellidoPaterno"] : null, ApellidoMaterno = row["ApellidoMaterno"] != DBNull.Value ? (string)row["ApellidoMaterno"] : null, Telefono = row["Telefono"] != DBNull.Value ? (string)row["Telefono"] : null, CorreoElectronico = row["CorreoElectronico"] != DBNull.Value ? (string)row["CorreoElectronico"] : null, Calle = row["Calle"] != DBNull.Value ? (string)row["Calle"] : null, Numero = row["Numero"] != DBNull.Value ? (string)row["Numero"] : null, Colonia = row["Colonia"] != DBNull.Value ? (string)row["Colonia"] : null }; p.Contactos.Add(contacto); } } return(p); } catch (Exception ex) { if (connection != null) { connection.Close(); connection.Dispose(); } throw ex; } }