Estendendo o SQL Server Reporting Services usando funções com valor de tabela SQL CLR

  • Artigo

Artigos técnicos do Microsoft SQL Server 9.0

Publicado em: 18 de abril de 2007

Por Ryan Ackley

Microsoft Corporation

Aplica-se ao:

Microsoft SQL Server 2005 Reporting Services

**Resumo:**Este artigo descreve como utilizar as funções com valor de tabela SQL CLR para combinar tipos diferentes de fontes de dados a fim de criar relatórios do SQL Server Reporting Services ricos e interessantes. (13 páginas impressas)

Clique aqui para baixar a versão deste artigo no formato de documento do Word: SSRSandTableValuedFunctions.doc.

Clique aqui para baixar o código de exemplo associado a este artigo: GetMoreChartsSamples.exe.

Conteúdo

Nesta página

Introdução Introdução
Estendendo o Reporting Services Estendendo o Reporting Services
Usando funções com valor de tabela Usando funções com valor de tabela
Usando funções com valor de tabela como alternativa às extensões de processamento de dados Usando funções com valor de tabela como alternativa às extensões de processamento de dados
Usando funções com valor de tabela com o SQL Server Reporting Services Usando funções com valor de tabela com o SQL Server Reporting Services
Serviços Web e funções com valor de tabela Serviços Web e funções com valor de tabela
Conclusão Conclusão

Introdução

Um novo recurso do Microsoft SQL Server 2005 é a sua integração com o CLR (Common Language Runtime) do .Microsoft .NET Framework. Isso permite a incorporação de funções e classes do .NET Framework em consultas e instruções Transact-SQL.

Existem diversos mecanismos de integração CLR disponíveis, como, por exemplo:

  • Funções CLR definidas pelo usuário (incluindo funções com valor de tabela).

  • Tipos CLR definidos pelo usuário.

  • Procedimentos armazenados CLR.

  • Disparadores CLR.

Este white paper mostrará como as funções com valor de tabela CLR podem ser usadas para criar dados de relatório a partir de várias fontes, além de bancos de dados, a fim de gerar relatórios do Reporting Services robustos.

Estendendo o Reporting Services

Existem diversas maneiras de estender o Reporting Services e integrar a funcionalidade CLR usando o .NET Framework. Por exemplo:

  • Extensões de entrega ― fornecem relatórios em resposta a um evento

  • Extensões de processamento ― exibem um relatório em formato diferente do aceito pelo Reporting Services

  • Extensões de segurança ― fornecem seu próprio mecanismo de autenticação e autorização para exibir e gerenciar relatórios

  • Extensões de processamento de dados ― podem ser desenvolvidas para processar dados de fontes de dados sem suporte no Reporting Services

  • Itens de relatório personalizados ― são controles do servidor personalizados que podem ser incorporados em relatórios para fornecer funcionalidade adicional além dos controles internos

Este artigo discute como implementar funções com valor de tabela para processar dados como alternativa ao uso de extensões de processamento de dados. Para obter mais informações sobre como estender o Reporting Services, consulte Extensões do Reporting Services nos Manuais Online do SQL Server 2005.

Usando funções com valor de tabela

As funções com valor de tabela são usadas para criar, via programação, uma tabela em tempo de execução. As tabelas criadas por elas poderão então ser usadas em instruções de consulta Transact-SQL como qualquer outra tabela de banco de dados. Quando as funções com valor de tabela foram introduzidas no SQL Server 2000, elas só podiam ser criadas com o uso de Transact-SQL. Veja a seguir um exemplo de função com valor de tabela implementada no Transact-SQL.

CREATE function EmployeeNames()
returns @employeeNames table (id int, name nvarchar(20), )
as begin
INSERT @employeeNames values(1, 'Ryan');
INSERT @employeeNames values(2, 'John');
INSERT @employeeNames values(3, 'Bob');
return
end

A função pode então ser referenciada de uma instrução SELECT como se fosse uma tabela:

SELECT name from EmployeeNames() where id = 1

A consulta retorna o seguinte:

name
--------------------
Ryan

Embora útil, esse procedimento é limitado pela linguagem Transact-SQL, que foi projetada para uso com dados relacionais. Se você tentar deixar esse domínio, o Transact-SQL se torna um tanto inflexível. No SQL Server 2005, agora é possível usar sua linguagem favorita do .NET Framework para criar funções com valor de tabela, abrindo algumas possibilidades surpreendentes. Os programadores podem abstrair as informações que desejarem em uma tabela de banco de dados relacional.

Por exemplo, o código a seguir é uma função com valor de tabela SQL Server 2005 implementada no Microsoft Visual C# que retorna uma tabela criada a partir de logs de eventos do sistema.

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;

public class TabularEventLog
{
    [SqlFunction(TableDefinition="logTime datetime,Message" +
        "nvarchar(4000),Category nvarchar(4000),InstanceId bigint",
        Name="ReadEventLog", FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod(String logname)
    {
        return new EventLog(logname, Environment.MachineName).Entries;
    }

    public static void FillRow(Object obj, out SqlDateTime timeWritten,
        out SqlChars message, out SqlChars category,
        out long instanceId)
    {
        EventLogEntry eventLogEntry = (EventLogEntry)obj;
        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
        message = new SqlChars(eventLogEntry.Message);
        category = new SqlChars(eventLogEntry.Category);
        instanceId = eventLogEntry.InstanceId;
    }
}

A função com valor de tabela é implementada como dois métodos estáticos na classe TabularEventLog. O primeiro método (InitMethod) recebe o atributo SqlFunction para designá-lo como ponto de entrada para a função com valor de tabela. O método deve retornar um objeto IEnumerable ou IEnumerator. Esse objeto contém os dados que serão usados para preencher a tabela de retorno. Durante a execução da função, o SQL Server irá iterar através de cada objeto no objeto IEnumerator, usando essas informações para preencher uma linha de dados. Ele faz isso passando o objeto para o segundo método na classe, FillRow, que converte o objeto em uma linha na tabela de retorno. Esse método está especificado no parâmetro FillRowMethodName do atributo SqlFunction.

Os metadados adicionais são definidos nos argumentos do atributo SqlFunction. No exemplo anterior, são definidos os tipos e nomes das colunas, assim como o nome da tabela de retorno neste atributo.

Depois de implantar essa função em uma instância do SQL Server, você pode executar a consulta a seguir para ver os últimos dez itens no log de aplicativo.

SELECT TOP 10 T.logTime, T.Message, T.InstanceId
FROM dbo.ReadEventLog(N'Application') as T

Os resultados são mostrados na Figura 1.

Figura 1. Resultados da consulta

Para obter informações mais detalhadas, consulte Funções com valor de tabela CLR nos Manuais Online do SQL Server 2005.

Usando funções com valor de tabela como alternativa às extensões de processamento de dados

As extensões de processamento de dados do Reporting Services permitem que uma fonte de dados seja modelada por meio da implementação de um conjunto de interfaces ADO.NET. É um conceito semelhante ao uso de funções com valor de tabela com o Reporting Services. As funções com valor de tabela têm vantagens importantes em relação às extensões de processamento de dados.

Vantagens

Em primeiro lugar, a implementação de funções com valor de tabela pode ser muito mais fácil que a de extensões de processamento de dados. Para implementar uma função com valor de tabela, somente dois métodos precisam ser criados, enquanto que, para uma extensão de processamento de dados, é necessário implementar várias interfaces. Sem contar o modelo de implantação, que é mais simples. O Microsoft Visual Studio 2005 pode implantar automaticamente uma função com valor de tabela .NET Framework no SQL Server, no qual ela é disponibilizada imediatamente para uso pelo Reporting Services. Para implantar uma extensão de processamento de dados, é preciso copiar o assembly para o cliente e para o Report Server, editando os arquivos de configuração XML nos dois locais.

Outra vantagem importante de uma função com valor de tabela é que ela pode fazer parte de uma junção no banco de dados em que está anexada. Isso significa que os dados relacionais no SQL Server podem ser misturados e filtrados com os dados personalizados definidos na função antes de serem colocados no relatório. Isso é impossível com uma extensão de processamento de dados, porque o Reporting Services não dá suporte a consultas de junção entre fontes de dados.

Desvantagens

As extensões de processamento de dados são muito mais flexíveis e eficientes do que as funções com valor de tabela. Uma função com valor de tabela pode modelar apenas uma única tabela de banco de dados, enquanto uma extensão de processamento de dados pode modelar o equivalente a um banco de dados inteiro. Além disso, uma extensão de processamento de dados funciona como uma fonte de dados totalmente personalizada e pode ter sua própria sintaxe de conexão e linguagem de consulta. O uso do SQL como linguagem de consulta nem sempre é ideal para diferentes tipos de dados. Por exemplo, o Reporting Services inclui uma extensão de processamento de dados para dados XML que usa uma linguagem de consulta semelhante ao XPath. As extensões de dados são úteis quando um desenvolvedor deseja ter controle total sobre o caminho do código de acesso a dados.

Usando funções com valor de tabela com o SQL Server Reporting Services

Existem três coisas que você deve fazer para poder usar funções com valor de tabela com o Reporting Services. Em primeiro lugar, o SQL Server deve ser configurado para permitir a integração CLR. Em seguida, a função com valor de tabela deve ser desenvolvida no Visual Studio. Por último, a função precisa ser implantada em uma instância do SQL Server.

Para que o SQL Server permita a integração CLR, um sinalizador precisa ser definido por meio da execução de uma consulta ou do uso da ferramenta Configuração da Área de Superfície do SQL Server.

Para configurar o SQL Server para permitir a integração CLR

  1. Clique no botão Iniciar, aponte para Todos os Programas, Microsoft SQL Server 2005 e Configuration Tools (Ferramentas de Configuração) e clique em Configuração da Área de Superfície.

  2. Na ferramenta Configuração da Área de Superfície do SQL Server 2005, clique em Configuração da Área de Superfície de Recursos.

  3. Selecione a instância do servidor, expanda as opções de Database Engine e clique em CLR Integration (Integração CLR).

  4. Selecione Habilitar integração CLR.

Outra opção é executar a consulta a seguir no SQL Server (essa consulta requer a permissão ALTER SETTINGS).

USE master
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Para desenvolver uma função com valor de tabela

Para desenvolver uma função com valor de tabela, crie um novo projeto SQL Server no Visual Studio. Para criar um projeto SQL Server, abra a caixa de diálogo Novo Projeto, expanda Visual C# e selecione Banco de Dados. Será solicitado que você forneça informações sobre a conexão de banco de dados. Para obter mais informações, consulte Como: criar um projeto SQL Server nos Manuais Online do SQL Server 2005. Uma vez configurada sua conexão de banco de dados, você já pode escrever uma função com valor de tabela. Crie um arquivo .cs vazio em seu projeto com o nome EventLog.cs, copie a função de exemplo fornecida na seção anterior e cole-a nesse arquivo.

Para implantar uma função com valor de tabela

Para implantar, você deve registrar a função e o assembly que a contém em uma instância do SQL Server. Isso é feito com o uso de comandos Transact-SQL. O script a seguir registra o assembly tvfEventLogs e a função ReadEventLog:

CREATE ASSEMBLY tvfEventLog
   FROM'D:\assemblies\tvfEventLog\tvfeventlog.dll'
   WITH PERMISSION_SET = SAFE
   GO
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
   RETURNS TABLE (logTime datetime,Message nvarchar(4000),
      Category nvarchar(4000),InstanceId bigint)
   AS EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod GO

Outra opção é implantar o seu assembly diretamente do Visual Studio clicando com o botão direito do mouse no projeto no Solution Explorer e selecionando a opção Deploy (Implantar). O Visual Studio usa o atributo SqlFunction para determinar automaticamente a assinatura de função e outros metadados necessários.

Permissões de implantação

O SQL Server usa conjuntos de permissões para executar código de forma segura no ambiente hospedado por ele. Quando você cria um projeto de banco de dados no Visual Studio, o conjunto de permissões padrão é SAFE, que é o único que permite que você implante diretamente do Visual Studio sem precisar de nenhuma outra configuração. Para dar ao seu assembly um conjunto de permissões diferente de SAFE, você deve atribuir a ele um nome forte e executar as outras etapas de configuração antes de implantar o assembly no banco de dados.

Três conjuntos de permissões podem ser utilizados no registro de um assembly no SQL Server: SAFE, EXTERNAL ACCESS e UNSAFE.

  • SAFE permite o acesso do código no assembly somente a dados locais e computação interna.

  • EXTERNAL ACCESS permite o acesso a recursos externos do sistema como arquivos, recursos da rede e o Registro.

  • UNSAFE permite a execução irrestrita do código no assembly.

Para implantar o assembly com um conjunto de permissões diferente de SAFE, você deve seguir algumas etapas adicionais. Em primeiro lugar, uma chave assimétrica deve ser criada a partir do assembly que você deseja registrar no SQL Server. Em seguida, use essa chave para criar um logon. Por último, o conjunto de permissões apropriado deve ser concedido a esse logon. As instruções Transact-SQL a seguir usam essas etapas para conceder o conjunto de permissões UNSAFE ao assembly criado a partir do exemplo tvfEventLogs na seção anterior.

USE master
GO
CREATE ASYMMETRIC KEY EventLogKey FROM EXECUTABLE FILE =
'D:\assemblies\tvfEventLog\tvfeventlog.dll'
CREATE LOGIN EventLogLogin FROM ASYMMETRIC KEY EventLogKey
GRANT UNSAFE ASSEMBLY TO EventLogLogin
GO

Isso precisa ser feito apenas uma vez, e não sempre que você implanta o assembly. Para obter mais informações sobre como usar os diferentes conjuntos de permissões e registrar assemblies no SQL Server, consulte Segurança de acesso ao código na integração CLR e Criando um assembly, ambos nos Manuais Online do SQL Server 2005.

Relatório de log de eventos

Depois de implantada a função com valor de tabela, uma tabela virtual das entradas do log de eventos do sistema referentes ao computador é adicionada ao banco de dados. Como o SQL Server trata a função exatamente como uma tabela, ela pode ser usada perfeitamente no Reporting Services.

Depois de implantar o assembly, use o Visual Studio para criar um novo projeto Reporting Services. (Se você não estiver familiarizado com a criação de relatórios usando o Report Designer do Reporting Services, consulte Tutoriais do Reporting Services nos Manuais Online do SQL Server 2005.) Crie um relatório com uma fonte de dados do SQL Server que se conecte ao mesmo banco de dados no qual a função foi instalada. Em seguida, crie um banco de dados que use a seguinte consulta:

SELECT TOP 10 T.logTime, T.Message, T.InstanceId
FROM dbo.ReadEventLog(N'Security') as T
WHERE T.Category = N'Logon/Logoff'

Uma vez definido o conjunto de dados, acrescente uma região de dados de tabela ao layout do relatório e adicione os campos do conjunto de dados à linha de detalhes da tabela. Por último, execute o relatório. Ele mostrará os dez últimos eventos de segurança na categoria Logon/Logoff referentes ao computador local. A Figura 2 mostra um exemplo do relatório.

Figura 2. Exemplo do relatório

Esse exemplo simples pode ser expandido para incluir outras tarefas de monitoramento e gerenciamento do computador. Por exemplo, uma função com valor de tabela pode ser criada para analisar logs do IIS (Serviços de informações da Internet da Microsoft). O Reporting Services pode ser usado para criar um aplicativo de monitoramento de tráfego da Web.

Serviços Web e funções com valor de tabela

Um recurso interessante das funções com valor de tabela é a capacidade de extrair dados de serviços Web. Ele pode ser usado para criar relatórios não convencionais. Demonstrarei como usar o Microsoft MapPoint Web Service em uma função com valor de tabela e associá-lo a dados no banco de dados AdventureWorks, a fim de processar dados espaciais em um mapa e processá-lo em um relatório.

Usando o MapPoint Web Service

É necessário obter uma conta de desenvolvedor gratuita na Microsoft antes de começar a desenvolver o MapPoint Web Service. Você pode obtê-la visitando o site de atendimento ao cliente do MapPoint Web Service. Um bom lugar para obter informações antes de começar a desenvolver o serviço Web é Introdução ao SDK do MapPoint Web Service.

Você precisará usar o Visual Studio para adicionar uma Referência da Web ao seu projeto que aponte para o arquivo .wsdl no servidor intermediário do MapPoint. Para obter mais informações sobre como configurar uma Referência da Web para o MapPoint Web Service, consulte Acessando a SOAP API do MapPoint Web Service.

O MapPoint Web Service fornece quatro serviços, cada qual com seu próprio ponto de extremidade SOAP:

  • O serviço Common possui funcionalidade que pode ser usada pelos outros serviços. Serve para recuperar metadados e para funções de utilitário.

  • O serviço Find pode ser usado para pesquisar localizações, descobrir a latitude e longitude de um endereço ("geocodificação") e encontrar pontos de interesse próximos de um local.

  • O serviço Routing fornece informações sobre como chegar de carro de um local a outro.

  • O serviço Render pode ser usado para criar uma imagem do mapa usando informações de roteamento e de localização.

Função com valor de tabela do MapPoint Web Service

Finalmente, quero que minha função com valor de tabela use MapPoint Web Service para executar as seguintes tarefas:

  1. Usar o serviço Find para descobrir a latitude e longitude de uma loja de bicicletas no AdventureWorks.

  2. Usar o serviço Find para localizar os cinco caixas eletrônicos mais próximos dessa latitude e longitude.

  3. Usar o serviço Routing para traçar a rota da loja até o caixa eletrônico.

  4. Usar o serviço Render para processar essa rota em um mapa.

Primeiro, preciso definir uma função com valor de tabela chamada GetProximity. O código Transact-SQL a seguir mostra a assinatura da minha função com valor de tabela:

CREATE FUNCTION GetProximity(@city nvarchar(200), @state nvarchar(2),
   @count int, @entityTypeName nvarchar(200))
RETURNS TABLE 
(HitName nvarchar(200), HitAddress nvarchar(200), MapImage 
varbinary(max))

GetProximity pega um nome de cidade e um código de estado com dois dígitos para a localização inicial. Pega também o número de entidades a serem retornadas e um nome de tipo de entidade a ser procurado. Ela procura as n entidades mais próximas, onde n é especificado pelo parâmetro count e o tipo de entidade é especificado pelo parâmetro entityTypeName. Ela retorna uma tabela contendo colunas para o nome, o endereço e um mapa (imagem binária) que contém instruções para chegar a cada entidade.

As assinaturas do método C# devem ter este aspecto:

public static IEnumerable InitMap(string city, string state, int count,
   string entityTypeName)
public static void FillRow(Object obj, out SqlChars name, out SqlChars
   address, out SqlBinary map)

Observe que o tipo de dados Transact-SQL nvarchar mapeia para o tipo de dados .NET Framework SqlChars, enquanto o tipo de dados Transact-SQL varbinary mapeia para o tipo de dados .NET Framework SqlBinary. Para obter uma lista completa dos mapeamentos entre tipos de dados, consulte a documentação do namespace System.Data.SqlTypes.

No método InitMap, converto a cidade e o estado em uma latitude e longitude. Em seguida, localizo todas as entidades próximas a essa coordenada. Por último, localizo as informações sobre como chegar de carro do local inicial até a entidade encontrada. O valor de retorno é uma matriz de objetos Route que encapsulam essas informações.

public static IEnumerable InitMap(string city, string state, int count, string entityTypeName)
{
   FindServiceSoap find = new FindServiceSoap();
   find.PreAuthenticate = true;
   find.Credentials = new NetworkCredential(username, passwd);
      
   // Geocode the initial city and state
   FindAddressSpecification findSpec = new FindAddressSpecification();
   Address findAddr = new Address();
   findAddr.CountryRegion = "US";
   findAddr.Subdivision = state;
   findAddr.PrimaryCity = city;
   findSpec.InputAddress = findAddr;
   findSpec.DataSourceName = "MapPoint.NA";
   findSpec.Options = new FindOptions();
   findSpec.Options.ThresholdScore = 0.45;
   FindResults results = find.FindAddress(findSpec);

   if (results.NumberFound > 0)
   {
      // If the city and state exist, get the latitude and longitude
      Location startLocation = results.Results[0].FoundLocation;
      LatLong startPoint = startLocation.LatLong;

      // Find the nearby entities
      FindNearbySpecification findNearby = new 
FindNearbySpecification();
      FindFilter filter = new FindFilter();
      filter.EntityTypeName = entityTypeName;
      findNearby.Filter = filter;

      FindOptions options = new FindOptions();
      options.Range = new FindRange();
      // Set the count limit
      options.Range.Count = count;
      findNearby.Options = options;
      findNearby.DataSourceName = "NavTech.NA";
      findNearby.LatLong = startPoint;
      findNearby.Distance = 10.0;
      results = find.FindNearby(findNearby);

      Route[] routes = new Route[results.Results.Length];
      RouteServiceSoap routeService = new RouteServiceSoap();

      routeService.PreAuthenticate = true;
      routeService.Credentials = new NetworkCredential(username,passwd);

      RouteSpecification spec = new RouteSpecification();
      spec.DataSourceName = "MapPoint.NA";

      // Create the route to each entity
      spec.Segments = new SegmentSpecification[2];
      spec.Segments[0] = new SegmentSpecification();
      spec.Segments[0].Waypoint = new Waypoint();
      spec.Segments[0].Waypoint.Location = startLocation;
      spec.Segments[0].Waypoint.Name = "start";
      for (int x = 0; x < results.Results.Length; x++)
      {
         spec.Segments[1] = new SegmentSpecification();
         spec.Segments[1].Waypoint = new Waypoint();
         spec.Segments[1].Waypoint.Location = 
results.Results[x].FoundLocation;
         spec.Segments[1].Waypoint.Name = "end";
         routes[x] = routeService.CalculateRoute(spec);
      }
      return routes;
   }
   return null;
}

No método FillRow, uso o serviço Render para converter cada objeto Route em uma imagem do mapa. Em seguida, populo uma linha usando essa imagem e os dados de localização da entidade.

public static void FillRow(Object obj, out SqlChars name, out SqlChars 
address, out SqlBinary map)
{
   Route route = (Route)obj;

   // build the address string
   Address endAddress = 
route.Specification.Segments[1].Waypoint.Location.Address;
   string entityAddress = endAddress.AddressLine;
   string enitityCity = endAddress.PrimaryCity;
   string entityState = endAddress.Subdivision;
   string entityName = route.Specification.Segments[1].Waypoint.Location.Entity.DisplayName;

   // Assign the values of two of the columns
   name = new SqlChars(entityName);
   address = new SqlChars(entityAddress + ' ' + enitityCity + ' ' + 
entityState);

   // Get the view of the route
   ViewByHeightWidth view = route.Itinerary.View.ByHeightWidth;
   RenderServiceSoap renderService = new RenderServiceSoap();

   renderService.PreAuthenticate = true;
   renderService.Credentials = new NetworkCredential(username, passwd);

   // Render the map with the route
   MapSpecification mapSpec = new MapSpecification();
   mapSpec.DataSourceName = "MapPoint.NA";
   mapSpec.Views = new MapView[]{view};
   mapSpec.Route = route;

   // Assign the map image to the map column
   MapImage[] image = renderService.GetMap(mapSpec);
   map = new SqlBinary(image[0].MimeData.Bits);
}

Implantando e depurando GetProximity

A implantação de uma função com valor de tabela que usa serviços Web é mais envolvida que o exemplo anterior. Estas são as etapas completas para implantar uma função com valor de tabela que usa serviços Web:

  1. Configure o projeto contendo a função GetProximity para gerar previamente o assembly de serialização de XML. Quando o .NET Framework faz chamadas de serviço Web, gera dinamicamente um assembly para manipular a serialização e desserialização do SOAP XML. Isso representa um problema porque o host SQL Server CLR não permite o carregamento dinâmico de assemblies em tempo de execução. Por isso, o assembly de serialização de XML das chamadas de serviço Web deve ser gerado em tempo de compilação e registrado no SQL Server. Para gerar previamente esse assembly através do Visual Studio, no menu Project (Projeto), clique em Properties (Propriedades) e selecione Build (Criar). Defina Generate serialization assembly (Gerar assembly de serialização) como On (Ativado). A DLL da serialização de XML será criada com o seu projeto e adicionada ao diretório bin. Ela será nomeada como [Nome_do_projeto].XmlSerializers.dll.

  2. Adicione o atributo System.Security.AllowPartiallyTrustedCallers ao assembly acrescentando a seguinte linha a AssemblyInfo.cs no projeto:

    [assembly: System.Security.AllowPartiallyTrustedCallers]

    Isso permite que o assembly de serialização de XML fale com o assembly principal que contém a função GetProximity.

  3. Registre no SQL Server a DLL de serialização de XML criada na etapa 1. O conjunto de permissões SAFE será suficiente.

  4. Crie uma chave assimétrica para a DLL que contém a função com valor de tabela GetProximity.

  5. Crie um logon para a chave assimétrica e conceda-o ao conjunto de permissões EXTERNAL ACCESS.

  6. Registre o assembly contendo GetProximity no conjunto de permissões EXTERNAL ACCESS.

  7. Registre a função com valor de tabela GetProximity.

Como existe uma cadeia de dependência relativamente longa e complexa, abandonei o mecanismo de implantação do Visual Studio em favor de um script Transact-SQL capaz de executar uma etapa de pós-criação que realiza as etapas de implantação 3 a 7. Ele está incluído no projeto de exemplo.

A depuração de funções com valor de tabela é muito simples. Os projetos de banco de dados têm um diretório Test Scripts. Os scripts podem ser adicionados a esse diretório e executados diretamente do Visual Studio. Após a implantação com êxito da função, você pode criar uma consulta Transact-SQL que chame a função e percorra o código C# em busca da função sem sair do Visual Studio.

Para testar GetProximity, crie um script de teste chamado "Teste.sql" no diretório Test Scripts e adicione esta consulta ao arquivo:

SELECT * FROM GetProximity('Redmond', 'WA', 5, 'SIC3578')

Observe os argumentos da função. Estou centralizando minha consulta de proximidade na cidade de Redmond, no Estado de Washington, por isso usei "Redmond" para o argumento @city e "WA" para o argumento @state. Forneci o número 5 para o valor @count, que é o número de entidades a serem retornadas. Também forneci o valor "SIC3578" para o argumento @entityTypeName, que é o nome da entidade para os caixas eletrônicos na fonte de dados do MapPoint que estou usando. Para obter mais informações sobre tipos de entidades e fontes de dados do MapPoint, consulte Fontes de dados do MapPoint.

Para executar a consulta no Visual Studio, clique com o botão direito do mouse no arquivo Teste.sql no Solution Explorer e selecione Debug Script (Depurar Script). Você deverá receber resultados semelhantes a estes na janela Saída do Visual Studio:

HitName             HitAddress                          MapImage
----------------------------------------------------------------------
Woodgrove Bank      8502 160th Ave NE Redmond WA        <BINARY>
Woodgrove Bank      16025 NE 85th St Redmond WA         <BINARY>
Woodgrove Bank      16150 NE 85th St Redmond WA         <BINARY>
Woodgrove Bank      8867 161st Ave NE Redmond WA        <BINARY>
Woodgrove Bank      15600 Redmond Way Redmond WA        <BINARY>
No rows affected.
(5 row(s) returned)

Para depurar a função GetProximity, defina um ponto de interrupção no código C# para a função e execute o script novamente. A execução será interrompida no ponto especificado e você poderá depurá-la como faria em qualquer outro processo gerenciado.

GetProximity

O banco de dados de exemplo AdventureWorks que acompanha o SQL Server 2005 representa um fabricante fictício de bicicletas e acessórios que vende para lojas de varejo em todos os Estados Unidos. Neste exemplo, a Adventure Works Cycles decidiu não aceitar mais cheques nem cartões de crédito. A partir de agora, ela quer que todas as faturas sejam pagas em dinheiro. Como um serviço aos clientes durante essa transição, ela criará um relatório mostrando um endereço e um mapa para os caixas eletrônicos mais próximos das lojas dos clientes. Embora esse não seja um cenário realista, serve ao propósito de demonstrar como associar uma fonte de dados tradicional (banco de dados SQL) a uma não tradicional (MapPoint Web Service) usando funções com valor de tabela.

A primeira etapa na criação do nosso relatório é criar um novo projeto Report Server no Visual Studio e especificar uma fonte de dados. A fonte de dados do meu relatório é o banco de dados de exemplo Adventure Works, do SQL Server 2005, que tem a função com valor de tabela MapPoint (criada anteriormente por mim) instalada. Existe um conjunto de dados para o relatório. Ele contém campos para o nome, a cidade e o estado onde a loja está localizada, bem como para o nome, o endereço e o mapa com instruções referentes ao caixa eletrônico.

Para cada loja, vamos chamar GetProximity e obter os cinco caixas eletrônicos mais próximos. No SQL Server 2005, há o novo tipo de cláusula APPLY para fazer isso. É um pouco diferente de uma junção, porque queremos associar os argumentos da função em vez dos resultados dela. Isso significa que a função com valor de tabela é chamada para cada linha retornada pelo lado esquerdo de APPLY. Uma união dos resultados da função poderá então ser associado ao restante da consulta. Veja a seguir a consulta Transact-SQL referente ao conjunto de banco de dados do relatório.

SELECT TOP(40) Sales.Store.Name, Person.Address.City,
   Person.StateProvince.StateProvinceCode, GetProximity_1.HitName,
   GetProximity_1.HitAddress, GetProximity_1.MapImage
   FROM Sales.CustomerAddress
      INNER JOIN Person.Address
         ON Sales.CustomerAddress.AddressID = Person.Address.AddressID
         AND Sales.CustomerAddress.AddressID = Person.Address.AddressID
      INNER JOIN Sales.Store
      INNER JOIN Sales.StoreContact ON Sales.Store.CustomerID = 
Sales.StoreContact.CustomerID
         ON Sales.CustomerAddress.CustomerID = 
Sales.StoreContact.CustomerID
      INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID 
= Person.StateProvince.StateProvinceID
         AND Person.Address.StateProvinceID = 
Person.StateProvince.StateProvinceID
CROSS APPLY dbo.GetProximity(Person.Address.City,
   Person.StateProvince.StateProvinceCode, 5, 'SIC3578') AS 
GetProximity_1

Observe o uso de CROSS APPLY para vincular os argumentos da função GetProximity a outros dados de consulta representados por Person.Address.City e Person.StateProvince.StateProvinceCode.

Observação Para usar a cláusula APPLY em uma consulta de conjunto de dados, você deve usar o criador de consultas genérico. O criador de consultas baseado em GUI não pode exibi-la graficamente e lançará uma exceção.

O design do meu relatório usa duas listas aninhadas. A lista interna contém uma caixa de texto para o nome e endereço do caixa eletrônico e uma imagem do mapa.

A imagem no meu relatório está definida como AutoSize (AutoDimensionar), para que seja redimensionada em função do tamanho da imagem vinda do serviço Web. A lista externa contém caixas de texto para o nome e a localização da loja. A lista externa é agrupada por nome de loja. Uma imagem do meu relatório no Modo de layout é mostrada na Figura 3.

Figura 3. Exemplo de relatório no Modo de layout

A Figura 4 mostra o relatório processado com os mapas nos locais dos caixas eletrônicos.

Figura 4. Relatório processado, com mapas para os locais dos caixas eletrônicos

Conclusão

Este artigo mostrou como as funções com valor de tabela no SQL Server podem ser usadas para estender a funcionalidade de acesso a dados do SQL Server Reporting Services. As funções com valor de tabela fornecem flexibilidade aos programadores e criadores de relatórios, a fim de habilitar o relatório de cenários nos quais os dados não sejam armazenados diretamente em tabelas de bancos de dados.

Sobre o autor

Ryan Ackley é engenheiro de desenvolvimento de software no grupo Microsoft SQL Server Business Intelligence. Sua principal área é o mecanismo de processamento de dados e relatórios do SQL Server Reporting Services.

Para obter mais informações:

SQL Server 2005 Reporting Services

 

© 2007 Microsoft Corporation. Todos os direitos reservados. Termos de uso.