um. Prefácio:
Procedimento Armazenado é um conjunto de instruções SQL projetadas para completar funções específicas, que são compiladas e armazenadas no banco de dados. O usuário executa um procedimento armazenado especificando seu nome e fornecendo parâmetros (se o procedimento armazenado tiver parâmetros). Os procedimentos armazenados são um objeto importante no banco de dados e qualquer aplicativo de banco de dados bem projetado deve usar procedimentos armazenados. Em geral, os procedimentos armazenados têm as seguintes vantagens:
◆ Os procedimentos armazenados permitem a programação de componentes padrão
◆ Os procedimentos armazenados podem atingir velocidades de execução mais rápidas
◆ Os procedimentos armazenados podem reduzir o tráfego de rede
◆ Os procedimentos armazenados podem ser totalmente utilizados como um mecanismo de segurança
O autor deste artigo apresentará para você a aplicação de procedimentos armazenados em aplicativos de banco de dados .NET e como usá-los em conjunto com o objeto SqlDataAdapter, objeto DataSet, etc. no ADO.NET para melhorar o desempenho geral dos aplicativos de banco de dados .NET.
dois. Requisitos do sistema:
Ferramentas de desenvolvimento: Visual Studio.NET
Sistema de gerenciamento de banco de dados: SQL Server 2000 (incluindo o banco de dados Pubs usado no programa de exemplo)
3. Crie um procedimento armazenado simples:
Aqui apresentarei como usar o Visual Studio.NET IDE para criar um procedimento armazenado. É muito fácil e intuitivo criar procedimentos armazenados usando o Visual Studio.NET IDE. Contanto que você navegue até o banco de dados Pubs no Server Explorer e expanda o nó, encontrará vários objetos de banco de dados, incluindo procedimentos armazenados, conforme mostrado na Figura 1. Mostrar.
Clique com o botão direito no nó do procedimento armazenado para abrir um menu que contém o comando “Novo procedimento armazenado”. Após a criação de um novo procedimento armazenado, o modelo de código mostrado abaixo aparecerá na janela de edição de código do IDE:
CRIAR PROCEDIMENTO dbo.StoredProcedure1
/*
(
@parâmetro1 tipo de dados = valor padrão,
@parâmetro2 tipo de dados SAÍDA)
*/
COMO
/* DEFINIR NOCOUNT ON */
RETORNAR
O modelo de código acima está em conformidade com as regras de sintaxe simplificadas para a criação de procedimentos armazenados. As regras de sintaxe completas são as seguintes:
CREATE PROC [EDURE] nome_do_procedimento [;
[ { @parameter data_type }
[VARIANDO] [=padrão] [SAÍDA]
] [ ,...n ]
[ COM
{ RECOMPILAR | CRIPTOGRAFIA | RECOMPILAR, CRIPTOGRAFIA } ]
[PARA REPLICAÇÃO]
AS instrução_sql [ ...n ]
Devido a limitações de espaço, o significado de cada parâmetro não será apresentado aqui. Os leitores interessados podem consultar as informações sobre o sistema de gerenciamento de banco de dados SQL Server 2000.
Abaixo apresentarei brevemente cada componente gramatical neste modelo de código. A instrução CREATE PROCEDURE cria um procedimento armazenado, seguido pelo nome do procedimento armazenado. Os componentes em "/*...*/" são os parâmetros do procedimento armazenado, que podem incluir parâmetros de entrada e parâmetros de saída. O conteúdo após a palavra-chave AS é o corpo principal do procedimento armazenado, que é qualquer número e tipo de instruções SQL contidas no procedimento armazenado. A palavra-chave RETURN indica o fim do procedimento armazenado e pode retornar um valor de status inteiro para o chamador. Vamos criar um procedimento armazenado simples sem parâmetros e usá-lo:
CREATE PROCEDURE dbo.up_GetPublisherInfo
COMO
SELECIONE pub_id, pub_name, cidade, estado, país
DOS editores
RETORNAR
Após criar o procedimento armazenado acima, salve-o. Uma vez salvo, o nó correspondente ao procedimento armazenado aparecerá no Server Explorer. Observe também que a palavra-chave CREATE na janela de edição de código foi alterada para a palavra-chave ALTER, que é usada para alterar quaisquer procedimentos armazenados existentes. Para executar o procedimento armazenado acima, basta clicar em seu nó e selecionar “Executar procedimento armazenado” no menu pop-up do botão direito.
Quatro. Crie um procedimento armazenado com parâmetros:
Acima criamos um procedimento armazenado simples sem parâmetros, mas em aplicações reais muitos procedimentos armazenados com parâmetros são frequentemente usados. Procedimentos armazenados com parâmetros geralmente são usados para atualizar ou inserir dados. Abaixo podemos usar o mesmo método de operação para criar um procedimento armazenado com parâmetros:
CRIAR PROCEDIMENTO dbo.up_UpdatePublisherInfo
(
@pub_id caractere (4),
@pub_name varchar (40),
@cidadevarchar(20),
@estado caractere (2),
@país varchar (30)
)
COMO
ATUALIZAR editores
SET nome_pub = @nome_pub, cidade = @cidade, estado = @estado,
país = @país
ONDE (pub_id = @pub_id)
RETORNAR
No código acima, para criar um procedimento armazenado, declaramos as variáveis-parâmetros locais do procedimento armazenado adicionando um sinal “@” antes do nome. Também declaramos o tipo de cada parâmetro e determinamos o valor da direção de cada parâmetro. é, indica se o parâmetro é tipo de entrada ou tipo de saída ou tipo de entrada-saída ou tipo de valor de retorno. Os usuários podem chamar o procedimento armazenado por meio do nome do procedimento armazenado correspondente e de parâmetros corretos e válidos. Além disso, você pode adicionar parâmetros de saída aos parâmetros usando a palavra-chave OUTPUT. Consulte as regras de sintaxe acima para métodos específicos. Os parâmetros de saída podem retornar informações relevantes ao chamador.
O procedimento armazenado acima pode atualizar as informações do editor correspondente na tabela de editores. Você pode executá-lo clicando no nó do procedimento armazenado e selecionando "Executar procedimento armazenado" no menu pop-up do botão direito. Depois de executado, uma caixa de diálogo para inserir informações do editor aparecerá no IDE (conforme mostrado na Figura 3). Preencha as informações de atualização corretas e válidas nesta caixa de diálogo. Observe que o valor de pub_id deve existir na tabela original e clique no botão "OK" para atualizar os dados.
cinco. Crie um aplicativo de banco de dados com um procedimento armazenado simples:
A seguir, usaremos o procedimento armazenado acima sem parâmetros para criar um aplicativo de banco de dados, que também usa o objeto SqlDataAdapter e o objeto DataSet no ADO.NET. O objeto SqlDataAdapter serve como uma ponte entre o banco de dados SQL Server e o objeto DataSet para conectar os dois. O objeto SqlDataAdapter contém dois métodos comumente usados: método Fill() e método Update(). O método Fill() pode obter os dados correspondentes do banco de dados e preenchê-los no objeto DataSet, e o método Update(), como o nome sugere, atualiza o conjunto de dados. Antes de chamar o método Fill(), devemos definir a propriedade SelectCommand do objeto SqlDataAdapter, que na verdade é um objeto SqlCommand. A propriedade SelectCommand contém instruções SQL válidas e pode obter dados correspondentes do banco de dados e preenchê-los no objeto DataSet.
Primeiro, criamos uma aplicação Windows Forms, a linguagem de programação é C#. Depois de criar um novo projeto no Visual Studio.NET, adicione uma nova classe ao projeto - a classe Publishers, que encapsula a lógica de negócios de conexão ao banco de dados backend e obtenção do objeto do conjunto de dados. Os passos são os seguintes:
1. Adicione referências de namespace necessárias: usando System.Data.SqlClient
2. Adicione as seguintes variáveis necessárias a esta classe:
private SqlConnection cnPubs;
cmdPubs privados do SqlCommand;
daPubs SqlDataAdapter privados;
dsPubs do DataSet privado;
3. No construtor desta classe, conclua a conexão com o banco de dados backend e obtenha o objeto SqlDataAdapter e outras lógicas de negócios:
public Publishers()
{
tentar
{
//Cria um objeto de conexão com o banco de dados
cnPubs = new SqlConnection( "servidor=localhost;segurança integrada=true;banco de dados=pubs");
//Cria um objeto SqlCommand e especifica seu tipo de comando como um procedimento armazenado
cmdPubs = new SqlCommand();
cmdPubs.Connection = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure;
cmdPubs.CommandText = "up_GetPublisherInfo";
//Cria um objeto SqlDataAdapter e define sua propriedade SelectCommand para o objeto SqlCommand acima
daPubs = new SqlDataAdapter();
daPubs.SelectCommand=cmdPubs;
//Cria um objeto DataSet
dsPubs = new DataSet();
}
pegar(Exceção) {}
}
4. Finalmente, um método GetPublisherInfo() é fornecido para esta classe, que preenche o objeto DataSet com o objeto SqlDataAdapter e retorna o objeto DataSet preenchido. O método é o seguinte (vale a pena notar que o objeto SqlDataAdapter abrirá implicitamente a conexão com o banco de dados e abra implicitamente a conexão com o banco de dados após obter os dados. Feche a conexão formalmente, o que significa que o objeto DataSet funciona em modo de não conexão e quando você abre explicitamente a conexão com o banco de dados e obtém os dados, o objeto SqlDataAdapter não fechará a conexão):
Conjunto de dados público GetPublisherInfo().
{
// Chame o método Fill() do objeto SqlDataAdapter e retorne o objeto do conjunto de dados
daPubs.Fill(dsPubs);
retornar dsPubs;
}
Após concluir o design da classe Publishers, adicionamos um controle DataGrid ao formulário principal e o utilizamos para exibir os dados no objeto DataSet. Primeiro adicione as seguintes variáveis de membro à classe de formulário principal:
private Publishers pubs;
conjunto de dados privado ds;
Depois disso, modifique o construtor da classe principal do formulário da seguinte forma:
Formulário público1()
{
//
// Necessário para suporte ao Windows Forms Designer
//
InitializeComponent();
//
// TODO: Adicione qualquer código construtor após a chamada InitializeComponent
// pubs = novos editores();
ds = pubs.GetPublisherInfo();
dataGrid1.DataSource = ds.Tables[0];
}
Desta forma, assim que a aplicação for iniciada, os dados correspondentes obtidos do banco de dados Pubs utilizando o procedimento armazenado acima sem parâmetros serão exibidos no controle DataGrid do formulário principal. O diagrama de execução do programa é o seguinte:
6. Crie uma aplicação de banco de dados com um procedimento armazenado com parâmetros:
Acima criamos uma aplicação com um procedimento armazenado sem parâmetros, e agora criaremos uma aplicação de banco de dados mais complexa. Em aplicativos de banco de dados reais, muitas vezes precisamos obter dados e atualizar, inserir ou excluir dados. Neste momento, precisamos usar procedimentos armazenados com parâmetros. Ao mesmo tempo, ao usar o objeto SqlDataAdapter, chamaremos seu Update(). método. O método Update() concluirá automaticamente a operação correspondente com base nas alterações em cada registro no objeto DataTable no objeto DataSet. O objeto SqlDataAdapter também contém propriedades como UpdateCommand, InsertCommand, DeleteCommand, etc. Essas propriedades são, na verdade, objetos SqlCommand. O método Update() seleciona os atributos apropriados com base no tipo de operação.
Ao usar procedimentos armazenados com parâmetros para construir aplicativos de banco de dados, geralmente usamos a classe SqlParameter, que encapsula várias propriedades e métodos relacionados aos parâmetros Sql. As propriedades incluem ParameterName, SqlDBType, Direction, Size, Value, SourceColumn e SourceVersion, etc. Entre eles, ParameterName, SqlDBType, Direction, Size e outros atributos são usados para corresponder aos parâmetros definidos no procedimento armazenado. Por exemplo, o objeto SqlParameter definido abaixo é usado para corresponder ao parâmetro "@pub_id " no procedimento armazenado up_UpdatePublisherInfo definido anteriormente.
SqlParameter updParam = new SqlParameter( "@pub_id", SqlDbType.Char, 4 );
Na definição acima, embora o atributo Direction não seja fornecido explicitamente, seu valor padrão é Input, portanto, atende às nossas necessidades. E se a propriedade Direction de um objeto SqlParameter for InputOutput ou Output ou ReturnValue, então sua propriedade Direction deverá ser claramente indicada. Por exemplo, o código a seguir declara claramente que a propriedade Direction de um objeto SqlParameter é Output.
oParam.Direction = ParameterDirection.Output;
A propriedade SourceColumn é usada para corresponder ao objeto DataColumn em um objeto DataTable. Essa correspondência pode importar implicitamente o objeto SqlParameter necessário quando o método Update() é chamado para atualizar o objeto DataTable. Se essa propriedade não for declarada durante a definição, você deverá declarar explicitamente a propriedade SourceColumn do objeto SqlParameter em seu código.
O valor padrão da propriedade SourceVersion é o valor atual no campo correspondente do objeto DataRow, que é o valor a ser atualizado no banco de dados. É claro que a propriedade SourceVersion também pode apontar para o valor original no campo correspondente do objeto DataRow, ou seja, o valor inicial obtido do banco de dados. Em um sistema de processamento de transações de banco de dados, a questão da sincronização de dados é muito importante. Vamos construir um procedimento armazenado que possa detectar a sincronização de dados.
CRIAR PROCEDIMENTO dbo.up_UpdatePublisherName
(
@pub_id caracter(4),
@nome_pub varchar(40),
@Original_pub_name varchar(40)
)
COMO
se existir(selecione pub_id
de editores
onde (pub_id = @pub_id) E (pub_name = @Original_pub_name))
Começar
ATUALIZAR editores SET pub_name = @pub_name
ONDE (pub_id = @pub_id)
Fim
RETORNAR
A seguir, chamamos o procedimento armazenado no aplicativo acima para atualizar o nome do editor. Primeiro, melhore sua classe de lógica de negócios - classe Publishers com base no aplicativo original:
1. Adicione um novo objeto SqlCommand que pode ser usado como a propriedade UpdateCommand do objeto SqlDataAdapter:
cmdUpdPubs privados do SqlCommand;
2. Atualize a função construtora Publishers() desta classe para adicionar o seguinte:
// Cria outro objeto SqlCommand que faz referência ao procedimento armazenado que atualiza o nome do editor
cmdUpdPubs = new SqlCommand();
cmdUpdPubs.Connection = cnPubs;
cmdUpdPubs.CommandType = CommandType.StoredProcedure;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
//Adiciona os parâmetros necessários ao objeto SqlCommand acima
cmdUpdPubs.Parameters.Add( "@pub_id", SqlDbType.Char, 4, "pub_id");
cmdUpdPubs.Parameters.Add( "@pub_name", SqlDbType.VarChar, 40, "pub_name");
SqlParameter updParam = novo SqlParameter
( "@Original_pub_name", SqlDbType.VarChar, 40, "pub_name");
updParam.SourceVersion = DataRowVersion.Original;
cmdUpdPubs.Parameters.Add(updParam);
3. Especifique a propriedade UpdateCommand do objeto SqlDataAdapter como o objeto SqlCommand definido acima:
daPubs.UpdateCommand=cmdUpdPubs;
4. Adicione o método UpdatePublisherName():
public void UpdatePublisherName (DataSet dsChanges)
{
//Atualiza todas as alterações
daPubs.Update(dsChanges);
}
Depois que a classe de lógica de negócios do aplicativo for concluída, adicione um botão chamado "Atualizar conjunto de dados" no formulário principal e adicione a função de resposta de evento do botão da seguinte forma:
private void button1_Click(object sender, System.EventArgs e) { if ( ds.HasChanges()) { pubs.UpdatePublisherName(ds.GetChanges()); ds.Clear();
Até agora, a classe de lógica de negócios e a classe de formulário principal do aplicativo foram atualizadas. Agora o aplicativo pode atualizar o conteúdo relevante no banco de dados de acordo com as alterações do usuário.
Sete. Resumo:
Este artigo apresenta o conhecimento básico de procedimentos armazenados e como combinar objetos SqlDataAdapter, objetos DataSet etc. para criar aplicativos orientados a dados em aplicativos de banco de dados .NET. Neste artigo, usamos dois tipos de procedimentos armazenados: um é um procedimento armazenado simples sem parâmetros, que é relativamente fácil de usar; o outro é um procedimento armazenado com parâmetros, e você deve chamar esse tipo de procedimento armazenado; para o objeto SqlParameter. Ao mesmo tempo, não é difícil descobrir que o encapsulamento da lógica de negócios de atualização de dados em um procedimento armazenado é um bom método de design, que pode melhorar a capacidade de gerenciamento, escalabilidade e segurança do banco de dados do aplicativo. Da mesma forma, a lógica de negócios para inserção e exclusão de dados pode ser encapsulada em procedimentos armazenados e usada em aplicativos de maneira semelhante. Por fim, espero que este artigo seja de grande ajuda para todos.