C#- SQL Server - Aesso e manutenção de dados

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:

• VB.NET  2005 -  Criando Objetos Básicos de dados.

• Usando o SQL Server Management Studio

• Criando um Banco de dados no SQL Server

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:

• As conexões estão no pool somente se possuírem a mesma string de conexão;
• Quando encerrar a utilização de um objeto SqlConnection chama o método Dispose() ou Close() para liberar a conexão ao pool de conexões;
• Afim de tornar o máximo de conexões disponíveis, mantenha a conexão aberta pelo menor período de tempo possível;

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 tiposomente-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 eRecordsAffected.
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 :

• Por nome - identificando o nome da coluna desejada; Ex: dr["nome"]
• Por índice - indicando o índice da coluna que desejamos obter: Ex: dr[0] ou dr.GetString(0)) - Onde o valor zero refere-se a primeira coluna.

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 dadoswhile (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: