Criar procedimentos armazenados e funções definidas pelo usuário com código gerenciado (C#)
por Scott Mitchell
O Microsoft SQL Server 2005 se integra 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 SQL Server 2005 da Microsoft usam o T-SQL (Transact-Structured Query Language) 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 é a UDF (User-Defined Functions), uma construção 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 , UPDATE, e DELETE se aplicam inerentemente a todos os registros na tabela correspondente e são limitadas apenas por suas WHERE cláusulas. No entanto, existem 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 repetido 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, os procedimentos armazenados e as 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 CLR (Common Language Runtime), que é o tempo de execução usado por todos os assemblies do .NET. Consequentemente, os procedimentos armazenados e as 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 definidas pelo usuário 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 sobre seus equivalentes SQL. A riqueza e familiaridade da linguagem e a capacidade de reutilizar o código e a lógica existentes são as principais vantagens. 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 obter uma discussão mais detalhada 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 distribuição simplificada e executar esses tutoriais, pois todos os arquivos estavam localizados em um diretório e não exigia etapas de configuração adicionais para testar o tutorial.
No entanto, para este tutorial, vamos mover o banco de dados Northwind para fora e registrá-lo explicitamente na instância de banco de App_Data 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 etapas são muito mais simples registrando explicitamente o banco de dados na instância de banco de dados do 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 na instância de banco de dados do SQL Server 2005 Express Edition. Isso pode ser feito no SQL Server Management Studio. Se você tiver uma edição não Express do SQL Server 2005 instalada em seu computador, provavelmente já tem 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.
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 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 de 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: Conectar-se à instância de banco de dados apropriada (clique para exibir 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 uma busca detalhada 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. Provavelmente, o banco de dados Northwind tem 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 da UDF como código C# em uma classe. Depois que o código for escrito, precisaremos compilar essa classe em um assembly (um .dll arquivo), registrar o assembly no banco de dados do SQL Server e, em seguida, criar um procedimento armazenado ou objeto UDF no banco de dados que aponte 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 a partir da linha de comando usando o compilador C# (csc.exe), registrá-lo no banco de dados usando o CREATE ASSEMBLY comando ou no Management Studio e adicionar o procedimento armazenado ou 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, veremos 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, precisará usar a abordagem manual. A etapa 13 fornece instruções detalhadas para executar essas etapas manualmente. 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). Faça uma busca detalhada no tipo de projeto Banco de Dados e, em seguida, nos Modelos listados à direita, escolha criar um novo Projeto do SQL Server. 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 completo)
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, após a criação do 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 apontar para o banco de dados Northwind que registramos na instância de banco de dados 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 as UDFs que criaremos neste projeto, precisamos habilitar o suporte à 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 a 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. Veremos a depuração na Etapa 12.
Agora podemos adicionar novos procedimentos armazenados gerenciados e UDFs a este projeto, mas antes de fazermos isso, 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 projeto do SQL Server.
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 o registramos explicitamente na instância de banco de App_Data dados do SQL Server 2005 Express Edition, precisamos atualizar o NORTHWNDConnectionString valor de forma correspondente. Abra o Web.config arquivo no site e altere o NORTHWNDConnectionString valor 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 deve Web.config 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 do 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 as UDFs do site do 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 do 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 definidas pelo usuário, 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 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 arquivo de partial 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 CommandText define 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 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 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 de procedimento em vez de lógica baseada em conjunto. A lógica processual envolve trabalhar com conjuntos de dados linha por linha ou trabalhar com dados escalares. O GetDiscontinuedProducts método que acabamos de criar, no entanto, não envolve lógica processual. Portanto, o ideal seria implementado 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 completo, 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 no 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 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 no banco de dados Northwind. Para registrar um assembly em um banco de dados 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 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 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 Programação e expanda a pasta Montagens. 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 no 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 cadeado 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 podermos chamar o procedimento armazenado gerenciado: o banco de dados é 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 'clr enabled.
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á definida no momento como 0 (clique para exibir 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 você executar novamente, exec sp_configure verá que a instrução acima atualizou o valor de configuração da configuração clr enabled para 1, mas que o valor de execução ainda está definido como 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 você executar exec sp_configure agora, deverá ver um valor de 1 para os valores de configuração e execução da configuração habilitada clr.
Com a configuração habilitada para clr concluída, estamos prontos para executar o procedimento armazenado gerenciado GetDiscontinuedProducts . Na janela de consulta, digite e execute o comando exec GetDiscontinuedProducts. 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 que foram descontinuados e retorna esses dados para o aplicativo de chamada, que é o SQL Server Management Studio nessa 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 conjunto de dados tipado TableAdapters , 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 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 ManagedDatabaseConstructs ao 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 é 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 para o arquivo. 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 definição do GetProductsWithPriceLessThan 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 GetDiscontinuedProducts código do 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 recebe o valor da price variável.
Depois de adicionar esse código, reimplante o projeto do SQL Server. Em seguida, retorne ao SQL Server Management Studio e atualize a pasta Procedimentos Armazenados. Você deve 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 a imagem em tamanho real)
Etapa 6: Chamando o procedimento armazenado gerenciado da camada de acesso a dados
Neste ponto, adicionamos os procedimentos armazenados gerenciados GetDiscontinuedProducts e GetProductsWithPriceLessThan gerenciados ao ManagedDatabaseConstructs projeto e os registramos no banco 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 Conjunto de Dados Tipado, que foi criado inicialmente no tutorial Criando novos procedimentos armazenados para o Conjunto de Dados Tipado TableAdapters . Na Etapa 7, adicionaremos os métodos correspondentes à BLL.
Abra o Conjunto de Dados Tipado NorthwindWithSprocs no Visual Studio e comece adicionando um novo método ao ProductsTableAdapter .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 de banco de dados do SQL Server 2005 Express Edition, é imperativo que a cadeia de conexão correspondente em 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, verá a mensagem de erro Falha ao adicionar consulta. Não é possível encontrar a conexão NORTHWNDConnectionString para o objeto Web.config em uma caixa de diálogo ao tentar adicionar um novo método ao TableAdapter. Para resolver esse erro, clique em OK e atualize Web.config o valor conforme discutido NORTHWNDConnectionString 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 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 procedimento armazenado gerenciado (clique para exibir a 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 produto descontinuadas, escolha a primeira opção ( Dados tabulares ) e clique em Avançar.
Figura 17: Selecione 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: Nomeie os métodos FillByDiscontinued e GetDiscontinuedProducts (clique para exibir a imagem em tamanho real)
Repita essas etapas para criar métodos nomeados FillByPriceLessThan e GetProductsWithPriceLessThan no ProductsTableAdapter para o GetProductsWithPriceLessThan procedimento armazenado gerenciado.
A Figura 19 mostra uma captura de tela do DataSet Designer depois de adicionar os métodos aos ProductsTableAdapter procedimentos armazenados gerenciados GetProductsWithPriceLessThan GetDiscontinuedProducts e for.
Figura 19: O ProductsTableAdapter inclui os novos métodos adicionados nesta etapa (clique para exibir 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 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 a Lógica de Negócios e as Camadas de Acesso a Dados aumentadas para incluir suporte para chamar os procedimentos armazenados gerenciados GetDiscontinuedProducts GetProductsWithPriceLessThan , agora podemos exibir esses resultados de procedimentos armazenados por meio de uma página ASP.NET.
Abra a ManagedFunctionsAndSprocs.aspx página na pasta e, na AdvancedDAL Caixa de Ferramentas, arraste um GridView para o Designer. Defina a propriedade GridView ID como DiscontinuedProducts e, em sua marca inteligente, associe-a a um novo ObjectDataSource chamado DiscontinuedProductsDataSource. Configure o ObjectDataSource para extrair seus dados do ProductsBLLWithSprocs método da GetDiscontinuedProducts classe.
Figura 20: Configurar o ObjectDataSource para usar a classe (clique para exibir a ProductsBLLWithSprocs imagem em tamanho completo)
Figura 21: Escolha o GetDiscontinuedProducts método na lista suspensa na guia SELECIONAR (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 ATUALIZAR, INSERIR e EXCLUIR 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 ProductName Discontinued, momento em que a marcação declarativa do GridView e do 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 visualizar esta página por meio de um navegador. Quando a página é visitada, o ObjectDataSource chama o ProductsBLLWithSprocs método da GetDiscontinuedProducts classe. Como vimos na Etapa 7, esse método chama o método da GetDiscontinuedProducts classe DALProductsDataTable, 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 pela DAL e, em seguida, retornados para a BLL, que os retorna para a Camada de Apresentação, onde sã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 completo)
Para praticar mais, adicione um TextBox e outro GridView à página. Faça com que esse GridView exiba os produtos menores do que a quantidade inserida no TextBox chamando o ProductsBLLWithSprocs método da GetProductsWithPriceLessThan classe.
Etapa 9: Criando e chamando UDFs T-SQL
Funções definidas pelo usuário, 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#, 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 nos dois tipos de UDFs, começando com uma UDF que retorna um tipo de dados escalar.
A UDF a seguir calcula o valor estimado do estoque para um determinado produto. Ele faz isso recebendo 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 estoque 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 for adicionada ao banco de dados, ela poderá ser encontrada por meio do Management Studio expandindo a pasta Programabilidade, Funções e Funções de valor escalar. Ele pode ser usado em uma SELECT consulta assim:
SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
(UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC
Eu adicionei o udf_ComputeInventoryValue UDF ao banco de dados Northwind; A Figura 23 mostra a saída da consulta acima SELECT quando visualizada 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 estoque de cada produto estão listados (clique para exibir a imagem em tamanho real)
As 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 FROM na 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 adicionei o 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. As UDFs que retornam dados tabulares podem ser encontradas na pasta Funções de valor de tabela do Pesquisador de Objetos.
Figura 24: Os ProductID, ProductName, e CategoryID estão listados para cada bebida (clique para ver a imagem em tamanho real)
Observação
Para obter mais informações sobre como criar e usar UDFs, confira Introdução às funções definidas pelo usuário. Confira também Vantagens e Desvantagens das Funções Definidas pelo Usuário.
Etapa 10: Criando uma UDF gerenciada
As udf_ComputeInventoryValue UDFs e udf_GetProductsByCategoryID criadas nos exemplos acima são objetos de banco de dados T-SQL. O SQL Server 2005 também oferece 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 a udf_ComputeInventoryValue UDF no código gerenciado.
Para adicionar uma UDF gerenciada ManagedDatabaseConstructs ao 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 definido pelo usuário na caixa de diálogo Adicionar novo item e nomeie o novo arquivo udf_ComputeInventoryValue_Managed.csUDF .
Figura 25: Adicionar uma nova UDF gerenciada ao projeto (clique para exibir a ManagedDatabaseConstructs imagem em tamanho real)
O modelo de Função Definida pelo Usuário cria uma partial classe nomeada 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, o udf_ComputeInventoryValue método 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 estoque é idêntica à da UDF 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 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 que recebe um valor de 0. A Products tabela permite valores de banco de dados NULL nas UnitsInPrice colunas e UnitsInStock . Portanto, precisamos primeiro verificar se esses valores contêm NULL s, o que fazemos por meio da SqlMoney propriedade do IsNull objeto. Se ambos UnitPrice e UnitsInStock contiverem não-valoresNULL , então calculamos que o inventoryValue seja o produto dos dois. Então, se Discontinued for verdade, reduzimos o valor pela metade.
Observação
O SqlMoney objeto permite que apenas duas SqlMoney instâncias sejam multiplicadas. Ele não permite que uma SqlMoney instância seja multiplicada por um número de ponto flutuante literal. 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 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 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- a UDF T-SQL criada na Etapa 9 edbo.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 a UDF gerenciada udf ComputeInventoryValue_Managed em vez da UDF 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 Depuração de Procedimentos Armazenados , 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 Aplicativos 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 criamos o projeto pela primeira vez, o Visual Studio nos perguntou se queríamos habilitar a depuração SQL/CLR (consulte a ManagedDatabaseConstructs 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 Gerenciador de Servidores.
Figura 26: Garantir que o banco de dados permita a depuração SQL/CLR
Imagine que quiséssemos depurar o procedimento armazenado gerenciado GetProductsWithPriceLessThan . 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 (clique para exibir a GetProductsWithPriceLessThan imagem em tamanho real)
Vamos primeiro 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 do SQL Server junto com nosso site - para depurar a partir do projeto do SQL Server, precisamos instruir o Visual Studio a iniciar o projeto do SQL Server quando iniciarmos a ManagedDatabaseConstructs depuração. Clique com o botão direito do mouse no ManagedDatabaseConstructs 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 procedimento armazenado gerenciado GetProductsWithPriceLessThan , 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, inicie a depuração acessando o menu Depurar e escolhendo Iniciar depuração ou pressionando F5 ou o ícone verde de reprodução 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 executará o Test.sql script. Neste ponto, o ponto de interrupção será atingido e podemos 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 método foi atingido (clique para exibir a GetProductsWithPriceLessThan 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 Gerenciador de Servidores e verifique se a opção Depuração de Aplicativos está marcada. Além disso, precisamos configurar o aplicativo ASP.NET para integrar com o SQL Debugger 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á transferido para o 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, a compilação e a 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 a 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:
- Crie um arquivo que contenha o código-fonte do objeto de banco de dados gerenciado,
- Compile o objeto em um assembly,
- Registre o assembly no banco de dados do SQL Server 2005 e
- 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 maiores UnitPrice que um valor especificado. Crie um novo arquivo em seu computador com o nome 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 dos métodos, 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 s PATH, você terá que referenciar totalmente 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 exibir 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 sinalizador especifica o nome do assembly resultante.
Observação
Em vez de compilar o arquivo de classe da linha de comando, você também pode usar o GetProductsWithPriceGreaterThan.cs Visual 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 do Visual C# Express Edition com código para o GetProductsWithPriceGreaterThan procedimento armazenado e os dois procedimentos armazenados gerenciados e a UDF criados nas Etapas 3, 5 e 10. O projeto 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 Nova montagem (consulte a 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 cadeado 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 os produtos com mais de UnitPrice 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 uma função definida pelo usuário gerenciada.
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, oferece suporte avançado à 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.
Boa 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 definidas pelo usuário
- 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 definida pelo usuário do CLR SQL Server
- Como editar o
Test.sqlscript 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 ASP/ASP.NET e fundador da 4GuysFromRolla.com, trabalha com tecnologias da Web da Microsoft desde 1998. Scott trabalha como consultor, instrutor e escritor independente. 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 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 revisar meus próximos artigos do MSDN? Em caso afirmativo, envie-me uma mensagem para mitchell@4GuysFromRolla.com.