Enviando dados para um webservice usando o SQL Server

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.

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 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.

Anúncios

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: