Olá pessoal! Esse post será sobre objetivo 2.4 da certificação Microsoft 40-487, Query and manipulate data by using ADO.NET. Embora já coberto no objetivo 1.1, esse objetivo é bem específico sobre o ADO.NET e suas classes, assim como métodos assíncronos.
Fazendo Queries com Connection, DataReader, Command, DataAdapter e DataSet
SqlConnection
Não há muito segredo sobre a classe SqlConnection. Ela herda de IDbConnection e você deve instanciá-la passando uma connection string, seja inline ou de um arquivo de configuração (web.config).
Todas as connections precisam ser abertas com o método Open e fechadas explicitamente com Close ou num bloco using.
using (SqlConnection connection = new SqlConnection("ConnectionStringName"))
{
connection.Open();
}
Sempre abra uma conexão num bloco try/catch, pois fatores fora do seu alcança podem impedir que isso aconteça.
SqlCommand
A classe SqlCommand, herdeira de IDbCommand, é a responsável por fazer comandos ao banco de dados. Algumas de suas características são:
- Uma SqlCommand precisa de uma SqlConnection aberta para operar;
- Além da propriedade Connection, o único outro item importante é setar a propriedade CommandText, que é o SQL a ser executado. É possível também passar somente o nome de uma stored procedure no lugar do SQL, sendo necessário nesse caso setar a propriedade CommandType para StoredProcedure;
- Você nunca deve concatenar valores como string numa query SQL. Instancie a classe SqlParameter e o adicione na propriedade Parameters;
- Há muito debate entre stored procedures e “SQLs dinâmicos” (queries na propriedade CommandText). O fato é que, sendo parametrizados corretamente, o plano de execução dos SQLs dinâmicos é cacheado, o que pode aumentar a performance e ser menos vulnerável à ataques;
- Há várias maneiras de executar um SqlCommand. Você pode usá-lo como input da propriedade SelectCommand de um SqlDataAdapter, chamar ExecuteScalar para retornar um valor ou ExecuteReader para retornar um SqlDataReader;
- Cada método de execução tem seu par assíncrono, indicados pelo nome começando com Begin;
- É bom chamar o método Dispose de um SqlCommand. Faça isso explicitamente ou faça uso de um bloco using.
SqlDataReader
A classe SqlDataReader, invocada pelo método ExecuteReader de um SqlCommand, é responsável pela obtenção de dados. Você deve fechar explicitamente quaisquer SqlDataReader abertos.
SqlDataReader tem uma característica que você deve ter em mente: ao obter um SqlDataReader pelo método ExecuteReader, o buffer é carregado do banco de dados, mas o stream dos dados não é feito até você iterar pelos itens com o método Read. É possível saber se há itens no buffer com a propriedade HasRows, mas não há como saber quantos itens existem sem iterar pelos dados, o que esvazia o buffer no processo. Isso significa que você só pode iterar pelos dados uma única vez.
SqlDataAdapter
SqlDataAdapter é uma classe designada para fazer todas as operações no banco de dados (CRUD).
Na leitura, ela funciona bem diferente do SqlDataReader: um DataSet é populado (com uma mais DataTables), permitindo que você itere quantas vezes quiser – até com LINQ. Outra diferença é que você pode obter facilmente a quantidade de registros retornados com o Count de uma DataTable.
O método Fill é o jeito mais básico de carregar um SqlDataAdapter. Passando um SqlCommand válido, o método conecta ao banco de dados, executa o comando e cria uma DataTable para cada query SELECT.
Cada coluna gera uma DataColumn na DataTable sendo construída. O próprio adapter infere o tipo da DataColumn, de acordo com o schema no banco de dados. Se houver mais de uma coluna com o mesmo nome ou alias, o adapter adiciona um número no final do nome (FirstName1, FirstName2, FirstName3). É possível também chamar o método Fill especificando o valor AddWithKey do enum MissingSchemaAction, resultando na adição de uma primary key e outras contraints existentes no banco de dados. Caso haja mais de uma primary key numa query, você pode criar uma manualmente na DataTable. Com a DataTable e as DataColumns criadas, o adapter cria uma DataRow para cada registro retornado do banco de dados.
Outro método importante é o Update. Ele funciona assim: todas as DataRows, quando retornadas, tem o RowState como Unchanged. Você pode alterar o RowState de uma DataRow para Added, Modified ou Deleted. Basicamente, quando o método Update é chamado, ele varre todas as DataRows e, dependendo do RowState, chama o SqlCommand correspondente (InsertCommand, UpdateCommand ou DeleteCommand) do SqlDataAdapter. Para funcionar corretamente, você precisa setar todas essas propriedades. Caso o command não seja encontrado, uma exceção é lançada.
A sintaxe do SqlDataAdapter é assim: (note que o próprio método Fill chama o método Open da SqlConnection, e ele se encarrega de fechá-la. Caso você a abra, a responsabilidade de fechá-la é sua)
Por último, você pode definir o comportamento de um SqlDataAdapter com algumas propriedades:
- AcceptChangesDuringFill: por padrão, o método Fill carrega todas as DataRows com o RowState Unchanged. Se você setar essa propriedade como false, todas as DataRows terão o RowState Added;
- AcceptChangesDuringUpdate: como dito anteriormente, o método Update examina o RowState de cada DataRow para saber qual Command invocar. Ao final do método, todos os RowStates são retornados para Unchanged. Setar essa propriedade como false não reseta o RowState; mantém o estado que estava antes do método Fill ser chamado;
- UpdateBatchSize: por padrão, o SqlDataAdapter atualiza uma DataRow por vez. Setando essa propriedade, as atualizações são feitas em grupos, o que diminui as chamadas ao banco de dados;
- OnRowUpdating/OnRowUpdated: eventos na atualização de uma DataRow;
- ContinueUpdateOnError: por padrão, quando há vários comandos a serem executados e um deles lança uma Exception, todos os outros comandos não são feitos. Setando essa propriedade como true, o comando que deu erro é ignorado e os outros comandos são executados normalmente;
- FillSchema: FillSchema usa o SelectCommand para construir o schema do DataTable correspondente, sem popular a tabela.
Fazendo Operações Síncronas e Assíncronas
Há três métodos de um SqlCommand que têm suas partes assíncronas: BeginExecuteReader, BeginExecuteXmlReader e BeginExecuteNonQuery. Eles são bem fáceis de usar. Basta examinar a propriedade IsCompleted e, quando completo, chamar o método End correspondente:
Obrigado pela leitura e fiquem ligados no post do objetivo 2.5, Create an Entity Framework data model.
Até mais!