Artigo SQL Magazine 67 - Criando Stored Procedures Usando C#

Esse artigo faz parte da revista SQL Magazine edição 67. Clique aqui para ler todos os artigos desta edição

Criando Stored Procedures Usando C#

Integração do SQL Server com o .NET / CLR

Criar Stored Procedures sempre foi algo complicado, que exigia do desenvolvedor ou DBA conhecimentos específicos do banco de dados, uso de T-SQL avançado, tipos limitados etc. Tratar datas, strings, manipular exceções, escrever estruturas condicionais, de repetição, declarar variáveis, enfim, escrever lógica de programação dentro de uma SP difere da forma que fazemos com linguagens de programação orientadas a objeto como C#, Delphi ou VB.NET.

Com o lançamento do SQL Server 2005, tivemos um grande avanço nesse sentido. É possível criar SPs, Triggers, funções e até mesmo novos tipos de dados usando código gerenciado, como C#, ou qualquer outra linguagem que suporte .NET. Isso abre um leque enorme, pois é possível usar uma linguagem OO e uma infinidade de classes (leia-se .NET Framework) para trabalhar com as mais diferentes situações. Você pode até mesmo criar um Layer inteiro de acesso a dados que poderá ficar hospedado dentro do banco.

Naturalmente, a escrita de SPs, tema deste artigo, exige conhecimentos de ADO.NET, de forma que falaremos bastante sobre isso. Criaremos SPs com o Visual Studio 2008 e C#, e finalmente veremos como acessar uma SP a partir de um Web Site ASP.NET.

Introdução ao ADO.NET

Ao criar Stored Procedures com código C# usamos ADO.NET, o framework de acesso a dados do .NET Framework. Logo, faremos uma introdução à sua arquitetura, componentes e classes. É importante que você conheça e saiba utilizar cada uma das classes, tanto as que fazem parte do grupo “conectadas” (que exigem conexão com o banco) quanto “desconectadas” (que podem trabalhar com dados em memória, sem conexão).

O ADO.NET é a arquitetura de acesso a dados incorporada ao .NET Framework. O ADO.NET é uma evolução do ADO (Active Data Objects), sendo que o ADO.NET é totalmente escrito com código gerenciado (Managed Code).

O ADO.NET é uma evolução do modelo cliente/servidor, projetado especialmente para a construção de aplicações escaláveis, distribuídas e para Web. Uma das peças-chave do ADO.NET é o componente DataSet, onde os dados são trazidos para uma cache local, a conexão é fechada, o cliente trabalha “off-line”, e quando precisa atualizar os dados uma nova conexão é restabelecida.

Isso torna aplicações ADO.NET extremamente escaláveis, de forma que servidores possam atender a uma demanda maior de conexões sem prejudicar seu desempenho. Algumas vezes, no entanto, não é uma boa solução conectar e reconectar a um banco de dados a cada transação de uma aplicação cliente. Para isso, o ADO.NET oferece um mecanismo de Connection Pool, onde uma mesma conexão pode permanecer ativa e atender a um grande número de solicitações.

O ADO.NET pode ser utilizado em qualquer linguagem que dê suporte ao .NET, como VB.NET, C#, Delphi (isso significa que você pode utilizar qualquer uma dessas linguagens para escrever Stored Procedures). Se você conhecer o ADO.NET (e demais objetos do .NET Framework) poderá facilmente desenvolver aplicações em qualquer linguagem (pois a sintaxe de objetos, métodos e propriedades será sempre a mesma). Isso pode trazer uma grande produtividade a uma equipe de desenvolvimento que venha a utilizar a plataforma .NET.

As classes do ADO.NET estão divididas em dois grandes grupos: Managed Provides (provedores gerenciados) e Content Components (componentes de conteúdo). Os componentes do grupo Managed Providers são responsáveis pelo Acesso a Dados, e incluem classes para conexão, transações, execução de comandos e leitura de dados.

O .NET Framework já é distribuído com alguns Managed Providers, como: SQL Server, OLE DB e ODBC. Você utilizará o primeiro tipo quando precisar criar uma aplicação que se comunique exclusivamente com o SQL Server, que é o nosso caso.

Cada Managed Provider possui objetos básicos para a comunicação com um servidor de dados, incluindo: Connection, Command, DataReader e DataAdapter. No ADO.NET, seguindo princípios básicos da orientação a objeto, cada objeto deve fazer uma coisa e somente uma coisa. Você não pode, por exemplo, passar um comando SQL em um objeto de conexão, ou usar mesmo componente para conectar, obter dados.

Veja na Tabela 1 os principais objetos do grupo Managed Providers. Veja na Figura 1 a arquitetura de Managed Providers do ADO.NET e como as classes se relacionam.

Tabela 1. Classes ADO.NET (Managed Providers)

Figura 1. Managed Providers

Analisando a Figura 1, vemos que um DbCommand se conecta a um DbConnection e utiliza um objeto DbTransaction para gerenciar a transação com o banco de dados.

Um DbCommand pode utilizar três tipos de execução de comandos SQL através dos métodos:

·     ExecuteReader: quando o comando for uma consulta (select), que retornará um cursor. Esse método retorna um objeto do tipo IDataReader que você pode utilizar para fazer a leitura dos dados obtidos (unidirecional);

·     ExecuteNonQuery: utilize quando o comando for um update, delete ou insert;

·     ExecuteScalar: retorna o primeiro campo do primeiro registro da consulta, ideal para consultas como Select Max, Sum, Count.

Geralmente, em uma aplicação você utiliza um select para obter dados e comandos insert, update e delete para atualizar esses dados. Dessa forma, existe no ADO.NET um objeto chamado DataAdapter que abstrai o tratamento de quatro objetos DbCommand internos, responsáveis pelos comandos de select, delete, insert e update.

No caso do SelectCommand interno ao DataAdapter (o qual precisa retornar um cursor), o DataAdapter chama seu método ExecuteReader e obtém um objeto DataReader. Um DataReader é responsável pela manipulação do cursor SQL retornado pela execução de um SQL, e contém vários métodos Get para obter valores de campos.

Um DataReader não faz cache de dados, sua navegação é unidirecional, e não é possível alterar seus dados diretamente. Para isso, existe outro objeto, chamado DataSet, encarregado de fazer a cache. Esse objeto faz parte do segundo grupo de objetos do ADO.NET, os componentes de conteúdo (Content Components).

O objeto DataSet (acessível através do namespace ...