Criar novos procedimentos armazenados para os TableAdapters do conjunto de dados tipado (VB)
por Scott Mitchell
Em tutoriais anteriores, criamos instruções SQL em nosso código e passamos as instruções para o banco de dados a ser executado. Uma abordagem alternativa é usar procedimentos armazenados, em que as instruções SQL são predefinidas no banco de dados. Neste tutorial, aprendemos a fazer com que o Assistente tableAdapter gere novos procedimentos armazenados para nós.
Introdução
A DAL (Camada de Acesso a Dados) para esses tutoriais usa Conjuntos de Dados Tipado. Conforme discutido no tutorial Criando uma camada de acesso a dados , os Conjuntos de Dados Tipados consistem em DataTables e TableAdapters fortemente tipados. Os DataTables representam as entidades lógicas no sistema enquanto a interface TableAdapters com o banco de dados subjacente para executar o trabalho de acesso a dados. Isso inclui preencher os DataTables com dados, executar consultas que retornam dados escalares e inserir, atualizar e excluir registros do banco de dados.
Os comandos SQL executados pelo TableAdapters podem ser instruções SQL ad hoc, como SELECT columnList FROM TableName, ou procedimentos armazenados. Os TableAdapters em nossa arquitetura usam instruções SQL ad hoc. Muitos desenvolvedores e administradores de banco de dados, no entanto, preferem procedimentos armazenados em vez de instruções SQL ad hoc por motivos de segurança, manutenção e atualização. Outros preferem ardentemente instruções SQL ad hoc por sua flexibilidade. Em meu próprio trabalho, sou a favor de procedimentos armazenados em vez de instruções SQL ad hoc, mas optei por usar instruções SQL ad hoc para simplificar os tutoriais anteriores.
Ao definir um TableAdapter ou adicionar novos métodos, o assistente do TableAdapter torna tão fácil criar novos procedimentos armazenados ou usar procedimentos armazenados existentes quanto usar instruções SQL ad hoc. Neste tutorial, examinaremos como fazer com que o assistente tableAdapter gere automaticamente procedimentos armazenados. No próximo tutorial, veremos como configurar os métodos do TableAdapter para usar procedimentos armazenados existentes ou criados manualmente.
Observação
Veja a entrada no blog de Rob Howard , Don t Use Stored Procedures Yet? e a entrada de blog de Frans BoumaStored Procedures are Bad, M Kay? para um animado debate sobre os prós e contras de procedimentos armazenados e SQL ad hoc.
Conceitos básicos de procedimento armazenado
As funções são um constructo comum a todas as linguagens de programação. Uma função é uma coleção de instruções que são executadas quando a função é chamada. As funções podem aceitar parâmetros de entrada e, opcionalmente, retornar um valor. Os procedimentos armazenados são constructos de banco de dados que compartilham muitas semelhanças com funções em linguagens de programação. Um procedimento armazenado é composto por um conjunto de instruções T-SQL executadas quando o procedimento armazenado é chamado. Um procedimento armazenado pode aceitar zero a muitos parâmetros de entrada e pode retornar valores escalares, parâmetros de saída ou, mais comumente, conjuntos de resultados de SELECT consultas.
Observação
Os procedimentos armazenados geralmente são chamados de sprocs ou SPs .
Os procedimentos armazenados são criados usando a instrução CREATE PROCEDURE T-SQL. Por exemplo, o script T-SQL a seguir cria um procedimento armazenado chamado GetProductsByCategoryID que usa um único parâmetro chamado @CategoryID e retorna os ProductIDcampos , ProductName, UnitPricee Discontinued dessas colunas na Products tabela que têm um valor correspondente CategoryID :
CREATE PROCEDURE GetProductsByCategoryID
(
@CategoryID int
)
AS
SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID
Depois que esse procedimento armazenado tiver sido criado, ele poderá ser chamado usando a seguinte sintaxe:
EXEC GetProductsByCategory categoryID
Observação
No próximo tutorial, examinaremos a criação de procedimentos armazenados por meio do IDE do Visual Studio. Para este tutorial, no entanto, vamos permitir que o assistente TableAdapter gere automaticamente os procedimentos armazenados para nós.
Além de simplesmente retornar dados, os procedimentos armazenados geralmente são usados para executar vários comandos de banco de dados dentro do escopo de uma única transação. Um procedimento armazenado chamado DeleteCategory, por exemplo, pode usar um @CategoryID parâmetro e executar duas DELETE instruções: primeiro, uma para excluir os produtos relacionados e outra excluindo a categoria especificada. Várias instruções em um procedimento armazenado não são encapsuladas automaticamente em uma transação. Comandos T-SQL adicionais precisam ser emitidos para garantir que os vários comandos do procedimento armazenado sejam tratados como uma operação atômica. Veremos como encapsular os comandos de um procedimento armazenado dentro do escopo de uma transação no tutorial subsequente.
Ao usar procedimentos armazenados em uma arquitetura, os métodos da Camada de Acesso a Dados invocam um procedimento armazenado específico em vez de emitir uma instrução SQL ad hoc. Isso centraliza o local das instruções SQL executadas (no banco de dados) em vez de defini-la dentro da arquitetura do aplicativo. Essa centralização, sem dúvida, facilita a localização, análise e ajuste das consultas e fornece uma visão muito mais clara de onde e como o banco de dados está sendo usado.
Para obter mais informações sobre os conceitos básicos do procedimento armazenado, consulte os recursos na seção Leitura Adicional no final deste tutorial.
Etapa 1: Criando as páginas da Web cenários de camada de acesso a dados avançados
Antes de iniciarmos nossa discussão sobre como criar um DAL usando procedimentos armazenados, vamos primeiro levar um momento para criar as páginas de ASP.NET em nosso projeto de site que precisaremos para este e os próximos vários tutoriais. Comece adicionando uma nova pasta chamada AdvancedDAL. Em seguida, adicione as seguintes ASP.NET páginas a essa pasta, certificando-se de associar cada página à Site.master página master:
Default.aspxNewSprocs.aspxExistingSprocs.aspxJOINs.aspxAddingColumns.aspxComputedColumns.aspxEncryptingConfigSections.aspxManagedFunctionsAndSprocs.aspx
Figura 1: Adicionar as páginas de ASP.NET para os tutoriais de cenários de camada de acesso a dados avançados
Assim como nas outras pastas, Default.aspx na AdvancedDAL pasta listará os tutoriais em sua seção. Lembre-se de que o SectionLevelTutorialListing.ascx Controle de Usuário fornece essa funcionalidade. Portanto, adicione esse Controle de Usuário ao Default.aspx arrastando-o do Gerenciador de Soluções para o modo design da página.
Figura 2: Adicionar o controle de SectionLevelTutorialListing.ascx usuário a Default.aspx (clique para exibir a imagem em tamanho real)
Por fim, adicione essas páginas como entradas ao Web.sitemap arquivo. Especificamente, adicione a seguinte marcação após o trabalho com dados <siteMapNode>em lote:
<siteMapNode url="~/AdvancedDAL/Default.aspx"
title="Advanced DAL Scenarios"
description="Explore a number of advanced Data Access Layer scenarios.">
<siteMapNode url="~/AdvancedDAL/NewSprocs.aspx"
title="Creating New Stored Procedures for TableAdapters"
description="Learn how to have the TableAdapter wizard automatically
create and use stored procedures." />
<siteMapNode url="~/AdvancedDAL/ExistingSprocs.aspx"
title="Using Existing Stored Procedures for TableAdapters"
description="See how to plug existing stored procedures into a
TableAdapter." />
<siteMapNode url="~/AdvancedDAL/JOINs.aspx"
title="Returning Data Using JOINs"
description="Learn how to augment your DataTables to work with data
returned from multiple tables via a JOIN query." />
<siteMapNode url="~/AdvancedDAL/AddingColumns.aspx"
title="Adding DataColumns to a DataTable"
description="Master adding new columns to an existing DataTable." />
<siteMapNode url="~/AdvancedDAL/ComputedColumns.aspx"
title="Working with Computed Columns"
description="Explore how to work with computed columns when using
Typed DataSets." />
<siteMapNode url="~/AdvancedDAL/EncryptingConfigSections.aspx"
title="Protected Connection Strings in Web.config"
description="Protect your connection string information in
Web.config using encryption." />
<siteMapNode url="~/AdvancedDAL/ManagedFunctionsAndSprocs.aspx"
title="Creating Managed SQL Functions and Stored Procedures"
description="See how to create SQL functions and stored procedures
using managed code." />
</siteMapNode>
Depois de atualizar Web.sitemap, reserve um momento para exibir o site de tutoriais por meio de um navegador. O menu à esquerda agora inclui itens para os tutoriais avançados de cenários de DAL.
Figura 3: O mapa do site agora inclui entradas para os tutoriais de cenários avançados de DAL
Etapa 2: Configurar um TableAdapter para criar novos procedimentos armazenados
Para demonstrar a criação de uma Camada de Acesso a Dados que usa procedimentos armazenados em vez de instruções SQL ad hoc, vamos criar um novo Conjunto de Dados Tipado na ~/App_Code/DAL pasta chamada NorthwindWithSprocs.xsd. Como percorremos esse processo em detalhes nos tutoriais anteriores, continuaremos rapidamente nas etapas aqui. Se você ficar preso ou precisar de mais instruções passo a passo para criar e configurar um Conjunto de Dados Tipado, consulte o tutorial Criando uma camada de acesso a dados .
Adicione um novo DataSet ao projeto clicando com o botão direito do DAL mouse na pasta, escolhendo Adicionar Novo Item e selecionando o modelo DataSet, conforme mostrado na Figura 4.
Figura 4: Adicionar um novo conjunto de dados tipado ao projeto nomeado NorthwindWithSprocs.xsd (clique para exibir a imagem em tamanho real)
Isso criará o novo Conjunto de Dados Tipado, abrirá sua Designer, criará um novo TableAdapter e iniciará o Assistente de Configuração do TableAdapter. A primeira etapa do Assistente de Configuração do TableAdapter solicita que selecionemos o banco de dados com o qual trabalhar. O cadeia de conexão para o banco de dados Northwind deve estar listado na lista suspensa. Selecione esta opção e clique em Avançar.
Nesta próxima tela, podemos escolher como o TableAdapter deve acessar o banco de dados. Nos tutoriais anteriores, selecionamos a primeira opção, Usar instruções SQL. Para este tutorial, selecione a segunda opção, Criar novos procedimentos armazenados, e clique em Avançar.
Figura 5: Instruir o TableAdapter a criar novos procedimentos armazenados (clique para exibir a imagem em tamanho real)
Assim como acontece com o uso de instruções SQL ad hoc, na etapa a seguir, é solicitado que forneçamos a SELECT instrução para a consulta main do TableAdapter. Mas, em vez de usar a instrução SELECT inserida aqui para executar uma consulta ad hoc diretamente, o assistente tableAdapter criará um procedimento armazenado que contém essa SELECT consulta.
Use a seguinte SELECT consulta para este TableAdapter:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
Figura 6: Insira a SELECT consulta (clique para exibir a imagem em tamanho real)
Observação
A consulta acima difere ligeiramente da consulta main do ProductsTableAdapter no Northwind Conjunto de Dados Tipado. Lembre-se de que o ProductsTableAdapterNorthwind no Conjunto de Dados Tipado inclui duas subconsultas correlacionadas para trazer de volta o nome da categoria e o nome da empresa para cada categoria e fornecedor de produtos. No próximo tutorial Atualizando o TableAdapter para Usar JOINs , veremos como adicionar esses dados relacionados a este TableAdapter.
Reserve um momento para clicar no botão Opções Avançadas. A partir daqui, podemos especificar se o assistente também deve gerar instruções de inserção, atualização e exclusão para o TableAdapter, se deve usar simultaneidade otimista e se a tabela de dados deve ser atualizada após inserções e atualizações. A opção Gerar instruções Insert, Update e Delete é verificada por padrão. Deixe-o marcado. Para este tutorial, deixe as opções Usar simultaneidade otimista desmarcadas.
Ao ter os procedimentos armazenados criados automaticamente pelo assistente TableAdapter, parece que a opção Atualizar a tabela de dados é ignorada. Independentemente de essa caixa de seleção ser marcada, os procedimentos armazenados de inserção e atualização resultantes recuperam o registro just-inserted ou just-updated, como veremos na Etapa 3.
Figura 7: Deixe a opção Gerar instruções Inserir, Atualizar e Excluir marcada
Observação
Se a opção Usar simultaneidade otimista for verificada, o assistente adicionará condições adicionais à cláusula que impede que os WHERE dados sejam atualizados se houver alterações em outros campos. Consulte o tutorial Implementando simultaneidade otimista para obter mais informações sobre como usar o recurso de controle de simultaneidade otimista interno do TableAdapter.
Depois de inserir a SELECT consulta e confirmar se a opção Gerar instruções Inserir, Atualizar e Excluir está marcada, clique em Avançar. Esta próxima tela, mostrada na Figura 8, solicita os nomes dos procedimentos armazenados que o assistente criará para selecionar, inserir, atualizar e excluir dados. Altere esses nomes de procedimentos armazenados para Products_Select, Products_Insert, Products_Updatee Products_Delete.
Figura 8: renomear os procedimentos armazenados (clique para exibir a imagem em tamanho real)
Para ver o T-SQL que o assistente TableAdapter usará para criar os quatro procedimentos armazenados, clique no botão Visualizar Script SQL. Na caixa de diálogo Visualizar Script SQL, você pode salvar o script em um arquivo ou copiá-lo para a área de transferência.
Figura 9: visualizar o script SQL usado para gerar os procedimentos armazenados
Depois de nomear os procedimentos armazenados, clique em Avançar para nomear os métodos correspondentes do TableAdapter. Assim como ao usar instruções SQL ad hoc, podemos criar métodos que preencham um DataTable existente ou retornem um novo. Também podemos especificar se o TableAdapter deve incluir o padrão DB-Direct para inserir, atualizar e excluir registros. Deixe as três caixas de seleção marcadas, mas renomeie o método Return a DataTable para GetProducts (conforme mostrado na Figura 10).
Figura 10: nomeie os métodos Fill e GetProducts (clique para exibir a imagem em tamanho real)
Clique em Avançar para ver um resumo das etapas que o assistente executará. Conclua o assistente clicando no botão Concluir. Depois que o assistente for concluído, você será retornado à Designer do DataSet, que agora deve incluir o ProductsDataTable.
Figura 11: a Designer do DataSet mostra o recém-adicionado ProductsDataTable (clique para exibir a imagem em tamanho real)
Etapa 3: Examinando os procedimentos armazenados recém-criados
O assistente TableAdapter usado na Etapa 2 criou automaticamente os procedimentos armazenados para selecionar, inserir, atualizar e excluir dados. Esses procedimentos armazenados podem ser exibidos ou modificados por meio do Visual Studio acessando a Explorer do Servidor e analisando a pasta Procedimentos Armazenados do banco de dados. Como mostra a Figura 12, o banco de dados Northwind contém quatro novos procedimentos armazenados: Products_Delete, Products_Insert, Products_Selecte Products_Update.
Figura 12: Os quatro procedimentos armazenados criados na etapa 2 podem ser encontrados na pasta Procedimentos Armazenados do Banco de Dados
Observação
Se você não vir o servidor Explorer, vá para o menu Exibir e escolha a opção Servidor Explorer. Se você não vir os procedimentos armazenados relacionados ao produto adicionados na Etapa 2, tente clicar com o botão direito do mouse na pasta Procedimentos Armazenados e escolher Atualizar.
Para exibir ou modificar um procedimento armazenado, clique duas vezes em seu nome no servidor Explorer ou, como alternativa, clique com o botão direito do mouse no procedimento armazenado e escolha Abrir. A Figura 13 mostra o Products_Delete procedimento armazenado, quando aberto.
Figura 13: Procedimentos armazenados podem ser abertos e modificados de dentro do Visual Studio (clique para exibir imagem em tamanho real)
O conteúdo dos procedimentos armazenados e Products_Select são Products_Delete bastante simples. Os Products_Insert procedimentos armazenados e Products_Update , por outro lado, garantem uma inspeção mais detalhada, pois ambos executam uma SELECT instrução após suas INSERT instruções e UPDATE . Por exemplo, o SEGUINTE SQL compõe o Products_Insert procedimento armazenado:
ALTER PROCEDURE dbo.Products_Insert
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit
)
AS
SET NOCOUNT OFF;
INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit],
[UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])
VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice,
@UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = SCOPE_IDENTITY())
O procedimento armazenado aceita como parâmetros de entrada as Products colunas que foram retornadas pela SELECT consulta especificada no assistente TableAdapter s e esses valores são usados em uma instrução INSERT . Após a INSERT instrução , uma SELECT consulta é usada para retornar os valores de Products coluna (incluindo o ProductID) do registro recém-adicionado. Essa funcionalidade de atualização é útil ao adicionar um novo registro usando o padrão de Atualização do Lote, pois atualiza automaticamente as propriedades de instâncias recém-adicionadas ProductRowProductID com os valores incrementados automaticamente atribuídos pelo banco de dados.
O código a seguir ilustra esse recurso. Ele contém um ProductsTableAdapter e ProductsDataTable criado para o NorthwindWithSprocs Conjunto de Dados Tipado. Um novo produto é adicionado ao banco de dados criando uma ProductsRow instância, fornecendo seus valores e chamando o método TableAdapter s Update , passando o ProductsDataTable. Internamente, o método TableAdapter s Update enumera as ProductsRow instâncias no DataTable passado (neste exemplo há apenas um - aquele que acabamos de adicionar) e executa o comando apropriado de inserção, atualização ou exclusão. Nesse caso, o Products_Insert procedimento armazenado é executado, o que adiciona um novo registro à Products tabela e retorna os detalhes do registro recém-adicionado. Em ProductsRow seguida, o valor da ProductID instância é atualizado. Após a conclusão do Update método, podemos acessar o valor do ProductID registro recém-adicionado por meio da ProductsRow propriedade s ProductID .
' Create the ProductsTableAdapter and ProductsDataTable
Dim productsAPI As New NorthwindWithSprocsTableAdapters.ProductsTableAdapter
Dim products As New NorthwindWithSprocs.ProductsDataTable
' Create a new ProductsRow instance and set its properties
Dim product As NorthwindWithSprocs.ProductsRow = products.NewProductsRow()
product.ProductName = "New Product"
product.CategoryID = 1 ' Beverages
product.Discontinued = False
' Add the ProductsRow instance to the DataTable
products.AddProductsRow(product)
' Update the DataTable using the Batch Update pattern
productsAPI.Update(products)
' At this point, we can determine the value of the newly-added record's ProductID
Dim newlyAddedProductIDValue as Integer = product.ProductID
O Products_Update procedimento armazenado também inclui uma SELECT instrução após sua UPDATE instrução.
ALTER PROCEDURE dbo.Products_Update
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit,
@Original_ProductID int,
@ProductID int
)
AS
SET NOCOUNT OFF;
UPDATE [Products]
SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,
[UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,
[UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,
[Discontinued] = @Discontinued
WHERE (([ProductID] = @Original_ProductID));
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = @ProductID)
Observe que esse procedimento armazenado inclui dois parâmetros de entrada para ProductID: @Original_ProductID e @ProductID. Essa funcionalidade permite cenários em que a chave primária pode ser alterada. Por exemplo, em um banco de dados de funcionário, cada registro de funcionário pode usar o número do seguro social do funcionário como chave primária. Para alterar o número do seguro social de um funcionário existente, o novo número do seguro social e o original devem ser fornecidos. Para a Products tabela, essa funcionalidade não é necessária porque a ProductID coluna é uma IDENTITY coluna e nunca deve ser alterada. Na verdade, a UPDATE instrução no Products_Update procedimento armazenado não inclui a ProductID coluna em sua lista de colunas. Portanto, embora @Original_ProductID seja usado na UPDATE cláusula de instrução s WHERE , ele é supérfluo para a Products tabela e pode ser substituído pelo @ProductID parâmetro . Ao modificar os parâmetros de um procedimento armazenado, é importante que os métodos TableAdapter que usam esse procedimento armazenado também sejam atualizados.
Etapa 4: Modificar os parâmetros de um procedimento armazenado e atualizar o TableAdapter
Como o @Original_ProductID parâmetro é supérfluo, vamos removê-lo completamente do Products_Update procedimento armazenado. Abra o Products_Update procedimento armazenado, exclua o @Original_ProductID parâmetro e, na WHERE cláusula da UPDATE instrução , altere o nome do parâmetro usado de @Original_ProductID para @ProductID. Depois de fazer essas alterações, o T-SQL dentro do procedimento armazenado deve ser semelhante ao seguinte:
ALTER PROCEDURE dbo.Products_Update
(
@ProductName nvarchar(40),
@SupplierID int,
@CategoryID int,
@QuantityPerUnit nvarchar(20),
@UnitPrice money,
@UnitsInStock smallint,
@UnitsOnOrder smallint,
@ReorderLevel smallint,
@Discontinued bit,
@ProductID int
)
AS
SET NOCOUNT OFF;
UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,
[UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,
[UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,
[Discontinued] = @Discontinued
WHERE (([ProductID] = @ProductID));
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = @ProductID)
Para salvar essas alterações no banco de dados, clique no ícone Salvar na barra de ferramentas ou pressione Ctrl+S. Neste ponto, o Products_Update procedimento armazenado não espera um @Original_ProductID parâmetro de entrada, mas o TableAdapter está configurado para passar esse parâmetro. Você pode ver os parâmetros que o TableAdapter enviará para o Products_Update procedimento armazenado selecionando o TableAdapter no Designer DataSet, acessando o janela Propriedades e clicando nas reticências na UpdateCommand coleção sParameters. Isso abre a caixa de diálogo Editor coleção Parameters mostrada na Figura 14.
Figura 14: a coleção Parameters Editor Listas os parâmetros usados passados para o Products_Update procedimento armazenado
Você pode remover esse parâmetro daqui simplesmente selecionando o @Original_ProductID parâmetro na lista de membros e clicando no botão Remover.
Como alternativa, você pode atualizar os parâmetros usados para todos os métodos clicando com o botão direito do mouse no TableAdapter no Designer e escolhendo Configurar. Isso abrirá o assistente de Configuração tableAdapter, listando os procedimentos armazenados usados para selecionar, inserir, atualizar e excluir, juntamente com os parâmetros que os procedimentos armazenados esperam receber. Se você clicar na lista suspensa Atualizar, poderá ver os Products_Update procedimentos armazenados esperados parâmetros de entrada, que agora não incluem @Original_ProductID mais (consulte a Figura 15). Basta clicar em Concluir para atualizar automaticamente a coleção de parâmetros usada pelo TableAdapter.
Figura 15: Como alternativa, você pode usar o Assistente de Configuração do TableAdapter para atualizar suas coleções de parâmetros de métodos (clique para exibir a imagem em tamanho real)
Etapa 5: Adicionar métodos TableAdapter adicionais
Como a Etapa 2 ilustrada, ao criar um novo TableAdapter, é fácil ter os procedimentos armazenados correspondentes gerados automaticamente. O mesmo é verdadeiro ao adicionar métodos adicionais a um TableAdapter. Para ilustrar isso, vamos adicionar um GetProductByProductID(productID) método ao ProductsTableAdapter criado na Etapa 2. Esse método tomará como entrada um ProductID valor e retornará detalhes sobre o produto especificado.
Comece clicando com o botão direito do mouse no TableAdapter e escolhendo Adicionar Consulta no menu de contexto.
Figura 16: Adicionar uma nova consulta ao TableAdapter
Isso iniciará o assistente de Configuração de Consulta TableAdapter, que primeiro solicitará como o TableAdapter deve acessar o banco de dados. Para criar um novo procedimento armazenado, escolha a opção Criar um novo procedimento armazenado e clique em Avançar.
Figura 17: escolha a opção Criar um novo procedimento armazenado (clique para exibir a imagem em tamanho real)
A próxima tela nos pede para identificar o tipo de consulta a ser executado, se ele retornará um conjunto de linhas ou um único valor escalar ou executará uma instrução UPDATE, INSERTou DELETE . Como o GetProductByProductID(productID) método retornará uma linha, deixe a opção SELECT que retorna linha selecionada e clique em Avançar.
Figura 18: escolha a opção SELECT que retorna linha (Clique para exibir a imagem em tamanho real)
A próxima tela exibe a consulta main tableAdapter, que apenas lista o nome do procedimento armazenado (dbo.Products_Select). Substitua o nome do procedimento armazenado pela seguinte SELECT instrução, que retorna todos os campos do produto para um produto especificado:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
Figura 19: substituir o nome do procedimento armazenado por uma SELECT consulta (clique para exibir a imagem em tamanho real)
A tela subsequente solicita que você nomeie o procedimento armazenado que será criado. Insira o nome Products_SelectByProductID e clique em Avançar.
Figura 20: nomeie o novo procedimento Products_SelectByProductID armazenado (clique para exibir a imagem em tamanho real)
A etapa final do assistente nos permite alterar os nomes de método gerados, bem como indicar se deseja usar o padrão Fill a DataTable, Retornar um padrão DataTable ou ambos. Para esse método, deixe as duas opções marcadas, mas renomeie os métodos para FillByProductID e GetProductByProductID. Clique em Avançar para exibir um resumo das etapas que o assistente executará e clique em Concluir para concluir o assistente.
Figura 21: renomeie os métodos TableAdapter para FillByProductID e GetProductByProductID (Clique para exibir a imagem em tamanho real)
Depois de concluir o assistente, o TableAdapter tem um novo método disponível que, GetProductByProductID(productID) quando invocado, executará o Products_SelectByProductID procedimento armazenado que acabou de ser criado. Reserve um momento para exibir esse novo procedimento armazenado do servidor Explorer analisando a pasta Procedimentos Armazenados e abrindo Products_SelectByProductID (se você não o vir, clique com o botão direito do mouse na pasta Procedimentos Armazenados e escolha Atualizar).
Observe que o SelectByProductID procedimento armazenado usa @ProductID como um parâmetro de entrada e executa a SELECT instrução que inserimos no assistente.
ALTER PROCEDURE dbo.Products_SelectByProductID
(
@ProductID int
)
AS
SET NOCOUNT ON;
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
Etapa 6: Criando uma classe de camada de lógica de negócios
Ao longo da série de tutoriais, nos esforçamos para manter uma arquitetura em camadas na qual a Camada de Apresentação fez todas as suas chamadas para a BLL (Camada lógica de negócios). Para aderir a essa decisão de design, primeiro precisamos criar uma classe BLL para o novo Conjunto de Dados Tipado antes de podermos acessar os dados do produto da Camada de Apresentação.
Crie um novo arquivo de classe chamado ProductsBLLWithSprocs.vb na ~/App_Code/BLL pasta e adicione a ele o seguinte código:
Imports NorthwindWithSprocsTableAdapters
<System.ComponentModel.DataObject()> _
Public Class ProductsBLLWithSprocs
Private _productsAdapter As ProductsTableAdapter = Nothing
Protected ReadOnly Property Adapter() As ProductsTableAdapter
Get
If _productsAdapter Is Nothing Then
_productsAdapter = New ProductsTableAdapter()
End If
Return _productsAdapter
End Get
End Property
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, True)> _
Public Function GetProducts() As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductByProductID(ByVal productID As Integer) _
As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProductByProductID(productID)
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function AddProduct _
(ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
ByVal unitPrice As Nullable(Of Decimal), _
ByVal unitsInStock As Nullable(Of Short), _
ByVal unitsOnOrder As Nullable(Of Short), _
ByVal reorderLevel As Nullable(Of Short), _
ByVal discontinued As Boolean) _
As Boolean
' Create a new ProductRow instance
Dim products As New NorthwindWithSprocs.ProductsDataTable()
Dim product As NorthwindWithSprocs.ProductsRow = products.NewProductsRow()
product.ProductName = productName
If Not supplierID.HasValue Then
product.SetSupplierIDNull()
Else
product.SupplierID = supplierID.Value
End If
If Not categoryID.HasValue Then
product.SetCategoryIDNull()
Else
product.CategoryID = categoryID.Value
End If
If quantityPerUnit Is Nothing Then
product.SetQuantityPerUnitNull()
Else
product.QuantityPerUnit = quantityPerUnit
End If
If Not unitPrice.HasValue Then
product.SetUnitPriceNull()
Else
product.UnitPrice = unitPrice.Value
End If
If Not unitsInStock.HasValue Then
product.SetUnitsInStockNull()
Else
product.UnitsInStock = unitsInStock.Value
End If
If Not unitsOnOrder.HasValue Then
product.SetUnitsOnOrderNull()
Else
product.UnitsOnOrder = unitsOnOrder.Value
End If
If Not reorderLevel.HasValue Then
product.SetReorderLevelNull()
Else
product.ReorderLevel = reorderLevel.Value
End If
product.Discontinued = discontinued
' Add the new product
products.AddProductsRow(product)
Dim rowsAffected As Integer = Adapter.Update(products)
' Return true if precisely one row was inserted, otherwise false
Return rowsAffected = 1
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Update, True)> _
Public Function UpdateProduct
(ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
ByVal unitPrice As Nullable(Of Decimal), _
ByVal unitsInStock As Nullable(Of Short), _
ByVal unitsOnOrder As Nullable(Of Short), _
ByVal reorderLevel As Nullable(Of Short), _
ByVal discontinued As Boolean, ByVal productID As Integer) _
As Boolean
Dim products As NorthwindWithSprocs.ProductsDataTable = _
Adapter.GetProductByProductID(productID)
If products.Count = 0 Then
' no matching record found, return false
Return False
End If
Dim product As NorthwindWithSprocs.ProductsRow = products(0)
product.ProductName = productName
If Not supplierID.HasValue Then
product.SetSupplierIDNull()
Else
product.SupplierID = supplierID.Value
End If
If Not categoryID.HasValue Then
product.SetCategoryIDNull()
Else
product.CategoryID = categoryID.Value
End If
If quantityPerUnit Is Nothing Then
product.SetQuantityPerUnitNull()
Else
product.QuantityPerUnit = quantityPerUnit
End If
If Not unitPrice.HasValue Then
product.SetUnitPriceNull()
Else
product.UnitPrice = unitPrice.Value
End If
If Not unitsInStock.HasValue Then
product.SetUnitsInStockNull()
Else
product.UnitsInStock = unitsInStock.Value
End If
If Not unitsOnOrder.HasValue Then
product.SetUnitsOnOrderNull()
Else
product.UnitsOnOrder = unitsOnOrder.Value
End If
If Not reorderLevel.HasValue Then
product.SetReorderLevelNull()
Else
product.ReorderLevel = reorderLevel.Value
End If
product.Discontinued = discontinued
' Update the product record
Dim rowsAffected As Integer = Adapter.Update(product)
' Return true if precisely one row was updated, otherwise false
Return rowsAffected = 1
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Delete, True)> _
Public Function DeleteProduct(ByVal productID As Integer) As Boolean
Dim rowsAffected As Integer = Adapter.Delete(productID)
' Return true if precisely one row was deleted, otherwise false
Return rowsAffected = 1
End Function
End Class
Essa classe imita a ProductsBLL semântica de classe de tutoriais anteriores, mas usa os ProductsTableAdapter objetos e ProductsDataTable do NorthwindWithSprocs DataSet. Por exemplo, em vez de ter uma Imports NorthwindTableAdapters instrução no início do arquivo de classe como ProductsBLL faz, a ProductsBLLWithSprocs classe usa Imports NorthwindWithSprocsTableAdapters. Da mesma forma, os ProductsDataTable objetos e ProductsRow usados nessa classe são prefixados com o NorthwindWithSprocs namespace . A ProductsBLLWithSprocs classe fornece dois métodos de acesso a dados, GetProducts e GetProductByProductID, e métodos para adicionar, atualizar e excluir uma única instância de produto.
Etapa 7: Trabalhando com oNorthwindWithSprocsConjunto de Dados da Camada de Apresentação
Neste ponto, criamos um DAL que usa procedimentos armazenados para acessar e modificar os dados de banco de dados subjacentes. Também criamos uma BLL rudimentar com métodos para recuperar todos os produtos ou um produto específico, juntamente com métodos para adicionar, atualizar e excluir produtos. Para completar este tutorial, vamos criar uma página ASP.NET que usa a classe BLL s ProductsBLLWithSprocs para exibir, atualizar e excluir registros.
Abra a NewSprocs.aspx página na AdvancedDAL pasta e arraste um GridView da Caixa de Ferramentas para o Designer, nomeando-o Productscomo . Na marca inteligente GridView, escolha associá-la a um novo ObjectDataSource chamado ProductsDataSource. Configure o ObjectDataSource para usar a ProductsBLLWithSprocs classe , conforme mostrado na Figura 22.
Figura 22: Configurar o ObjectDataSource para usar a ProductsBLLWithSprocs classe (clique para exibir a imagem em tamanho real)
A lista suspensa na guia SELECT tem duas opções e GetProductsGetProductByProductID. Como queremos exibir todos os produtos no GridView, escolha o GetProducts método . As listas suspensas nas guias UPDATE, INSERT e DELETE têm apenas um método. Verifique se cada uma dessas listas suspensas tem seu método apropriado selecionado e clique em Concluir.
Depois que o assistente ObjectDataSource for concluído, o Visual Studio adicionará BoundFields e um CheckBoxField ao GridView para os campos de dados do produto. Ative os recursos internos de edição e exclusão do GridView verificando as opções Habilitar Edição e Habilitar Exclusão presentes na marca inteligente.
Figura 23: a página contém um GridView com o suporte de edição e exclusão habilitado (clique para exibir a imagem em tamanho real)
Como discutimos em tutoriais anteriores, na conclusão do assistente objectDataSource, o Visual Studio define a OldValuesParameterFormatString propriedade como original_{0}. Isso precisa ser revertido para seu valor padrão de para que os recursos de modificação de {0} dados funcionem corretamente, considerando os parâmetros esperados pelos métodos em nossa BLL. Portanto, defina a OldValuesParameterFormatString propriedade como {0} ou remova a propriedade completamente da sintaxe declarativa.
Depois de concluir o assistente Configurar Fonte de Dados, ativar a edição e a exclusão do suporte no GridView e retornar a propriedade ObjectDataSource para OldValuesParameterFormatString seu valor padrão, a marcação declarativa da página deve ser semelhante à seguinte:
<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False"
DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID"
InsertVisible="False" ReadOnly="True"
SortExpression="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName"
SortExpression="ProductName" />
<asp:BoundField DataField="SupplierID" HeaderText="SupplierID"
SortExpression="SupplierID" />
<asp:BoundField DataField="CategoryID" HeaderText="CategoryID"
SortExpression="CategoryID" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit"
SortExpression="QuantityPerUnit" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
SortExpression="UnitPrice" />
<asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock"
SortExpression="UnitsInStock" />
<asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder"
SortExpression="UnitsOnOrder" />
<asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel"
SortExpression="ReorderLevel" />
<asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued"
SortExpression="Discontinued" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server"
DeleteMethod="DeleteProduct" InsertMethod="AddProduct"
SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs"
UpdateMethod="UpdateProduct">
<DeleteParameters>
<asp:Parameter Name="productID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="supplierID" Type="Int32" />
<asp:Parameter Name="categoryID" Type="Int32" />
<asp:Parameter Name="quantityPerUnit" Type="String" />
<asp:Parameter Name="unitPrice" Type="Decimal" />
<asp:Parameter Name="unitsInStock" Type="Int16" />
<asp:Parameter Name="unitsOnOrder" Type="Int16" />
<asp:Parameter Name="reorderLevel" Type="Int16" />
<asp:Parameter Name="discontinued" Type="Boolean" />
<asp:Parameter Name="productID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="productName" Type="String" />
<asp:Parameter Name="supplierID" Type="Int32" />
<asp:Parameter Name="categoryID" Type="Int32" />
<asp:Parameter Name="quantityPerUnit" Type="String" />
<asp:Parameter Name="unitPrice" Type="Decimal" />
<asp:Parameter Name="unitsInStock" Type="Int16" />
<asp:Parameter Name="unitsOnOrder" Type="Int16" />
<asp:Parameter Name="reorderLevel" Type="Int16" />
<asp:Parameter Name="discontinued" Type="Boolean" />
</InsertParameters>
</asp:ObjectDataSource>
Neste ponto, poderíamos arrumar o GridView personalizando a interface de edição para incluir a validação, fazendo com que as CategoryID colunas e SupplierID sejam renderizadas como DropDownLists e assim por diante. Também podemos adicionar uma confirmação do lado do cliente ao botão Excluir e eu encorajo você a aproveitar o tempo para implementar esses aprimoramentos. Como esses tópicos foram abordados em tutoriais anteriores, no entanto, não os abordaremos novamente aqui.
Independentemente de você aprimorar o GridView ou não, teste os principais recursos da página em um navegador. Como mostra a Figura 24, a página lista os produtos em um GridView que fornece recursos de edição e exclusão por linha.
Figura 24: Os produtos podem ser exibidos, editados e excluídos do GridView (clique para exibir a imagem em tamanho real)
Resumo
Os TableAdapters em um Conjunto de Dados Tipado podem acessar dados do banco de dados usando instruções SQL ad hoc ou por meio de procedimentos armazenados. Ao trabalhar com procedimentos armazenados, os procedimentos armazenados existentes podem ser usados ou o assistente TableAdapter pode ser instruído a criar novos procedimentos armazenados com base em uma SELECT consulta. Neste tutorial, exploramos como criar automaticamente os procedimentos armazenados para nós.
Embora ter os procedimentos armazenados gerados automaticamente ajude a economizar tempo, há certos casos em que o procedimento armazenado criado pelo assistente não está alinhado com o que teríamos criado por conta própria. Um exemplo é o Products_Update procedimento armazenado, que esperava parâmetros @Original_ProductID de entrada e @ProductID , embora o @Original_ProductID parâmetro fosse supérfluo.
Em muitos cenários, os procedimentos armazenados podem já ter sido criados ou talvez queiramos criá-los manualmente para ter um grau mais fino de controle sobre os comandos do procedimento armazenado. Em ambos os casos, gostaríamos de instruir o TableAdapter a usar procedimentos armazenados existentes para seus métodos. Veremos como fazer isso no próximo tutorial.
Programação feliz!
Leitura Adicional
Para obter mais informações sobre os tópicos discutidos neste tutorial, consulte os seguintes recursos:
- Criando e mantendo procedimentos armazenados
- Procedimentos armazenados: uma visão geral
- Criar um procedimento armazenado
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 Hilton Geisenow. Interessado em revisar meus próximos artigos do MSDN? Nesse caso, solte-me uma linha em mitchell@4GuysFromRolla.com.
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de