Curso ASP.NET 3.5 em VB.NET e C# - Acesso a Dados

O ADO.NET cumpriu seu objetivo: Trouxe um modelo de acesso a dados poderoso e bastante flexível, otimizado para aplicações remotas.

por Fernando Amaral

9. Acesso a Dados

Um das novidades no lançamento do primeira versão do Framework .NET, foi o ADO.NET, sucessor do ADO que até então era o modelo de acesso a dados da Microsoft.

Surgiu com a promessa de ser um modelo desconectado para um mundo conectado: sua arquitetura era preparada para a Internet, onde as aplicações poderiam abrir uma conexão e recuperar dados, fechar a conexão, trabalhar desconectados e só voltar a abrir a conexão no momento de atualizar a fonte de dados.

O ADO.NET cumpriu seu objetivo: Trouxe um modelo de acesso a dados poderoso e bastante flexível, otimizado para aplicações remotas.

SQLDataSource e GridView

O modelo de acesso a dados proposto pelo ADO.NET embora poderoso, é extramente complexo: para executar operações em uma fonte de dados o desenvolvedor teria que utilizar uma grande quantidade de classes como Connection, Transaction, Command, DataReader, DataAdapter, DataSet, DataTable etc. e entender como elas se inter-relacionam.

Na verdade isso não mudou desde a versão 1.1: A estrutura do ADO.NET é basicamente a mesma. Porém uma novidade a partir do .NET 2.0 foram algumas classes denominados DataSource, entre eles o SQLDataSource.

Estudamos no módulo II a classe SiteMapDataSource

As classes DataSource é uma classe que nos permite acessar diversos bancos de dados relacionais ou não, e que internamente utiliza diversas objetos do ADO.NET, tornando o seu uso mais simples através de um único objeto. Por exemplo, para retornar um conjunto de dados de um servidor SQLServer, por exemplo, você  precisar no mínimo de um SQLConnection, um SQLCommand e um SQLDataReader. Por outro lado você pode obter o mesmo conjunto de dados utilizando um único SQLDataSource, que internamente fará uso destes objetos.

Apesar do SQLDataSource fornecer um modelo simplificado e mais amigável, você deve conhecer os componentes do ADO.NET, pois a verdadeira funcionalidade de acesso a dados está nestas classes. ADO.NET será estudado mais adiante neste capitulo.

Para demonstrar como é fácil e rápido criar uma aplicação que retorne uma tabela de um banco de dados em uma página ASP.NET, vamos demonstrar passo a passo a criação de uma aplicação utilizando o SQLDataSource.

Uma tabela sem nenhum código

Neste exemplo você verá que é possível retornar um conjunto de dados sem escrever sequer uma linha de código:

Nestes exemplos estaremos utilizando o banco de dados AdventureWorks, que é o banco de dados de demonstração do SQL Server 2005.  Você pode baixar este banco de dados do site oficial do SQL Server e instalá-lo, inclusive na versão Express. Se preferir você pode praticar com qualquer outro banco de dados, inclusive da versão 2000.

Crie uma nova aplicação ASP.NET;

Da barra de ferramentas Data, arraste um controle SQLDataSorce para o Web Form Default.aspx;

Na Smart Tag do controle, clique em Configure DataSource;

É exibido um assistente de configuração. Na primeira etapa você deve informar com qual servidor deseja realizar a conexão. Para isto clique em New Connection, selecione Microsoft SQL Server em Data Source e .NET FrameWork Data Provider for SQL Server em Data Provider, em seguida clique em continue.

Em seguida é exibida a caixa de dialogo Add Connection, onde você deve informar as opções de conexão com os servidor:

De volta ao assistente, clique em Next;

Neste passo você deve definr uma clausula Where e / ou  OrderBy. Se você informar que deseja salvar a String de Conexão. Mantenha a opção marcada com o nome padrão para a string de conexão;

No próximo passo você deve montar a consulta SQL. Informe uma tabela e as colunas que deseja incluir na consulta. Se incluir a chave primaria entre as colunas de seus Select, através da opção Advanced poderá ainda solicitar que sejam criados comandos SQL para inclusão, alteração e exclusão:

A próxima etapa permite que você teste a consulta SQL Criada. Clique em Finish.

Se você examinar seu arquivo aspx poderá notas que o assistente configurou diversas propriedades do controle:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>" DeleteCommand="DELETE FROM [Department] WHERE [DepartmentID] = @DepartmentID" InsertCommand="INSERT INTO [Department] ([Name], [GroupName], [ModifiedDate]) VALUES (@Name, @GroupName, @ModifiedDate)" SelectCommand="SELECT * FROM [Department]" UpdateCommand="UPDATE [Department] SET [Name] = @Name, [GroupName] = @GroupName, [ModifiedDate] = @ModifiedDate WHERE [DepartmentID] = @DepartmentID"> <DeleteParameters> <asp:Parameter Name="DepartmentID" Type="Int16" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="GroupName" Type="String" /> <asp:Parameter Name="ModifiedDate" Type="DateTime" /> <asp:Parameter Name="DepartmentID" Type="Int16" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="GroupName" Type="String" /> <asp:Parameter Name="ModifiedDate" Type="DateTime" /> </InsertParameters> </asp:SqlDataSource>

Além dos comandos de seleção, atualização, exclusão e inclusão, foram criados os parâmetros para a execução destas operações. Isto foi possível graças a inclusão da chave primária entre os campos a serem utilizados durante o assistente.

Vamos agora exibir os dados na página:

Adicione um GridView da barra de ferramentas data. Use a opção Auto Format da Smart Tag do controle para definir um layout para a tabela.

O GridView será estudado melhor neste capitulo, por enquanto ele é um acessório para o estudo do SQLDataSource.

Ainda na Smart Tag, selecione SQLDataSource1 na opção ChoseDataSource.

Marque as opções Enable Paging. Enable Sorting, Enable Editing, Enable Deleting e Enable Selecion na Smart Tag:

Por fim, adicione a propriedade DataKeyNames, o(s) nome(s) do(s) campo(s)  que compõe a chave primária da tabela.

Rode a aplicação

Parabéns! Você acabou de criar um software para exibir uma tabela em uma aplicação Web, onde é possível ordenar, paginar, alterar e excluir registros sem escrever nenhuma única linha de código!!

Obviamente se a operação de inclusão ou atualização violar alguma regra de integridade do banco de dados, será gerado um erro.

As operações de atualização e exclusão só ocorrerão de forma correta desde que você tenha as instruções SQL respectivas configuradas de forma correta.

Vamos ver agora algumas propriedades importantes do SQLDataSource:

Cache

O SQLDataSource possui embutido suporte a cache. Isto significa que após a execução de uma consulta, o conjunto de resultados será armazenados na memória do servidor. Na próxima consulta ao invés de recuperar as informações do banco de dados, o SQLDataSource irá recuperá-los do cache.

Para habilitar cache no SQLDataSource, basta definir a propriedade EnableCache como True. A propriedade CacheDurantion deve conter o tempo, em segundos, que os dados serão mantidos em cache antes de nova consulta.

DataSourceMode

Esta propriedade indica como os dados serão carregados: Em um objeto DataSet ou um objeto DataReader. No modo DataSet o controle que estiver ligado ao SQLDataSource terá mais funcionalidades, como paginação e ordenação. No modo DataReader estas funcionalidades não estarão disponíveis, porém por se tratar de um cursor unidirecional e somente leitura, seu desempenho é superior.

ConnectionString

Através desta propriedade é definida a string de conexão, que contem as informações para conexão como sistema gerenciador de banco de dados.

Executando uma consulta com parâmetros

Você pode querer definir dinamicamente um valor para a clausula where de sua consulta. Para isto criamos um parâmetro e definimos um valor em tempo de execução.

O ASP.NET 2.0 prove funcionalidade para diversos tipos de parâmetros, como uma QueryString, um controle, um cookie, uma variável de sessão entre outros.

Para exemplificar, vamos filtrar nossa consulta a partir de um valor da QueryString do formulário, para isso faremos uma pequena alteração na propriedade SelectQuery do SQLDataSource, adicionando uma clausula where e um parâmetro de nome DepartmentID do tipo QueryString:

Ao rodar a aplicação passando um valor para a QueryString, nossa consulta é filtrada:

Neste segundo exemplo, vamos obter o valor para passar ao parâmetro através de um controle. Primeiramente adicionamos um controle textbox ao formulário, em seguida modificamos o mesmo parâmetro criado há pouco:

Coloque um button no formulário para causar um postback, preencha um valor no textbox e clique no button, mesmo sem código nenhum nos eventos de servidor o filtro deverá funcionar graças ao viewstate do textbox.

Você pode utilizar parametros também para as operações de atualização, exclusão e inclusão.

Mais GridView

Na primeira versão do ASP.NET, um dos controles mais famoso foi o DataGrid. Com ele era possível exibir dados tabulares com pouco código. No ASP.NET 2.0 ele foi substituído pelo GridView, um controle ainda mais poderoso e que permite que muita coisa possa ser feita sem uma linha de código sequer.

Na seção anterior estudamos rapidamente o GridView, vamos agora entende-lo melhor.

Editando Colunas

Você já viu como configurar um SQLDataSource. Faça isso e adicione um GridView a um Web Form. Na Smart Tag do GridView indique o SQLDataSource no item Choose Data Source. Isto também pode ser feito através da propriedade DataSourceID. Ainda na Smart Tag, selecione Edit Colums. É exibido o editor de colunas:

A área de edição de colunas é dividida em três partes.

Na pequena área acima à esquerda temos a relação de campos disponíveis. BoundFields são campos oriundos de nossa fonte de dados, se você já ligou seu DataGridView ao SQLDataSource, os campos já devem aparecer disponíveis nesta sessão. Podemos ainda definir campos como CheckBoxField, HyperLinkField, ButtonField, ImageField, CommandField que podem ser Edit, Update, Cancel ou Select ou Delete, e TemplateField, onde podemos criar um campo Customizado.

Em baixo a esquerda vemos os campos selecionados para exibição em nosso GridView, e a direita, podemos configurar as propriedades de cada campo. Você pode, por exemplo, criar uma mascara de formatação para exibir um valor de moeda, ou mesmo mudar o texto do cabeçalho do campo.

Na tela acima, observe que é exibido entre os campos selecionados, um campo CommandField. Isto porque na Smart Tag do controle foram marcadas as opções de habilitar seleção, exclusão e edição. Podemos definir a aparência destes botões.

Por exemplo, se você quer que eles apareçam a direita do GridView e não a esquerda, basta move-los para baixo. A propriedade ButtonType permite os mesmos sejam exibidos como, por exemplo, botões ou imagens ao invés de links. Se você optar por imagem deve informar o caminho delas através da propriedade ImageUrl, formado pelo prefixo do nome do botão. Outra propriedade importante é definir o texto de cada botão.

Vejam um exemplo de um GridView com alguma personalização:

Se seu DataSource tiver um campo do tipo boleano, será incluido automaticamente entre as colunas disponíveis um CheckBoxFiled

Não vamos estudar todas as propridades de cada tipo de campo. O importante é você saber onde encontra-las.

Utilizando um DetailView

Uma outra novidade a partir do ASP.NET 2.0 é o DetailView. Sua principal utilidade é exibir detalhes de um único registro, que normalmente são ocultados num GridView por otimização de espaço. Você também pode utilizá-lo para incluir ou até mesmo excluir um registro.

Para utilizá-lo basta adicionar ao Web Form e configurar suas principais propriedades através da smart tag, não esquecendo de informar a fonte de dados e de configurar as colunas. O ideal é que colunas que estejam marcadas como invisíveis no GridView, sejam visualizadas no DetailView.

Para que o controle se mantenha atualizado, ou seja, exiba sempre os detalhes do registro selecionado no GridView, é preciso adicionar um segundo SQLDataSource, cujo comando de seleção tenha um parâmetro que aponte para campo chave do item selecionado no GridView. A tela abaixo exibe a construção de um parâmetro. Observe que é indicada a coluna, o operador, a origem (source) que é control, e em ControlID informamos o GridView que fornecerá o valor para o parâmetro.

Abaixo você pode observar a aplicação em execução:

FormView

Com certa semelhança com o DetailView, o FormView é formado por diversos templates com controles como labels e caixas de texto. È ideal para exibir dados na tela em forma de formulário. Também possui templates prontos e incluir um novo registro. Se seus SQLDataSource possui a instrução Insert, o FormView vai estar pronto para a inclusão de novos registros. No imagem abaixo podemos ver um FormView conectado a um SQLDataSource:

Abaixo você pode observar os templates que formam o FormsView. Cada um deles pode ser configurado de acordo com sua funcionalidade.

Controles ListView e DataPager

Os controles ListView e DataPager foram introduziados no ASP.NET .35. Desde o lançamento da primeira versão do ASP.NET a Microsoft incluiu em suas IDEs diversos controle de listagem de dados: DataGrid, GridView, Repeater entre outros. Embora sejam todos controles de listagem de dados, eles tem focos diferentes: o Repeater permite maior controle sobre o conteúdo renderizado, o DataList veio otimizado para exibição de dados em várias colunas etc. A evolução mesmo ficou por conta dos controles “multifuncionais”, inicialmente o DataGrid na versão 1.X, o GridView surgindo na versão 2.0 trazendo novas características e agora, no ASP.NET 3.5, o ListView.

O ListView é tudo de bom: Permite maior controle sobre o conteúdo gerado através de diversos eventos e templates, e, na minha opinião, o mais interessante: agora com um InsertItemTemplate. O que isto significa? As operações de insert para o DataGrid e para o GridView eram órfãs e tinham que ser tratadas a parte. Particularmente, com o GridView, eu colocava um Formview em modo de inserção no EmptyDataTemplate do GridView, e, ao clicar em um botão inserir normalmente colocado sobre o cabeçalho de uma coluna, desconectava o controle de seu datasource. O efeito era que a listagem de dados desaparecia dando lugar ao controle para inserção.Agora finalmente posso ter todas as operações (Select, Insert, Update, Delete) em um mesmo controle! A inserção é por padrão feita próxima ao rodapé do Listview.

ListView na prática

Se você já usou algum ancestral do ListView não terá qualquer dificuldade em se familiarizar com este novo controle. Neste exemplo utilizo a boa e velha base de dados NorthWind, rodando em uma instancia do SQL Server 2005 Express. Vale lembrar que você pode baixar esta base de dados como um download independente a qualquer momento, através do site da Microsoft, ou utilize qualquer base de dados de sua preferência.

Primeiramente abra o Visual Studio 2008, clique em New Web Site. Selecione .NET Framework 3.5 em na parte superior da janela, marque a opção ASP.NET Web Site e escolha a linguagem de sua preferência.

Abra a guia Data na caixa de ferramentas. Neste tutorial vamos trabalhar com três controles: ListView, DataPager e SqlDataSource. Observe na asp3imagem abaixo os controles circulados:

Inicialmente jogue um controle ListView sobre o WebForm.

Na SmartTag do controle, clique em New data source, desta forma o Visual Studio vai criar um novo controle de acesso a dados. Poderíamos ter feito isto manualmente, arrastando um controle SqlDataSource para o WebForm.

É exibido o DataSource Configuration Wizard. Na primeira etapa devemos informar a origem dos dados. Selecione Database e clique em Ok.

Na próxima etapa devemos configurar a conexão:

Clique em New Connection e informe os dados de sua conexão, como no exemplo abaixo:

De volta ao assistente, clique em Next. Nesta etapa você deve informar se deseja salvar a string de conexão no arquivo de configuração da aplicação (Web.config). Marque sim e clique em Next.

Agora é o momento de informar a tabela ou a consulta que vai dar origem aos dados.

No exemplo abaixo selecionei a tabela Employees com algumas colunas.

Antes de prosseguir, clique em Advanced e marque a opção Generate Insert, Update, and Delete statements para que o Visual Studio produza automaticamente as instruções Sql para que o ListVew possa incluir, atualizar e excluir dados automaticamente.

Importante observar que para esta opção estar habilitada é fundamental que entre as colunas selecionadas estejam aquelas que compõem a chave primária da tabela. No exemplo acima trata-se da coluna EmployeeID.

Clique em Next, nesta última opção do assistente você pode testar a consulta gerada. Clique em Finish.

Se observarmos o código gerado para o ListView até o momento é demasiadamente simples, pois ainda não definimos nenhuma propriedade além de DataSourceID:

<asp:ListView ID="ListView1" runat="server" DataSourceID="SqlDataSource1"> </asp:ListView>

Vamos tratar de configurar o ListView. Voltando a área de design, na Smart tag do ListView clique em configure ListView:

Nesta janela podemos configurar as funcionalidade principais do ListView. Primeiramente um Layout, teste todas as opções e veja o comportamento na janela Preview. Abaixo você pode selecionar um estilo. Em opções devemos informar se o ListView vai ter habilitadas funções de edição, inserção, exclusão e paginação. Para este exemplo deixa marcado todas as opções, com exceção de paginação:

Clique em ok e rode a aplicação. Observe que temos agora uma listagem de dados 100% funcional. Observe que a linha de inserção é incluída no junto ao rodapé da listagem:

DataPager

Assim como seus ancestrais, o ListView tem paginação nativa embutida. Porém agora temos um controle de paginação à parte: O DataPager.

Curioso é que o DataPager funciona apenas com o ListView. Você deve estar se perguntando: Então porque usá-lo ao invés da paginação “embutida” no ListView? Como um controle separado temos mais poder sobre como queremos nossa paginação. Também devemos lembrar que o DataPager não foi concebido para funcionar exclusivamente com o ListView, mas com qualquer controle que implemente a interface IPageableItemContainer. No ambiente do ASP.NET 3.5 este é o único controle nativo que implementa tal interface.

Mãos a obra. Arraste um controle DataPager para o Web Form. Na janela de propriedades do controle, defina PagedControlId para ListView1 e PageSize para 5, pois a tabela que escolhi para o exemplo possui menos que 10 registros:

Na Smart Tag do DataPager, defina um estilo de paginação, que pode ser com botões do tipo próximo/anterior ou mesmo numérica. Para o exemplo escolhi Próximo/anterior:

Neste momento, ao rodar a aplicação, já temos nosso ListView com as funcionalidades de paginação em funcionamento:

Ado.net

Como dissemos no inicio do capitulo, o ADO.NET é foi criado para ser um modelo de dados desconectado, pronto para Web.

È formando por uma série de objetos, alguns independentes de fonte de dados, outros não. Um objeto independe de fonte pode abrigar ao mesmo tempo dados de origens diversas, como por exemplo, uma tabela de um banco de dados Oracle e outra de um arquivo Access.

O Objeto de conexão para o SQLSever é o SQLConnection. Havendo uma conexão os dados podem ser recuperados para um SQLDatareader, SQLCommand ou mesmo um DataSet.

O SQLCommand permite executar um comando SQL contra uma fonte de dados. Pode trabalhar apenas com um SQLConnection ou em conjunto com outras classes ADO.NET.

Um SQLDataReader é um cursor unidirecional e somente leitura. É ótimo para leitura de dados ou geração de relatórios, pois é veloz. Porém não permite qualquer alteração nos dados, além de monopolizar a conexão com o banco de dados enquanto esta operando.

Um DataSet é um pequeno banco de dados em memória. Você não precisa de uma conexão permanente com o banco de dados enquanto estiver utilizando os dados. Você pode abrir a conexão, recuperar os dados, executar alterações, exclusões ou inclusões, abrir novamente a conexão e atualizar a fonte de dados. Dentro de uma DataSet podemos ter uma ou mais DataTables, que são tabelas, que podem ter origens de fontes de dados independentes. Podemos ainda ter DataRelations, que representam relações entre tabelas. Uma DataTable é formado por DataRows, que representam colunas. Outra classe importante é DataView, que é exatamente o que parece: Uma visão de dados. Um DataTable possui um DataView padrão, outros podem ser criados. Todos os objetos descritos neste parágrafo são independentes da fonte dos dados, pois são armazenados em memória no formato XML.

Agora como transformar dados de uma fonte especifica em um DataSet? Esta tarefa é do SQLDataAdapter , que faz a transformação dos dados entre a sua origem, através da conexão, até o DataSet e vice-versa. O SQLDataAdapter devolve a conexão no estado que a encontrou: Se estava aberta, mantém aberta. Se estava fechado, abre, executa a operação e fecha a conexão.

Vamos ver as principais classes do ADO.NET

Embora existam provedores para acessar as mais variadas fontes de dados, iremos demostrar as funcionalidades do ADO.NET com classes de acesso ao SQLServer. A funcionalidade das demais classes é bem semelhante.

SQLConnection

Para utilizar as classes ADO.NET para SQL Server, você deve importar o namespace system.data.sqlclient. Para as classes independentes de fonte de dados, como o DataSet, o namespace é sytem.data

O SQLConnection representa uma conexão com um banco de dados SQLServer. Suas principais propriedades são ConnectionString, que é a string com os parâmetros de conexão com o servidor. Entre os métodos temos Open, que abre a conexão, e close, que fecha a mesma. A propriedade State retorna o estado atual da conexão. Vejamos o exemplo abaixo:

Dim Conexao As New SqlConnection Conexao.ConnectionString = "Data Source=LOCALHOST\SQLEXPRESS;" & _ "Initial Catalog=AdventureWorks;Integrated Security=True" Conexao.Open() "Executa algum código Conexao.Close()

SqlConnection Conexao = new SqlConnection(); Conexao.ConnectionString = "Data Source=LOCALHOST"\"SQLEXPRESS;" + "Initial Catalog=AdventureWorks;Integrated Security=True"; Conexao.Open(); //Executa algum código Conexao.Close();

Ao abrir uma conexão mil coisas podem dar errado: O servidor não estar disponível, a string de conexão esta errada, o usuário não ter permissão de acesso...só para citar algumas. Por este motivo a abertura da conexão acima deveria ser protegida em um bloco try...except. Isto foi omitido para fins de clareza do código...no mundo real, é uma prática que deve ser considerada.

SQLCommand

A classe SQLCommand permite que sejam executados quaisquer comando SQL. Para ser executado com sucesso precisa de uma instrução SQL ou de um nome de tabela ou procedure, a ser informada na propriedade CommandText e uma conexão informada através da propriedade Connection, que deve ser um objeto SQLConnection.

A execução do comando pode ser feito através de quatro métodos distintos:

ExecuteNonQuery: Ideal para execução de instruções que não retornam um conjunto de dados. Retorna um valor inteiro informando o número de linhas afetadas;

ExecuteReader: Para utilização com um SQLDataReader, a ser estudado na próxima sessão;

ExecuteScalar: Retorna apenas a primeira coluna da primeira linha, o restante dos valores são ignorados. Ideal para sumarização de valores.

O exemplo abaixo retorna o total de registros de uma tabela e exibe em um textbox, para funcionar perfeitamente basta encaixá-lo no comentário do exemplo anterior, de demonstração da conexão:

Dim comando As New SqlCommand comando.Connection = Conexao comando.CommandText = "SELECT COUNT(*) FROM Sales.Currency" Dim a As Integer = comando.ExecuteScalar TextBox1.Text = Convert.ToString(a)

SqlCommand comando = new SqlCommand(); comando.Connection = Conexao; comando.CommandText = "SELECT COUNT(*) FROM Sales.Currency"; int a = Convert.ToInt32(comando.ExecuteScalar()); TextBox1.Text = Convert.ToString(a);

SQLDataReader

Como dito o SQLDataReader é um cursor unidirecional e somente leitura, porém muito veloz. Você pode ligar seu resultado diretamente a um controle:

Dim comando As New SqlCommand comando.Connection = Conexao comando.CommandText = "SELECT CurrencyCode,Name FROM Sales.Currency" Dim reader As SqlDataReader = comando.ExecuteReader GridView1.DataSource = reader GridView1.DataBind()

SqlCommand comando = new SqlCommand(); comando.Connection = Conexao; comando.CommandText = "SELECT CurrencyCode,Name FROM Sales.Currency"; SqlDataReader reader = comando.ExecuteReader(); GridView1.DataSource = reader; GridView1.DataBind();

Neste outro exemplo, ele é ligado a um DropDownList. Note que nome da moeda é ligado a propriedade TextField, enquanto o código a propriedade Valuefield, desta forma será exibida a nome da moeda para o usuário, mas programaticamente poderemos facilmente recuperar o código da moeda selecionada:

Dim comando As New SqlCommand comando.Connection = Conexao comando.CommandText = "SELECT CurrencyCode,Name FROM Sales.Currency" Dim reader As SqlDataReader = comando.ExecuteReader DropDownList1.DataSource = reader DropDownList1.DataValueField = "CurrencyCode" DropDownList1.DataTextField = "Name" DropDownList1.DataBind()

SqlCommand comando = new SqlCommand(); comando.Connection = Conexao; comando.CommandText = "SELECT CurrencyCode,Name FROM Sales.Currency"; SqlDataReader reader = comando.ExecuteReader(); DropDownList1.DataSource = reader; DropDownList1.DataValueField = "CurrencyCode"; DropDownList1.DataTextField = "Name"; DropDownList1.DataBind();

No próximo exemplo, temos apenas uma pequena alteração: È passado um argumento a sobrecarga do construtor do ExecuteReader: CommandBehavior, que é um enumerador que vai determinar o comportamento do SQLDataReader. Neste exemplo, CommandBehavior.CloseConnection fecha a conexão com o banco de dados após a utlização:

Dim comando As New SqlCommand comando.Connection = Conexao comando.CommandText = "SELECT CurrencyCode,Name FROM Sales.Currency" Dim reader As SqlDataReader = comando.ExecuteReader(CommandBehavior.CloseConnection) DropDownList1.DataSource = reader DropDownList1.DataValueField = "CurrencyCode" DropDownList1.DataTextField = "Name" DropDownList1.DataBind()

SqlCommand comando = new SqlCommand(); comando.Connection = Conexao; comando.CommandText = "SELECT CurrencyCode,Name FROM Sales.Currency"; SqlDataReader reader = comando.ExecuteReader(CommandBehavior.CloseConnection); DropDownList1.DataSource = reader; DropDownList1.DataValueField = "CurrencyCode"; DropDownList1.DataTextField = "Name"; DropDownList1.DataBind();

Finalmente, para percorrer os itens de um SQLDataReader, podemos utilizar o método Read, que retorna verdadeiro e avança um registro, enquanto houverem linhas disponíveis. Neste exemplo as linhas são adicionadas a um ListBox:

Dim comando As New SqlCommand comando.Connection = Conexao comando.CommandText = "SELECT CurrencyCode,Name FROM Sales.Currency" Dim reader As SqlDataReader = comando.ExecuteReader(CommandBehavior.CloseConnection) While reader.Read ListBox1.Items.Add(reader.Item("Name")) End While

SqlCommand comando = new SqlCommand(); comando.Connection = Conexao; comando.CommandText = "SELECT CurrencyCode,Name FROM Sales.Currency"; SqlDataReader reader = comando.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { ListBox1.Items.Add(Convert.ToString(reader["Name"])); }

Parâmetros

A utilização dos classes de parametros do ADO.NET são um ponto importante no quesito segurança, pois eliminam o risco de injeção de SQL

A classe parâmetros permite que sejam criados parâmetros para a execução de instruções sql. Na instrução o parâmetro deve ser identificado por @ mais um identificador único. O objeto parâmetro deverá ter o mesmo nome o qual vai passar o valor. Vejamos o exemplo abaixo:

Dim comando As New SqlCommand comando.Connection = Conexao comando.CommandText = "SELECT * FROM Person.Contact " & _ " where ContactID = @ContactID" Dim parametro As New SqlParameter("@ContactID", SqlDbType.Int) parametro.Value = Convert.ToInt32("1") comando.Parameters.Add(parametro) Dim reader As SqlDataReader = comando.ExecuteReader(CommandBehavior.CloseConnection) GridView1.DataSource = reader GridView1.DataBind()

SqlCommand comando = new SqlCommand(); comando.Connection = Conexao; comando.CommandText = "SELECT CurrencyCode,Name FROM Sales.Currency"; SqlParameter parametro = new SqlParameter("@ContactID", SqlDbType.Int); parametro.Value = Convert.ToInt32("1"); comando.Parameters.Add(parametro); SqlDataReader reader = comando.ExecuteReader(CommandBehavior.CloseConnection); GridView1.DataSource = reader; GridView1.DataBind();

Primeiro instanciamos o objeto SQLParameter utilizando um de seus construtores. Existem sete sobrecargas. No exemplo, é passado o nome do parâmetro e o tipo. Em seguida atribuímos um valor ao parâmetro, e finalmente o adicionamos a coleção de parâmetros do comando.

O objeto parametro não precisa ter o mesmo nome do campo da tabela

Se você estiver usando outro provider, como o OleDb, os parametros devem ser identificados por “?”. Os valores devem ser fornecidos na ordem em que eles estão no SQL, pois não há como nomealos

SQLDataAdapter , DataSet e DataTable

A unica função do SQLDataAdapter é fazer a ligação entre a fonte de dados e o DataSet. Um DataAdapter, assim como um SQLDataSource, pode receber até quatro instruções SQL: Para consulta, inclusão, exlcusão e alteração.

Existem quatro sobrecargas do contrutor de um DataAdapter. Se você não quizer instanciar objetos connection e command, pode utilizar uma sobrecarga que recebe o comando sql de seleção e a string de conexão, que estes objetos são criados implicitamente.

O preenchimento de um DataSet por um DataAdapter é feito através do método fill:

Dim Adp As New SqlDataAdapter("SELECT * FROM Person.Contact ",_ Conexao) Dim Ds As New DataSet Adp.Fill(Ds) GridView1.DataSource = Ds GridView1.DataBind()

SqlDataAdapter Adp = new SqlDataAdapter ("SELECT * FROM Person.Contact ", Conexao); DataSet Ds = new DataSet(); Adp.Fill(Ds); GridView1.DataSource = Ds; GridView1.DataBind();

A atualização dos dados é feita através do método update. Apenas quando o método é executado que qualquer alteração nos dados serão replicados ao banco de dados, pois, lembrando, o DataSet trabalha desconectado.

Outro aspecto importante é que o para fazer uma atualização, seja exclusão, inclusão ou alteração, o Adapter precisa obter os comando SQL para tais operações. Através da classe CommadBuilder podemos gerar automaticamente estas instruções para o SQLDataAdapter. No exemplo abaixo, o campo FirstName da tabela Person.Contact é alterado para Gustavo II:

Dim Adp As New SqlDataAdapter("SELECT * FROM Person.Contact ",_ Conexao) Dim CmdBuilder As New SqlCommandBuilder(Adp) Dim Ds As New DataSet Ds.Tables(0).Rows(0).Item("FirstName") = "Gustavo II" Adp.Update(Ds)

SqlDataAdapter Adp = new SqlDataAdapter ("SELECT * FROM Person.Contact ", Conexao); SqlCommandBuilder CmdBuilder = new SqlCommandBuilder; DataSet Ds = new DataSet(); Ds.Tables[0].Rows[0]["FistName"] = "Gustavo II"; Adp.Update(Ds);

Como o DataSet é um pequeno banco de dados em memória, podemos adicionar varias tabelas ao mesmo, inclusive de fontes diferentes. No exemplo a seguir dois objetos DataTable são preenchidos e depois adicionados a um mesmo DataSet.

Dim Adp1 As New SqlDataAdapter("SELECT * FROM Person.Contact ",_ Conexao) Dim Dt1 As New DataTable Adp1.Fill(Dt1) Dim Adp2 As New SqlDataAdapter("SELECT * FROM Person.ContactType ",_ Conexao) Dim Dt2 As New DataTable Adp2.Fill(Dt2) Dim Ds As New DataSet Ds.Tables.Add(Dt1) Ds.Tables.Add(Dt2)

SqlDataAdapter Adp1 = new SqlDataAdapter ("SELECT * FROM Person.Contact ", Conexao); DataTable Dt1 = new DataTable(); Adp1.Fill(Dt1); SqlDataAdapter Adp2 = new SqlDataAdapter ("SELECT * FROM Person.ContactType ", Conexao); DataTable Dt2 = new DataTable(); Adp2.Fill(Dt2); DataSet Ds = new DataSet(); Ds.Tables.Add(Dt1); Ds.Tables.Add(Dt2);

Finalmente, neste último exemplo adicionamos uma linha a uma tabela. Primeiramente preechemos a tabela através de um DataAdapter. Em seguida instanciamos um objeto DataRow a partir da tabela, ou seja, com sua definição de dados, preenchemos os valores dos campos, adicionamos a coleção de dados do DataTable e finalmente invocamos o método Update do Adapter para atualizar a fonte de dados:

Dim Adp As New SqlDataAdapter("SELECT * FROM Sales.Currency ", Conexao) Dim CmdBuilder As New SqlCommandBuilder(Adp) Dim Dt As New DataTable Adp.Fill(Dt) Dim Dr As DataRow = Dt.NewRow Dr("CurrencyCode") = "BRU" Dr("Name") = "Brazilian UNReal" Dr("ModifiedDate") = "01/01/2006" Dt.Rows.Add(Dr) Adp.Update(Dt)

SqlDataAdapter Adp = new SqlDataAdapter ("SELECT * FROM Sales.Currency ", Conexao); SqlCommandBuilder CmdBuilder = new SqlCommandBuilder(Adp); DataTable Dt = new DataTable(); Adp.Fill(Dt); DataRow Dr = Dt.NewRow(); Dr["CurrencyCode"] = "BRU"; Dr["Name"] = "Brazilian UNReal"; Dr["ModifiedDate"] = "01/01/2006"; Dt.Rows.Add(Dr); Adp.Update(Dt);

Observe que neste último exemplo sequer utlizamos um DataSet!

SQLTransaction

O ADO.NET também possui classes que dão suporte a controle de transações. Devemos declarar uma transação e iniciá-la pelo objeto de conexão e ainda atribuí-la aos objetos que farão parte da operação. Para confirmar as operações chamamos o método commit do objeto Transaction, para desfazer o método é rollback.

No exemplo abaixo, adaptado de exemplo anterior, é criado um objeto SQLTransaction e atribuído a propriedade Transaction do comando insert do DataDdapter, que é o comando utilizado para inserir um novo registro. O método update do DataAdapter, que é o método que de fato atualiza a base de dados, é chamado em um bloco protegido try..cacth. Se tudo der certo a transação é confirmada. Caso algo de errado a transação é desfeita.

Dim Trans As SqlTransaction = conexao.BeginTransaction Dim Adp As New SqlDataAdapter("SELECT * FROM Sales.Currency ",_ Conexao) Adp.InsertCommand.Transaction = Trans Dim CmdBuilder As New SqlCommandBuilder(Adp) Dim Dt As New DataTable Adp.Fill(Dt) Dim Dr As DataRow = Dt.NewRow Dr("CurrencyCode") = "BRU" Dr("Name") = "Brazilian UNReal" Dr("ModifiedDate") = "01/01/2006" Dt.Rows.Add(Dr) Try Adp.Update(Dt) Trans.Commit() Catch ex As Exception Trans.Rollback() End Try

SqlTransaction Trans = Conexao.BeginTransaction(); SqlDataAdapter Adp = new SqlDataAdapter( "SELECT * FROM Sales.Currency ", Conexao); Adp.InsertCommand.Transaction = Trans; SqlCommandBuilder CmdBuilder = new SqlCommandBuilder(Adp); DataTable Dt = new DataTable(); Adp.Fill(Dt); DataRow Dr = Dt.NewRow(); Dr["CurrencyCode"] = "BRU"; Dr["Name"] = "Brazilian UNReal"; Dr["ModifiedDate"] = "01/01/2006"; Dt.Rows.Add(Dr); try { Adp.Update(Dt); Trans.Commit(); } catch (Exception s) { Trans.Rollback(); }