Enviando dados para um webservice usando o SQL Server
08/07/2011 Deixe um comentário
Nesse artigo 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.
2 – Agora vamos programar a inclusão do livro e a baixa no estoque;
Importe o namespace 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 habilita-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 um 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.