Arquivo da tag: Transação

ADO.NET: Manipulando banco de dados no .NET Framework

Este tópico vai tratar como acessar base de dados relacionais no .NET Framework.

Toda linguagem/plataforma de programação disponibiliza meios de acesso e manipulação de dados em banco de dados relacional, no .NET Framework não é diferente e trouxe consigo uma forma única e padronizada de acesso a dados, chamada ADO.NET, visando não só aplicações locais, mas também a Internet e os dispositivos móveis.

O ADO.NET é uma tecnologia baseada no clássico ADO (Active Data Objects) e faz parte de um conceito chamado UDA (Universal Data Access), o que permite que aplicativos se comuniquem com as mais diversas bases de dados usando a mesma metodologia, os mesmos objetos, a mesma abordagem. Ou seja, com o ADO.NET é possível acessar uma base dados SQL Server, Oracle, Firebird, Informix, PostGress, MySQL, e por aí vai…, sempre do mesmo jeito. Não temos que ficar apredendo uma nova forma acesso para cada tipo de SGBD.

Após definir o SGBD da aplicação é necessário procurar seu um provider .NET. A maioria dos fornecedores de SGBD disponibilizam em seus sites os providers. O provider para o SQL Server vem incluso na plataforma .NET. Se o SGBD escolhido não possui o provider, então é possível usar os providers genéricos ODBC ou OLEDB.

Os esses provider, tanto o específico (pode ser chamado de Nativo) como os genéricos são baseados num conjunto de classes, interfaces, tipos e enumerações, fornecedidos pelo .NET Framework o que permite aos fornecedores de bancos de dados implementarem as interfaces que permitirão acesso aos seus respectivos SGBDs, por isso é possível acessar diferentes SGBDs usando a mesma metodologia.

Este post vai utilizar o SGBD SQLServer nos exemplos com a base de dados Nortwind. Para usar outro SGBD, o provider deste deve ser referenciado ao projeto.

Estabelecendo uma conexão

Só é possível manipular uma base de dados após o estabelecimento da conexão. O ADO.NET oferece uma classe de interface para isso, a System.Data.IDbConnection. E para cada SGDB que possua seu provider específico existe a implementação dessa interface. Para o SQL Server a classe é SqlConnection, para o Oracle a OraConnection, para o MySQL a MySqlConnection, e por aí vai.

Para acontecer a conexão do ADO.NET com o banco de dados, é necessário informar o computador onde está o banco de dados, qual seu usuário e senha, através de uma string de conexão, que para cada SGBD é diferente. O site http://www.connectionstrings.com mantém uma ótima relação de tipos e strings de conexão.

Sabendo de tudo isso, aí vai o primeiro exemplo usando o banco de dados Northwind que acompanha o SQL Server Express.

//Importação do provider do SQLServer
using System.Data.SqlClient;

namespace appTeste
{
    class Program
    {
        static void Main(string[] args)
        {
            //Criação do objeto de conexão apontado para o banco de dados através da string de conexão.
            SqlConnection con = new SqlConnection(@"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Persist Security Info=True;User ID=usuario; Password=senha");

            //Abertura do banco de dados
            con.Open();

            //Fechamento do banco de dados
            con.Close();
        }
    }
}

Para estabelecer a conexão não há segredo. É importar o provider, instanciar o objeto de conexão informando a string de conexão para a base e dados, abrir e fechar a conexão.

Uma boa prática de programação não demostrada aqui é sempre usar tratamento de erros ao acessar um banco de dados.

Definindo instruções SQL

A executação de instruções SQL é de responsabilidade da classe de interface System.Data.IDbCommand. Da mesma forma que acontece com a classe Connection, existe uma implementação para cada SGDB.

Um objeto do tipo Command deve estar ligado a uma conexão através de sua propriedade Connection e a instrução SQL a ser executada deve ser adicionada na propriedade CommandText no formato de string.

//Importação do provider do SQLServerusing System.Data.SqlClient;
using System.Data.SqlClient;

namespace appTeste
{
    class Program
    {
        static void Main(string[] args)
        {
            //Criação do objeto de conexão apontado para o banco de dados através da string de conexão.
            SqlConnection con = new SqlConnection(@"Data Source=.SQLEXPRESS;Initial Catalog=northwind;Persist Security Info=True;User ID=usuario; Password=senha");

            //Abertura do banco de dados
            con.Open();

            //Criando o objeto command para execução de instruções SQL
            SqlCommand cmd = new SqlCommand();

            //Ligando o Command a conexão.
            cmd.Connection = con;

            //Informando a instrução SQL.
            cmd.CommandText = "Insert into Region (RegionID, RegionDescription) Values (1000, 'Região...')";

            //Fechamento do banco de dados
            con.Close();
        }
    }
}

Um objeto do tipo Command possui a propriedade CommandType que define o tipo da execução. O valor padrão é Text, e indica que a string informada na propriedade CommandText é uma instrução Insert, Update, Delete ou Select.  Para executar Stored Procedures seu valor deve ser alterado para StoredProcedure.

cmd.CommandType = System.Data.CommandType.StoredProcedure;

Executando instruções SQL

Com a instrução SQL definida no objeto do tipo Command é possível executá-la. O objeto fornece basicamente três métodos de execução, o ExecuteNonQuery, ExecuteScalar e ExecuteReader.

1. ExecuteNonQuery: Utilizado quando se quer executar uma instrução que não retorna como resultado um conjunto de dados: Insert, Update, Delete e chamada a Stored Procedure. Seu retorno é um valor inteiro indicando o número de linhas afetadas na base de dados.

cmd.CommandText = "Insert into Region (RegionID, RegionDescription) Values (1000, 'Região...')";

if (cmd.ExecuteNonQuery() > 0)
    Console.WriteLine("Inserido");
else Console.WriteLine("Não inserido");
cmd.CommandText = "Delete From Region Where RegionID = 1000";

if (cmd.ExecuteNonQuery() > 0)
    Console.WriteLine("Excluído");
else Console.WriteLine("Não excluído");

2. ExecuteScalar: Retorna um único valor (uma coluna e uma linha). Tem seu desempenho otimizado para ser usado em funções SQL de agregação como count(*), sum(campo), avg(campo), max(campo), etc. Seu retorno é um objeto do tipo object, o que significa que pode representar qualquer tipo de dados.

cmd.CommandText = "Select Count(*) From Region";

object conta = cmd.ExecuteScalar();

if (conta != null)
    Console.WriteLine("Quantidade de registros: " + conta);
cmd.CommandText = "Select Max(RegionID) From Region";

object maxRegionId = cmd.ExecuteScalar();

int proximoRegionId;

if (maxRegionId == null)
    proximoRegionId = 1;
else proximoRegionId = Convert.ToInt32(maxRegionId) + 1;

Console.WriteLine("Próximo registro: " + proximoRegionId);

3. ExecuteReader: Utilizado para executar consultas (querys) que retornam um conjunto de dados. Este método tem como returno um objeto do tipo DataReader. Um objeto do tipo DataReader representa um cursor aberto somente para a frente e somente de leitura no banco de dados com os dados retornado de uma consulta. Após a leitura completa dos dados é necessário fechar o DataReader. Só é possível ter um DataReader aberto por conexão.

Um objeto do tipo DataReader somente dá acesso a uma linha do resultado por vez, e inicialmente não aponta para primeira linha. Através do método Read é possível acessar cada linha indivualmente, além disso retorna um valor booleano indicando se foi possível mover para a próxima linha. Para acessar a primeira linha do resultado é necessário usar o método Read movendo o cursor para ela.

cmd.CommandText = "Select RegionID, RegionDescription From Region";

//Executando a consulta e recuperando o DataReader com o resultado
SqlDataReader dr = cmd.ExecuteReader();

//Para acessar a primeira linha é necessário mover o cursor para ela.
if (dr.Read())
    Console.WriteLine(dr["RegionDescription"]);

//Liberando o cursor.
dr.Close();

Basta usar uma estrutura de repetição para percorrer todas as linhas do resultado.

while (dr.Read())
    Console.WriteLine(dr["RegionDescription"]);

//Liberando o cursor.
dr.Close();

Importante destacar que não é possível acessar uma linha que já foi lida pelo DataReader, pois ele é um cursor que somente anda para frente.

Usando parâmetros nas instruções SQL

Uma boa prática é utilizar parâmetros e evitar a concatenação de strings para passar valores para as instruções SQL. Para indicar um parâmetro dentro da instrução, o SQL Server utiliza o símbolo “@”. Esta sintaxe pode variar de acordo com o banco de dados utilizado (o Oracle utiliza “:”).

Para atribuir valores aos parâmetros é necessário criar objetos de classes que implementam a interface System.Data.IDbDataParameter e adicioná-los ao objeto de comando (Command) na propriedade Parameters. No SQL Server a implementação é a classe SqlParameter.

cmd.CommandText = "Insert into Region (RegionID, RegionDescription) Values (@RegionID, @RegionDescription)";

//Criando os parâmetros e atribuindo valores
SqlParameter regionID = cmd.CreateParameter();
regionID.ParameterName = "@RegionID";
regionID.Value = "1000";
//adicionando o parâmetro ao Command
cmd.Parameters.Add(regionID);

SqlParameter regionDescription = cmd.CreateParameter();
regionDescription.ParameterName = "@RegionDescription";
regionDescription.Value = "Região...";
//adicionando o parâmetro ao Command
cmd.Parameters.Add(regionDescription);

if (cmd.ExecuteNonQuery() > 0)
    Console.WriteLine("Inserido");
else Console.WriteLine("Não inserido");

Também é possível utilizar métodos da propriedade Parameters do objeto Command para criar rapidamento os parâmetros e passar os valores.

cmd.CommandText = "Insert into Region (RegionID, RegionDescription) Values (@RegionID, @RegionDescription)";

//Criando os parâmetros e atribuindo valores ao mesmo tempo
cmd.Parameters.AddWithValue("RegionID", 1000);
cmd.Parameters.AddWithValue("RegionDescription", "Região....");

if (cmd.ExecuteNonQuery() > 0)
    Console.WriteLine("Inserido");
else Console.WriteLine("Não inserido");

//Limpando a lista de parâmetros para aproveitar o mesmo Command
cmd.Parameters.Clear();

cmd.CommandText = "Delete From Region Where RegionID = @RegionID";
cmd.Parameters.AddWithValue("RegionID", 1000);

if (cmd.ExecuteNonQuery() > 0)
    Console.WriteLine("Excluído");
else Console.WriteLine("Não excluído");

Em algumas situações temos fornecer mais detalhes sobre os parâmetros além de seu nome e valor, por exemplo definir o tamanho da informação, o tipo de dados, ou mesmo informar que determinado parâmetro  é de saída (que retorna valores).

SqlParameter regionID = cmd.CreateParameter();
regionID.ParameterName = "@RegionID";
regionID.Direction = System.Data.ParameterDirection.Output;
regionID.DbType = System.Data.DbType.String;
regionID.Size = 20;

Transações

Muitas vezes é necessário trabalhar com operações de insert, update ou delete que envolvem diversas tabelas, no estilo execute tudo ou nada. Um exemplo clássico é uma operação a venda de produtos, onde não pode haver uma venda de um produto sem um atualização em seu respectivo estoque.

No .NET transações são gerenciadas por objetos de classes derivados da interface System.Data.IDbTransaction, como a SqlTransacation para SQL Server.

//Abertura do banco de dados
con.Open();

//Criando o objeto de transação e iniciando
SqlTransaction transacao = con.BeginTransaction();

//Criando o objeto command e associando à transação
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;

try
{
    cmd.Transaction = transacao;

    //Executando a primeira atualização da base dados
    cmd.CommandText = "Delete from Products Where CategoryID = 1";
    cmd.ExecuteNonQuery();

    //Executando a segunda atualização da base dados
    cmd.CommandText = "Delete Categories Where CategoryID = 1";
    cmd.ExecuteNonQuery();

    //Confirmando as atualizações na base de dados, pois NÃO ocorreram erros.
    transacao.Commit();
}
catch
{
    //Desfazendo as atualizações na base de dados, pois ocorreram erros.
    transacao.Rollback();
}

con.Close();

É isso pessoal. Espero que ajude. 😉