public void Delete(int id) { try { ProgramacaoWebEntities _db = new ProgramacaoWebEntities(); var query = "UPDATE [ProgramacaoWeb].[dbo].[TB_USUARIO]" + "SET[usr_deletado] = @Deletado" + " WHERE" + " [usr_id] = " + id; using (SqlConnection connection = new SqlConnection(_db.Database.Connection.ConnectionString.ToString())) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Parameters.AddWithValue("@Deletado", 1); cmd.Connection = connection; connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } } } catch (Exception exc) { throw exc; } }
public void Update(Usuario usuario) { try { ProgramacaoWebEntities _db = new ProgramacaoWebEntities(); var query = "UPDATE [ProgramacaoWeb].[dbo].[TB_USUARIO] " + "SET[usr_nome] = @Nome" + ",[usr_cpf] = @Cpf" + ",[usr_sexo] = @Sexo" + ",[usr_dt_nascimento] = @Dt_nascimento" + ",[usr_telefone] = @Telefone" + ",[usr_email] = @Email" + ",[usr_logradouro] = @Logradouro" + ",[usr_complemento_logradouro] = @ComplementoLogradouro" + ",[usr_bairro] = @CodigoBairro" + ",[usr_municipio] = @CodigoMunicipio" + ",[usr_cep] = @Cep" + ",[usr_uf] = @CodigoUf" + ",[emp_id] = @CodigoEmpresa" + ",[pfl_id] = @CodigoPerfil" + ",[usr_dt_atualizacao] = GETDATE()" + "WHERE" + " [usr_id] = " + usuario.Id; using (SqlConnection connection = new SqlConnection(_db.Database.Connection.ConnectionString.ToString())) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Parameters.AddWithValue("@Nome", usuario.Nome); cmd.Parameters.AddWithValue("@Cpf", usuario.Cpf); cmd.Parameters.AddWithValue("@Sexo", usuario.Sexo); cmd.Parameters.AddWithValue("@Dt_nascimento", usuario.Dt_nascimento); cmd.Parameters.AddWithValue("@Telefone", usuario.Telefone); cmd.Parameters.AddWithValue("@Email", usuario.Email); cmd.Parameters.AddWithValue("@Logradouro", usuario.Logradouro); cmd.Parameters.AddWithValue("@ComplementoLogradouro", usuario.ComplementoLogradouro); cmd.Parameters.AddWithValue("@CodigoBairro", usuario.Bairro); cmd.Parameters.AddWithValue("@CodigoMunicipio", usuario.Municipio); cmd.Parameters.AddWithValue("@Cep", usuario.Cep); cmd.Parameters.AddWithValue("@CodigoUf", usuario.Uf); cmd.Parameters.AddWithValue("@CodigoEmpresa", usuario.CodigoEmpresa); cmd.Parameters.AddWithValue("@CodigoPerfil", usuario.CodigoPerfil); cmd.Connection = connection; connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } } } catch (Exception exc) { throw exc; } }
public Usuario GetUsuarioCPF(string cpf) { try { ProgramacaoWebEntities _db = new ProgramacaoWebEntities(); Usuario usuario = new Usuario(); var query = "SELECT * FROM TB_USUARIO WHERE usr_cpf =" + "'" + cpf + "'"; using (SqlConnection connection = new SqlConnection(_db.Database.Connection.ConnectionString.ToString())) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Connection = connection; connection.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { while (sdr.Read()) { usuario = new Usuario { Id = Convert.ToInt32(sdr["usr_id"]), Nome = Convert.ToString(sdr["usr_nome"]), Sexo = Convert.ToString(sdr["usr_sexo"]), Senha = Convert.ToString(sdr["usr_senha"]), Cpf = Convert.ToString(sdr["usr_cpf"]), Dt_nascimento = Convert.ToDateTime(sdr["usr_dt_nascimento"]), Telefone = Convert.ToString(sdr["usr_telefone"]), Email = Convert.ToString(sdr["usr_email"]), Logradouro = Convert.ToString(sdr["usr_logradouro"]), ComplementoLogradouro = Convert.ToString(sdr["usr_complemento_logradouro"]), Bairro = Convert.ToString(sdr["usr_bairro"]), Municipio = Convert.ToString(sdr["usr_municipio"]), Cep = Convert.ToString(sdr["usr_cep"]), Uf = Convert.ToString(sdr["usr_uf"]), CodigoEmpresa = Convert.ToInt32(sdr["emp_id"]), CodigoPerfil = Convert.ToInt32(sdr["pfl_id"]), Dt_cadastro = Convert.ToDateTime(sdr["usr_dt_cadastro"]), Dt_atualizacao = Convert.ToDateTime(sdr["usr_dt_atualizacao"]), Deletado = Convert.ToBoolean(sdr["usr_deletado"]), }; } } connection.Close(); } } return(usuario); } catch (Exception exc) { throw exc; } }
public List <Empresa> ListEmpresa() { try { ProgramacaoWebEntities _db = new ProgramacaoWebEntities(); List <Empresa> listEmpresa = new List <Empresa>(); var query = "SELECT * FROM TB_EMPRESA WHERE [emp_deletado] = 0"; using (SqlConnection connection = new SqlConnection(_db.Database.Connection.ConnectionString.ToString())) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Connection = connection; connection.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { while (sdr.Read()) { Empresa Empresa = new Empresa() { Id = Convert.ToInt32(sdr["emp_id"]), Nomefantasia = Convert.ToString(sdr["emp_nm_fantasia"]), Logradouro = Convert.ToString(sdr["emp_logradouro"]), Complemento = Convert.ToString(sdr["emp_complemento"]), Bairro = Convert.ToString(sdr["emp_bairro"]), Municipio = Convert.ToString(sdr["emp_municipio"]), Uf = Convert.ToString(sdr["emp_uf"]), Cep = Convert.ToString(sdr["emp_cep"]), Telefone = Convert.ToString(sdr["emp_telefone"]), Dt_cadastro = Convert.ToDateTime(sdr["emp_dt_cadastro"]), Dt_atualizacao = Convert.ToDateTime(sdr["emp_dt_atualizacao"]), Deletado = Convert.ToBoolean(sdr["emp_deletado"]), }; listEmpresa.Add(Empresa); } } connection.Close(); } } return(listEmpresa); } catch (Exception exc) { throw exc; } }
public void Save(Usuario usuario) { try { ProgramacaoWebEntities _db = new ProgramacaoWebEntities(); var query = "INSERT INTO[ProgramacaoWeb].[dbo].[TB_USUARIO]" + "([usr_nome]" + ",[usr_cpf]" + ",[usr_sexo]" + ",[usr_dt_nascimento]" + ",[usr_telefone]" + ",[usr_email]" + ",[usr_senha]" + ",[usr_logradouro]" + ",[usr_complemento_logradouro]" + ",[usr_bairro]" + ",[usr_municipio]" + ",[usr_cep]" + ",[usr_uf]" + ",[emp_id]" + ",[pfl_id]" + ",[usr_dt_cadastro]" + ",[usr_dt_atualizacao]" + ",[usr_deletado])" + "VALUES(@Nome,@Cpf,@Sexo,@Dt_nascimento,@Telefone,@Email,@Senha,@Logradouro,@ComplementoLogradouro,@CodigoBairro,@CodigoMunicipio,@Cep,@CodigoUf,@CodigoEmpresa,@CodigoPerfil,GETDATE(),GETDATE(),0)"; using (SqlConnection connection = new SqlConnection(_db.Database.Connection.ConnectionString.ToString())) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Parameters.AddWithValue("@Nome", usuario.Nome); cmd.Parameters.AddWithValue("@Cpf", usuario.Cpf); cmd.Parameters.AddWithValue("@Sexo", usuario.Sexo); cmd.Parameters.AddWithValue("@Dt_nascimento", usuario.Dt_nascimento); cmd.Parameters.AddWithValue("@Telefone", usuario.Telefone); cmd.Parameters.AddWithValue("@Email", usuario.Email); cmd.Parameters.AddWithValue("@Senha", usuario.Senha); cmd.Parameters.AddWithValue("@Logradouro", usuario.Logradouro); cmd.Parameters.AddWithValue("@ComplementoLogradouro", usuario.ComplementoLogradouro); cmd.Parameters.AddWithValue("@CodigoBairro", usuario.Bairro); cmd.Parameters.AddWithValue("@CodigoMunicipio", usuario.Municipio); cmd.Parameters.AddWithValue("@Cep", usuario.Cep); cmd.Parameters.AddWithValue("@CodigoUf", usuario.Uf); cmd.Parameters.AddWithValue("@CodigoEmpresa", usuario.CodigoEmpresa); cmd.Parameters.AddWithValue("@CodigoPerfil", usuario.CodigoPerfil); cmd.Connection = connection; connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } } } catch (Exception exc) { throw exc; } }
public List <Perfil> ListPerfil() { try { ProgramacaoWebEntities _db = new ProgramacaoWebEntities(); List <Perfil> listPerfil = new List <Perfil>(); var query = "SELECT * FROM TB_PERFIL WHERE [pfl_deletado] = 0"; using (SqlConnection connection = new SqlConnection(_db.Database.Connection.ConnectionString.ToString())) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Connection = connection; connection.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { while (sdr.Read()) { Perfil Perfil = new Perfil() { Id = Convert.ToInt32(sdr["pfl_id"]), NomePerfil = Convert.ToString(sdr["pfl_nome"]), DescricaoPerfil = Convert.ToString(sdr["pfl_descricao"]), Dt_cadastro = Convert.ToDateTime(sdr["pfl_dt_cadastro"]), Dt_atualizacao = Convert.ToDateTime(sdr["pfl_dt_atualizacao"]), Deletado = Convert.ToBoolean(sdr["pfl_deletado"]), }; listPerfil.Add(Perfil); } } connection.Close(); } } return(listPerfil); } catch (Exception exc) { throw exc; } }