Trabalhando com Stored Procedures no .NET
Procedures são recursos interessantes e amplamente utilizandos no desenvolvimento de aplicativos por vários motivos. O principal destes motivos é que, uma procedure é um código independente que fica pré-compilado no servidor de banco de dados, assim, retiramos a onerosidade de processamento da aplicação e a passamos para o servidor sendo que, a aplicação, apenas recebe o resultado do processamento destas procedures e continua seu fluxo de execução.
Bom estou trabalhando com a linguagem C# da plataforma .NET para efetuarmos uma inserção de usuário no banco de dados e, posteriormente, apresentar na tela se o registro foi inserido com sucesso ou não. Este exemplo é baseado no modelo em camadas.
Bom vamos lá, mãos a obra. Vamos então criar o projeto. Após criar as camadas, as classes de cada camada e configurar as referências das mesmas, sua Solution Explorer deve se parecer com aquela apresentada na Figura 1:
Figura 1: Solution Explorer da Solution UsuarioProcedureCom nosso projeto criado e configurado, vamos então a implementação das classes. Vamos começar a trabalhar da camada mais baixa para a camada mais alta, ou seja, implementaremos em primeira instância a classe clsDados.cs. A Listagem 1 apresenta o código desta classe.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
namespace Core.DAL
{
public class clsDados
{
#region ------------------ ATRIBUTOS DA CLASSE DADOS -------------------
private static string stringConexao = @"Data Source=FABRÍCIO-PC;Initial Catalog=db_Teste;User ID=sa;Password=shell";
private static SqlConnection conexaoDecup = new SqlConnection(stringConexao);
#endregion
#region ------------------ MÉTODOS ------------------
///
/// Este método encerra a conexão com o Banco de Dados.
///
public SqlConnection fechaConexao()
{
if (conexaoDecup.State == ConnectionState.Open)
{
conexaoDecup.Close();
}
return conexaoDecup;
}
///
/// Este método verifica o status da conexão com o Banco de Dados. Caso esta esteja fechada, o método abre a conexão.
///
public SqlConnection abreConexao()
{
if (conexaoDecup.State == ConnectionState.Closed)
{
conexaoDecup.Open();
}
return conexaoDecup;
}
#endregion
}
}
Listagem 1: Fonte da classe clsDados
Novamente não entrar nos detalhes da implementação da classe por não ser o objetivo aqui, entretanto, algumas características da mesma precisam de um pouco mais de nossa atenção. Primeiramente, as linhas abaixo:
private static string stringConexao = @"Data Source=FABRÍCIO-PC;Initial Catalog=db_Teste;User ID=sa;Password=shell";
private static SqlConnection conexaoDecup = new SqlConnection(stringConexao);
Nas linhas acima criamos uma variável e um objeto respectivamente: stringConexao e conexaoDecup. Como podemos notar, stringConexao recebe a string de conexão com a base SQL Server. Em seguida, o objeto conexaoDecup estabelece a conexão com a base através do construtor da classe SqlConnection.
Outro aspecto importante a ressaltar neste código é a criação de dois métodos – fechaConexao() e abreConexao(). Estes métodos serão os responsáveis por abrir a conexão com a base de dados e fechá-la em um momento posterior respectivamente.
Como já foi dito, o foco deste artigo é apresentar a utilização das procedures em conjunto com a linguagem C#.
Para apresentar esta funcionalidade, iremos realizar uma inserção de registro no banco utililzando uma procedure. A estrutura da tabela onde o registro será inserido pode ser visualizada na Figura 2:
Figura 2: Estrutura da tabela sobre a qual a procedure trabalhará
Bom, agora que conhecemos a estrutura da tabela tbUsuarios já possuímos as informações necessárias para criarmos nossa procedure. Para isso, na base de dados expanda a guia Programmability. Em seguida, clique com o botão direito sobre a pasta Stored Procedure e selecione a opção New Stored Procedure, conforme apresenta a Figura 3:
Figura 3: Criando uma nova Stored Procedure
Ao selecionar esta uma opção, uma nova query será criada com algumas sugestões do SQL Server para criar a procedure. Limpe as sugestões do SQL Server de modo que sua tela fique semelhante a Figura 4.
Figura 4: Tela de criação da procedure limpa
Vamos então criar a procedure. A Listagem 3 apresenta o código da SP (Stored Procedure) de inserção do usuário:
CREATE PROCEDURE ____Inserir_Usuario
-- Parâmetros de entrada da procedure
@NomeUsuario VARCHAR(100),
@EmailUsuario VARCHAR(100),
@UsuarioUsuario VARCHAR(20),
@SenhaUsuario VARCHAR(10)
AS
BEGIN
-- Inserindo o registro no banco
INSERT INTO dbo.tbUsuarios(Nome, Email, Usuario, Senha)
VALUES(@NomeUsuario, @EmailUsuario, @UsuarioUsuario, @SenhaUsuario)
END
GO
Listagem 3: Código fonte da SP de Inserção
O código da SP dispensa muitos comentários pois é relativamente simples. O que este trecho que código faz é simplesmente alocar quatro variáveis (parâmetros): @NomeUsuario, @EmailUsuario, @UsuarioUsuario e @SenhaUsuario. Estes parâmetros receberão os valores provenientes do método C# e os passarão o segundo bloco do código para inserção na SP. Como já mencionado no início deste artigo, as procedures ficam pré-compiladas no servidor de banco de dados, portanto, precisamos agora compilar a SP. Para isso, basta ir até o menu superior do SQL Server Management Studio e clicar na opção “Execute” ou simplesmente selecionar o código da SP e pressionar F5. Se tudo ocorreu bem, você deverá visualizar uma tela semelhante a apresentada na Figura 5:
Figura 5: Sucesso no processo de compilação da SP
Muito bem, criada a tabela de usuários onde iremos inserir o registro, criada e compilada a procedure que fará a inserção e criada e implementada a classe de acesso a dados na camada DAL, vamos agora até a camada de negócios (BLL) e implementar o método responsável por chamar a SP e passar os parâmetros para a mesma. A Listagem 4 apresenta o código da classe clsUsuario.cs.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Core.DAL;
using System.Net;
namespace Core.BLL
{
public class clsUsuario
{
///
/// Método que recebe os valores do formulário e os insere através da procedure na tabela tbUsuarios.
///
///
///
///
///
///
public int inserirUsuario(string _nome, long _mail, string _user, string _senha)
{
clsDados objetoDados = new clsDados();
SqlCommand cmdAjuda = new SqlCommand("____Inserir_Usuario", objetoDados.abreConexao());
cmdAjuda.CommandType = CommandType.StoredProcedure;
cmdAjuda.Parameters.AddWithValue("@NomeUsuario", _mail);
cmdAjuda.Parameters.AddWithValue("@EmailUsuario", _nome);
cmdAjuda.Parameters.AddWithValue("@UsuarioUsuario", _user);
cmdAjuda.Parameters.AddWithValue("@SenhaUsuario", _senha);
try
{
int nLinhas;
nLinhas = cmdAjuda.ExecuteNonQuery();
return nLinhas;
}
catch (SqlException ex)
{
throw new Exception("Erro encontrado: " + ex.Message);
}
finally
{
objetoDados.fechaConexao();
}
}
}
}
Listagem 4: Código fonte da classe clsUsuario
Os aspectos importantes na Listagem 4 são os seguintes:
SqlCommand cmdAjuda = new SqlCommand("____Inserir_Usuario", objetoDados.abreConexao());
cmdAjuda.CommandType = CommandType.StoredProcedure;
Nas linhas acima um objeto do tipo SqlCommand é criado e, é passado ao construtor deste objeto o nome da SP (____Inserir_Usuario) e o método que abre a conexão com o banco (objetoDados.abreConexao()); Na linha inferior, apenas dizemos ao objeto que ele deverá executar um procedimento do tipo StoredProcedure.
cmdAjuda.Parameters.AddWithValue("@NomeUsuario", _mail);
cmdAjuda.Parameters.AddWithValue("@EmailUsuario", _nome);
cmdAjuda.Parameters.AddWithValue("@UsuarioUsuario", _user);
cmdAjuda.Parameters.AddWithValue("@SenhaUsuario", _senha);
Nas linhas acima associamos cada valor que está vindo como parâmetro para o método inserirUsuario com o parâmetro que a SP está esperando. Vale a pena frisar que os valores passados para os parâmetros da SP devem ser do mesmo tipo, caso contrário a procedure não conseguirá inserir os dados.
Finalmente, no bloco do try temos a execução através do método executeNonQuery() do objeto cmdAjuda e, por extensão, da procedure. A informação importante aqui é que o método executeNonQuery() retornará o número de linhas afetadas pela procedure, assim, atribuímos o retorno da execução da procedure à variável inteira nLinhas para que possamos verificar posteriormente o sucesso ou falha da execução da procedure.
Ok, para finalizar temos que testar nosso aplicativo de cadastro certo? Muito bem, vamos então até a camada de visualização/apresentação e criarmos nossa interface. O modelo sugerido por mim é aquele apresentado na Figura 6:
Figura 7: Campos preenchidos com as informações solicitadas
Att,
Leonardo Rocha.
