[Resolvido] Enviando dados para um webservice usando o SQL Server

[RSS iMasters 5]

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 FunctionAgora 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.XmlO 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 ClassEu 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;

GO6- Agora é só criar o Assembly no banco de dados;

 

CREATE ASSEMBLY LivrariaFict FROM 'C:SqlAcessarWebservice.dll' WITH PERMISSION_SET = UNSAFE7 - 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.

 

 

 

http://imasters.com.br/artigo/21531/sql-server/enviando-dados-para-um-webservice-usando-o-sql-server