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", "[email protected]");
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"]);
}
}