Veremos como acessar e enviar dados para um webservice usando o recurso Assembly do SQL Server, uma ferramenta que pode ajudar muito na hora de realizar tarefas complexas onde apenas os recursos nativos não são o bastante.
O recurso Assembly pode estender (e muito) o poder de processamento do SQL Server utilizando um aplicativo escrito em VB, C# e F#.
O cliente
Agora vamos imaginar o seguinte cenário: uma livraria contratou você para adicionar novas funcionalidades no programa que ela já possui, um programa que gerencia as vendas e todo o estoque.
Se o programa foi criado por você fica fácil resolver o problema, mas se esse programa foi criado por uma empresa e você não tem acesso ao código-fonte ou se tem acesso, a linguagem usada não é a mesma que você programa. Fica complicado resolver!
A solução
Então você descobre que o banco de dados utilizado é o SQL Server e você pode acessá-lo facilmente, agora ficou melhor!
Graças ao Assembly nosso problema ficou fácil de resolver!
Só precisamos programar um pouco e depois criar um assembly no SQL Server para executar a tarefa especificada por nosso cliente.
Especificações do cliente
Atualmente nosso aplicativo de gerenciamento não se integra com nossa plataforma web e cada vez que um novo livro chega em nosso estabelecimento devemos cadastra-lo 2 vezes, uma no sistema de gerenciamento da loja (física) e outra no sistema web onde vendemos nossos produto.
O problema é que além da grande perda de tempo cadastrando o mesmo produto 2 vezes, outro ponto crítico é que se um produto está em falta no estoque meus funcionários devem atualizar o estoque do site, o que nem sempre acontece.
É fundamental que as tarefas citadas sejam automáticas e independente de manipulação por parte dos meus funcionários.
Como você pode notar que existe duas tarefas que devem ser feitas, a inclusão de produtos na loja virtual quando são cadastrados no sistema de gerenciamento e atualização do estoque quando uma venda é realizada. Tudo isso será feito através do webservice que será criado para o cliente.
O sistema deve funcionar como na ilustração abaixo.
Criando o webservice (exemplo)
Vamos criar um webservice muito simples, nosso webservice vai apenas incluir novos produtos e dar baixa no estoque no momento que um produto é vendido.
Vamos ao código!
A criação das tabelas são apenas para ilustrar, de forma mais simples, as tabelas que o cliente já possui na loja virtual .
1 – Crie a tabela Livros;
CREATE TABLE [dbo].[Livros](
[Codigo] [int] IDENTITY(1,1) NOT NULL,
[DescricaoProduto] [nvarchar](max) NOT NULL,
[ValorProduto] [float] NOT NULL,
[ISBN] [bigint] NOT NULL,
[QtdEstoque] [int] NOT NULL,
CONSTRAINT [PK_Livros] PRIMARY KEY CLUSTERED
(
[Codigo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2 – Crie a tabela Vendas onde vamos guardar as vendas feitas no site;
CREATE TABLE [dbo].[Vendas](
[Codigo] [int] IDENTITY(1,1) NOT NULL,
[DataCompra] [datetime] NOT NULL,
[CodLivro] [int] NOT NULL,
[QtdComprada] [int] NOT NULL,
CONSTRAINT [PK_Vendas] PRIMARY KEY CLUSTERED
(
[Codigo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Vendas] WITH CHECK ADD CONSTRAINT [FK_Vendas_Livros] FOREIGN KEY([CodLivro])
REFERENCES [dbo].[Livros] ([Codigo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Vendas] CHECK CONSTRAINT [FK_Vendas_Livros]
GO
ALTER TABLE [dbo].[Vendas] ADD CONSTRAINT [DF_Vendas_DataCompra] DEFAULT (getdate()) FOR [DataCompra]
GO
3 – Agora vamos criar um novo projeto do tipo webservice, o meu VS está em português;
4 – Vamos codificar nosso webservice;
''' <summary>
''' Procedimento necessário para incluir um novo livro na loja virtual
''' </summary>
''' <param name="DescricaoProduto">Descrição completa do livro</param>
''' <param name="ValorProduto">Preço do livro</param>
''' <param name="ISBN">ISBN do livro</param>
''' <param name="QtdEstoque">Quantidaden disponível em estoque</param>
<WebMethod()> _
Public Function IncluirNovoLivro(ByVal DescricaoProduto As String, ByVal ValorProduto As Double, _
ByVal ISBN As Integer, ByVal QtdEstoque As Integer) As Boolean
Try
Dim cmdSQL As New SqlClient.SqlCommand("INSERT INTO dbo.Livros (DescricaoProduto, ValorProduto, ISBN, QtdEstoque) VALUES (@DescricaoProduto, @ValorProduto, @ISBN, @QtdEstoque)", objConexao)
With cmdSQL
.CommandType = CommandType.Text
.Parameters.AddWithValue("@DescricaoProduto", DescricaoProduto)
.Parameters.AddWithValue("@ValorProduto", ValorProduto)
.Parameters.AddWithValue("@ISBN", ISBN)
.Parameters.AddWithValue("@QtdEstoque", QtdEstoque)
If .ExecuteNonQuery() > 0 Then
Return True
Else
Return False
End If
End With
Catch ex As Exception
Return False
End Try
End Function
''' <summary>
''' Função que da baixa no estoque da loja virtual.
''' </summary>
''' <param name="CodigoProduto">Código do produto</param>
''' <param name="QtdVendido">Quantidade de produtos vendidos</param>
<WebMethod()> _
Public Function BaixaNoEstoque(ByVal CodigoProduto As Integer, ByVal QtdVendido As Integer)
Try
If QtdVendido > 0 Then
Dim cmdSQL As New SqlClient.SqlCommand("UPDATE dbo.Livros SET QtdEstoque = QtdEstoque - @QtdVendida WHERE Codigo = @Codigo", objConexao)
With cmdSQL
.CommandType = CommandType.Text
.Parameters.AddWithValue("@QtdVendida", QtdVendido)
.Parameters.AddWithValue("@Codigo", CodigoProduto)
If .ExecuteNonQuery() > 0 Then
Return True
Else
Return False
End If
End With
Else
Return False
End If
Catch ex As Exception
Return False
End Try
End Function
Agora que o webservice está pronto vamos criar o Assembly do SQL Server.
Usando o SQL Server para enviar as informações
Agora chegou a hora de criar o Assembly, antes disso nós precisamos saber mais algumas coisas sobre como o Assembly funciona no SQL Server. Um assembly é uma biblioteca dinâmica (.dll) que pode ser escrito utilizando as principais linguagens da Microsoft como o VB.NET, C# e F#.
Para utilizar o assembly no SQL Server é preciso seguir algumas regras de segurança exigidas no momento da criação, ao criar um assembly o SQL Server primeiro vai validá-lo para ter certeza de que ele não carrega códigos do tipo inseguro.
A validação do SQL Server faz as seguintes verificações antes de criar nosso assembly:
- Os segmentos de código deve ter instruções do Microsoft Intermediate Language (MSIL) válidas;
- Deve fazer referência apenas para assemblies do .NET Framework válidos, atualmente são suportados os seguintes assemblies;
custommarshallers.dll, Microsoft.visualbasic.dll, Microsoft.visualc.dll, mscorlib.dll, system.data.dll, System.Data.SqlXml.dll, system.dll, system.security.dll, system.web.services.dll, system.xml.dll, System.Transactions, System.Data.OracleClient, System.Configuration;
- Obedecer as regras da permissão que foi dada durante o camando CREATE ASSEMBLY, veja os tipos de permissões disponíveis:
- SAFE – é o conjunto de permissões mais restritivo e recomendado, o código executado por um assembly com as permissões SAFE não pode acessar recursos externos do sistema;
- EXTERNAL_ACCESS – permite que os assemblies acessem certos recursos externos do sistema, como arquivos, redes, variáveis de ambiente e o Registro;
- UNSAFE – concede aos assemblies acesso irrestrito aos recursos, internos ou externos, de uma instância do SQL Server.
Agora vamos ao programar um pouco.
1 – Inicie seu visual studio e crie um novo projeto chamado LivrariaFict do tipo Biblioteca de Classes ou Class Library (.dll);
2 – Agora vamos programar a inclusão do livro e a baixa no estoque;
Importe os namespaces abaixo.
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Xml
O atributo <SqlProcedure> marca este código como um procedimento armazenado e <SqlTrigger()> indica que a função será usada como uma Trigger. O atributo não é obrigatório, mas é boa forma de documentação do código.
OBS.: Para utilizar os métodos o SQL Server exige que todos eles sejam estáticos.
Public Class MateriaClrSQLServer
''' <summary>
''' Pega o XML retornado pelo webservice e retorna 0/1 de forma compatível com SQL Server
''' </summary>
''' <param name="xmlRetorno">XML que é retornado quando a requisição ao webservice é concluída.</param>
''' <returns> 1 - True / 0 - False </returns>
Public Shared Function getRetorno(ByVal xmlRetorno As String) As SqlTypes.SqlInt32
Dim xmlLeitor As New XmlDocument()
xmlLeitor.LoadXml(xmlRetorno)
Dim blnRetorno As XmlNodeList = xmlLeitor.GetElementsByTagName("boolean")
If blnRetorno(0).InnerText = "true" Then
getRetorno = 1
Else
getRetorno = 0
End If
End Function
'Função que será usada como Procedimento Armazenado dentro do BD
<SqlProcedure()> _
Public Shared Function spIncluirNovoLivro(ByVal DescricaoProduto As String, ByVal ValorProduto As Double, _
ByVal ISBN As Long, ByVal QtdEstoque As Integer) As SqlTypes.SqlInt32
Dim objEnviarDados As New System.Net.WebClient()
objEnviarDados.Headers.Add("Content-Type", "application/x-www-form-urlencoded")
Dim strArgumentos As String = "DescricaoProduto=" & Trim(DescricaoProduto) & "&ValorProduto=" & ValorProduto & "&ISBN=" & ISBN & "&QtdEstoque=" & QtdEstoque
Dim bArgumentos As Byte() = System.Text.Encoding.ASCII.GetBytes(strArgumentos)
Dim bRetorno As Byte() = objEnviarDados.UploadData("http://localhost:4740/LivrariaFict.asmx/IncluirNovoLivro", "POST", bArgumentos)
Return getRetorno(System.Text.Encoding.ASCII.GetString(bRetorno))
End Function
'A tag abaixo informa o nome da trigger, a tabela onde ela será acionada e o evento que
'vai dispara o gatilho
<SqlTrigger(Name:="trgBaixaEstoque", Target:="[dbo].[Vendas]", Event:="FOR INSERT")> _
Public Shared Sub trgBaixaEstoque()
''Fornece informações contextuais sobre as Trigger que foi acionada.
Dim trgContexto As SqlTriggerContext = SqlContext.TriggerContext
Dim objDataReader As SqlDataReader = Nothing
Dim CodigoProduto As Integer = 0, QtdVendido As Integer = 0
''Se a ação realizada for insert entra no bloco
If trgContexto.TriggerAction = TriggerAction.Insert Then
'Pegamos a conexão usada na operação
Dim Conexao As New SqlConnection("context connection=true")
Conexao.Open()
'Pegamos os dados inseridos na tabela
Dim cmdTrg As New SqlCommand("SELECT * FROM INSERTED;", Conexao)
objDataReader = cmdTrg.ExecuteReader
objDataReader.Read()
'Pega as colunas da tabela
CodigoProduto = objDataReader!CodLivro.ToString
QtdVendido = objDataReader!QtdComprada.ToString
'Envia os dados
Dim objEnviarDados As New System.Net.WebClient()
objEnviarDados.Headers.Add("Content-Type", "application/x-www-form-urlencoded")
Dim strArgumentos As String = "CodigoProduto=" & CodigoProduto & "&QtdVendido=" & QtdVendido
'Configura os argumentos
Dim bArgumentos As Byte() = System.Text.Encoding.ASCII.GetBytes(strArgumentos)
objEnviarDados.UploadData("http://localhost:4740/LivrariaFict.asmx/BaixaNoEstoque", "POST", bArgumentos)
objDataReader.Close()
End If
End Sub
End Class
Eu decidi utilizar o método de envio diretamente por HTTP POST ao invés do método tradicional. O visual studio usa o namespace System.ServiceModel no método tradicional e para usá-lo dentro do SQL Server seria necessário importar vários assemblies junto, isso iria causar muita confusão entre os usuários menos experientes.
Após compilar a DLL é necessário importá-la para o SQL Server.
5 – Para utilizar o Assembly é necessário habilitá-lo e dar as permissões necessárias para o banco de dados;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
ALTER DATABASE LivrariaFict SET TRUSTWORTHY ON;
GO
6– Agora é só criar o Assembly no banco de dados;
CREATE ASSEMBLY LivrariaFict FROM 'C:SqlAcessarWebservice.dll' WITH PERMISSION_SET = UNSAFE
7 – Após a criação do Assembly no banco de dados, vamos criar a STORED PROCEDURE que vai executar nosso assembly e incluir novos títulos na loja virtual do cliente;
CREATE PROCEDURE [dbo].[spIncluirNovoLivro]
@DescricaoProduto [nvarchar](max),
@ValorProduto [float],
@ISBN [bigint],
@QtdEstoque [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [LivrariaFict].[LivrariaFict.MateriaClrSQLServer].[spIncluirNovoLivro]
Também é possível criar uma trigger que atualiza o estoque da loja virtual no momento que uma venda é realizada na loja física.
CREATE TRIGGER trgBaixaEstoque
ON [Vendas]
AFTER INSERT
AS EXTERNAL NAME [LivrariaFict].[LivrariaFict.MateriaClrSQLServer].[trgBaixaEstoque]
Você pode criar uma trigger que executa a STORED PROCEDURE spIncluirNovoLivro para cadastrar automaticamente um novo livro na loja virtual, ou pode modificar o código e criar uma trigger diretamente no banco de dados como foi feito a pouco.
Por enquanto é só, espero que tenham gostado e aproveitem o poder do Assembly no SQL Server com responsabilidade.
Até a próxima.
De 0 a 10, o quanto você recomendaria este artigo para um amigo?