ASP.NET - Usando stored procedures com C#

Eu já abordei esse assunto em um artigo mas usava VB .NET; para variar vou mostrar como criar uma aplicação ASP .NET que usa o controle GridView para exibir dados obtidos por meio de um procedimento armazenado (Stored Procedure) em um banco de dados SQL Server 2005 Express.

Como de praxe para acompanhar este artigo você não vai precisar gastar um centavo pois vou usar as ferramentas gratuitas oferecidas pela Microsoft:

A primeira coisa a fazer será criar um novo web site a partir do VWD 2008 acionando a opção File-> New Web Site a partir do menu;

A seguir na janela New Web Site selecione o template ASP .NET Web Site , a linguagem C#, o location File System e informe o nome sp_GridView clicando a seguir no botão OK;

Na janela Solution Explorer selecione o formulário web Default.aspx e a partir da Caixa de Ferramentas (ToolBox) faça o seguinte:

Vamos criar agora o banco de dados Clientes.mdf no SQL Server 2005 que será usado neste artigo.

No menu WebSite selecione Add New Item;

A seguir na janela Add New Item, selecione o template SQL Server Database, informe o nome Clientes.mdf e clique em Add;

Nota: Será apresentada uma caixa de diálogo solicitando a sua confirmação para copiar o arquivo para a pasta App_Data; Confirme;

Abra a pasta App_Data e clique no arquivo Clientes.mdf para abrir o DataBase Explorer;

Expanda os objetos do DataBase e clique com o botão direito sobre o objeto Tables e selecione Add New Table;

Vamos definir a tabela Contatos com a seguinte estrutura da seguinte forma:

Vamos incluir alguns dados na tabela:

Na janela DataBase Explorer clique com o botão direito sobre o nome da tabela Contatos recém-criada e selecione Show Table Data;

A seguir informe alguns dados conforme mostra a figura a seguir:

Com isso já temos o nosso banco de dados Clientes.mdf e nossa tabela Contatos. Vamos continuar...

Agora temos que criar as nossas stored procedures ou procedimentos armazenados no banco de dados Clientes.mdf.

Abra o DataBase Explorer e após expandir os objetos do banco de dados Clientes.mdf clique com o botão direito do mouse sobre o objeto Stored Procedures e selecione a opção Add New Stored Procedure;

No editor do SQL Server 2005 vamos criar a stored procedure exibeContatos que irá retornar todos os contatos cadastrados na tabela Contatos.

Digite o comando conforme ao lado e clique no botão Save para salvar
a stored procedure no banco de dados;

A instrução SQL usada é: SELECT * from Contatos

Repita o procedimento agora para criar a stored procedure SelecionaContatosPorIdade onde iremos retornar todos os contatos com idade superior a uma idade definida.

Observe que criarmos o parâmetro idade do tipo int para ser usado
na cláusula WHERE.

O comando SQL usado é :
SELECT * from Contatos WHERE idade > @ idade

Novamente repita o processo para criar a stored procedure selecionaContatosPorCidade que irá retornar os contados para uma determinada cidade;

Observe que criarmos o parâmetro cidade do tipo varchar para ser usado na cláusula WHERE.

O comando SQL usado é :
SELECT * from Contatos WHERE cidade = @ cidade

Ao final teremos as stored procedures criadas no banco de dados e prontas para uso conforme a figura abaixo:

No arquivo web.config temos a string de conexão com o banco de dados definida e pronta para ser recuperada e usada.

  <appSettings/>
    <connectionStrings>
        <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Clientes.mdf;Integrated Security=True;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

Retornando ao formulário Default.aspx , selecione o controle GridView e acione a barra GridView Tasks clicando no link Edit Column;

A seguir em Availabe Fields selecione BoundField e clique no botão Add definindo a seguir as propriedades HeaderText e DataField para cada um dos campos da nossa tabela Contatos: id, Nome, Sexo, Idade,Cidade e Email;

Para poder usar os 3 procedimentos armazenados que foram criados vamos incluir no formulário Default.aspx uma tabela, a partir do menu Table->Insert Table,  com 2 linhas e 4 colunas e incluir os seguintes controles:

TextBox txtCidade Button => ID = btnContatosCidade
TextBox txtIdade Button => ID = btnContatosIdade
Label lblmsg  

conforme a figura abaixo:

Vejamos como vamos usar as stored procedures criadas:

exibeContatos  Será usada para preencher o GridView na carga do formulário no evento Load do formulário
SelecionaContatosPorCIdade  Será usada para retornar os contatos por cidade quando o usuário acionar o botão - ContatosPorCidade no evento Click do botão
selecionaContatosPorIdade  Será usada para retornar os contatos por cidade quando o usuário acionar o botão - ContatosPorIdade no evento Click do botão

Temos então que definir o código para cada um dos eventos mapeados a cima.

1- Usando a stored procedure exibeContatos

Vamos iniciar com o evento Load definindo a chamada para a rotina PopulaGrid() e a seguir definindo o código da rotina PopulaGrid() conforme a seguir:

protected void Page_Load(object sender, EventArgs e)

{

   PopulaGrid();

}
 

public void PopulaGrid()

{


  DataSet ds = new DataSet();
  SqlConnection conexao= null;

 

  try

  {

     conexao = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);


      SqlDataAdapter da;

     da = new SqlDataAdapter("exibeContatos", conexao );

     da.SelectCommand.CommandType = CommandType.StoredProcedure;


     conexao .Open();

     da.Fill(ds);

 

     GridView1.DataSource = ds;

     GridView1.DataBind();

  }

   catch (Exception ex)

  {

       lblmsg.Text = ex.Message;

  }

  Finally

  {

     conexao.Close();

  }

}

Na rotina PopulaGrid() temos:

- A definição de um objeto DataSet(ds) ;
- A definição do objeto Connection (conexao) obtendo a string a partir do arquivo web.config;
- Definição do objeto DataAdapter (da);
- Criação de uma instância do objeto DataAdapter usando a store procedures exibeContatos na conexão definida;
- Definição do tipo de comando que iremos executar :  CommandType.StoredProcedure;
- Abertura da conexão: conexao .Open();
- Preenchimento do dataset a partir do DataAdatper definido :  da.Fill(ds);
- Preenchimento do GridView com os dados;
- Observe o tratamento de erros usando o bloco Try/Catch/Finally. O código no bloco Finally será será executado fechando a conexão.

2- Usando a stored procedure SelecionaContatosPorCIdade

Para selecionar os contatos com base em uma cidade especifica inclua o código abaixo no evento Click do botão : ContatosPorCidade

protected void btnContatosCidade_Click(object sender, EventArgs e)

{

   if (txtCidade.Text.Equals(""))

   {

     lblmsg.Text = "Valor inv lido";

       return;

   }
 

   DataSet ds = new DataSet();

    SqlConnection conexao = null;
 

    try

   {

      conexao = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        SqlCommand cmd = new SqlCommand("selecionaContatosPorCidade", conexao);


      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.AddWithValue("@cidade", txtCidade.Text);
 

      SqlDataAdapter da = new SqlDataAdapter(cmd);

      da.Fill(ds);

      lblmsg.Text = "Contatos da cidade : " + txtCidade.Text;

      GridView1.DataSource = ds;

      GridView1.DataBind();

  }

   catch (Exception ex)

  {

      lblmsg.Text = ex.Message;

  }

   finally

  {

     conexao.Close();

   }

}

A diferença para o código anterior é a definição do parâmetro cidade que deve ser definido para ser usado pela stored procedure - selecionaContatosPorCidade.

Definimos também um objeto SqlCommand (cmd) para poder preencher o DataSet e assim atribuir o retorno ao controle GridView.

3- Usando a stored procedure selecionaContatosPorIdade

Para selecionar os contatos com base em uma cidade especifica inclua o código abaixo no evento Click do botão : ContatosPorIdade :

protected void btnContatosIdade_Click(object sender, EventArgs e)

{

   DataSet ds = new DataSet();

   SqlConnection conexao = null;

 

   try

   {

        conexao = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

          SqlCommand cmd = new SqlCommand("selecionaContatosPorIdade", conexao);

       cmd.CommandType = CommandType.StoredProcedure;

       cmd.Parameters.AddWithValue("@idade", Convert.ToInt32(txtIdade.Text));

         SqlDataAdapter da = new SqlDataAdapter(cmd);

       da.Fill(ds);

       lblmsg.Text = "Contatos com idade superior a " + Convert.ToInt32(txtIdade.Text);

       GridView1.DataSource = ds;

       GridView1.DataBind();

   }

    catch (Exception ex)

   {

      lblmsg.Text = ex.Message;

   }

    finally

   {

      conexao.Close();

    }

}

O código é praticamente idêntico ao anterior o único detalhe é a conversão do valor informado para a idade para inteiro : Convert.ToInt32(txtIdade.Text);

Para dar um toque diferente implementei o código abaixo que destaca a linha selecionada no GridView :

public void ItemDataBoundEventHandler1(object sender, GridViewRowEventArgs e)

{

  if (e.Row.RowType == DataControlRowType.DataRow)

  {

     e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='Silver'");

    e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='#FFFBD6'");

  }

}

A seguir o projeto exibindo o resultado para as store procedures :  exibeContatos e selecionaContatosPorCidade.

Com isso concluímos o nosso objetivo que é mostrar como criar e usar stored procedures em páginas ASP .NET com C#. Não houve a preocupação em trabalhar com uma camada de acesso a dados , o que sempre é recomendável, mesmo para aplicações bem simples com esta.

Pegue o projeto completo aqui : sp_GridView.zip

Eu sei é apenas ASP .NET e C#, mas eu gosto...

Referências:

José Carlos Macoratti