public static void Update(dtoCategoriaPeca CategoriaPeca) { using (SqlConnection connection = new SqlConnection(DataAccess.Configuracao.getConnectionString())) { string stringSQL = @"UPDATE tbCategoriaPeca SET Descricao = @Descricao, dataUltimaAlteracao = getdate() WHERE idCategoriaPeca = @idCategoriaPeca"; SqlCommand cmdCategoriaPeca = new SqlCommand(stringSQL, connection); ValidaCampos(ref CategoriaPeca); cmdCategoriaPeca.Parameters.Add("idCategoriaPeca", SqlDbType.Int).Value = CategoriaPeca.idCategoriaPeca; cmdCategoriaPeca.Parameters.Add("Descricao", SqlDbType.VarChar).Value = CategoriaPeca.Descricao; try { connection.Open(); cmdCategoriaPeca.ExecuteNonQuery(); } catch { throw new ApplicationException("Erro ao atualizar registro"); } finally { connection.Close(); } } }
public static void Delete(dtoCategoriaPeca CategoriaPeca) { using (SqlConnection connection = new SqlConnection(DataAccess.Configuracao.getConnectionString())) { string stringSQL = @"DELETE tbCategoriaPeca WHERE idCategoriaPeca = @idCategoriaPeca"; SqlCommand cmdMenu = new SqlCommand(stringSQL, connection); cmdMenu.Parameters.Add("idCategoriaPeca", SqlDbType.Int).Value = CategoriaPeca.idCategoriaPeca; try { connection.Open(); cmdMenu.ExecuteNonQuery(); } catch { throw new ApplicationException("Erro ao excluir registro"); } finally { connection.Close(); } } }
private static void ValidaCampos(ref dtoCategoriaPeca CategoriaPeca) { if (String.IsNullOrEmpty(CategoriaPeca.Descricao)) { CategoriaPeca.Descricao = String.Empty; } }
private static void PreencheCampos(SqlDataReader drCategoriaPeca, ref dtoCategoriaPeca CategoriaPeca) { if (drCategoriaPeca["idCategoriaPeca"] != DBNull.Value) { CategoriaPeca.idCategoriaPeca = Convert.ToInt32(drCategoriaPeca["idCategoriaPeca"].ToString()); } if (drCategoriaPeca["dataCadastro"] != DBNull.Value) { CategoriaPeca.dataCadastro = Convert.ToDateTime(drCategoriaPeca["dataCadastro"]); } else { CategoriaPeca.dataCadastro = null; } if (drCategoriaPeca["dataUltimaAlteracao"] != DBNull.Value) { CategoriaPeca.dataUltimaAlteracao = Convert.ToDateTime(drCategoriaPeca["dataUltimaAlteracao"]); } else { CategoriaPeca.dataUltimaAlteracao = null; } if (drCategoriaPeca["Descricao"] != DBNull.Value) { CategoriaPeca.Descricao = drCategoriaPeca["Descricao"].ToString(); } }
public static int Insert(dtoCategoriaPeca CategoriaPeca) { using (SqlConnection connection = new SqlConnection(DataAccess.Configuracao.getConnectionString())) { string stringSQL = @"INSERT INTO tbCategoriaPeca(Descricao, dataCadastro) VALUES(@Descricao, getdate()); SET @idCategoriaPeca = SCOPE_IDENTITY()"; SqlCommand cmdCategoriaPeca = new SqlCommand(stringSQL, connection); ValidaCampos(ref CategoriaPeca); cmdCategoriaPeca.Parameters.Add("idCategoriaPeca", SqlDbType.Int); cmdCategoriaPeca.Parameters["idCategoriaPeca"].Direction = ParameterDirection.Output; cmdCategoriaPeca.Parameters.Add("Descricao", SqlDbType.VarChar).Value = CategoriaPeca.Descricao; try { connection.Open(); cmdCategoriaPeca.ExecuteNonQuery(); return((int)cmdCategoriaPeca.Parameters["idCategoriaPeca"].Value); } catch { throw new ApplicationException("Erro ao inserir registro"); } finally { connection.Close(); } } }
public static List <dtoCategoriaPeca> GetAll(string SortExpression, string termoPesquisa) { List <dtoCategoriaPeca> CategoriaPecas = new List <dtoCategoriaPeca>(); using (SqlConnection connection = new SqlConnection(DataAccess.Configuracao.getConnectionString())) { StringBuilder sbCondicao = new StringBuilder(); // CONDIÇÕES if (termoPesquisa != null && termoPesquisa != String.Empty) { if (sbCondicao.ToString() != String.Empty) { sbCondicao.Append(" AND "); } else { sbCondicao.Append(" WHERE "); } sbCondicao.AppendFormat(@" (tbCategoriaPeca.Descricao LIKE '%{0}%') ", termoPesquisa); } string stringSQL = String.Format("SELECT * FROM tbCategoriaPeca {0} ORDER BY {1}", sbCondicao.ToString(), (SortExpression.Trim() != String.Empty ? SortExpression.Trim() : "idCategoriaPeca")); SqlCommand cmdCategoriaPeca = new SqlCommand(stringSQL, connection); try { connection.Open(); SqlDataReader drCategoriaPeca = cmdCategoriaPeca.ExecuteReader(); while (drCategoriaPeca.Read()) { dtoCategoriaPeca CategoriaPeca = new dtoCategoriaPeca(); PreencheCampos(drCategoriaPeca, ref CategoriaPeca); CategoriaPecas.Add(CategoriaPeca); } } catch { throw new ApplicationException("Erro ao capturar todos os registros"); } finally { connection.Close(); } } return(CategoriaPecas); }
protected void btnExcluirSelecionados_Click(object sender, EventArgs e) { foreach (GridViewRow row in grdResultado.Rows) { if (row.RowType == DataControlRowType.DataRow) { CheckBox chkExcluir = (CheckBox)row.FindControl("chkExcluir"); HiddenField hdIdCategoriaPeca = (HiddenField)row.FindControl("hdIdCategoriaPeca"); dtoCategoriaPeca categoriaPeca = bllCategoriaPeca.Get(Convert.ToInt32(hdIdCategoriaPeca.Value)); if (chkExcluir.Checked && categoriaPeca != null) { bllCategoriaPeca.Delete(Convert.ToInt32(categoriaPeca.idCategoriaPeca)); } } } grdResultado.DataBind(); }
public static dtoCategoriaPeca Get(int idCategoriaPeca) { dtoCategoriaPeca CategoriaPeca = new dtoCategoriaPeca(); using (SqlConnection connection = new SqlConnection(DataAccess.Configuracao.getConnectionString())) { string stringSQL = @"SELECT * FROM tbCategoriaPeca WHERE idCategoriaPeca = @idCategoriaPeca"; SqlCommand cmdMenu = new SqlCommand(stringSQL, connection); cmdMenu.Parameters.Add("idCategoriaPeca", SqlDbType.Int).Value = idCategoriaPeca; try { connection.Open(); SqlDataReader drCategoriaPeca = cmdMenu.ExecuteReader(); if (drCategoriaPeca.Read()) { PreencheCampos(drCategoriaPeca, ref CategoriaPeca); } } catch { throw new ApplicationException("Erro ao capturar registro"); } finally { connection.Close(); } } return(CategoriaPeca); }