Depois de uma breve introdução sobre SQL CLR, para a nossa
primeira funcionalidade da série de artigos sobre SQL CLR, vamos tratar das
Stored Procedure (procedimento armazenado), que podem ser utilizadas
basicamente para retornar valores, várias consultas e/ou manipular dados no SQL
Server.
Depois de criado o projeto SQL CLR, para criarmos uma Stored
Procedure com CLR, nós precisaremos adicionar um novo item (“New Item...” ou
“Stored Procedure...”) no projeto, determinando também as características
básicas deste item, como o nome do arquivo e o template, no casso “Stored
Procedure”.
Já com a Stored Procedure adicionada no projeto, partimos
para o código da nossa primeira funcionalidade, que é basicamente imprimir um
texto qualquer nas mensagens de notificação (como uma chamada ao comando PRINT
do SQL).
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
public partial
class StoredProcedures
{
[SqlProcedure]
public static void
Primeiro()
{
SqlContext.Pipe.Send("SQL From Hell!!!");
}
};
|
E adicionamos o seguinte comando SQL no arquivo script de
teste do projeto para executar nossa Stored Procedure.
Mais informações sobre DEBUG de projetos SQL CLR:
http://sqlfromhell.wordpress.com/2009/10/03/debug-clr
Após o Debug (F5), podemos validar o resultado na caixa de
Output do Visual Studio.
O segundo passo para desenvolver nosso procedimento
armazenado é a criação de parâmetros, que basicamente consiste na criação de
novos parâmetros no método.
|
using System;
using System.Data;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
public partial
class StoredProcedures
{
[SqlProcedure]
public static void
Primeiro(SqlString texto)
{
SqlContext.Pipe.Send(texto.ToString());
}
};
|
|
EXEC Primeiro 'SQL From Hell!!!'
|
E após o deploy deste procedimento armazenado, percebemos
que o nome do método (ex.: “Primeiro”) também será o nome do procedimento
armazenado no banco de dados, assim como os argumentos.
A possibilidade de criarmos sobrecargas uma procedure (dois
ou mais métodos com mesmo nome, mas como parâmetros distintos) não é permitida
pelo SQL Server, mesmo que seja possível gerar um assembly compilável. Desta
forma é recomendado definir nomes adequados para os nomes dos métodos para que
não se repitam.
Mas para não ser necessário alterar os nomes dos métodos
podemos determinar os nomes das procedures, simplesmente pela customização do
atributo SqlProcedure, conforme o exemplo:
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
public partial
class StoredProcedures
{
[SqlProcedure(Name = "USP_Primeiro")]
public static void
Primeiro(SqlString texto)
{
SqlContext.Pipe.Send(texto.ToString());
}
};
|
|
EXEC USP_Primeiro 'SQL From Hell!!!'
|
Que se refletirá no banco de dados, sem necessidade de
customização do script SQL de deploy do projeto CLR, conforme demonstrado
abaixo:
Mais informações sobre Deploy de projetos SQL CLR:
http://sqlfromhell.wordpress.com/2010/05/15/publicando-assemblies-clr-no-sql-server-pelo-visual-studio
Assim como podemos customizar o nome dos procedimentos
armazenados, também é possível customizar algumas características dos
parâmetros do método por meio de atributos (ex.: SqlFacet), conforme
demonstrada abaixo a definição da quantidade máxima de 40 caracteres do
parâmetro “texto”:
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
public partial
class StoredProcedures
{
[SqlProcedure(Name
= "USP_Primeiro")]
public static void
Primeiro
([SqlFacet(MaxSize = 40)]SqlString
texto)
{
SqlContext.Pipe.Send(texto.ToString());
}
};
|
|
EXEC USP_Primeiro 'SQL From Hell!!!'
|
E como resultado, novamente sem qualquer customização no
script SQL de deploy:
Desta forma, também será possível tratar as características
dos tipos decimais (ou numéricos), como no exemplo que determinamos um
parâmetro decimal de tamanho 10 (precisão), tendo duas casas após a vírgula (escala).
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
public partial
class StoredProcedures
{
[SqlProcedure(Name
= "USP_Primeiro")]
public static void
Primeiro
([SqlFacet(Precision = 10, Scale = 2)]SqlDecimal valor)
{
SqlContext.Pipe.Send(valor.ToString());
}
};
|
|
EXEC
USP_Primeiro
12345678.90
|
Após o deploy, os valores determinados pelos atributos
estarão refletidos no banco de dados, sem qualquer intervenção direta do
desenvolvedor no script SQL:
Outras características também podem ser trabalhadas com os
atributos, como atributos que recebem valores do procedimento armazenado (OUTPUT):
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
public partial
class StoredProcedures
{
[SqlProcedure(Name
= "USP_Primeiro")]
public static void
Primeiro
(out
SqlString texto)
{
texto = new
SqlString("Executado");
}
};
|
|
DECLARE
@VAR NVARCHAR(4000)
EXEC
USP_Primeiro @VAR OUT
PRINT
@VAR
|
Ou parâmetros que têm seus valores alterados durante a
execução de procedimentos armazenados (INPUT/OUTPUT):
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
public partial
class StoredProcedures
{
[SqlProcedure(Name
= "USP_Primeiro")]
public static void
Primeiro
(ref
SqlString texto)
{
SqlContext.Pipe.Send(texto.ToString());
texto = new
SqlString("VALOR
2");
}
};
|
|
DECLARE @VAR NVARCHAR(4000)
SET @VAR = N'VALOR 1'
EXEC
USP_Primeiro @VAR OUT
PRINT @VAR
|
Para as duas situações demonstradas anteriormente, o deploy
se encarregará de criar estruturas adequadas para os procedimentos armazenados.
Com os procedimentos armazenados também é possível retornar
valores inteiros, como detalhado abaixo.
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using
Microsoft.SqlServer.Server;
public partial
class StoredProcedures
{
[SqlProcedure(Name
= "USP_Primeiro")]
public static SqlInt32
Primeiro()
{
return 333;
}
};
|
|
DECLARE
@VAR INT
EXEC
@VAR =
USP_Primeiro
PRINT @VAR
|
Concluindo este artigo, espero que tenham gostado desta
primeira demonstração dos procedimentos armazenados, onde podemos identificar
conceitos básicos de como o deploy se comporta com a customização do CLR, e a
facilidade de customizar características dos procedimentos armazenados, sem a
necessidade de envolvimento direto com o script de deploy.
No próximo artigo, estarei trabalhando as três principais
formas de trabalhar com Stored Procedures desenvolvidas em CLR para retornar
resultados de consultas ou simplesmente criar um resultado qualquer.