C#-  SQL Server - acesso e manutenção de dados (CRUD)

 Este é o primeiro artigo de uma série onde irei mostrar como trabalhar com o banco de dados SQL Server usando a linguagem C# e os recursos da ADO .NET.

Nestes artigos eu pretendo abordar os seguintes tópicos :

Eu já abordei todos esses assuntos mas usando a linguagem VB .NET e estou atendo diversos pedidos para que o mesmo seja feito com a linguagem C#.

Em todos os artigos eu vou usar o Visual C# 2008 Express Edition e o banco de dados SQL Server 2005 Express Edition que são gratuitos e você pode baixar do site da Microsoft sem precisar gastar nada.

Nota: Você pode usar também o framework SharpDevelop 2.2 para criar os projetos dos artigos.

Partindo da premissa que você tenha o Visual C# Express Edition e o SQL Server 2005 Express instalados você só precisa ter vontade de aprender , se este for o caso , vamos lá...

Para começar , abra o Visual C# Express e crie um novo projeto através do menu File opção New Project do tipo Windows Forms Application com o nome acessoDados.

Será criado uma nova solução contendo o formulário Form1.cs.

O modelo de acesso a dados ADO .NET

O modelo de acesso a dados usado pela ADO .NET pode ser resumido da seguinte forma:

1 - Você abre uma conexão;
2- Obtêm os dados ou realiza uma operação;
3- Você fecha a conexão;

Muito simples não é mesmo ? Para trabalhar com este modelo a ADO .NET fornece duas estratégias básicas:

Armazenar os dados em um DataSet , que é um cache de dados em memória de registros nos quais você pode trabalhar enquanto esta desconectado da fonte de dados.

Para usar um DataSet você cria uma instância do mesmo e usa um DataAdapter ou TableAdapter para preencher o dataset a partir da fonte de dados. Feito isso você pode trabalhar com os dados no dataset , realizar operações , exibir os dados em formulários , etc.

Realizar operações diretamente no banco de dados através de comandos SQL ou stored procedures. Este modelo exige que você esteja conectado a fonte de dados e utilize o objeto Command incluindo uma instrução SQL ou uma referência a uma stored procedure para em seguida executar o comando e realizar a operação desejada.

Os comandos de dados ADO.NET (data commands) permitem a execução de comandos diretamente contra uma base de dados sem necessitar de um dataset ou de um data adapter.)

Nota: O objeto DataReader é usado somente para leitura de dados de forma conectada e percorre os dados até o fim em um único sentido (forward-only) e no modo somente leitura (read-only).

O DataReader consome menos recursos do que o dataset e oferece uma acesso mais rápido que o objeto dataset mas não pode efetuar alterações na fonte de dados original, sendo usado para acessar os dados de forma rápida e para apresentá-los sempre atualizados.

Conectando com o banco de dados SQL Server

A primeira tarefa que devemos efetuar para usar um banco de dados,  no nosso caso o SQL Server, é criar uma conexão com o mesmo.

Vamos usar ADO .NET e para ter acesso as classes que vão realizar tal tarefa devemos importar dois namespaces (espaço de nomes) através da diretiva using no início código e antes de qualquer declaração ou definição de variável;

Namespaces são grupamentos lógicos de elementos , classes ou mesmo outros namespaces, e tem como objetivo evitar conflitos de nomes e organizar código sendo usados para distinguir entre diferentes tipos de conteúdo.

using System.Data;
using System.Data.SqlClient;

O namespace System.Data fornece acesso as classes que representa a arquitetura ADO .NET;

O namespace System.Data.SqlClient é o Data Provider para o SQL Server e consiste de uma coleção de classes usadas para acessar o SQL Server.

Para estabelecer uma conexão com o SQL Server vamos usar o objeto SqlConnection;

Antes de continuar é importante lembrar que você deve ter um banco de dados criado no seu SQL Server.

Você mesmo pode criar seu banco de dados, e, existem diferentes formas de fazer isso. Veja os meus artigos sobre este assunto:

Você pode também fazer o download do banco de dados de exemplo da Microsoft , o famoso Northwind, baixando o  arquivo SQL2000SampleDb.msi no link : http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Após isso pode criar o banco de dados Northwind.mdf  (veja o artigo : Usando o SQL Server Management Studio ).

No exemplo deste artigo eu tenho o banco de dados Northwind.mdf localizado na pasta c:\dados na minha máquina local. Se você pode colocar o banco de dados em qualquer outro local mas lembre-se de mudar a string de conexão para indicar onde o banco de dados esta localizado.

Tendo o banco de dados Northwind.mdf criado e sabendo a sua localização podemos usar o objeto SqlConnection para efetuar a conexão. Fazemos isso em C# assim:

SqlConnection sqlConn = new SqlConnection(connectionString);

Onde connectionString é a string de conexão usada para se conectar com o banco de dados. Para o nosso exemplo a string de conexão com o banco de dados Northwind.mdf no SQL Server Express fica assim:

"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\dados\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"

De forma geral para o SQL Server podemos usar também a seguinte string de conexão:

"server=Nome_Do_Computador\SQLEXPRESS; Database=xxxxxx;Integrated Security=SSPI;"

Nota: Você pode consultar diversas strings de conexão no site : http://www.connectionstrings.com/

Após criar um objeto para fazer a conexão temos que abria a conexão usando o método Open do objeto SqlConnection:

SqlConn.Open();

Então até o momento temos o seguinte código:

string connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\dados\\NORTHWND.MDF;Integrated Security=True; Connect Timeout=30;User Instance=True";

SqlConnection sqlConn = new SqlConnection(connectionString);

SqlConn.Open();
 

Obs: Veremos adiante uma forma mais elegante de armazenar a string de conexão.

Usar o recurso de um banco de dados exige abrir uma conexão e isso geralmente tem um custo. Para melhorar o desempenho o provedor de dados do SQL Server fornece de forma automática um pool de conexões.  Um pool de conexões gerencia a abertura e o fechamento de conexões de forma que quando uma conexão é fechada ele fica disponível para ser usada novamente; com isso temos um ganho de desempenho e escalabilidade na aplicação.

Nota: Você pode ler um bom artigo sobre pool de conexões em : SQL Server Connection Pool ou ADO .NET - Pool de Conexões

Para poder tirar o vantagem deste recurso você deve observar as seguintes orientações:

Após abrir a conexão você já pode realizar as tarefas que precisa para em seguida fechar a conexão usando o método Close() do objeto SqlConnection.

Podemos definir o código no evento Load do formulário conforme a figura abaixo:

Executando consultas no banco de dados

Agora que já temos uma conexão com o banco de dados SQL Server podemos executar uma consulta SQL ou um procedimento armazenado.

Para podemos executar uma consulta SQL usamos o objeto SqlCommand.

Vamos usar o construtor do SqlCommand que usa dois parâmetros :

  1. A instrução SQL a ser executa;
  2. A conexão com o banco de dados SQL;

Então podemos escrever o comando dessa forma na linguagem C#:

SqlCommand cmd = new SqlCommand("SELECT * FROM Categories ORDER BY  CategoryName", sqlConn);

Nesta instrução temos a criação do objeto SqlCommand usando os parâmetros:

  • SELECT * FROM Customers ORDER BY  City - A instrução SQL para selecionar todos os dados da tabela Categories e ordenar pelo nome da categoria;

  • sqlConn - A conexão que já criamos anteriormente;

Definido o objeto SqlCommand com seus parâmetros podemos executá-lo para obter o resultado. Para isso temos 3 opções:

Método Descrição
ExecuteReader Executa a consulta e retorna um objeto SqlDataReader
ExecuteNonQuery Executa a consulta e não retorna nenhuma coleção. Usado para instruções DELETE e UPDATE onde retorna o número de registros afetados.
Ex:
    SqlCommand sqlComm = new SqlCommand("DELETE FROM Categories WHERE CategoryID=1", sqlConn);
    sqlComm.ExecuteNonQuery();
ExcecuteScalar Executa a consulta e retorna um único valor.(uma linha e uma coluna)

Vamos iniciar usando o método ExecuteReader que irá retornar um objeto SqlDataReader.

Este é o modo de utilização mais comum pois temos o retorno de múltiplas linhas e colunas de dados. O objeto SqlDataReader retornado fornece o acesso do tipo somente-leitura e somente-para-frente e é muito recomendado quando desejamos somente exibir as informações.

Se você estiver interessado em saber quantas linhas foram afetados pelo comando use o método ExecuteNonQuery e verifique as propriedades HasRows e RecordsAffected.

Quando usarmos um objeto SqlDataReader é importante ressaltar que este objeto mantêm a conexão SqlConnection aberta enquanto realiza a leitura dos dados.

Os principais métodos do objeto SqlDataReader são:

Método Descrição
Close Encerra o objeto SqlDataReader;
Read Efetua a leitura do próximo registro no SqlDataReader;
NextResult Descola o leitor de dados para o próximo resultado.
GetBoolean
GetByte
GetChar
GetDouble		 Usados para obter o valor da coluna levando em conta o tipo de dados.(há também ganho de desempenho, pois  menos passos são executados internamente e obtemos o tipo de dado correto.)

Então após executar o comando e obter o objeto SqlDataReader chamamos o método Read() que avança o leitor para primeira linha e então para a próxima linha nas chamas seguintes, retornando um valor boleano que indica se ainda existe uma linha a ser lida.

Durante a leitura podemos acessar as informações a através do objeto SqlDataReader podemos fazer isso :

Então devemos criar um objeto do tipo SqlDataReader para receber o retorno da execução do comando via método ExecuteReader();

Vejamos como fica o nosso código para obter todos os dados da tabela Categories:

SqlCommand cmd = new SqlCommand("SELECT * FROM Categories ORDER BY CategoryName", sqlConn);
SqlDataReader dr = cmd.ExecuteReader();

Resta agora percorrer o SqlDataReader usando o método Read() e exibir os dados no formulário:

while ( r.Read() )
{
   //comandos para obter os dados
}

Vamos exibir o resultado em um controle ListBox no formulário form1.cs; Neste caso o nosso código ficará assim:

//percorre o SqlDataReader para obter os dados
while (dr.Read())
{
   //Exibindo o código da categoria em dr[0]
   //e o nome da categoria em dr["CategoryName"]
    listBox1.Items.Add(dr[0] + " - " + dr["CategoryName"]);
}

O código completo criado até aqui pode ser visto abaixo:

Apresentamos nesta primeira parte uma introdução básica sobre os principais conceitos relacionados com ADO .NET usando a linguagem C#.

Acompanhe o curso sobre ADO  .NET para iniciantes onde darei mais detalhes sobre como acessar e efetuar as operações básicas ADO .NET com C#

Eu sei é apenas ADO .NET, mas eu gosto...

referências:

José Carlos Macoratti