Inserir, atualizar e excluir dados com o SqlDataSource (C#)
por Scott Mitchell
Nos tutoriais anteriores, aprendemos como o controle ObjectDataSource permitia inserir, atualizar e excluir dados. O controle SqlDataSource dá suporte às mesmas operações, mas a abordagem é diferente e este tutorial mostra como configurar o SqlDataSource para inserir, atualizar e excluir dados.
Introdução
Conforme discutido em Uma visão geral da inserção, atualização e exclusão, o controle GridView fornece funcionalidades internas de atualização e exclusão, enquanto os controles DetailsView e FormView incluem a inserção de suporte, juntamente com a edição e exclusão da funcionalidade. Esses recursos de modificação de dados podem ser conectados diretamente a um controle de fonte de dados sem que uma linha de código precise ser gravada. Uma visão geral da inserção, atualização e exclusão examinada usando o ObjectDataSource para facilitar a inserção, atualização e exclusão com os controles GridView, DetailsView e FormView. Como alternativa, o SqlDataSource pode ser usado no lugar do ObjectDataSource.
Lembre-se de que para dar suporte à inserção, atualização e exclusão, com o ObjectDataSource, precisamos especificar os métodos de camada de objeto a serem invocados para executar a ação de inserção, atualização ou exclusão. Com o SqlDataSource, precisamos fornecer INSERTinstruções , UPDATEe DELETE SQL (ou procedimentos armazenados) para execução. Como veremos neste tutorial, essas instruções podem ser criadas manualmente ou podem ser geradas automaticamente pelo assistente Configurar Fonte de Dados do SqlDataSource.
Observação
Como já discutimos os recursos de inserção, edição e exclusão dos controles GridView, DetailsView e FormView, este tutorial se concentrará em configurar o controle SqlDataSource para dar suporte a essas operações. Se você precisar melhorar a implementação desses recursos no GridView, DetailsView e FormView, retorne aos tutoriais Editando, Inserindo e Excluindo Dados, começando com Uma Visão Geral de Inserção, Atualização e Exclusão.
Etapa 1: Especificando instruções INSERT, UPDATE e DELETE
Como vimos nos últimos dois tutoriais, para recuperar dados de um controle SqlDataSource, precisamos definir duas propriedades:
ConnectionString, que especifica para qual banco de dados enviar a consulta eSelectCommand, que especifica a instrução SQL ad hoc ou o nome do procedimento armazenado a ser executado para retornar os resultados.
Para SelectCommand valores com parâmetros, os valores de parâmetro são especificados por meio da coleção sqlDataSource SelectParameters e podem incluir valores embutidos em código, valores de origem de parâmetro comuns (campos de querystring, variáveis de sessão, valores de controle da Web e assim por diante) ou podem ser atribuídos programaticamente. Quando o método do Select() controle SqlDataSource é invocado programaticamente ou automaticamente de um controle da Web de dados, uma conexão com o banco de dados é estabelecida, os valores de parâmetro são atribuídos à consulta e o comando é transportado para o banco de dados. Em seguida, os resultados são retornados como um DataSet ou DataReader, dependendo do valor da propriedade do DataSourceMode controle.
Juntamente com a seleção de dados, o controle SqlDataSource pode ser usado para inserir, atualizar e excluir dados fornecendo INSERTinstruções SQL UPDATE, e DELETE da mesma maneira. Basta atribuir as InsertCommandpropriedades , UpdateCommande DeleteCommand as INSERTinstruções , UPDATEe DELETE SQL a serem executadas. Se as instruções tiverem parâmetros (como sempre terão), inclua-os InsertParametersnas coleções , UpdateParameterse DeleteParameters .
Depois que um InsertCommandvalor , UpdateCommandou DeleteCommand tiver sido especificado, a opção Habilitar Inserção, Habilitar Edição ou Habilitar Exclusão na marca inteligente do controle web de dados correspondente ficará disponível. Para ilustrar isso, vamos usar um exemplo da Querying.aspx página que criamos no tutorial Consultando Dados com o SqlDataSource Control e ampliá-los para incluir recursos de exclusão.
Comece abrindo as InsertUpdateDelete.aspx páginas e Querying.aspx da SqlDataSource pasta . No Designer na Querying.aspx página, selecione SqlDataSource e GridView no primeiro exemplo (os ProductsDataSource controles e GridView1 ). Depois de selecionar os dois controles, vá para o menu Editar e escolha Copiar (ou basta clicar em Ctrl+C). Em seguida, vá para a Designer de InsertUpdateDelete.aspx e cole nos controles. Depois de mover os dois controles para InsertUpdateDelete.aspx, teste a página em um navegador. Você deve ver os valores das ProductIDcolunas , ProductNamee UnitPrice para todos os registros na tabela de Products banco de dados.
Figura 1: Todos os produtos são listados, ordenados por ProductID (Clique para exibir imagem em tamanho real)
Adicionando as propriedades DeleteCommand e DeleteParameters do SqlDataSource
Neste ponto, temos um SqlDataSource que simplesmente retorna todos os registros da Products tabela e um GridView que renderiza esses dados. Nossa meta é estender este exemplo para permitir que o usuário exclua produtos por meio do GridView. Para fazer isso, precisamos especificar valores para as propriedades e o controle DeleteCommand SqlDataSource e DeleteParameters , em seguida, configurar o GridView para dar suporte à exclusão.
As DeleteCommand propriedades e DeleteParameters podem ser especificadas de várias maneiras:
- Por meio da sintaxe declarativa
- Do janela Propriedades no Designer
- Na tela Especificar uma instrução SQL personalizada ou procedimento armazenado no assistente Configurar Fonte de Dados
- Por meio do botão Avançado na tela Especificar colunas de uma tabela de exibição no assistente Configurar Fonte de Dados, que gerará automaticamente a
DELETEinstrução SQL e a coleção de parâmetros usadasDeleteCommandnas propriedades eDeleteParameters
Examinaremos como criar automaticamente a DELETE instrução na Etapa 2. Por enquanto, vamos usar o janela Propriedades no Designer, embora o assistente Configurar Fonte de Dados ou a opção de sintaxe declarativa também funcione.
No Designer em , clique no ProductsDataSource SqlDataSource e, em InsertUpdateDelete.aspxseguida, abra o janela Propriedades (no menu Exibir, escolha janela Propriedades ou simplesmente pressione F4). Selecione a propriedade DeleteQuery, que exibirá um conjunto de reticências.
Figura 2: selecione a propriedade DeleteQuery na janela Propriedades
Observação
O SqlDataSource não tem uma propriedade DeleteQuery. Em vez disso, DeleteQuery é uma combinação das DeleteCommand propriedades e DeleteParameters e só é listado no janela Propriedades ao exibir a janela por meio do Designer. Se você estiver examinando o janela Propriedades no modo de exibição Origem, encontrará a DeleteCommand propriedade em vez disso.
Clique nas reticências na propriedade DeleteQuery para abrir a caixa de diálogo Command e Parameter Editor (consulte Figura 3). Nessa caixa de diálogo, você pode especificar a DELETE instrução SQL e especificar os parâmetros. Insira a consulta a seguir na caixa de texto de DELETE comando (manualmente ou usando o Construtor de Consultas, se preferir):
DELETE FROM Products
WHERE ProductID = @ProductID
Em seguida, clique no botão Atualizar Parâmetros para adicionar o @ProductID parâmetro à lista de parâmetros abaixo.
@ProductID adicionado à lista de parâmetros de comando DELETE.". />
Figura 3: selecione a propriedade DeleteQuery na janela Propriedades (Clique para exibir a imagem em tamanho real)
Não forneça um valor para esse parâmetro (deixe sua origem de parâmetro em Nenhum ). Depois de adicionarmos o suporte de exclusão ao GridView, o GridView fornecerá automaticamente esse valor de parâmetro, usando o valor de sua DataKeys coleção para a linha cujo botão Excluir foi clicado.
Observação
O nome do DELETE parâmetro usado na consulta deve ser o mesmo que o nome do DataKeyNames valor em GridView, DetailsView ou FormView. Ou seja, o parâmetro na DELETE instrução é nomeado @ProductID propositalmente (em vez de, digamos, @ID), porque o nome da coluna de chave primária na tabela Products (e, portanto, o valor DataKeyNames no GridView) é ProductID.
Se o nome e DataKeyNames o valor do parâmetro não corresponderem, o GridView não poderá atribuir automaticamente ao parâmetro o valor da DataKeys coleção.
Depois de inserir as informações relacionadas à exclusão na caixa de diálogo Comando e Parâmetro Editor clique em OK e vá para o modo de exibição Origem para examinar a marcação declarativa resultante:
<asp:SqlDataSource ID="ProductsDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
SelectCommand=
"SELECT [ProductID], [ProductName], [UnitPrice] FROM [Products]"
DeleteCommand="DELETE FROM Products WHERE ProductID = @ProductID">
<DeleteParameters>
<asp:Parameter Name="ProductID" />
</DeleteParameters>
</asp:SqlDataSource>
Observe a adição da DeleteCommand propriedade, bem como a <DeleteParameters> seção e o objeto Parameter chamado productID.
Configurando o GridView para exclusão
Com a DeleteCommand propriedade adicionada, a marca inteligente GridView agora contém a opção Habilitar Exclusão. Vá em frente e marcar essa caixa de seleção. Conforme discutido em Uma visão geral da inserção, atualização e exclusão, isso faz com que o GridView adicione um CommandField com sua ShowDeleteButton propriedade definida truecomo . Como mostra a Figura 4, quando a página é visitada por meio de um navegador, um botão Excluir é incluído. Teste esta página excluindo alguns produtos.
Figura 4: cada linha GridView agora inclui um botão Excluir (clique para exibir a imagem em tamanho real)
Ao clicar em um botão Excluir, ocorre um postback, o GridView atribui ao parâmetro o ProductID valor do valor da DataKeys coleção para a linha cujo botão Excluir foi clicado e invoca o método sqlDataSource Delete() . Em seguida, o controle SqlDataSource se conecta ao banco de dados e executa a DELETE instrução . Em seguida, o GridView se vincula ao SqlDataSource, voltando e exibindo o conjunto atual de produtos (que não inclui mais o registro excluído).
Observação
Como o GridView usa sua DataKeys coleção para preencher os parâmetros SqlDataSource, é vital que a propriedade GridView DataKeyNames seja definida como as colunas que constituem a chave primária e que o SqlDataSource retorne SelectCommand essas colunas. Além disso, é importante que o nome do parâmetro no SqlDataSource s DeleteCommand esteja definido como @ProductID. Se a DataKeyNames propriedade não estiver definida ou o parâmetro não for nomeado @ProductsID, clicar no botão Excluir causará um postback, mas não excluirá nenhum registro.
A Figura 5 ilustra essa interação graficamente. Consulte o tutorial Examinando os eventos associados à inserção, atualização e exclusão para obter uma discussão mais detalhada sobre a cadeia de eventos associada à inserção, atualização e exclusão de um controle da Web de dados.
Figura 5: Clicar no botão Excluir no GridView invoca o método SqlDataSource s Delete()
Etapa 2: Gerar automaticamente as instruções INSERT, UPDATE e DELETE
Como a Etapa 1 examinou, INSERTas instruções , UPDATEe DELETE SQL podem ser especificadas por meio do janela Propriedades ou da sintaxe declarativa do controle. No entanto, essa abordagem exige que escrevamos manualmente as instruções SQL manualmente, que podem ser monótonas e propensas a erros. Felizmente, o assistente Configurar Fonte de Dados fornece uma opção para ter as INSERTinstruções , UPDATEe DELETE geradas automaticamente ao usar a tela Especificar colunas de uma tabela de exibição.
Vamos explorar essa opção de geração automática. Adicione um DetailsView ao Designer e InsertUpdateDelete.aspx defina sua ID propriedade como ManageProducts. Em seguida, na marca inteligente DetailsView, escolha criar uma nova fonte de dados e criar um SqlDataSource chamado ManageProductsDataSource.
Figura 6: Criar um Novo SqlDataSource Nomeado ManageProductsDataSource (Clique para exibir a imagem em tamanho real)
No assistente Configurar Fonte de Dados, opte por usar o NORTHWINDConnectionString cadeia de conexão e clique em Avançar. Na tela Configurar a Instrução Select, deixe o botão de opção Especificar colunas de uma tabela ou exibição selecionado e escolha a Products tabela na lista suspensa. Selecione as ProductIDcolunas , ProductName, UnitPricee Discontinued na lista de caixas de seleção.
Figura 7: Usando a Products tabela, retorne as ProductIDcolunas , ProductName, UnitPricee Discontinued (clique para exibir a imagem em tamanho real)
Para gerar INSERTinstruções , UPDATEe DELETE automaticamente com base na tabela e nas colunas selecionadas, clique no botão Avançado e marcar caixa de seleção Gerar INSERTinstruções , UPDATEe DELETE .
Figura 8: Marque a caixa de seleção Gerar INSERTinstruções , UPDATEe DELETE
A caixa de seleção Gerar INSERTinstruções , UPDATEe DELETE só será verificável se a tabela selecionada tiver uma chave primária e a coluna de chave primária (ou colunas) estiverem incluídas na lista de colunas retornadas. A caixa de seleção Usar simultaneidade otimista, que se torna selecionável depois que a caixa de seleção Gerar INSERTinstruções , UPDATEe DELETE tiver sido marcada, aumentará as WHERE cláusulas nas instruções e DELETE resultantes UPDATE para fornecer controle de simultaneidade otimista. Por enquanto, deixe esta caixa de seleção desmarcada; Examinaremos a simultaneidade otimista com o controle SqlDataSource no próximo tutorial.
Depois de verificar a caixa de seleção Gerar INSERTinstruções , UPDATEe DELETE , clique em OK para retornar à tela Configurar Instrução Select e, em seguida, clique em Avançar e, em seguida, Concluir, para concluir o assistente Configurar Fonte de Dados. Ao concluir o assistente, o Visual Studio adicionará BoundFields ao DetailsView para as ProductIDcolunas , ProductNamee e UnitPrice e um CheckBoxField para a Discontinued coluna. Na marca inteligente DetailsView, marcar a opção Habilitar Paginação para que o usuário que visita essa página possa percorrer os produtos. Desmarque também as propriedades e Height DetailsViewWidth.
Observe que a marca inteligente tem as opções Habilitar Inserção, Habilitar Edição e Habilitar Exclusão disponíveis. Isso ocorre porque o SqlDataSource contém valores para , InsertCommandUpdateCommande DeleteCommand, como mostra a sintaxe declarativa a seguir:
<asp:DetailsView ID="ManageProducts" runat="server" AllowPaging="True"
AutoGenerateRows="False" DataKeyNames="ProductID"
DataSourceID="ManageProductsDataSource" EnableViewState="False">
<Fields>
<asp:BoundField DataField="ProductID" HeaderText="ProductID"
InsertVisible="False" ReadOnly="True" SortExpression="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName"
SortExpression="ProductName" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
SortExpression="UnitPrice" />
<asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued"
SortExpression="Discontinued" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="ManageProductsDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
DeleteCommand=
"DELETE FROM [Products] WHERE [ProductID] = @ProductID"
InsertCommand=
"INSERT INTO [Products] ([ProductName], [UnitPrice], [Discontinued])
VALUES (@ProductName, @UnitPrice, @Discontinued)"
SelectCommand=
"SELECT [ProductID], [ProductName], [UnitPrice], [Discontinued]
FROM [Products]"
UpdateCommand=
"UPDATE [Products] SET [ProductName] = @ProductName,
[UnitPrice] = @UnitPrice, [Discontinued] = @Discontinued
WHERE [ProductID] = @ProductID">
<DeleteParameters>
<asp:Parameter Name="ProductID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="UnitPrice" Type="Decimal" />
<asp:Parameter Name="Discontinued" Type="Boolean" />
<asp:Parameter Name="ProductID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="UnitPrice" Type="Decimal" />
<asp:Parameter Name="Discontinued" Type="Boolean" />
</InsertParameters>
</asp:SqlDataSource>
Observe como o controle SqlDataSource teve valores definidos automaticamente para suas InsertCommandpropriedades , UpdateCommande DeleteCommand . O conjunto de colunas referenciadas nas InsertCommand propriedades e UpdateCommand baseia-se naqueles na SELECT instrução . Ou seja, em vez de ter todas as colunas Products no InsertCommand e UpdateCommand, há apenas essas colunas especificadas no SelectCommand (menos ProductID, que é omitido porque é uma IDENTITY coluna, cujo valor não pode ser alterado quando editado e que é atribuído automaticamente ao inserir). Além disso, para cada parâmetro nas InsertCommandpropriedades , UpdateCommande DeleteCommand há parâmetros correspondentes nas InsertParameterscoleções , UpdateParameterse DeleteParameters .
Para ativar os recursos de modificação de dados do DetailsView, marcar as opções Habilitar Inserção, Habilitar Edição e Habilitar Exclusão em sua marca inteligente. Isso adiciona um CommandField com suas ShowInsertButtonpropriedades , ShowEditButtone ShowDeleteButton definidas como true.
Visite a página em um navegador e anote os botões Editar, Excluir e Novo incluídos no DetailsView. Clicar no botão Editar transforma o DetailsView em modo de edição, que exibe cada BoundField cuja ReadOnly propriedade está definida false como (o padrão) como uma Caixa de Texto e o CheckBoxField como uma caixa de seleção.
Figura 9: Interface de Edição Padrão do DetailsView (clique para exibir imagem em tamanho real)
Da mesma forma, você pode excluir o produto selecionado no momento ou adicionar um novo produto ao sistema. Como a InsertCommand instrução funciona apenas com as ProductNamecolunas , UnitPricee Discontinued , as outras colunas têm NULL ou seu valor padrão atribuído pelo banco de dados após a inserção. Assim como acontece com ObjectDataSource, se o InsertCommand estiver faltando colunas de tabela de banco de dados que não permitem NULL s e não têm um valor padrão, ocorrerá um erro SQL ao tentar executar a INSERT instrução .
Observação
As interfaces de inserção e edição do DetailsView não têm qualquer tipo de personalização ou validação. Para adicionar controles de validação ou personalizar as interfaces, você precisa converter BoundFields em TemplateFields. Consulte os tutoriais Adicionando controles de validação às interfaces de edição e inserção e personalizando os tutoriais da Interface de Modificação de Dados para obter mais informações.
Além disso, tenha em mente que, para atualizar e excluir, o DetailsView usa o valor atual do DataKey produto, que só estará presente se a DataKeyNames propriedade estiver configurada. Se a edição ou exclusão parecer não ter efeito, verifique se a DataKeyNames propriedade está definida.
Limitações da geração automática de instruções SQL
Como a opção Gerar INSERTinstruções , UPDATEe DELETE só está disponível ao escolher colunas de uma tabela, para consultas mais complexas, você terá que escrever suas próprias INSERTinstruções , UPDATEe DELETE como fizemos na Etapa 1. Normalmente, as instruções SQL SELECT usam JOIN s para trazer de volta dados de uma ou mais tabelas de pesquisa para fins de exibição (como trazer de volta o Categories campo da tabela ao exibir informações do CategoryName produto). Ao mesmo tempo, convém permitir que o usuário edite, atualize ou insira dados na tabela principal (Productsnesse caso).
Embora as INSERTinstruções , UPDATEe DELETE possam ser inseridas manualmente, considere a seguinte dica de economia de tempo. Inicialmente, configure o SqlDataSource para que ele efetue pull de dados apenas da Products tabela. Use o assistente Configurar Fonte de Dados e especificar colunas de uma tabela ou tela de exibição para que você possa gerar automaticamente as INSERTinstruções , UPDATEe DELETE . Depois de concluir o assistente, escolha configurar o SelectQuery do janela Propriedades (ou, como alternativa, volte para o assistente Configurar Fonte de Dados, mas use a opção Especificar uma instrução SQL personalizada ou procedimento armazenado). Em seguida, atualize a SELECT instrução para incluir a JOIN sintaxe. Essa técnica oferece os benefícios de economia de tempo das instruções SQL geradas automaticamente e permite uma instrução mais personalizada SELECT .
Outra limitação de gerar automaticamente as INSERTinstruções , UPDATEe DELETE é que as colunas nas INSERT instruções e UPDATE são baseadas nas colunas retornadas pela SELECT instrução . No entanto, talvez seja necessário atualizar ou inserir mais ou menos campos. Por exemplo, no exemplo da Etapa 2, talvez queiramos que BoundField UnitPrice seja somente leitura. Nesse caso, ele não deve aparecer no UpdateCommand. Ou talvez queiramos definir o valor de um campo de tabela que não aparece no GridView. Por exemplo, ao adicionar um novo registro, talvez queiramos que o QuantityPerUnit valor seja definido como TODO.
Se essas personalizações forem necessárias, você precisará torná-las manualmente, por meio do janela Propriedades, a opção Especificar uma instrução SQL personalizada ou procedimento armazenado no assistente ou por meio da sintaxe declarativa.
Observação
Ao adicionar parâmetros que não têm campos correspondentes no controle da Web de dados, tenha em mente que esses valores de parâmetros precisarão ser atribuídos valores de alguma maneira. Esses valores podem ser: codificados diretamente no InsertCommand ou UpdateCommand; podem vir de alguma fonte predefinida (a querystring, o estado da sessão, os controles da Web na página e assim por diante); ou podem ser atribuídos programaticamente, como vimos no tutorial anterior.
Resumo
Para que os controles da Web de dados utilizem seus recursos internos de inserção, edição e exclusão, o controle da fonte de dados ao qual eles estão associados deve oferecer essa funcionalidade. Para o SqlDataSource, isso significa que INSERTas instruções , UPDATEe DELETE SQL devem ser atribuídas às InsertCommandpropriedades , UpdateCommande DeleteCommand . Essas propriedades e as coleções de parâmetros correspondentes podem ser adicionadas manualmente ou geradas automaticamente por meio do assistente Configurar Fonte de Dados. Neste tutorial, examinamos ambas as técnicas.
Examinamos o uso da simultaneidade otimista com o ObjectDataSource no tutorial Implementando simultaneidade otimista . O controle SqlDataSource também fornece suporte de simultaneidade otimista. Conforme observado na Etapa 2, ao gerar automaticamente as INSERTinstruções , UPDATEe DELETE , o assistente oferece uma opção Usar simultaneidade otimista. Como veremos no próximo tutorial, o uso da simultaneidade otimista com o SqlDataSource modifica as WHERE cláusulas nas UPDATE instruções e DELETE para garantir que os valores das outras colunas não tenham sido alterados desde que os dados foram exibidos pela última vez na página.
Programação feliz!
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.