public void UpdateProveedor(Proveedor p) { SqlConnection connection = null; SqlCommand cmd = null; try { connection = GetConnection(); connection.Open(); cmd = connection.CreateCommand(); string InsertProveedor = "UPDATE [Compras].[Proveedores] SET " + Environment.NewLine + "Nombre = @Nombre, " + Environment.NewLine + "Telefono = @Telefono, " + Environment.NewLine + "CorreoElectronico = @CorreoElectronico, " + Environment.NewLine + "Calle = @Calle, " + Environment.NewLine + "Numero = @Numero, " + Environment.NewLine + "Colonia = @Colonia " + Environment.NewLine + "WHERE IdProveedor = @IdProveedor"; cmd.CommandText = InsertProveedor; cmd.Parameters.AddWithValue("@Nombre", p.Nombre); cmd.Parameters.AddWithValue("@Telefono", p.Telefono); cmd.Parameters.AddWithValue("@CorreoElectronico", p.CorreoElectronico); cmd.Parameters.AddWithValue("@Calle", p.Calle); cmd.Parameters.AddWithValue("@Numero", p.Numero); cmd.Parameters.AddWithValue("@Colonia", p.Colonia); cmd.Parameters.AddWithValue("@IdProveedor", p.IdProveedor); cmd.ExecuteNonQuery(); if (p.Contactos != null && p.Contactos.Count > 0) { foreach (Modelo.Contacto c in from con in p.Contactos where con.IdContacto == 0 select con) { ControladorContacto.InsertarContacto(c, p.Contactos.IdListaContactos, cmd); } } } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } } }
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 void InsertarCliente(Cliente c) { SqlConnection connection = null; SqlTransaction transaction = null; SqlCommand cmd = null; try { connection = GetConnection(); connection.Open(); cmd = connection.CreateCommand(); transaction = connection.BeginTransaction(); cmd.Transaction = transaction; cmd.CommandText = Utils.InsertListaContactos; int idLista = (int)cmd.ExecuteScalar(); string InsertCliente = "INSERT INTO [Ventas].[Clientes](" + Environment.NewLine + " Nombre," + Environment.NewLine + " Telefono," + Environment.NewLine + " CorreoElectronico," + Environment.NewLine + " Calle," + Environment.NewLine + " Numero," + Environment.NewLine + " Colonia," + Environment.NewLine + " RegimenFiscal," + Environment.NewLine + " RFC," + Environment.NewLine + " Estatus," + Environment.NewLine + " ListaContactos" + Environment.NewLine + ")VALUES(" + Environment.NewLine + " @Nombre," + Environment.NewLine + " @Telefono," + Environment.NewLine + " @CorreoElectronico," + Environment.NewLine + " @Calle," + Environment.NewLine + " @Numero," + Environment.NewLine + " @Colonia," + Environment.NewLine + " @RegimenFiscal," + Environment.NewLine + " @RFC," + Environment.NewLine + " 1," + Environment.NewLine + " @ListaContactos" + Environment.NewLine + ")" + Environment.NewLine + "SELECT CAST(SCOPE_IDENTITY() as int)"; cmd.CommandText = InsertCliente; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Nombre", c.Nombre); 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("@RegimenFiscal", c.RegimenFiscal); cmd.Parameters.AddWithValue("@RFC", c.RFC); cmd.Parameters.AddWithValue("@ListaContactos", idLista); int IdCliente = (int)cmd.ExecuteScalar(); if (c.ListaContactos != null && c.ListaContactos.Count > 0) { foreach (Modelo.Contacto contacto in c.ListaContactos) { ControladorContacto.InsertarContacto(contacto, idLista, cmd); } } transaction.Commit(); } catch (Exception ex) { if (transaction != null) { transaction.Rollback(); } if (connection != null) { connection.Close(); } 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; } }
public void InsertarProveedor(Proveedor p) { SqlConnection connection = null; SqlTransaction transaction = null; SqlCommand cmd = null; try { connection = GetConnection(); connection.Open(); cmd = connection.CreateCommand(); transaction = connection.BeginTransaction(); cmd.Transaction = transaction; cmd.CommandText = Utils.InsertListaContactos; int idLista = (int)cmd.ExecuteScalar(); string InsertProveedor = "INSERT INTO [Compras].[Proveedores](" + Environment.NewLine + " Nombre," + Environment.NewLine + " Telefono," + Environment.NewLine + " CorreoElectronico," + Environment.NewLine + " Calle," + Environment.NewLine + " Numero," + Environment.NewLine + " Colonia," + Environment.NewLine + " Estatus," + Environment.NewLine + " ListaContactos" + Environment.NewLine + ")VALUES(" + Environment.NewLine + " @Nombre," + Environment.NewLine + " @Telefono," + Environment.NewLine + " @CorreoElectronico," + Environment.NewLine + " @Calle," + Environment.NewLine + " @Numero," + Environment.NewLine + " @Colonia," + Environment.NewLine + " 1," + Environment.NewLine + " @ListaContactos" + Environment.NewLine + ")" + Environment.NewLine; cmd.CommandText = InsertProveedor; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Nombre", p.Nombre); cmd.Parameters.AddWithValue("@Telefono", p.Telefono); cmd.Parameters.AddWithValue("@CorreoElectronico", p.CorreoElectronico); cmd.Parameters.AddWithValue("@Calle", p.Calle); cmd.Parameters.AddWithValue("@Numero", p.Numero); cmd.Parameters.AddWithValue("@Colonia", p.Colonia); cmd.Parameters.AddWithValue("@ListaContactos", idLista); cmd.ExecuteNonQuery(); if (p.Contactos != null && p.Contactos.Count > 0) { foreach (Modelo.Contacto c in p.Contactos) { ControladorContacto.InsertarContacto(c, idLista, cmd); } } transaction.Commit(); } catch (Exception ex) { if (transaction != null) { transaction.Rollback(); } if (connection != null) { connection.Close(); } throw ex; } }