Criar procedimentos armazenados e funções definidas pelo usuário com código gerenciado (C#)

por Scott Mitchell

Baixar 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 que você depure esses objetos de banco de dados gerenciados.

Introdução

Bancos de dados como o microsoft SQL Server 2005 usam o T-SQL (Transact-linguagem SQL) para inserir, modificar e recuperar dados. A maioria dos sistemas de banco de dados inclui constructos 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 é UDFs ( Funções Definidas pelo Usuário), um constructo que examinaremos com mais detalhes na Etapa 9.

Em sua essência, o SQL foi projetado para trabalhar com conjuntos de dados. As SELECTinstruções , UPDATEe DELETE se aplicam inerentemente a todos os registros na tabela correspondente e são limitadas apenas por suas WHERE cláusulas. No entanto, há muitos recursos de linguagem projetados para trabalhar com um registro por vez e para manipular dados escalares. CURSOR s permitem que um conjunto de registros seja executado em loop por um de cada vez. Funções de manipulação de cadeia de caracteres como LEFT, CHARINDEXe PATINDEX funcionam com dados escalares. O SQL também inclui instruções de fluxo de controle como IF e WHILE.

Antes do Microsoft SQL Server 2005, procedimentos armazenados e UDFs só podiam ser definidos como uma coleção de instruções T-SQL. SQL Server 2005, no entanto, foi projetado para fornecer integração com o CLR (Common Language Runtime), que é o runtime usado por todos os assemblies .NET. Consequentemente, os procedimentos armazenados e UDFs em um banco de dados 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 em suas próprias classes personalizadas.

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

Observação

Os objetos de banco de dados gerenciados oferecem algumas vantagens em relação aos respectivos equivalentes do SQL. A riqueza e familiaridade da linguagem e a capacidade de reutilizar o código e a lógica existentes são as vantagens main. Mas os objetos de banco de dados gerenciados provavelmente serão menos eficientes ao trabalhar com conjuntos de dados que não envolvem muita lógica de procedimento. Para uma discussão mais detalhada sobre as vantagens de usar código gerenciado versus T-SQL, marcar as vantagens de usar código gerenciado para criar objetos de banco de dados.

Etapa 1: Mover 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 na App_Data distribuição simplificada e na execução desses tutoriais, pois todos os arquivos estavam localizados em um diretório e não exigiam etapas de configuração adicionais para testar o tutorial.

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

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

Inicialização do SQL Server Management Studio. Como mostra a Figura 1, o Management Studio começa perguntando a qual servidor se conectar. Insira localhost\SQLExpress para o nome do servidor, escolha Autenticação do Windows na lista suspensa Autenticação e clique em Conectar.

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

Depois de se conectar, a janela Pesquisador de Objetos listará informações sobre a instância do banco de dados 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ê possa tê-lo movido) à instância do banco de dados 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 drill down no arquivo apropriado NORTHWND.MDF e clique em OK. Neste ponto, sua tela deve ser semelhante à Figura 2.

Figura 2: Conectar-se à Instância de Banco de Dados Apropriada (Clique para exibir a imagem em tamanho real)

Observação

Ao se conectar à instância 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 uma busca detalhada em diretórios de perfil do usuário, como Meus Documentos. Portanto, certifique-se de colocar os NORTHWND.MDF arquivos e NORTHWND_log.LDF em um diretório de perfil não 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 a Pesquisador de Objetos agora deve listar o banco de dados anexado. As chances são de 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 de 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 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 essas etapas podem ser executadas manualmente. Podemos criar o código em qualquer editor de texto, compilá-lo na 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 SQL Server que automatiza essas tarefas. Neste tutorial, abordaremos o uso do tipo SQL Server Project para criar um procedimento armazenado gerenciado e um UDF.

Observação

Se você estiver usando o Visual Web Developer ou a edição Standard do Visual Studio, precisará usar a abordagem manual. A etapa 13 fornece instruções detalhadas para executar essas etapas manualmente. Encorajo você a ler as Etapas 2 a 12 antes de ler a Etapa 13, pois essas etapas incluem instruções importantes de configuração 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 Figura 4). Faça drill down até o tipo de projeto Banco de dados e, em seguida, nos Modelos listados à direita, escolha criar um novo SQL Server Project. Escolhi nomear este projeto ManagedDatabaseConstructs e colocá-lo em uma solução chamada Tutorial75.

Figura 4: Criar um novo projeto de 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 SQL Server Projeto.

Um SQL Server Project está vinculado a um banco de dados específico. Consequentemente, depois de criar o novo SQL Server Project, 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 apontar para o banco de dados Northwind que registramos na instância do banco de dados SQL Server 2005 Express Edition novamente na Etapa 1.

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

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

Figura 6: Habilitar a depuração do SQL/CLR

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

Agora podemos adicionar novos procedimentos armazenados gerenciados e UDFs a este projeto, mas antes disso, vamos primeiro incluir 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 SQL Server Project.

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

O NORTHWNDConnectionString valor em Web.config atualmente faz referência ao NORTHWND.MDF arquivo na App_Data pasta . Como removemos esse banco de dados e App_Data o registramos explicitamente na instância do banco de dados SQL Server 2005 Express Edition, precisamos atualizar o NORTHWNDConnectionString valor correspondentemente. Abra o Web.config arquivo no site e altere o NORTHWNDConnectionString valor para que o cadeia de conexão leia: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. Após essa alteração, sua <connectionStrings> seção em Web.config deverá 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 SQL Server de um aplicativo cliente, como um site ASP.NET, precisamos desabilitar o pool de conexões. O cadeia de conexão mostrado 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 SQL Server Project. 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 User-Defined Functions, 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 chamado GetDiscontinuedProducts.cs (clique para exibir 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 Products seguir cria um SqlCommand objeto e o define CommandText como uma SELECT consulta que retorna todas as colunas da 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 método GetDiscontinuedProducts.

// 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 SqlPipe objeto por meio de sua Pipe propriedade. Esse SqlPipe objeto é usado para transportar informações entre o banco de dados SQL Server e o aplicativo de chamada. Como o 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 de procedimento em vez de lógica baseada em conjunto. A lógica de procedimento 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 de procedimento. Portanto, ele seria implementado idealmente 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. Implantar um SQL Server Project 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 é escrito 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 em um valor mais alto para habilitar este recurso. Consulte a ajuda para o procedimento sp_dbcmptlevelarmazenado.

Essa mensagem de erro ocorre ao tentar registrar o assembly com o banco de dados Northwind. Para registrar um assembly com um banco de dados SQL Server 2005, o nível de compatibilidade do banco de dados deve ser definido como 90. Por padrão, novos bancos de dados 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 do 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 insira:

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 exibir a imagem em tamanho real)

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

Volte para 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 Programação e, em seguida, expanda a pasta Assemblies. Como mostra a Figura 10, o banco de dados Northwind agora inclui o assembly gerado pelo ManagedDatabaseConstructs projeto.

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

Expanda também a pasta Procedimentos Armazenados. Lá, você verá um procedimento armazenado chamado GetDiscontinuedProducts. Esse procedimento armazenado foi criado pelo processo de implantação e aponta para o GetDiscontinuedProducts método no ManagedDatabaseConstructs assembly. Quando o GetDiscontinuedProducts procedimento armazenado é executado, ele, por sua vez, executa o GetDiscontinuedProducts método . Como esse é um procedimento armazenado gerenciado, ele não pode ser editado por meio do Management Studio (daí o ícone de bloqueio ao lado do nome do procedimento armazenado).

Figura 11: o GetDiscontinuedProducts procedimento armazenado está listado na pasta procedimentos armazenados

Ainda há mais um obstáculo que precisamos superar antes de chamarmos o procedimento armazenado gerenciado: o banco de dados está configurado para impedir a execução do 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 do código do usuário no .NET Framework está desabilitada. Habilite a opção de configuração habilitada para clr.

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 habilitada para clr está definida como 0 no momento.

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

Observe que cada 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 habilitada para clr, execute o seguinte comando:

exec sp_configure 'clr enabled', 1

Se você executar novamente, exec sp_configure verá que a instrução acima atualizou o valor de configuração do clr habilitado para 1, mas que o valor de execução ainda está definido como 0. Para que essa alteração de configuração afete, precisamos executar o RECONFIGURE comando , que definirá o valor de execução como o valor de configuração atual. Basta inserir RECONFIGURE na janela de consulta e clicar no ícone Executar na Barra de Ferramentas. Se você executar exec sp_configure agora, verá um valor de 1 para os valores de configuração e execução habilitados para clr.

Com a configuração habilitada para clr concluída, estamos prontos para executar o procedimento armazenado gerenciado GetDiscontinuedProducts . Na janela de consulta, insira e execute o comando execGetDiscontinuedProducts. Invocar o procedimento armazenado faz com que o código gerenciado correspondente no GetDiscontinuedProducts método seja executado. Esse código emite uma SELECT consulta para retornar todos os produtos descontinuados e retorna esses dados para o aplicativo de chamada, que é 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 exibir 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 Criando novos procedimentos armazenados para o TableAdapters do Conjunto de Dados Tipado , criamos um procedimento armazenado chamado GetProductsByCategoryID que aceitava um parâmetro de entrada chamado @CategoryID. Em seguida, 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 aceita 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 que especifica um preço e retornará todos os produtos cujo UnitPrice campo é 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. Atribua um nome ao arquivo GetProductsWithPriceLessThan.cs. 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 se assemelham muito à definição e ao código do GetDiscontinuedProducts método criado na Etapa 3. As únicas diferenças são que o GetProductsWithPriceLessThan método aceita como parâmetro de entrada (price), a SqlCommand consulta s inclui um parâmetro (@MaxPrice) e um parâmetro é adicionado à SqlCommand coleção s Parameters é e atribuído o valor da price variável.

Depois de adicionar esse código, reimplante o projeto SQL Server. Em seguida, retorne ao SQL Server Management Studio e Atualize a pasta Procedimentos Armazenados. Você deverá ver uma nova entrada, GetProductsWithPriceLessThan. Em uma janela de consulta, insira 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 exibir imagem em tamanho real)

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

Neste ponto, adicionamos os GetDiscontinuedProducts procedimentos armazenados gerenciados e GetProductsWithPriceLessThan ao projeto e os ManagedDatabaseConstructs registramos no banco de dados northwind SQL Server. Também invocamos esses procedimentos armazenados gerenciados de 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 Conjunto de Dados Tipado, que foi criado inicialmente no tutorial Criando novos procedimentos armazenados para tableAdapters do Conjunto de Dados TipadoNorthwindWithSprocs. Na Etapa 7, adicionaremos métodos correspondentes à BLL.

Abra o NorthwindWithSprocs Conjunto de Dados Tipado no Visual Studio e comece adicionando um novo método ao ProductsTableAdapter chamado 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

Como movemos o banco de dados Northwind da App_Data pasta para a instância do banco de dados SQL Server 2005 Express Edition, é imperativo que os cadeia de conexão correspondentes no Web.config sejam atualizados 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, verá a mensagem de erro Falha ao adicionar consulta. Não é possível localizar a conexão NORTHWNDConnectionString do objeto Web.config em uma caixa de diálogo ao tentar adicionar um novo método ao TableAdapter. Para resolve esse erro, clique em OK e vá para Web.config e atualize o NORTHWNDConnectionString valor conforme discutido na Etapa 2. Em seguida, tente adicionar novamente o método ao TableAdapter. Desta vez, ele deve funcionar sem erros.

A adição de um novo método inicia o assistente de Configuração de Consulta tableAdapter, que usamos muitas vezes em tutoriais anteriores. A primeira etapa nos solicita que especifique 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 com o banco de dados, escolha a opção Usar procedimento armazenado existente e clique em Avançar.

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

A próxima tela nos solicita o procedimento armazenado que o método invocará. Escolha o GetDiscontinuedProducts procedimento armazenado gerenciado na lista suspensa e clique em Avançar.

Figura 16: Selecionar o GetDiscontinuedProducts procedimento armazenado gerenciado (clique para exibir a imagem em tamanho real)

Em seguida, será solicitado que especifique se o procedimento armazenado retorna linhas, um único valor ou nada. Como GetDiscontinuedProducts retorna o conjunto de linhas de produto descontinuadas, escolha a primeira opção ( Dados tabulares ) e clique em Avançar.

Figura 17: Selecionar a opção Dados Tabulares (clique para exibir a imagem em tamanho real)

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

Figura 18: Nomear os métodos FillByDiscontinued e GetDiscontinuedProducts (clique para exibir a imagem em tamanho real)

Repita essas etapas para criar métodos chamados FillByPriceLessThan e GetProductsWithPriceLessThan no ProductsTableAdapter para o GetProductsWithPriceLessThan procedimento armazenado gerenciado.

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

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

Etapa 7: Adicionar 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 DataObjectMethodAttribute marcação acima de cada método faz com que esses métodos sejam incluídos na lista suspensa na guia SELECT do assistente Configurar Fonte de Dados do ObjectDataSource.

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

Com as Camadas de Acesso a Dados e Lógica de Negócios aumentadas para incluir suporte para chamar os GetDiscontinuedProducts procedimentos armazenados gerenciados e GetProductsWithPriceLessThan , agora podemos exibir esses resultados de procedimentos armazenados por meio de uma página ASP.NET.

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

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

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

Como essa grade será usada apenas para exibir informações do produto, defina as listas suspensas nas guias UPDATE, INSERT e DELETE como (Nenhum) e clique em Concluir.

Ao concluir o assistente, o Visual Studio adicionará automaticamente um BoundField ou CheckBoxField para cada campo de dados no ProductsDataTable. Reserve um momento para remover todos esses campos, exceto e ProductNameDiscontinued, em que ponto a marcação declarativa de GridView e ObjectDataSource deve ser 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 exibir esta página por meio de um navegador. Quando a página é visitada, o ObjectDataSource chama o ProductsBLLWithSprocs método da classe s GetDiscontinuedProducts . Como vimos na Etapa 7, esse método chama para baixo o método da classe s da ProductsDataTableGetDiscontinuedProducts DAL, que invoca o GetDiscontinuedProducts procedimento armazenado. Esse 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 pelo DAL e, em seguida, retornados para a BLL, que os retorna para a Camada de Apresentação em que estão associados ao GridView e exibidos. Como esperado, a grade lista os produtos que foram descontinuados.

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

Para mais práticas, adicione um TextBox e outro GridView à página. Fazer com que esse GridView exiba os produtos menores que a quantidade inserida na TextBox chamando o ProductsBLLWithSprocs método da classe s GetProductsWithPriceLessThan .

Etapa 9: Criando e chamando UDFs do T-SQL

User-Defined Functions, ou UDFs, são objetos de banco de dados que imitam de perto a semântica das funções em linguagens de programação. Como uma função em C#, as UDFs podem incluir um número variável de parâmetros de entrada e retornar um valor de um tipo específico. Uma 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 uma UDF que retorna um tipo de dados escalar.

A UDF a seguir calcula o valor estimado do inventário para um produto específico. Ele faz isso usando três parâmetros de entrada - os UnitPricevalores , UnitsInStocke Discontinued para um produto específico - e retorna um valor do tipo money. Ele 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 essa UDF tiver sido adicionada ao banco de dados, ela poderá ser encontrada por meio do Management Studio expandindo a pasta Programação, Funções e, em seguida, Funções de valor escalar. Ele pode ser usado em uma SELECT consulta como esta:

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

Eu adiciono a udf_ComputeInventoryValue UDF ao banco de dados Northwind; A Figura 23 mostra a saída da consulta acima SELECT quando exibida por meio do Management Studio. Observe também que a UDF está listada na pasta Funções de valor escalar no Pesquisador de Objetos.

Figura 23: Os valores de inventário de cada produto são listados (clique para exibir a imagem em tamanho real)

UDFs também podem retornar dados tabulares. Por exemplo, podemos criar uma 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
)

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

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

Eu adiciono a udf_GetProductsByCategoryID UDF ao banco de dados Northwind; A Figura 24 mostra a saída da consulta acima SELECT quando exibida por meio do Management Studio. UDFs que retornam dados tabulares podem ser encontradas na pasta Funções de valor de tabela do Pesquisador de Objetos.

Figura 24: o ProductID, ProductNamee CategoryID são listados para cada bebida (clique para exibir a imagem em tamanho real)

Observação

Para obter mais informações sobre como criar e usar UDFs, marcar introdução ao User-Defined Functions. Além disso, marcar vantagens e desvantagens das funções User-Defined.

Etapa 10: Criando uma UDF gerenciada

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

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

Figura 25: Adicionar um novo UDF gerenciado ao ManagedDatabaseConstructs projeto (clique para exibir a imagem em tamanho real)

O modelo User-Defined Function cria uma partial classe chamada UserDefinedFunctions com um método cujo nome é o mesmo que o nome do arquivo de classe (udf_ComputeInventoryValue_Managed, nesta instância). Esse método é decorado usando o SqlFunction atributo , que sinaliza o método como uma UDF gerenciada.

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");
    }
};

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

[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 UnitsInStocke 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 que recebe um valor igual a 0. A Products tabela permite valores de banco de dados NULL nas UnitsInPrice colunas e UnitsInStock . Portanto, precisamos primeiro marcar para ver se esses valores contêm NULL s, o que fazemos por meio da SqlMoney propriedade do IsNullobjeto. Se e UnitPriceUnitsInStock contiverem valores nãoNULL , calcularemos o inventoryValue para ser o produto dos dois. Em seguida, se Discontinued for true, reduziremos pela metade o valor.

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 de ponto flutuante literal. Portanto, para multiplicá-la pela metade inventoryValue por uma nova SqlMoney instância que tenha o valor 0,5.

Etapa 11: Implantando a UDF gerenciada

Agora que a UDF gerenciada foi criada, estamos prontos para implantá-la no banco de dados Northwind. Como vimos na Etapa 4, os objetos gerenciados em um SQL Server Project 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 Functions com valor escalar. Agora você deve ver duas entradas:

• dbo.udf_ComputeInventoryValue - a UDF do T-SQL criada na Etapa 9 e
• dbo.udf ComputeInventoryValue_Managed – a UDF gerenciada criada na Etapa 10 que acabou de ser implantada.

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

Esse comando usa o UDF gerenciado udf ComputeInventoryValue_Managed em vez do UDF do T-SQL udf_ComputeInventoryValue , mas a saída é a mesma. Consulte 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 Procedimentos Armazenados de Depuração, discutimos as três opções para depurar SQL Server por meio do Visual Studio: Depuração Direta de Banco de Dados, Depuração de Aplicativos e Depuração de um projeto de 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 SQL Server. No entanto, para que a depuração funcione, o banco de dados SQL Server 2005 deve permitir a depuração do SQL/CLR. Lembre-se de que, quando criamos o projeto pela primeira vez, o ManagedDatabaseConstructs Visual Studio nos perguntou se queríamos habilitar a depuração do 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 da janela Servidor Explorer.

Figura 26: Verifique se o banco de dados permite a depuração de 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 GetProductsWithPriceLessThan método (clique para exibir a imagem em tamanho real)

Primeiro, vamos examinar a depuração dos objetos de banco de dados gerenciados do SQL Server Project. Como nossa Solução inclui dois projetos - o ManagedDatabaseConstructs projeto SQL Server junto com nosso site - para depurar do projeto SQL Server, precisamos instruir o Visual Studio a iniciar o ManagedDatabaseConstructs projeto de SQL Server quando iniciarmos a depuração. Clique com o botão direito do ManagedDatabaseConstructs mouse no projeto em Gerenciador de Soluções e escolha a opção Definir como Projeto de Inicialização no menu de contexto.

Quando o ManagedDatabaseConstructs projeto é iniciado 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 seguinte instrução, 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, inicie a depuração acessando o menu Depurar e escolhendo Iniciar Depuração ou clicando em F5 ou no ícone de reprodução verde na Barra de Ferramentas. Isso criará os projetos na 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 poderemos percorrer o GetProductsWithPriceLessThan método , examinar os valores dos parâmetros de entrada e assim por diante.

Figura 28: o ponto de interrupção no GetProductsWithPriceLessThan método foi atingido (clique para exibir a imagem 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 servidor Explorer e verifique se a opção Depuração de Aplicativo está marcada. Além disso, precisamos configurar o aplicativo ASP.NET para integrar ao Depurador de SQL e desabilitar o pool de conexões. Essas etapas foram discutidas detalhadamente na Etapa 2 do tutorial Procedimentos Armazenados de Depuração .

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: Compilar e implantar manualmente objetos de banco de dados gerenciados

SQL Server Projetos facilitam a criação, compilação e implantação de objetos de banco de dados gerenciados. Infelizmente, SQL Server Projetos 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. Isso envolve quatro etapas:

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

Para ilustrar essas tarefas, vamos criar um novo procedimento armazenado gerenciado que retorna esses produtos cujo UnitPrice é maior que um valor especificado. Crie um novo arquivo no 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);
    }
};

Esse código é quase idêntico ao do GetProductsWithPriceLessThan método criado na Etapa 5. As únicas diferenças são os nomes de 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 em que 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 em não no sistema, PATHvocê terá que referenciar totalmente seu caminho, %WINDOWS%\Microsoft.NET\Framework\version\, da seguinte maneira:

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 exibir 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 sinalizador especifica o nome do assembly resultante.

Observação

Em vez de compilar o GetProductsWithPriceGreaterThan.cs arquivo de classe da linha de comando, você poderia, como alternativa, usar o Visual C# Express Edition ou criar um projeto separado da Biblioteca de Classes no Visual Studio Standard Edition. S ren Jacob Lauritsen gentilmente forneceu um projeto do 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 SQL Server 2005. Isso pode ser executado por meio do T-SQL, usando o comando CREATE ASSEMBLYou por meio de SQL Server Management Studio. Vamos nos concentrar em usar o Management Studio.

No Management Studio, expanda a pasta Programação 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 Novo Assembly no menu de contexto. Isso exibe a caixa de diálogo Novo Assembly (consulte Figura 30). Clique no botão Procurar, selecione o ManuallyCreatedDBObjects.dll assembly que acabamos de compilar e 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 exibir a imagem em tamanho real)

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

Embora tenhamos adicionado o assembly ao banco de dados Northwind, ainda não associamos um procedimento armazenado ao GetProductsWithPriceGreaterThan método no assembly. 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 Procedimentos Armazenados no Pesquisador de Objetos. Você deve ver uma nova entrada de procedimento armazenado – GetProductsWithPriceGreaterThan que tem um ícone de bloqueio ao lado dela. Para testar esse 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 esses produtos com um UnitPrice valor superior a US$ 24,95.

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

Resumo

O Microsoft SQL Server 2005 fornece integração com o CLR (Common Language Runtime), 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 um gerenciado User-Defined Function.

O tipo de projeto SQL Server do Visual Studio facilita a criação, a compilação e a implantação de objetos de banco de dados gerenciados. Além disso, ele oferece suporte a depuração avançada. No entanto, SQL Server tipos de projeto 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.

Programação feliz!

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 SQL Server 2005 no código gerenciado
• Como criar e executar um procedimento armazenado de 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)
• Referência do Transact-SQL
• Passo a passo: criando um procedimento armazenado no código gerenciado

Sobre o autor

Scott Mitchell, autor de sete livros do ASP/ASP.NET e fundador da 4GuysFromRolla.com, trabalha com tecnologias da Microsoft Web desde 1998. Scott trabalha como consultor independente, treinador e escritor. Seu último livro é Sams Teach Yourself ASP.NET 2.0 em 24 Horas. Ele pode ser contatado em mitchell@4GuysFromRolla.com. ou através de seu blog, que pode ser encontrado em http://ScottOnWriting.NET.

Agradecimentos Especiais

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

AnteriorAvançar