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;
}
}
