por Cesar Cassiano Schimanco

Stored Procedure com ASP.NET C#

Depois de vários questionamentos sobre como criar e como utilizar stored procedures no C#,
resolvi criar este artigo.
Outra pergunta, qual a diferença entre executar uma query no C# e usar stored procedure?
Bom, eu particularmente prefiro usar as stored procedures apenas para fazer coisas mais complexas, abaixo vou citar alguns dos benefícios das stored procedures.

  • O código fica todo centralizado no SQL.
  • Stored procedures são pré-compiladas, isso normalmente as torna mais rápidas na execução. Pois no C#, a cada chamada a query é compilada novamente.
  • Quando estiver usando um banco de dados remoto, menos dados serão enviados, apenas o nome e os parâmetros.

Resumo: Em termos de desempenho, usar stored procedures pode não fazer nenhuma diferença, e se fizer, elas podem ser imperceptíveis. A grande vantagem é centralizar todas as consultas e ter um projeto mais limpo. Sem contar que a query vai ficar colorida e vai ter a ajuda do intellisense para auto completar...
 

Vamos iniciar criando uma stored procedure e depois vou mostrar como alterar ela.
 
Para quem usa o SQL Server Management Studio em português.

Criar uma stored procedure Editar uma stored procedure
   

 

Para quem usa o SQL Server Management Studio em inglês.

Criar uma stored procedure Editar uma stored procedure
   

 

Exemplo de como criar uma stored procedure

-- ================================================
--Criar nova PROCEDURE
CREATE PROCEDURE spInsertUser
   -- Adicionar os parâmetros da stored procedure
   -- Nome e tipo do parâmetro.
   @userName NVARCHAR(400),
   @userEmail NVARCHAR(400)
AS
BEGIN
   --variável para o numero de usuários cadastrados
   DECLARE @count AS INT;
   --Setando o número de usuários a variável @count
   SET @count = (SELECT COUNT(*) FROM tblUsers);
   
   --Verificar o número de usuários cadastrados
   IF @count < 5
       BEGIN
           INSERT INTO tblUsers
           (userName, userEmail, userPhone)
           VALUES
           (@userName,@userEmail,@userPhone)
       END
   END

Exemplo de como editar/modificar uma stored procedure

-- ================================================
--ALTERAR PROCEDURE
ALTER PROCEDURE [dbo].[spInsertUser]
   -- Adicionar os parâmetros da stored procedure
   -- Nome e tipo do parâmetro.
   @userName NVARCHAR(400),
   @userEmail NVARCHAR(400),
   @userPhone NVARCHAR(15)
AS
BEGIN
   --variável para o numero de usuários cadastrados
   DECLARE @count AS INT;
   --Setando o número de usuários a variável @count
   SET @count = (SELECT COUNT(*) FROM tblUsers);
   
   --Verificar o número de usuários cadastrados
   IF @count < 5
       BEGIN
           INSERT INTO tblUsers
           (userName, userEmail, userPhone)
           VALUES
           (@userName,@userEmail,@userPhone)
       END
   END

 O exemplo acima é de uma stored procedure que vai verificar se já existem 5 usuários cadastrados. Se tiver retorna 0, se não tiver, cadastra e retorna 1.

Como fica no C#

Exemplo 1 (Exemplo simples de como executar uma Stored procedure no C# para fazer um INSERT)

using (SqlConnection conn = new SqlConnection(
    "server=.\\SQLEXPRESS;Database=dbTeste;User ID=user;Password=password;Trusted_Connection=False;"))
{
    //Criando o SqlCommand
    SqlCommand cmd = new SqlCommand();
    //Setando a conexão para o SqlCommand
    cmd.Connection = conn;
    //Nome da stored procedure
    cmd.CommandText = "spInsertUser";
    //Definindo o tipo de comando como StoredProcedure
    cmd.CommandType = System.Data.CommandType.StoredProcedure;

    //Adicionar os parâmetros
    cmd.Parameters.AddWithValue("@userName", "Cesar Cassiano");
    cmd.Parameters.AddWithValue("@userEmail", "cesar@cbsa.com.br");
    cmd.Parameters.AddWithValue("userPhone", "00 0000 0000");

    //Abrir a conexão
    conn.Open();
    //Executar a stored procedure
    int intReturn = (int)cmd.ExecuteNonQuery();
    //Verifica se executou o INSERT
    if (intReturn != -1)
        Response.Write("Usuário cadastrado.");
    else
        Response.Write("Limite máximo de usuários atingido.");
    //Fechar a conexão
    conn.Close();
}

Exemplo 2 (Exemplo simples de como executar uma Stored procedure no C# para retornar dados fazendo um SELECT com IF)

Stored procedure

CREATE PROCEDURE spSelectUser
	@userName NVARCHAR(400),
	@userEmail NVARCHAR(400),
	@userPhone NVARCHAR(15)
AS
BEGIN
	SET NOCOUNT ON;

    DECLARE @sql AS NVARCHAR(4000); 
    --Montando a query, verificando se o usuário está ativo
    SET @sql = 'SELECT * FROM tblUsers WHERE userActive = 1 '
    --Verificar se o parâmetro @userName é null ou ''
    --caso for, ele não será adicionado a query.
    IF (@userName <> null OR @userName <> '')
		SET @sql = @sql + ' AND userName LIKE ''%' + @userName + '%'''
		
	--Verificar se o parâmetro @userEmail é null ou ''
    IF (@userEmail <> null OR @userEmail <> '')
		SET @sql = @sql + ' AND userEmail LIKE ''%' + @userEmail + '%'''
				
	--Verificar se o parâmetro @userPhone é null ou ''
    IF (@userPhone <> null OR @userPhone <> '')
		SET @sql = @sql + ' AND userPhone = ' + @userEmail
	
	--Executa a query
	EXECUTE(@sql)
END
GO

C#

using (SqlConnection conn = new SqlConnection(
    "server=.\\SQLEXPRESS;Database=dbTeste;User ID=user;Password=password;Trusted_Connection=False;"))
{
    //Criando o SqlCommand
    SqlCommand cmd = new SqlCommand();
    //Setando a conexão para o SqlCommand
    cmd.Connection = conn;
    //Nome da stored procedure
    cmd.CommandText = "spSelectUser";
    //Definindo o tipo de comando como StoredProcedure
    cmd.CommandType = System.Data.CommandType.StoredProcedure;

    //Adicionar os parâmetros
    cmd.Parameters.AddWithValue("userName", "Cassiano");
    //Enviando @userEmail = NULL
    cmd.Parameters.AddWithValue("userEmail", DBNull.Value);
    //Enviando @userPhone = ''
    cmd.Parameters.AddWithValue("userPhone", "");

    //Abrir a conexão
    conn.Open();

    //CommandBehavior vai fechar a conexão após usar o DataReader
    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

    //Lendo o DataReader
    while (dr.Read())
    {
        //Imprimir os dados do DataReader
        Response.Write(dr["userName"]);
        Response.Write(dr["userEmail"]);
    }
}

 

Comentários

Carregando comentários

Postar um novo comentário



Processando...