Criando procedimentos armazenados e funções User-Defined com código gerenciado (C#)

por Scott Mitchell

Descarregar PDF

O Microsoft SQL Server 2005 integra-se ao .NET Common Language Runtime para permitir que os desenvolvedores criem objetos de banco de dados por meio de código gerenciado. Este tutorial mostra como criar procedimentos armazenados gerenciados e funções gerenciadas definidas pelo usuário com seu código Visual Basic ou C#. Também vemos como essas edições do Visual Studio permitem depurar esses objetos de banco de dados gerenciados.

Introdução

Bancos de dados como o Microsoft SQL Server 2005 usam a linguagem de consultaTransact-Structured (T-SQL) para inserir, modificar e recuperar dados. A maioria dos sistemas de banco de dados inclui construções para agrupar uma série de instruções SQL que podem ser executadas como uma única unidade reutilizável. Os procedimentos armazenados são um exemplo. Outra é User-Defined Functions(UDFs), uma construção que examinaremos com mais detalhes na Etapa 9.

Em sua essência, o SQL é projetado para trabalhar com conjuntos de dados. As SELECTdeclarações , UPDATE, e DELETE aplicam-se intrinsecamente a todos os registos da tabela correspondente e são limitadas apenas pelas suas WHERE cláusulas. No entanto, há muitos recursos de linguagem projetados para trabalhar com um registro de cada vez e para manipular dados escalares. CURSOR s permitem que um conjunto de registos seja percorrido um de cada vez. Funções de manipulação de cadeia de caracteres como LEFT, CHARINDEX e PATINDEX trabalham com dados escalares. SQL também inclui instruções de fluxo de controle como IF e WHILE.

Antes do Microsoft SQL Server 2005, os procedimentos armazenados e UDFs só podiam ser definidos como uma coleção de instruções T-SQL. O SQL Server 2005, no entanto, foi projetado para fornecer integração com o Common Language Runtime (CLR), que é o tempo de execução usado por todos os assemblies .NET. Consequentemente, os procedimentos armazenados e UDFs em um banco de dados do SQL Server 2005 podem ser criados usando código gerenciado. Ou seja, você pode criar um procedimento armazenado ou UDF como um método em uma classe C#. Isso permite que esses procedimentos armazenados e UDFs utilizem a funcionalidade no .NET Framework e de suas próprias classes personalizadas.

Neste tutorial, examinaremos como criar procedimentos armazenados gerenciados e funções User-Defined e como integrá-los em nosso banco de dados Northwind. Vamos começar!

Observação

Os objetos de banco de dados gerenciados oferecem algumas vantagens em relação aos seus homólogos SQL. A riqueza e familiaridade linguística e a capacidade de reutilizar código e lógica existentes são as principais vantagens. Mas é provável que os objetos de banco de dados gerenciados sejam menos eficientes ao trabalhar com conjuntos de dados que não envolvem muita lógica processual. Para uma discussão mais completa sobre as vantagens de usar código gerenciado versus T-SQL, confira as Vantagens de usar código gerenciado para criar objetos de banco de dados.

Etapa 1: Movendo o banco de dados Northwind para fora do App_Data

Todos os nossos tutoriais até agora usaram um arquivo de banco de dados do Microsoft SQL Server 2005 Express Edition na pasta do App_Data aplicativo Web. Colocar o banco de dados em App_Data simplificou a distribuição e execução desses tutoriais, pois todos os arquivos estavam localizados em um único diretório e não exigiam etapas adicionais de configuração para testar os tutoriais.

Para este tutorial, no entanto, moveremos o banco de dados Northwind para fora de App_Data e registrá-lo-emos explicitamente na instância de banco de dados do SQL Server 2005 Express Edition. Embora possamos executar as etapas deste tutorial com o banco de dados na App_Data pasta, várias das etapas são muito mais simples registrando explicitamente o banco de dados com a instância de banco de dados do SQL Server 2005 Express Edition.

O download para este tutorial tem os dois arquivos de banco de dados - NORTHWND.MDF e NORTHWND_log.LDF - colocados em uma pasta chamada DataFiles. Se você estiver a seguir a sua própria implementação dos tutoriais, feche o Visual Studio e mova os arquivos NORTHWND.MDF e NORTHWND_log.LDF da pasta do website para uma pasta fora do App_Data. Depois que os arquivos de banco de dados tiverem sido movidos para outra pasta, precisamos registrar o banco de dados Northwind com a instância de banco de dados do SQL Server 2005 Express Edition. Isso pode ser feito a partir do SQL Server Management Studio. Se você tiver uma edição não Express do SQL Server 2005 instalada no computador, provavelmente já terá o Management Studio instalado. Se você tiver apenas o SQL Server 2005 Express Edition em seu computador, reserve um momento para baixar e instalar o Microsoft SQL Server Management Studio.

Inicie o SQL Server Management Studio. Como mostra a Figura 1, o Management Studio começa perguntando a qual servidor se conectar. Digite localhost\SQLExpress como nome do servidor, seleccione Autenticação do Windows na lista suspensa de Autenticação e clique em Conectar.

Figura 1: Conectar-se à instância de banco de dados apropriada

Depois de se conectar, a janela do Pesquisador de Objetos listará informações sobre a instância de banco de dados do SQL Server 2005 Express Edition, incluindo seus bancos de dados, informações de segurança, opções de gerenciamento e assim por diante.

Precisamos anexar o banco de dados Northwind na DataFiles pasta (ou onde quer que você o tenha movido) à instância do banco de dados do SQL Server 2005 Express Edition. Clique com o botão direito do mouse na pasta Bancos de dados e escolha a opção Anexar no menu de contexto. Isso abrirá a caixa de diálogo Anexar Bancos de Dados. Clique no botão Adicionar, faça uma busca detalhada no arquivo apropriado NORTHWND.MDF e clique em OK. Neste ponto, sua tela deve ser semelhante à Figura 2.

Figura 2: Conecte-se à instância de banco de dados apropriada (Clique para visualizar a imagem em tamanho real)

Observação

Ao conectar-se à instância do SQL Server 2005 Express Edition por meio do Management Studio, a caixa de diálogo Anexar Bancos de Dados não permite que você faça drill down em diretórios de perfil de usuário, como Meus Documentos. Portanto, certifique-se de colocar os NORTHWND.MDF arquivos e NORTHWND_log.LDF em um diretório de perfil que não seja de usuário.

Clique no botão OK para anexar o banco de dados. A caixa de diálogo Anexar Bancos de Dados será fechada e o Pesquisador de Objetos agora deve listar o banco de dados recém-anexado. É provável que o banco de dados Northwind tenha um nome como 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. Renomeie o banco de dados para Northwind clicando com o botão direito do mouse no banco de dados e escolhendo Renomear.

Figura 3: Renomear o banco de dados para Northwind

Etapa 2: Criando uma nova solução e um projeto do SQL Server no Visual Studio

Para criar procedimentos armazenados gerenciados ou UDFs no SQL Server 2005, escreveremos o procedimento armazenado e a lógica UDF como código C# em uma classe. Depois que o código tiver sido escrito, precisaremos compilar essa classe em um assembly (um .dll arquivo), registrar o assembly com o banco de dados do SQL Server e, em seguida, criar um procedimento armazenado ou objeto UDF no banco de dados que aponta para o método correspondente no assembly. Todas estas etapas podem ser executadas manualmente. Podemos criar o código em qualquer editor de texto, compilá-lo a partir da linha de comando usando o compilador C# (csc.exe), registrá-lo no banco de dados usando o CREATE ASSEMBLY comando ou do Management Studio, e adicionar o procedimento armazenado ou o objeto UDF por meios semelhantes. Felizmente, as versões Professional e Team Systems do Visual Studio incluem um tipo de projeto do SQL Server que automatiza essas tarefas. Neste tutorial, mostraremos como usar o tipo de projeto do SQL Server para criar um procedimento armazenado gerenciado e UDF.

Observação

Se você estiver usando o Visual Web Developer ou a edição Standard do Visual Studio, então você terá que usar a abordagem manual em vez disso. A etapa 13 fornece instruções detalhadas para executar essas etapas manualmente. Eu recomendo que você leia as etapas 2 a 12 antes de ler a etapa 13, pois essas etapas incluem instruções importantes de configuração do SQL Server que devem ser aplicadas independentemente da versão do Visual Studio que você está usando.

Comece abrindo o Visual Studio. No menu Arquivo, escolha Novo Projeto para exibir a caixa de diálogo Novo Projeto (consulte a Figura 4). Analise detalhadamente o tipo de projeto Banco de Dados e, em seguida, nos Modelos listados à direita, escolha criar um novo Projeto do SQL Server. Eu escolhi nomear este projeto ManagedDatabaseConstructs e colocá-lo dentro de uma solução chamada Tutorial75.

Figura 4: Criar um novo projeto do SQL Server (Clique para exibir a imagem em tamanho real)

Clique no botão OK na caixa de diálogo Novo Projeto para criar a Solução e o Projeto do SQL Server.

Um projeto do SQL Server está vinculado a um banco de dados específico. Consequentemente, depois de criar o novo projeto do SQL Server, somos imediatamente solicitados a especificar essas informações. A Figura 5 mostra a caixa de diálogo Nova Referência de Banco de Dados que foi preenchida para referência ao banco de dados Northwind que registrámos na instância do SQL Server 2005 Express Edition na Etapa 1.

Figura 5: Associar o projeto do SQL Server ao banco de dados Northwind

Para depurar os procedimentos armazenados gerenciados e UDFs que criaremos dentro deste projeto, precisamos habilitar o suporte de depuração SQL/CLR para a conexão. Sempre que associamos um projeto do SQL Server a um novo banco de dados (como fizemos na Figura 5), o Visual Studio nos pergunta se queremos habilitar a depuração SQL/CLR na conexão (consulte a Figura 6). Clique em Sim.

Figura 6: Habilitar depuração SQL/CLR

Neste ponto, o novo projeto do SQL Server foi adicionado à solução. Ele contém uma pasta nomeada Test Scripts com um arquivo chamado Test.sql, que é usado para depurar os objetos de banco de dados gerenciados criados no projeto. Vamos analisar o processo de depuração na Etapa 12.

Agora podemos adicionar novos procedimentos armazenados gerenciados e UDFs a este projeto, mas antes de incluirmos primeiro nosso aplicativo Web existente na Solução. No menu Arquivo, selecione a opção Adicionar e escolha Site existente. Navegue até a pasta do site apropriada e clique em OK. Como mostra a Figura 7, isso atualizará a Solução para incluir dois projetos: o site e o ManagedDatabaseConstructs Projeto SQL Server.

Figura 7: O Gerenciador de Soluções agora inclui dois projetos

O valor NORTHWNDConnectionString em Web.config atualmente faz referência ao arquivo NORTHWND.MDF na pasta App_Data. Desde que removemos esta base de dados do App_Data e a registámos explicitamente na instância de base de dados do SQL Server 2005 Express Edition, precisamos de atualizar o valor do NORTHWNDConnectionString em conformidade. Abra o ficheiro Web.config no site e altere o valor NORTHWNDConnectionString para que a cadeia de conexão seja: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Após essa alteração, sua <connectionStrings> seção em Web.config deve ser semelhante à seguinte:

<connectionStrings>
    <add name="NORTHWNDConnectionString" connectionString=
        "Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
            Integrated Security=True;Pooling=false"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Observação

Conforme discutido no tutorial anterior, ao depurar um objeto do SQL Server de um aplicativo cliente, como um site ASP.NET, precisamos desabilitar o pool de conexões. A cadeia de conexão mostrada acima desabilita o pool de conexões ( Pooling=false ). Se você não planeja depurar os procedimentos armazenados gerenciados e UDFs do site ASP.NET, habilite o pool de conexões.

Etapa 3: Criando um procedimento armazenado gerenciado

Para adicionar um procedimento armazenado gerenciado ao banco de dados Northwind, primeiro precisamos criar o procedimento armazenado como um método no Projeto SQL Server. No Gerenciador de Soluções, clique com o botão direito do mouse no nome do ManagedDatabaseConstructs projeto e escolha adicionar um novo item. Isso exibirá a caixa de diálogo Adicionar Novo Item, que lista os tipos de objetos de banco de dados gerenciados que podem ser adicionados ao projeto. Como mostra a Figura 8, isso inclui procedimentos armazenados e funções User-Defined, entre outros.

Vamos começar adicionando um procedimento armazenado que simplesmente retorna todos os produtos que foram descontinuados. Nomeie o novo arquivo GetDiscontinuedProducts.csde procedimento armazenado .

Figura 8: Adicionar um novo procedimento armazenado nomeado GetDiscontinuedProducts.cs (Clique para visualizar a imagem em tamanho real)

Isso criará um novo arquivo de classe C# com o seguinte conteúdo:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetDiscontinuedProducts()
    {
        // Put your code here
    }
};

Observe que o procedimento armazenado é implementado como um static método dentro de um partial arquivo de classe chamado StoredProcedures. Além disso, o GetDiscontinuedProducts método é decorado com o SqlProcedure attribute, que marca o método como um procedimento armazenado.

O código a seguir cria um SqlCommand objeto e o define CommandText como uma SELECT consulta que retorna todas as colunas da Products tabela para produtos cujo Discontinued campo é igual a 1. Em seguida, ele executa o comando e envia os resultados de volta para o aplicativo cliente. Adicione este código ao GetDiscontinuedProducts método.

// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = 
      @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
               QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
               ReorderLevel, Discontinued
        FROM Products 
        WHERE Discontinued = 1";
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);

Todos os objetos de banco de dados gerenciados têm acesso a um SqlContext objeto que representa o contexto do chamador. O SqlContext fornece acesso a um objeto SqlPipe através de sua propriedade Pipe. Esse SqlPipe objeto é usado para transferir informações entre o banco de dados do SQL Server e o aplicativo de chamada. Como o próprio nome indica, o ExecuteAndSend método executa um objeto passado SqlCommand e envia os resultados de volta para o aplicativo cliente.

Observação

Os objetos de banco de dados gerenciados são mais adequados para procedimentos armazenados e UDFs que usam lógica processual em vez de lógica baseada em conjunto. A lógica processual envolve trabalhar com conjuntos de dados linha a linha ou trabalhar com dados escalares. O GetDiscontinuedProducts método que acabamos de criar, no entanto, não envolve nenhuma lógica processual. Portanto, o ideal seria implementá-lo como um procedimento armazenado T-SQL. Ele é implementado como um procedimento armazenado gerenciado para demonstrar as etapas necessárias para criar e implantar procedimentos armazenados gerenciados.

Etapa 4: Implantando o procedimento armazenado gerenciado

Com esse código concluído, estamos prontos para implantá-lo no banco de dados Northwind. A implantação de um projeto do SQL Server compila o código em um assembly, registra o assembly com o banco de dados e cria os objetos correspondentes no banco de dados, vinculando-os aos métodos apropriados no assembly. O conjunto exato de tarefas executadas pela opção Implantar é explicado com mais precisão na Etapa 13. Clique com o botão direito do mouse no nome do ManagedDatabaseConstructs projeto no Gerenciador de Soluções e escolha a opção Implantar. No entanto, a implantação falha com o seguinte erro: Sintaxe incorreta perto de 'EXTERNAL'. Talvez seja necessário definir o nível de compatibilidade do banco de dados atual para um valor mais alto para habilitar esse recurso. Consulte a ajuda para o procedimento armazenado sp_dbcmptlevel.

Essa mensagem de erro ocorre ao tentar registrar o assembly com o banco de dados Northwind. Para registrar um assembly em um banco de dados do SQL Server 2005, o nível de compatibilidade do banco de dados deve ser definido como 90. Por padrão, os novos bancos de dados do SQL Server 2005 têm um nível de compatibilidade de 90. No entanto, os bancos de dados criados usando o Microsoft SQL Server 2000 têm um nível de compatibilidade padrão de 80. Como o banco de dados Northwind era inicialmente um banco de dados Microsoft SQL Server 2000, seu nível de compatibilidade está atualmente definido como 80 e, portanto, precisa ser aumentado para 90 para registrar objetos de banco de dados gerenciados.

Para atualizar o nível de compatibilidade do banco de dados, abra uma janela Nova Consulta no Management Studio e digite:

exec sp_dbcmptlevel 'Northwind', 90

Clique no ícone Executar na barra de ferramentas para executar a consulta acima.

Figura 9: Atualizar o nível de compatibilidade do banco de dados Northwind (Clique para visualizar a imagem em tamanho real)

Depois de atualizar o nível de compatibilidade, reimplante o projeto do SQL Server. Desta vez, a implantação deve ser concluída sem erros.

Retorne ao SQL Server Management Studio, clique com o botão direito do mouse no banco de dados Northwind no Pesquisador de Objetos e escolha Atualizar. Em seguida, faça uma busca detalhada na pasta Programabilidade e expanda a pasta Assemblies. Como mostra a Figura 10, o banco de dados Northwind agora inclui o assembly gerado pelo projeto ManagedDatabaseConstructs.

Figura 10: O ManagedDatabaseConstructs assembly agora está registrado no banco de dados Northwind

Expanda também a pasta Stored Procedures. Lá você verá um procedimento armazenado chamado GetDiscontinuedProducts. Esta procedura armazenada foi criada pelo processo de implantação e aponta para o método GetDiscontinuedProducts da assembly ManagedDatabaseConstructs. Quando o GetDiscontinuedProducts procedimento armazenado é executado, ele, por sua vez, executa o GetDiscontinuedProducts método. Como este é um procedimento armazenado gerenciado, ele não pode ser editado através do Management Studio (daí o ícone de cadeado ao lado do nome do procedimento armazenado).

Figura 11: O GetDiscontinuedProducts procedimento armazenado está listado na pasta Stored Procedures

Há ainda mais um obstáculo que temos que superar antes de podermos chamar o procedimento armazenado gerenciado: o banco de dados é configurado para impedir a execução de código gerenciado. Verifique isso abrindo uma nova janela de consulta e executando o GetDiscontinuedProducts procedimento armazenado. Você receberá a seguinte mensagem de erro: A execução de código de usuário no .NET Framework está desabilitada. Ative a opção de configuração 'clr habilitado'.

Para examinar as informações de configuração do banco de dados Northwind, insira e execute o comando exec sp_configure na janela de consulta. Isso mostra que a configuração clr enabled está atualmente definida como 0.

Figura 12: A configuração habilitada para clr está atualmente definida como 0 (Clique para visualizar a imagem em tamanho real)

Observe que cada definição de configuração na Figura 12 tem quatro valores listados com ela: os valores mínimo e máximo e os valores de configuração e execução. Para atualizar o valor de configuração para a configuração clr enabled, execute o seguinte comando:

exec sp_configure 'clr enabled', 1

Se executar novamente o exec sp_configure, verá que a instrução acima atualizou o valor da configuração 'clr enabled' para 1, mas que o valor de execução ainda é 0. Para que essa alteração de configuração tenha efeito, precisamos executar o RECONFIGURE comando, que definirá o valor de execução para o valor de configuração atual. Basta entrar RECONFIGURE na janela de consulta e clicar no ícone Executar na barra de ferramentas. Se executar exec sp_configure agora, verá um valor de 1 para as definições de configuração e valores de execução com clr ativado.

Com a configuração habilitada do CLR concluída, estamos prontos para executar o procedimento armazenado gerido GetDiscontinuedProducts. Na janela de consulta, digite e execute o comando execGetDiscontinuedProducts. Invocar o procedimento armazenado leva a que o código gerenciado correspondente no método GetDiscontinuedProducts seja executado. Esse código emite uma SELECT consulta para retornar todos os produtos que foram descontinuados e retorna esses dados para o aplicativo de chamada, que é o SQL Server Management Studio nesta instância. O Management Studio recebe esses resultados e os exibe na janela Resultados.

Figura 13: O GetDiscontinuedProducts procedimento armazenado retorna todos os produtos descontinuados (Clique para visualizar a imagem em tamanho real)

Etapa 5: Criando procedimentos armazenados gerenciados que aceitam parâmetros de entrada

Muitas das consultas e procedimentos armazenados que criamos ao longo desses tutoriais usaram parâmetros. Por exemplo, no tutorial Creating New Stored Procedures for the Typed DataSet s TableAdapters , criamos um procedimento armazenado chamado GetProductsByCategoryID que aceitava um parâmetro de entrada chamado @CategoryID. O procedimento armazenado retornou todos os produtos cujo CategoryID campo correspondia ao valor do parâmetro fornecido @CategoryID .

Para criar um procedimento armazenado gerenciado que aceite parâmetros de entrada, basta especificar esses parâmetros na definição do método. Para ilustrar isso, vamos adicionar outro procedimento armazenado gerenciado ao ManagedDatabaseConstructs projeto chamado GetProductsWithPriceLessThan. Esse procedimento armazenado gerenciado aceitará um parâmetro de entrada especificando um preço e retornará todos os produtos cujo UnitPrice campo seja menor que o valor do parâmetro.

Para adicionar um novo procedimento armazenado ao projeto, clique com o botão direito do mouse no nome do ManagedDatabaseConstructs projeto e escolha adicionar um novo procedimento armazenado. Dê o nome GetProductsWithPriceLessThan.cs ao ficheiro. Como vimos na Etapa 3, isso criará um novo arquivo de classe C# com um método chamado GetProductsWithPriceLessThan colocado dentro da partial classe StoredProcedures.

Atualize a GetProductsWithPriceLessThan definição do método para que ele aceite um SqlMoney parâmetro de entrada chamado price e escreva o código para executar e retornar os resultados da consulta:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(SqlMoney price)
{
    // Create the command
    SqlCommand myCommand = new SqlCommand();
    myCommand.CommandText =
          @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                   QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                   ReorderLevel, Discontinued
            FROM Products
            WHERE UnitPrice < @MaxPrice";
    myCommand.Parameters.AddWithValue("@MaxPrice", price);
    // Execute the command and send back the results
    SqlContext.Pipe.ExecuteAndSend(myCommand);
}

A GetProductsWithPriceLessThan definição e o código do método são muito semelhantes à definição e ao GetDiscontinuedProducts código do método criado na Etapa 3. As únicas diferenças são que o método GetProductsWithPriceLessThan aceita price como parâmetro de entrada, a consulta SqlCommand inclui um parâmetro @MaxPrice, e um parâmetro é adicionado à coleção SqlCommand de Parameters e atribuído o valor à variável price.

Depois de adicionar esse código, reimplante o projeto do SQL Server. Em seguida, retorne ao SQL Server Management Studio e atualize a pasta Stored Procedures. Você deve ver uma nova entrada, GetProductsWithPriceLessThan. Em uma janela de consulta, digite e execute o comando exec GetProductsWithPriceLessThan 25, que listará todos os produtos com menos de US$ 25, como mostra a Figura 14.

Figura 14: Produtos abaixo de US$ 25 são exibidos (Clique para visualizar a imagem em tamanho real)

Etapa 6: Chamando o procedimento armazenado gerenciado da camada de acesso a dados

Neste ponto, adicionámos os GetDiscontinuedProducts procedimentos armazenados geridos ao projeto GetProductsWithPriceLessThan e registámo-los na base de dados Northwind SQL Server. Também invocamos esses procedimentos armazenados gerenciados do SQL Server Management Studio (consulte as Figuras 13 e 14). No entanto, para que nosso aplicativo ASP.NET use esses procedimentos armazenados gerenciados, precisamos adicioná-los às camadas de acesso a dados e lógica de negócios na arquitetura. Nesta etapa, adicionaremos dois novos métodos ao ProductsTableAdapter no NorthwindWithSprocs Typed DataSet, que foi inicialmente criado no tutorial Creating New Stored Procedures for the Typed DataSet s TableAdapters . No Passo 7 adicionaremos os métodos correspondentes à BLL.

Abra o DataSet Tipado NorthwindWithSprocs no Visual Studio e comece por adicionar um novo método ao ProductsTableAdapter denominado GetDiscontinuedProducts. Para adicionar um novo método a um TableAdapter, clique com o botão direito do mouse no nome do TableAdapter no Designer e escolha a opção Adicionar consulta no menu de contexto.

Observação

Desde que movemos o banco de dados Northwind da pasta para a instância do banco de dados do SQL Server 2005 Express Edition, é imperativo que a cadeia de conexão correspondente a App_Data no Web.config seja atualizada para refletir essa alteração. Na Etapa 2, discutimos a atualização do NORTHWNDConnectionString valor em Web.config. Se você esqueceu de fazer essa atualização, então você verá a mensagem de erro Falha ao adicionar consulta. Não é possível encontrar conexão NORTHWNDConnectionString para objeto Web.config em uma caixa de diálogo ao tentar adicionar um novo método para o TableAdapter. Para resolver esse erro, clique em OK e, em seguida, vá para Web.config e atualize o valor NORTHWNDConnectionString, conforme discutido na Etapa 2. Em seguida, tente adicionar novamente o método ao TableAdapter. Desta vez, deve funcionar sem erros.

Adicionar um novo método inicia o assistente de Configuração de Consulta TableAdapter, que usamos muitas vezes em tutoriais anteriores. A primeira etapa nos pede para especificar como o TableAdapter deve acessar o banco de dados: por meio de uma instrução SQL ad-hoc ou por meio de um procedimento armazenado novo ou existente. Como já criamos e registramos o GetDiscontinuedProducts procedimento armazenado gerenciado no banco de dados, escolha a opção Usar procedimento armazenado existente e pressione Avançar.

Figura 15: Escolha a opção Usar procedimento armazenado existente (Clique para visualizar a imagem em tamanho real)

A próxima tela nos solicita o procedimento armazenado que o método invocará. Selecione o GetDiscontinuedProducts procedimento armazenado sob gestão na lista suspensa e pressione Avançar.

Figura 16: Selecione o procedimento armazenado gerenciado (GetDiscontinuedProducts imagem em tamanho real)

Em seguida, somos solicitados a especificar se o procedimento armazenado retorna linhas, um único valor ou nada. Como GetDiscontinuedProducts retorna o conjunto de linhas de produtos descontinuados, escolha a primeira opção (Dados tabulares) e clique em Avançar.

Figura 17: Selecione a opção Dados tabulares (Clique para visualizar a imagem em tamanho real)

A tela final do assistente nos permite especificar os padrões de acesso aos dados usados e os nomes dos métodos resultantes. Deixe ambas as caixas de seleção marcadas e nomeie os métodos FillByDiscontinued e GetDiscontinuedProducts. Clique em Concluir para finalizar o assistente.

Figura 18: Nomeie os métodos FillByDiscontinued e GetDiscontinuedProducts (Clique para visualizar a imagem em tamanho real)

Repita estes passos para criar métodos nomeados FillByPriceLessThan e GetProductsWithPriceLessThan no ProductsTableAdapter para o procedimento armazenado com gestão GetProductsWithPriceLessThan.

A Figura 19 mostra uma captura de tela do DataSet Designer depois de adicionar os métodos no ProductsTableAdapter para os GetDiscontinuedProducts e GetProductsWithPriceLessThan procedimentos armazenados geridos.

Figura 19: Inclui ProductsTableAdapter os novos métodos adicionados nesta etapa (Clique para visualizar a imagem em tamanho real)

Etapa 7: Adicionando métodos correspondentes à camada de lógica de negócios

Agora que atualizamos a Camada de Acesso a Dados para incluir métodos para chamar os procedimentos armazenados gerenciados adicionados nas Etapas 4 e 5, precisamos adicionar métodos correspondentes à Camada de Lógica de Negócios. Adicione os dois métodos a seguir à ProductsBLLWithSprocs classe:

[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
    return Adapter.GetDiscontinuedProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable 
    GetProductsWithPriceLessThan(decimal priceLessThan)
{
    return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}

Ambos os métodos simplesmente chamam o método DAL correspondente e retornam a ProductsDataTable instância. A marcação DataObjectMethodAttribute acima de cada método faz com que estes métodos sejam incluídos na lista suspensa no separador SELECT do assistente de configuração da fonte de dados ObjectDataSource.

Etapa 8: Invocando os procedimentos armazenados gerenciados da camada de apresentação

Com as Camadas de Lógica de Negócios e Acesso a Dados expandidas para incluir suporte à chamada dos procedimentos armazenados geridos GetDiscontinuedProducts e GetProductsWithPriceLessThan, agora podemos exibir os resultados desses procedimentos armazenados através de uma página ASP.NET.

Abra a página ManagedFunctionsAndSprocs.aspx na pasta AdvancedDAL e, na Caixa de Ferramentas, arraste um GridView para o Designer. Defina a propriedade do GridView ID como DiscontinuedProducts e, a partir da sua marca inteligente, vincule-a a um novo ObjectDataSource chamado DiscontinuedProductsDataSource. Configure o ObjectDataSource para extrair os seus dados do método da classe ProductsBLLWithSprocs s GetDiscontinuedProducts.

Figura 20: Configurar o ObjectDataSource para usar a classe (ProductsBLLWithSprocs imagem em tamanho real)

Figura 21: Escolha o GetDiscontinuedProducts método na lista de Drop-Down na guia SELECT (Clique para visualizar a imagem em tamanho real)

Uma vez que esta grelha será usada apenas para exibir informações do produto, defina as listas pendentes nas abas ATUALIZAR, INSERIR e EXCLUIR como Nenhuma e clique em 'Concluir'.

Ao concluir o assistente, o Visual Studio adicionará automaticamente um BoundField ou CheckBoxField para cada campo de dados no ProductsDataTable. Tire um momento para remover todos esses campos, exceto ProductName e Discontinued, em que a marcação declarativa de GridView e ObjectDataSource deve ficar semelhante à seguinte:

<asp:GridView ID="DiscontinuedProducts" runat="server" 
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="DiscontinuedProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:CheckBoxField DataField="Discontinued" 
            HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

Reserve um momento para visualizar esta página através de um navegador. Quando a página é visitada, o ObjectDataSource chama o método s ProductsBLLWithSprocs da GetDiscontinuedProducts classe. Como vimos na Etapa 7, esse método chama o método s da ProductsDataTable classe DAL GetDiscontinuedProducts , que invoca o GetDiscontinuedProducts procedimento armazenado. Este procedimento armazenado é um procedimento armazenado gerenciado e executa o código que criamos na Etapa 3, retornando os produtos descontinuados.

Os resultados retornados pelo procedimento armazenado gerenciado são empacotados em um ProductsDataTable pela DAL e, em seguida, retornados para a BLL, que os retorna à Camada de Apresentação, onde são vinculados ao GridView e exibidos. Como esperado, a grade lista os produtos que foram descontinuados.

Figura 22: Os produtos descontinuados estão listados (Clique para visualizar a imagem em tamanho real)

Para mais prática, adicione um TextBox e outro GridView à página. Faça este GridView exibir os produtos inferiores à quantidade inserida no TextBox chamando o método ProductsBLLWithSprocs da classe GetProductsWithPriceLessThan.

Etapa 9: Criando e chamando UDFs T-SQL

User-Defined Funções, ou UDFs, são objetos de banco de dados que imitam de perto a semântica de funções em linguagens de programação. Como uma função em C#, UDFs podem incluir um número variável de parâmetros de entrada e retornar um valor de um tipo específico. Um UDF pode retornar dados escalares - uma cadeia de caracteres, um inteiro e assim por diante - ou dados tabulares. Vamos dar uma olhada rápida em ambos os tipos de UDFs, começando com um UDF que retorna um tipo de dados escalar.

O UDF a seguir calcula o valor estimado do estoque de um determinado produto. Ele faz isso ao receber três parâmetros de entrada - os valores UnitPrice, UnitsInStock e Discontinued para um determinado produto - e retorna um valor do tipo money. Calcula o valor estimado do inventário multiplicando o UnitPrice pelo UnitsInStock. Para itens descontinuados, esse valor é reduzido pela metade.

CREATE FUNCTION udf_ComputeInventoryValue
(
    @UnitPrice money,
    @UnitsInStock smallint,
    @Discontinued bit
)
RETURNS money
AS
BEGIN
    DECLARE @Value decimal
    SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
    IF @Discontinued = 1
        SET @Value = @Value * 0.5
    
    RETURN @Value
END

Depois que esse UDF tiver sido adicionado ao banco de dados, ele poderá ser encontrado por meio do Management Studio expandindo a pasta Programabilidade, depois Funções e, em seguida, Funções de valor escalar. Ele pode ser usado em uma SELECT consulta como:

SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
    (UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Adicionei a udf_ComputeInventoryValue UDF à base de dados Northwind; A Figura 23 mostra a saída da consulta acima SELECT quando visualizada pelo Management Studio. Observe também que o UDF está listado na pasta Funções de valor escalar no Pesquisador de Objetos.

Figura 23: Os valores de inventário de cada produto estão listados (Clique para visualizar a imagem em tamanho real)

UDFs também podem retornar dados tabulares. Por exemplo, podemos criar um UDF que retorna produtos que pertencem a uma categoria específica:

CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(    
    @CategoryID int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ProductID, ProductName, SupplierID, CategoryID, 
           QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
           ReorderLevel, Discontinued
    FROM Products
    WHERE CategoryID = @CategoryID
)

O udf_GetProductsByCategoryID UDF aceita um @CategoryID parâmetro de entrada e retorna os resultados da consulta especificada SELECT . Uma vez criada, esta UDF pode ser referenciada na cláusula FROM (ou JOIN) de uma consulta SELECT. O exemplo a seguir retornaria os ProductIDvalores , ProductNamee CategoryID para cada uma das bebidas.

SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)

Adicionei a udf_GetProductsByCategoryID UDF à base de dados Northwind; A Figura 24 mostra a saída da consulta acima SELECT quando visualizada por meio do Management Studio. UDFs que retornam dados tabulares podem ser encontrados na pasta Funções de valor de tabela do Pesquisador de Objetos.

Figura 24: O ProductID, ProductNamee CategoryID estão listados para cada bebida (Clique para visualizar a imagem em tamanho real)

Observação

Para obter mais informações sobre como criar e usar UDFs, confira Introdução ao User-Defined Funções. Confira também Vantagens e Desvantagens de Funções de User-Defined.

Etapa 10: Criando uma UDF gerenciada

Os udf_ComputeInventoryValue e udf_GetProductsByCategoryID UDFs criados nos exemplos acima são objetos de banco de dados T-SQL. O SQL Server 2005 também oferece suporte a UDFs gerenciados, que podem ser adicionados ao ManagedDatabaseConstructs projeto assim como os procedimentos armazenados gerenciados das Etapas 3 e 5. Para esta etapa, vamos implementar o udf_ComputeInventoryValue UDF em código gerenciado.

Para adicionar um UDF gerenciado ao projeto, clique com o ManagedDatabaseConstructs botão direito do mouse no nome do projeto no Gerenciador de Soluções e escolha Adicionar um Novo Item. Selecione o User-Defined Template na caixa de diálogo Adicionar Novo Item e nomeie o novo arquivo UDF udf_ComputeInventoryValue_Managed.cs.

Figura 25: Adicionar uma nova UDF gerenciada ao projeto (ManagedDatabaseConstructs imagem em tamanho real)

O modelo User-Defined Function cria uma partial classe nomeada UserDefinedFunctions com um método cujo nome é o mesmo que o nome do arquivo de classe (udf_ComputeInventoryValue_Managed, neste caso). Esse método é decorado usando o SqlFunction atributo , que sinaliza o método como um UDF gerenciado.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString udf_ComputeInventoryValue_Managed()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

O udf_ComputeInventoryValue método atualmente retorna um SqlString objeto e não aceita nenhum parâmetro de entrada. Precisamos atualizar a definição do método para que ele aceite três parâmetros de entrada - UnitPrice, UnitsInStock, e Discontinued - e retorne um SqlMoney objeto. A lógica para calcular o valor do inventário é idêntica à do T-SQL udf_ComputeInventoryValue UDF.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_ComputeInventoryValue_Managed
    (SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
    SqlMoney inventoryValue = 0;
    if (!UnitPrice.IsNull && !UnitsInStock.IsNull)
    {
        inventoryValue = UnitPrice * UnitsInStock;
        if (Discontinued == true)
            inventoryValue = inventoryValue * new SqlMoney(0.5);
    }
    return inventoryValue;
}

Observe que os parâmetros de entrada do método UDF são de seus tipos SQL correspondentes: SqlMoney para o UnitPrice campo, SqlInt16 para UnitsInStock, e SqlBoolean para Discontinued. Esses tipos de dados refletem os tipos definidos na Products tabela: a UnitPrice coluna é do tipo money, a UnitsInStock coluna do tipo smallinte a Discontinued coluna do tipo bit.

O código começa criando uma SqlMoney instância chamada inventoryValue à qual é atribuído um valor de 0. A tabela Products permite que valores do banco de dados NULL sejam utilizados nas colunas UnitsInPrice e UnitsInStock. Portanto, primeiro precisamos verificar se esses valores contêm NULLs, o que fazemos por meio da propriedade SqlMoney do objeto IsNull. Se ambos UnitPrice e UnitsInStock contêm valores não correspondentes a NULL, então calculamos inventoryValue como o produto dos dois. Então, se Discontinued for verdade, então reduzimos o valor pela metade.

Observação

O SqlMoney objeto só permite que duas SqlMoney instâncias sejam multiplicadas juntas. Ele não permite que uma SqlMoney instância seja multiplicada por um número literal de vírgula flutuante. Portanto, para reduzir inventoryValue pela metade, multiplicamos por uma nova SqlMoney instância que tem o valor 0,5.

Etapa 11: Implantando a UDF gerenciada

Agora que o UDF gerenciado foi criado, estamos prontos para implantá-lo no banco de dados Northwind. Como vimos na Etapa 4, os objetos gerenciados em um projeto do SQL Server são implantados clicando com o botão direito do mouse no nome do projeto no Gerenciador de Soluções e escolhendo a opção Implantar no menu de contexto.

Depois de implantar o projeto, retorne ao SQL Server Management Studio e atualize a pasta Funções com valor escalar. Agora você deve ver duas entradas:

• dbo.udf_ComputeInventoryValue - o T-SQL UDF criado na Etapa 9, e
• dbo.udf ComputeInventoryValue_Managed - o UDF gerenciado criado na Etapa 10 que acabou de ser implantado.

Para testar essa UDF gerenciada, execute a seguinte consulta no Management Studio:

SELECT ProductID, ProductName, 
       dbo.udf_ComputeInventoryValue_Managed(
                 UnitPrice, 
                 UnitsInStock, 
                 Discontinued
              ) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Este comando usa o UDF gerenciado udf ComputeInventoryValue_Managed em vez do UDF T-SQL udf_ComputeInventoryValue , mas a saída é a mesma. Volte a consultar a Figura 23 para ver uma captura de tela da saída da UDF.

Etapa 12: Depurando os objetos de banco de dados gerenciados

No tutorial Debugging Stored Procedures , discutimos as três opções para depurar o SQL Server por meio do Visual Studio: Depuração Direta de Banco de Dados, Depuração de Aplicativo e Depuração de um Projeto do SQL Server. Os objetos de banco de dados gerenciados não podem ser depurados por meio da depuração direta de banco de dados, mas podem ser depurados de um aplicativo cliente e diretamente do projeto do SQL Server. No entanto, para que a depuração funcione, o banco de dados do SQL Server 2005 deve permitir a depuração SQL/CLR. Lembre-se de que, quando criámos o projeto ManagedDatabaseConstructs, o Visual Studio nos perguntou se queríamos habilitar a depuração SQL/CLR (consulte a Figura 6 na Etapa 2). Essa configuração pode ser modificada clicando com o botão direito do mouse no banco de dados na janela Server Explorer.

Figura 26: Verifique se o banco de dados permite depuração SQL/CLR

Imagine que queríamos depurar o GetProductsWithPriceLessThan procedimento armazenado gerenciado. Começaríamos definindo um ponto de interrupção dentro do código do GetProductsWithPriceLessThan método.

Figura 27: Definir um ponto de interrupção no método (GetProductsWithPriceLessThan imagem em tamanho real)

Vamos examinar primeiro a depuração dos objetos de base de dados geridos do Projeto SQL Server. Como a nossa Solução inclui dois projetos - o ManagedDatabaseConstructs Projeto SQL Server junto com o nosso site - para depurar a partir do Projeto SQL Server, precisamos instruir o Visual Studio a iniciar o Projeto SQL Server quando começarmos a ManagedDatabaseConstructs depuração. Clique com o botão direito do ManagedDatabaseConstructs mouse no projeto no Gerenciador de Soluções e escolha a opção Definir como Projeto de Inicialização no menu de contexto.

Quando o ManagedDatabaseConstructs projeto é iniciado a partir do depurador, ele executa as instruções SQL no Test.sql arquivo, que está localizado na Test Scripts pasta. Por exemplo, para testar o GetProductsWithPriceLessThan procedimento armazenado gerenciado, substitua o conteúdo do arquivo existente Test.sql pela instrução a seguir, que invoca o GetProductsWithPriceLessThan procedimento armazenado gerenciado passando o @CategoryID valor de 14,95:

exec GetProductsWithPriceLessThan 14.95

Depois de inserir o script acima no Test.sql, comece a depuração indo para o menu Depurar e escolhendo Iniciar Depuração ou pressionando F5 ou o ícone de reprodução verde na Barra de Ferramentas. Isso criará os projetos dentro da Solução, implantará os objetos de banco de dados gerenciados no banco de dados Northwind e, em seguida, executará o Test.sql script. Neste ponto, o ponto de interrupção será atingido e podemos percorrer o método GetProductsWithPriceLessThan, examinar os valores dos parâmetros de entrada e assim por diante.

Figura 28: O ponto de interrupção no método foi atingido (Clique para visualizar a GetProductsWithPriceLessThanimagem em tamanho real)

Para que um objeto de banco de dados SQL seja depurado por meio de um aplicativo cliente, é imperativo que o banco de dados seja configurado para dar suporte à depuração de aplicativos. Clique com o botão direito do mouse no banco de dados no Gerenciador de Servidores e verifique se a opção Depuração de Aplicativo está marcada. Além disso, precisamos configurar o aplicativo ASP.NET para integrar com o depurador SQL e desabilitar o pool de conexões. Essas etapas foram discutidas em detalhes na Etapa 2 do tutorial Depurando procedimentos armazenados .

Depois de configurar o aplicativo ASP.NET e o banco de dados, defina o site ASP.NET como o projeto de inicialização e inicie a depuração. Se você visitar uma página que chama um dos objetos gerenciados que tem um ponto de interrupção, o aplicativo será interrompido e o controle será entregue ao depurador, onde você poderá percorrer o código, conforme mostrado na Figura 28.

Etapa 13: Compilando e implantando manualmente objetos de banco de dados gerenciados

Os Projetos do SQL Server facilitam a criação, compilação e implantação de objetos de banco de dados gerenciados. Infelizmente, os projetos do SQL Server só estão disponíveis nas edições Professional e Team Systems do Visual Studio. Se você estiver usando o Visual Web Developer ou o Standard Edition do Visual Studio e quiser usar objetos de banco de dados gerenciados, precisará criá-los e implantá-los manualmente. Isto envolve quatro etapas:

1. Crie um arquivo que contenha o código-fonte para o objeto de banco de dados gerenciado,
2. Compile o objeto em um assembly,
3. Registre o assembly no banco de dados do SQL Server 2005 e
4. Crie um objeto de banco de dados no SQL Server que aponte para o método apropriado no assembly.

Para ilustrar essas tarefas, vamos criar um novo procedimento armazenado gerenciado que retorna os produtos cujo UnitPrice valor é maior do que um valor especificado. Crie um novo arquivo no seu computador chamado GetProductsWithPriceGreaterThan.cs e insira o seguinte código no arquivo (você pode usar o Visual Studio, o Bloco de Notas ou qualquer editor de texto para fazer isso):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetProductsWithPriceGreaterThan(SqlMoney price)
    {
        // Create the command
        SqlCommand myCommand = new SqlCommand();
        myCommand.CommandText =
            @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                     QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                     ReorderLevel, Discontinued
              FROM Products
              WHERE UnitPrice > @MinPrice";
        myCommand.Parameters.AddWithValue("@MinPrice", price);
        // Execute the command and send back the results
        SqlContext.Pipe.ExecuteAndSend(myCommand);
    }
};

Este código é quase idêntico ao do GetProductsWithPriceLessThan método criado na Etapa 5. As únicas diferenças são os nomes do método, a WHERE cláusula e o nome do parâmetro usado na consulta. De volta ao GetProductsWithPriceLessThan método, a WHERE cláusula dizia: WHERE UnitPrice < @MaxPrice. Aqui, em GetProductsWithPriceGreaterThan, usamos: WHERE UnitPrice > @MinPrice .

Agora precisamos compilar essa classe em um assembly. Na linha de comando, navegue até o diretório onde você salvou o GetProductsWithPriceGreaterThan.cs arquivo e use o compilador C# (csc.exe) para compilar o arquivo de classe em um assembly:

csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

Se a pasta que contém csc.exe não estiver no sistema PATH, terá que referenciar totalmente o seu caminho, %WINDOWS%\Microsoft.NET\Framework\version\, assim:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

Figura 29: Compilar GetProductsWithPriceGreaterThan.cs em um assembly (Clique para visualizar a imagem em tamanho real)

O /t sinalizador especifica que o arquivo de classe C# deve ser compilado em uma DLL (em vez de um executável). O /out indicador especifica o nome do assembly resultante.

Observação

Em vez de compilar o arquivo de classe da linha de comando, você pode, alternativamente, usar o GetProductsWithPriceGreaterThan.csVisual C# Express Edition ou criar um projeto de biblioteca de classes separado no Visual Studio Standard Edition. S ren Jacob Lauritsen gentilmente forneceu esse projeto Visual C# Express Edition com código para o GetProductsWithPriceGreaterThan procedimento armazenado e os dois procedimentos armazenados gerenciados e UDF criados nas etapas 3, 5 e 10. O projeto S ren s também inclui os comandos T-SQL necessários para adicionar os objetos de banco de dados correspondentes.

Com o código compilado em um assembly, estamos prontos para registrar o assembly no banco de dados do SQL Server 2005. Isso pode ser feito por meio do T-SQL, usando o comando CREATE ASSEMBLY, ou por meio do SQL Server Management Studio. Vamos nos concentrar no uso do Management Studio.

No Management Studio, expanda a pasta Programabilidade no banco de dados Northwind. Uma de suas subpastas é Assemblies. Para adicionar manualmente um novo Assembly ao banco de dados, clique com o botão direito do mouse na pasta Assemblies e escolha New Assembly no menu de contexto. Isso exibe a caixa de diálogo New Assembly (consulte a Figura 30). Clique no botão Procurar, selecione o ManuallyCreatedDBObjects.dll assembly que acabamos de compilar e, em seguida, clique em OK para adicionar o assembly ao banco de dados. Você não deve ver o ManuallyCreatedDBObjects.dll assembly no Pesquisador de Objetos.

Figura 30: Adicionar o ManuallyCreatedDBObjects.dll assembly ao banco de dados (Clique para visualizar a imagem em tamanho real)

Figura 31: O ManuallyCreatedDBObjects.dll está listado no Pesquisador de Objetos

Embora tenhamos adicionado o conjunto ao banco de dados Northwind, ainda não associámos um procedimento armazenado ao método GetProductsWithPriceGreaterThan no conjunto. Para fazer isso, abra uma nova janela de consulta e execute o seguinte script:

CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan] 
( 
    @price money 
) 
WITH EXECUTE AS CALLER 
AS 
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan] 
GO

Isso cria um novo procedimento armazenado no banco de dados Northwind chamado GetProductsWithPriceGreaterThan e o associa ao método GetProductsWithPriceGreaterThan gerenciado (que está na classe StoredProcedures, que está no assembly ManuallyCreatedDBObjects).

Depois de executar o script acima, atualize a pasta Stored Procedures no Pesquisador de Objetos. Você verá uma nova entrada de procedimento armazenado - GetProductsWithPriceGreaterThan - que tem um ícone de cadeado ao lado dela. Para testar este procedimento armazenado, insira e execute o seguinte script na janela de consulta:

exec GetProductsWithPriceGreaterThan 24.95

Como mostra a Figura 32, o comando acima exibe informações para os produtos com um UnitPrice superior a US$ 24,95.

Figura 32: O ManuallyCreatedDBObjects.dll está listado no Pesquisador de Objetos (Clique para visualizar a imagem em tamanho real)

Resumo

O Microsoft SQL Server 2005 fornece integração com o Common Language Runtime (CLR), que permite que objetos de banco de dados sejam criados usando código gerenciado. Anteriormente, esses objetos de banco de dados só podiam ser criados usando T-SQL, mas agora podemos criar esses objetos usando linguagens de programação .NET como C#. Neste tutorial, criamos dois procedimentos armazenados gerenciados e uma função de User-Defined gerenciada.

O tipo de projeto SQL Server do Visual Studio facilita a criação, compilação e implantação de objetos de banco de dados gerenciados. Além disso, oferece um suporte abrangente de depuração. No entanto, os tipos de projeto do SQL Server só estão disponíveis nas edições Professional e Team Systems do Visual Studio. Para aqueles que usam o Visual Web Developer ou a Standard Edition do Visual Studio, as etapas de criação, compilação e implantação devem ser executadas manualmente, como vimos na Etapa 13.

Feliz Programação!

Leitura adicional

Para obter mais informações sobre os tópicos discutidos neste tutorial, consulte os seguintes recursos:

• Vantagens e desvantagens das funções User-Defined
• Criando objetos do SQL Server 2005 em código gerenciado
• Como criar e executar um procedimento armazenado do SQL Server CLR
• Como criar e executar uma função CLR SQL Server User-Defined
• Como: Editar o Test.sql script para executar objetos SQL
• Introdução às funções definidas pelo usuário
• Código gerenciado e SQL Server 2005 (vídeo)
• Transact-SQL Referência
• Passo a passo: Criando um procedimento armazenado em código gerenciado

Sobre o Autor

Scott Mitchell, autor de sete livros sobre ASP/ASP.NET e fundador da 4GuysFromRolla.com, trabalha com tecnologias Web da Microsoft desde 1998. Scott trabalha como consultor, formador e escritor independente. Seu último livro é Sams Teach Yourself ASP.NET 2.0 in 24 Hours. Ele pode ser contatado em mitchell@4GuysFromRolla.com.

Um agradecimento especial a

Esta série de tutoriais foi revisada por muitos revisores úteis. O revisor principal deste tutorial foi S ren Jacob Lauritsen. Além de revisar este artigo, S ren também criou o projeto Visual C# Express Edition incluído no download deste artigo para compilar manualmente os objetos de banco de dados gerenciados. Interessado em rever meus próximos artigos do MSDN? Se for o caso, envie-me uma mensagem para mitchell@4GuysFromRolla.com.

AnteriorPróximo