SQL Server – Como capturar dados de uma página Web (Webscraping com HttpRequest) e inserir os dados no banco com SQLCLR (C#)

Visualizações: 1.069
Tempo de Leitura: 8 minutos

Fala galera!
Tudo bem com vocês ?

Neste post eu gostaria de compartilhar uma solução que precisei desenvolver na minha empresa para realizar um web scraping e extrair dados das agências do site dos Correios (http://www2.correios.com.br/sistemas/agencias/). Como vocês devem imaginar, para atingir essa solução e trazer os dados para dentro do meu banco de dados rapidamente, utilizei o meu velho companheiro SQLCLR para realizar essa tarefa não tão simples, já que o site dos correios continha alguns erros no XHTML do site e isso acabou complicando um pouco a minha vida para tratar os dados.

Para conseguir tratar esses dados, vou utilizar técnicas de expressões regulares (Regexp ou Regex), as quais demonstrei seu uso no meu último post SQL Server – Como utilizar expressões regulares (RegExp) no seu banco de dados, portanto, recomendo a leitura desse artigo de Regexp antes desse artigo aqui.

Informação no site de origem:

Informação retornada pela Stored Procedure utilizando Web Scraping:

Como funciona a solução na teoria

Vou tentar resumir os passos para realizar o web scraping e explicar como fiz para conseguir atingir esse resultado.

  • O primeiro passo é identificar a URL utilizada para retornar os dados via Ajax no site

  • Agora que identifiquei a URL, o próximo passo é garantir que o nome da cidade não tenha acentos (Ex: Vitória). Para isso, vou utilizar a função fncRemove_Acentuacao (vou liberar os códigos mais abaixo).
  • Outro ponto importante, é codificar a URL para transformar espaços e outros caracteres como entidades HTML. (Ex: Espaço = %20). Para isso, vou utilizar a função fncCodifica_URL
  • Agora já posso montar a URL completa para enviar a requisição HttpRequest. Para isso, vou utilizar a Stored Procedure stpWs_Requisicao, que realiza a requisição e retorna o resultado em uma string de output
  • O retorno dessa requisição é o código HTML retornado pelo site, que é nesse formato:

  • Como vocês podem reparar, não é uma tarefa trivial tratar esse XHTML, ainda mais que ele contém alguns elementos que são abertos e não são fechados, gerando erro na hora de tentar converter para XML e tratar pelo SQL Server. Diante disso, vou tratar o retorno utilizando outra técnica: Expressão regular (RegExp)
  • Utilizo a máscara “<td .*?>(.*?)</td>” na função fncRegex_Match para retornar todos os valores que estão dentro das tags td. Aliado a isso, utilizo também as funções fncRemove_Html_String para transformar uma string HTML para texto e também a função fncRecupera_Letras_Sem_Caractere_Especial para remover quaisquer caracteres especiais na string.
  • A partir daí, vou aplicar alguns filtros para separar e categorizar os resultados desses tratamentos e assim conseguir classificar corretamente as informações e retornar esses dados no formato de tabela

Código-fonte da solução

Agora que expliquei na teoria como funciona a solução, chegou a hora de aplicá-la e demonstrá-la na prática.

Código-fonte do assembly SQLCLR, que será utilizado nesse artigo (T-SQL):

Código-fonte da stpBusca_Agencias_Correios (T-SQL):
Visualizar código-fonte

Código-fonte dos objetos SQLCLR

Acima, eu disponibilizei o fonte para a criação do assembly já pronto com as funções do SQLCLR e também os objetos de banco de dados (funções e Stored Procedure) para utilizar o assembly. Isso facilita muito a utilização desses objetos e não requer conhecimentos em C# ou ter o Visual Studio na sua máquina para compilar a DLL do SQLCLR.

Caso você seja um cara que goste de conhecer tudo a fundo e entender o código do SQLCLR, vou disponibilizar aqui também o código original desses objetos, para você incluir no seu projeto de SQLCLR, por exemplo.

Se você não conhece o SQLCLR, não pode deixar de ler os artigos abaixo:
Introdução ao SQL CLR (Common Language Runtime) no SQL Server
SQL Server – Comparação de performance entre Scalar Function e CLR Scalar Function
Outros posts sobre SQLCLR

Código-fonte da Procedure stpWs_Requisicao (C#):
Visualizar código-fonte

Código-fonte da fncCodifica_URL (C#):
Visualizar código-fonte

Código-fonte da fncRemove_Acentuacao (C#):
Visualizar código-fonte

Código-fonte da fncRegex_Match (C#):
Visualizar código-fonte

Código-fonte da fncRemove_Html_String (C#):
Visualizar código-fonte

Código-fonte da fncRecupera_Letras_Sem_Caractere_Especial (C#):
Visualizar código-fonte

É isso aí, pessoal!
Espero que tenham gostado desse post e até a próxima!

Abraço!