O Common Language Runtime é o coração do .NET Framework. O SQL Server fornece recursos que permitem a incorporação de componentes CLR ao seu ambiente de execução, desta forma podemos construir stored procedures, triggers, user-defined functions, user-defined types e user-defined aggregates utilizando código gerenciado.
Este post descreve os passos necessários para criação de um componente SQL CLR para o registro de mensagens no event log do Windows a partir da execução de stored procedures.
Trabalhando em alguns sistemas, nos foi recomendado, pelo próprio cliente, que fizéssemos uso do operador NOLOCK em todos os comandos de consulta que fizéssemos no banco de dados. Isso parece uma atitude exagerada, mas por experiência do cliente, com receio de deadlocks, tínhamos de seguir à risca essa indicação.
O SQL Server utiliza recursos de bloqueio (tanto para escrita quanto leitura) para garantir a integridade dos dados. Enquanto executamos uma transação seguimos o conceito ACID (se você não conhece este conceito, veja este post Entity Framework 6 – Database.BeginTransaction() e Database.UseTransaction(DbTransaction)). Por conta disso, dentro de uma transação qualquer comando paralelo que tente acessar os dados com os quais estamos trabalhando entram nesse bloqueio, e assim são forçados a aguardar o término de nossa transação.
O Windows PowerShell é um prompt de comando muito poderoso. Com ele podemos criar scripts para automatização de tarefas, consumir componentes Microsoft .Net, objetos COM e outras APIs de aplicações Microsoft (como por exemplo Sharepoint, Exchange, Active Diretory, etc).
Devido ao seu uso bastante genérico, sempre surgem diferentes situações para o seu emprego. Gostaria de demonstrar nesse post como é feita a leitura e inclusão de dados em bases de dados SQL via Windows PowerShell.
Transações são necessárias quando realizamos um conjunto de operações em um determinado recursos distribuído (banco de dados, serviço de mensageria, serviço de gerenciamento de arquivos, etc) e queremos garantir a completude deste conjunto de operações ao seu término, evitando que qualquer possível erro durante a execução do conjunto de operações deixe os recursos distribuídos inconsistentes.
Em versões anteriores ao SQL Server 2008 não é possível utilizar tabelas como parâmetro de Stored Procedures. Quando temos de enviar múltiplos dados para o banco de dados, acabamos por enviar tais dados um de cada vez, linha por linha, orientados por um laço de repetição. O SQL Server 2008 proporciona um contorno para esta solução através do uso de Table-Valued Parameters. Os Table-Valued Parameters permitem a passagem de tabelas como parâmetro para Stored Procedures.
Para a utilização deste recurso é necessário seguir um conjunto de passos, vou enumerá-los a seguir:
1 – Tipo dados de tabela
A primeira coisa a se fazer é criar um tipo de dados de tabela (table type). Para a criação deste tipo de dados deve-se utilizar a seguinte sintaxe.
CREATE TYPE dbo.PointAttention AS TABLE
(
Id int NOT NULL,
Description varchar(100) NOT NULL,
Date datetime NOT NULL,
PRIMARY KEY (Id)
)
GO
2 – Trabalhando com o tipo de dados de tabela
Para utilizar este tipo de dados, na sintaxe Transaction-SQL, é preciso criar uma variável deste tipo de dados e então valorizá-la como se fosse uma tabela.
DECLARE @varPoint PointAttention
INSERT INTO @varPoint(Id,Description,Date)
VALUES (100,’Alerta de segurança 1′,’2011-01-20′),
(101,’Alerta de segurança 2′,’2011-01-20′),
(102,’Alerta de segurança 3′,’2011-01-20′),
(103,’Alerta de segurança 4′,’2011-01-20′),
(104,’Alerta de segurança 5′,’2011-01-20′)SELECT * FROM @varPoint
Assim como qualquer outra tabela, variáveis desse tipo podem ser utilizadas para popular outras tabelas ou servirem em clausulas JOIN dentro de comandos de seleção.
* Observação: por ser uma variável, será limpa da memória automaticamente assim que o escopo de execução for concluído, não consumindo recursos além daqueles necessários durante seu bloco de execução.
3 – Utilizando tipos de dados tabela como parâmetro de Stored Procedures
Agora vamos ao ponto focal deste texto, que é receber uma estrutura de tabela como parâmetro de uma Stored Procedure.
Para execução do exemplo, criemos uma tabela que possua uma estrutura semelhante a do nosso tipo de dados tabela.
CREATE TABLE dbo.tbPointAttention
(
pntId int NOT NULL PRIMARY KEY,
pntDescr varchar(20) NOT NULL,
pntDate datetime NOT NULL
)
GO
Agora, para construção da Stored Procure mantemos a mesma sintaxe, apenas incluindo um parâmetro com o mesmo tipo de dados de tabela criado anteriormente.
CREATE PROC spInsertPAttention @PAttentions PointAttention READONLY
AS
INSERT INTO tbPointAttention
(pntID, pntDescr, pntDate)
SELECT ID,Description,Date
FROM @PAttentions
GO
4 – Consumindo tipos de dados tabela através de aplicações .Net
O ponto chave de utilização deste novo tipo de estrutura é a utilização do tipo de dado SqlDbType.Structured (presente no namespace System.Data.SQLClient, disponível a partir do Microsoft .Net Framework 3.5).
O exemplo abaixo demonstra como essa interação pode ser codificada utilizando C#.
Vantagens da utilização de Table-Valued Parameters:
– Simplificação da lógica do projeto;
– Permite aos desenvolvedores construírem aplicações performáticas;
Referências:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
http://msdn.microsoft.com/en-us/library/bb675163.aspx
http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters
Por
Fernando Henrique Inocêncio Borba Ferreira.
Fernando Henrique Ferreira 