C# - CRUD com ADO .NET e Stored Procedures

C# - CRUD com ADO .NET e Stored Procedures usando uma DAL

Este artigo é essencialmente prático e mostra como realizar as operações CRUD (Create, Upate, Delete) em um banco de dados SQL Server usando ADO .NET e Stored Procedures em uma aplicação ASP .NET usando uma camada de acesso a dados ou Data Access Layer (DAL).

Ferramentas usadas:

Visual Web Developer 2010 Express Edition (VWD)
SQL Server 2005 Express Edition
Banco de dados Escola.mdf
Tabela Contatos : Estrutura da tabela:

Eu criei o banco de dados Escola.mdf e a tabela Contatos
no SQL Server 2005.
A estrutura da tabela é mostrada ao lado:

1- codigo
2- nome
3- email
4- idade

onde codigo é chave primária do tipo Identity.

Criando o projeto

Inicie o VWD e crie um novo web site (File -> New WebSite), selecionando a linguagem Visual C# e o modelo ASP.NET Empty Web Site informando o nome CRUD_DAL_ADONET;

Criando as Stored Procedures

No SQL Server vamos expandir o nó Stored Procedures e criar 4 stored procedures:

AtualizarDados - atualiza dados dos contatos
CarregarDados - retorna dados dos contatos
DeletarDados - deleta um contato
InserirDados - inclui um contato
getContato - obtém um único contato

Conforme o código abaixo:

1- AtualizarDados

2- CarregarDados

CREATE PROCEDURE dbo.CarregarDados
AS
    select * from contatos
	RETURN

3- DeletarDados

CREATE PROCEDURE dbo.DeletarDados
(
@codigo int
)
AS
DELETE
    FROM dbo.Contatos
    WHERE
    codigo = @codigo
RETURN

4- InserirDados

CREATE PROCEDURE dbo.InserirDados
(
@nome nvarchar(50),
@email nvarchar(100),
@idade int
)
AS
Insert into Contatos(nome,email,idade) values
(@nome,@email,@idade)
RETURN

5- getContato

CREATE PROCEDURE dbo.getContato
(
@codigo int
)
AS
select * from Contatos Where codigo = @codigo
RETURN

Definindo o código do projeto

Vamos agora definir o código do projeto começando pela definição da string de conexão no arquivo web.Config conforme mostrada a seguir:

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
   <system.web>
        <compilation debug="false" targetFramework="4.0" />
    </system.web>

  <connectionStrings>
    <add name="Macoratti" connectionString="Data Source=.\SQLEXPRESS;Catalog=Escola;Integrated Security=True;User Instance=True"
              providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Vamos agora incluir uma classe ao projeto via menu Project -> Add New Item, selecionando o modelo Class com o nome AcessoDAL.cs:

Confirme a inclusão deste arquivo na pasta App_Code e inclua o código abaixo na classe AcessoDB que esta no arquivo AcessoDAL.cs:

using System;
using System.Configuration;
using System.Web.Configuration;

public class AcessoDB
{
    static public String ConnectionString
    {
        get
        {    // pega a string de conexão do web.config
            return WebConfigurationManager.ConnectionStrings["Macoratti"].ConnectionString;
        }
    }
}

Note que o método ConnectionString da classe AcessoDB é estático (static) o que indica que não precisaremos instanciar a classe AcessoDB para usar o método.

Vamos agora incluir outra classe chamada Contato no projeto para representar um Contato com o código abaixo:

/// <summary>
/// Summary description for Contato
/// </summary>
public class Contato
{
       public int Codigo { get; set; }
       public string Nome { get; set; }
       public string Email { get; set; }
       public int Idade { get; set; }
}

Vamos incluir outra classe no projeto chamada contatoDAL que irá conter os métodos de acesso aos dados:

Na classe contatoDAL temos os seguintes métodos:

GetContatos
GetContato
incluirContato
deletarContato
atualizarContato

O código é visto abaixo:

using System;
using System.Data.SqlClient;
using System.Data;

public class BooksDAL
{
    public static DataSet GetContatos()
    {
        SqlConnection con = new SqlConnection(Database.ConnectionString);
        SqlDataAdapter da = new SqlDataAdapter("CarregarDados", con);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        DataSet ds = new DataSet();
        da.Fill(ds, "contatos");
        return ds;
    }

    public static Contato GetContato(int codigo)
    {
        SqlConnection con = new SqlConnection(Database.ConnectionString);
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("getContato", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@codigo", codigo);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                Contato ct = new Contato();
                ct.Nome = dr["nome"].ToString();
                ct.Email = dr["email"].ToString();
                ct.Idade = Int32.Parse(dr["idade"].ToString());
                return ct;
            }
            else
                return null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }

    public void incluirContato(Contato contato)
    {
        SqlConnection con = new SqlConnection(Database.ConnectionString);
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("InserirDados", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@nome", contato.Nome);
            cmd.Parameters.AddWithValue("@email", contato.Email);
            cmd.Parameters.AddWithValue("@idade", contato.Idade);
            cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw ex;  // retorna mensagem de erro
        }
        finally
        {
            con.Close();
        }
    }
    public static string deletarContato(int codigo)
    {
        SqlConnection con = new SqlConnection(Database.ConnectionString);
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("DeletarDados", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@codigo", codigo);
            cmd.ExecuteNonQuery();
            return null; // success 
        }
        catch (Exception ex)
        {
            throw ex;  // retorna mensagem de erro
        }
        finally
        {
            con.Close();
        }
    }
    public static string atualizarContato(Contato contato)
    {
        SqlConnection con = new SqlConnection(Database.ConnectionString);
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("AtualizarDados", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@codigo", contato.Codigo);
            cmd.Parameters.AddWithValue("@nome", contato.Nome);
            cmd.Parameters.AddWithValue("@email", contato.Email);
            cmd.Parameters.AddWithValue("@idade", contato.Idade);
            cmd.ExecuteNonQuery();
            return null; // success 
        }
        catch (Exception ex)
        {
            throw ex;  // retorna mensagem de erro
        }
        finally
        {
            con.Close();
        }
    }
}

Criando a Interface

Vamos incluir no projeto uma Web Form via menu WebSiste-> Add New Item -> Web Form com o nome Default.aspx;

A seguir vamos incluir uma tabela com 8 colunas e 1 linha e incluir 5 controles LinkButton conforme o leiaute da figura abaixo:

Após isso inclua 4 novas páginas web (Web Form) com os seguintes nomes:

Incluir.aspx
Deletar.aspx
Atualizar.aspx
Listar.aspx

Agora vamos atribuir a cada propriedade PostbackUrl de cada um dos controles LinkButtons, o caminho e nome dada respectiva página:

O código completo da página Default.aspx é o seguinte:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1  {  width: 90%; }
        .style2  {
            font-family: "Trebuchet MS";
            color: #3333FF;
        }
        .style3 {
            height: 27px;
            text-align: center;  }
        .style4  { height: 19px; }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <table class="style1">
            <tr>
                <td class="style2">
                    <strong>Macoratti.net - CRUD com ADO .NET usando a camada DAL</strong></td>
            </tr>
            <tr>
                <td bgcolor="#33CCFF">
                    &nbsp;</td>
            </tr>
            <tr>
                <td style="text-align: center">
&nbsp;<asp:LinkButton ID="LinkButton1" runat="server" PostBackUrl="~/Incluir.aspx" 
                        style="font-family: 'Trebuchet MS'">1 - Incluir novo Contato </asp:LinkButton>
                </td>
            </tr>
            <tr>
                <td style="text-align: center">
                    <asp:LinkButton ID="LinkButton2" runat="server" PostBackUrl="~/Atualizar.aspx" 
                        style="font-family: 'Trebuchet MS'">2- Atualizar Contato</asp:LinkButton>
                </td>
            </tr>
            <tr>
                <td class="style3">
                    <asp:LinkButton ID="LinkButton3" runat="server" PostBackUrl="~/Deletar.aspx" 
                        style="font-family: 'Trebuchet MS'">3- Deletar Contato</asp:LinkButton>
                </td>
            </tr>
            <tr>
                <td style="text-align: center">
                    <asp:LinkButton ID="LinkButton4" runat="server" PostBackUrl="~/Listar.aspx" 
                        style="font-family: 'Trebuchet MS'">4- Listar Contatos</asp:LinkButton>
                </td>
            </tr>
            <tr>
                <td bgcolor="#33CCFF" class="style4">
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
            </tr>
        </table>
    
    </div>
    </form>
</body>
</html>

Vamos agora definir o leiaute de cada uma das páginas usadas no projeto:

1- página Incluir.aspx :

leiaute :

código do arquivo code-behind Incluir.aspx.cs :

using System;

public partial class Incluir : System.Web.UI.Page
{
    
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnIncluir_Click(object sender, EventArgs e)
    {
        ContatoDAL ctDal = new ContatoDAL();
        Contato _contato = new Contato();

        _contato.Nome = txtNome.Text;
        _contato.Email = txtEmail.Text;
        _contato.Idade = Int32.Parse( txtIdade.Text);

        try
        {
            ctDal.incluirContato(_contato);
            lblMsg.Text = "Contato incluído com sucesso!";
        }
        catch (Exception ex)
        {
            lblMsg.Text = "Error -> " + ex.Message;
        }
    }
}

2- página Deletar.aspx

Código do arquivo code-behind Deletar.aspx.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Deletar : System.Web.UI.Page
{
    protected void btnDeletar_Click(object sender, EventArgs e)
    {
        try
        {
            ContatoDAL.deletarContato(Int32.Parse(txtCodigo.Text));
            lblmsg.Text = "Contato excluído com sucesso!";
        }
        catch (Exception ex)
        {
            lblmsg.Text = "Error -> " + ex.Message;
        }
    }
}

3- página Atualizar.aspx

Código do arquivo code-behind Atualizar.aspx.cs

using System;

public partial class Atualizar : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnGetDetalhes_Click(object sender, EventArgs e)
    {
        if (txtCodigo.Text == string.Empty)
        {
            lblmsg.Text = "Código inválido";
            return;
        }

        Contato  c = ContatoDAL.GetContato(Int32.Parse(txtCodigo.Text));
        if (c != null)
        {
            txtNome.Text = c.Nome;
            txtEmail.Text = c.Email;
            txtIdade.Text = c.Idade.ToString();
            btnAtualiza.Enabled = true;
        }
        else
        {
            lblmsg.Text = "Contato não encontrado";
            btnAtualiza.Enabled = false;
        }

    }
    protected void btnAtualizar_Click(object sender, EventArgs e)
    {
        Contato _contato = new Contato();
        _contato.Nome = txtNome.Text;
        _contato.Email = txtEmail.Text;
        _contato.Idade = Int32.Parse(txtIdade.Text);

        try
        {
            ContatoDAL.atualizarContato(_contato);
            lblmsg.Text = "Contato excluído com sucesso!";
        }
        catch (Exception ex)
        {
            lblmsg.Text = "Error -> " + ex.Message;
        }
    }

3- página da página Listar.aspx

Código do arquivo code-behind Listar.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Listar : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        GridView1.DataSource = ContatoDAL.GetContatos();
        GridView1.DataBind();
    }
}

Agora é só alegria...

Vamos executar a aplicação ASP .NET e ver se tudo esta funcionando corretamente:

1- Página principal Default.aspx

2- Página para exibir a lista de contatos Listar.aspx

4- Página para incluir um novo contato Incluir.aspx

5- Página para atualizar um contato Atualizar.aspx

a- Obtendo detalhes do último registro que incluímos que deverá possuir o código 9:

b- Atualizando a idade e o email do contato:

6- Página para excluir um contato Excluir.aspx

E assim terminamos essa pequena revisão sobre como usar stored procedures e uma camada de acesso a dados em uma aplicação ASP .NET usando Web Forms.

Pegue o projeto completo com os exemplos aqui : CRUD_DAL_ADONET.zip

1Pedro 1:24 Porque: Toda a carne é como a erva, e toda a sua glória como a flor da erva. Secou-se a erva, e caiu a sua flor;
1Pedro 1:25 mas a palavra do Senhor permanece para sempre. E esta é a palavra que vos foi evangelizada.

Referências:

José Carlos Macoratti

C# - Acesso SQL Server com CRUD (ADO .NET) - Macoratti.net

C# - Acesso SQL Server com CRUD (ADO .NET) - Macoratti.net

C# - CRUD básico com Entity Framework - Macoratti.net