Seções iMasters
.NET + Banco de Dados + SQL Server + Visual Basic

Enviando dados para um webservice usando o SQL Server

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.

Comente também

5 Comentários

Poderia poupar tempo de ler o artigo inteiro, e ficar só na trigger mesmo.

    Diogo Luiz Corrêa Ponce

    Olá amigo, primeiramente obrigado pelo comentário.
    Quando terminei o artigo eu também achei que ele tinha ficado grande (-; mas a minha intenção é mostrar todo o processo para que até programadores novatos possam acompanhar o raciocínio e entender esse assunto que é um pouco delicado.

Thales Valentim

Muito bom o artigo! Creio que é uma grande desafio achar documentos de qualidade e bem explicado como o seu. Até para profissionais experientes, é importante analisar todo o processo, pois abre margens para novos caminhos nos nossos sistemas.

sergio bastos

Olá, criei minha dll seguindo seus paços, porém nao estou conseguindo criar o assembly.

CREATE ASSEMBLY Assinantes
FROM ‘C:\subscribe\Assinantes.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

erro:
Mensagem 6257, Nível 16, Estado 1, Linha 2
Falha de CREATE ASSEMBLY para o assembly ‘hsmAssinantes’ porque o assembly foi criado para uma versão sem suporte do Common Language Runtime.

festkl?nningar 2013e

If you set out to make me think today; mission accomplished! I really like your writing style and how you express your ideas. Thank you. festkl?nningar 2013e http://rangeping95.soup.io/post/302674028/Strategies-for-Getting-a-Marriage-ceremony-Guest

Qual a sua opinião?