LINQ To SQL - O/R Designer , DataContext e Stored Procedures

Uma das grandes novidades na nova versão da plataforma .NET é o LINQ , e, dentre as variações do LINQ  o LINQ To SQL. Para usar o LINQ To SQL em suas aplicações basta incluir uma referência usando o template LINQ To SQL classes. Fazendo isso você terá acesso ao Descritor Objeto Relacional ou Object Relational Designer (O/R Designer).

O Descritor Objeto Relacional - Object Relational Designer (O/R Designer) - fornece uma interface visual para criar e editar classes  LINQ to SQL , classes das entidades - entity classes-  que são baseadas em objetos do banco de dados.

O descritor O/R é usado para criar um modelo de objetos na aplicação que efetua o mapeamento para os objetos do banco de dados gerando um DataContext fortemente tipado que é usado para enviar e receber dados entre as classes das entidades e o banco de dados; além disso o descritor O/R também permite efetuar o mapeamento de stored procedures e funções para o métodos do DataContext com o intuito de retornar e popular as classes das entidades provendo ainda a habilidade de desenhar relacionamentos herdados entre as classes das entidades.

A classe DataContext é uma classe LINQ to SQL que atua como uma ponte entre o banco de dados SQL Server e as classes das entidades LINQ To SQL mapeadas para o banco de dados. Ela contém a informação e os métodos para efetuar a conexão com o banco de dados e manipular os dados.

Essa classe possui diversos métodos que você pode chamar , como o método SubmitChanges que envia as atualizações das classes LINQ To SQL para o banco de dados.

Além disso você pode criar métodos adicionais na classe DataContext para mapear stored procedures e funções de forma que executando os métodos criados as stored procedures e as funções que foram mapeadas pela classe DataContext serão executadas.

O LINQ To SQL trata as stored procedures e funções da mesma forma, sendo que ambas são mapeadas para as classes das entidades usando o mesmo StoredProcedureAttribute.

Existem dois tipos distintos de métodos DataContext (que mapeiam para stored procedures e funções) que você pode criar e incluir no painel de métodos do  Object Relational Designer (O/R Designer) :

Após essa teoria vamos mostrar a parte prática realizando as seguintes tarefas:

O que você vai precisar ?

  1. Visual Basic 2008 Express Edition ou Versão trial do VS 2008 :http://msdn2.microsoft.com/en-us/vstudio/products/aa700831.aspx
  2. SQL Server Express 
  3. O banco de dados Northwind.mdf para SQLServer (Veja como instalar em : http://msdn2.microsoft.com/en-us/library/8b6y4c7s.aspx )

Abra o VB 2008 Express Edition e a partir do menu File crie um novo projeto do tipo Windows Forms Application chamado : LINQ_OR_app;

O projeto LINQ_OR_app será criado e incluído no Solution Explorer apresentando o formulário form1.vb;

Agora vamos incluir a referência a LINQ To SQL classes em nosso projeto para ter acesso ao Descritor Objeto Relacional (O/R Designer). As classes são criadas e armazenadas nos arquivos LINQ To SQL Classes ; os arquivos são identificados pela extensão .dbml.

Clique com o botão direito sobre o projeto no Solution Explorer e selecione Add-> New Item. A seguir selecione o template LINQ To SQL classes e informe o nome Northwind.dbml;

O Descritor Objeto Relacional (O/R Designer) será aberto e o arquivo LINQ To SQL Northwind.dbml vazio será incluído no Solution Explorer;

A interface vazia representa um DataContext que esta pronto para ser configurado onde o nome do DataContext corresponde ao nome que foi fornecido para o arquivo .dbml.

Como neste exemplo informamos o nome Northwind.dbml o nome do DataContext será NorthwindDataContext. Para verificar o nome clique na área vazia e verifique a janela de propriedades:

O O/R Designer apresenta dois painéis :(Para visualizar os dois painéis clique com o botão direito do mouse sobre o O/R Designer e selecione Show Methods Pane)

Criando as classes das entidades Customer e Order

Agora vamos criar as classes LINQ To SQL que serão mapeadas para as tabelas do banco de dados. Fazemos isso arrastando e soltando a partir do DataBase Explorer para o O/R Designer as tabelas Customers e Orders , neste momento serão criadas as classes de entidades que  mapeiam para as tabelas no banco de dados.

Serão criadas as classes de entidades Customer e Order exibindo propriedades que correspondem aos campos das tabelas Customers e Orders. Os nomes estão no singular por que as classes representam um único Customer e Order a partir das tabelas Customers e Orders. (Este comportamento de nomear as classes de entidades é chamado Pluralization e pode ser configurado no menu Options)

Note que também é criado um relacionamento de associação entre a entidade Customer e Order. A classe Pai - Customer - possui uma propriedade Orders que representa uma coleção de pedidos para um cliente específico.

Criando um Object Data Source com a entidade Customer

As classes de entidades podem ser usadas como objeto de fonte de dados. Elas podem ser incluídas em janelas de Data Sources e arrastadas em formulários para criar controles de dados vinculados. Para incluir as classes como Data Sources rode o assistente de configuração do Data Source e clique no objeto. Vamos fazer isto para a classe Customer executando os passos descritos a seguir:

   

   

Criando controles vinculados para exibir os dados no Windows Forms

Para criar controles vinculados no formulário Windows Forms basta arrastar e soltar os itens da classe Customer a partir da janela Data Sources.

        Private db As New NorthwindDataContext

        CustomerBindingSource.DataSource = db.Customers

Ao final você deverá visualizar o seguinte código:


Executando a aplicação teclando F5 iremos obter:

Iremos ver um componente DataGridView exibindo os dados da tabela Customer e um segundo DataGridView exibindo os pedidos relacionados ao Customer selecionado no primeiro DataGridView. Note que o botão Salvar esta desabilitado e a funcionalidade para Salvar não foi implementada.

Para habilitar o botão Save (Salvar) e implementar a funcionalidade para Salvar faça o seguinte:

Private Sub CustomerBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomerBindingNavigatorSaveItem.Click


Try

     db.SubmitChanges()

Catch ex As Exception

      MessageBox.Show(ex.Message)

End Try


End Sub

Execute a aplicação pressionando F5 , efetue alguma alteração nos dados e clique no botão Save. Feche o formulário e abra novamente para ver as alterações persistidas.

Usando Consultas LINQ

Vamos incrementar nossa aplicação com a criação de uma consulta LINQ que efetue a seleção de Clientes para uma determinada cidade. Para isso inclua no formulário uma caixa de Texto com sua propriedade Name igual a txtCidade , uma Label , e um botão de comando com sua propriedade Name igual a btnFiltrar.

As seguir clique duas vezes sobre o controle Button e no evento Click informe o seguinte código:

Private Sub btnFiltrar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFiltrar.Click

 

Dim consultaClientes = From customers In db.Customers _

                                 Where customers.City = txtCidade.Text _

                                 Select customers
 

     CustomerBindingSource.DataSource = consultaClientes


End Sub

 

Note que no código não definimos string de conexão nem objetos para efetuar a conexão pois já foi tudo definido no DataContext.

A linha de código :

Dim consultaClientes = From customers In db.Customers _

                                 Where customers.City = txtCidade.Text _

                                 Select customers
 

Seleciona os clientes da tabela Customers onde o nome da cidade for igual ao informado na caixa de texto txtCidade;

A linha de código

       CustomerBindingSource.DataSource = consultaClientes

vincula os dados obtidos ao objeto BindingSource gerado exibindo os dados no DataGridView que tem a sua propriedade DataSource definida para o CustomerBindingSource.

Executando o projeto e informando o nome da cidade como London na caixa de texto e clicando no botão de comando iremos obter:

Perceba que somente os clientes cuja cidade é London são exibidos com seus respectivos pedidos.

Realizando operações de atualização, inclusão e exclusão (Updates, Inserts e Deletes)

Por padrão a lógica para realizar atualizações é fornecida pelo LINQ To SQL como Use RunTime (em tempo de execução) que cria instruções para inclusão , atualização e exclusão baseadas na instrução SELECT usada para preencher a entidade com dados. Se você não desejar usar este comportamento pode configurar o comportamento para atualizações e criar stored procedures específicas para as operações de inclusão, atualização e exclusão requeridas para manipular as informações do seu banco de dados. Pode fazer isto quando o comportamento padrão ainda não tiver sido gerado, e pode também sobrepor o comportamento padrão.

Primeiro vamos criar as stored procedures no banco de dados Northwind.mdf. Abra o DataBase Explorer e selecione a conexão com o banco de dados Northwind; em seguida clique com o botão direito do mouse sobre o nó Stored Procedures e selecione a opção Add Stored Procedure;

Na janela a direita informe o código de script que irá gerar as stored procedures:

Script para gerar as stored procedures: SelectCustomers, InsertCustomers, UpdateCustomers e DeleteCustomers.

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
    DROP PROCEDURE dbo.[SelectCustomers]
GO

CREATE PROCEDURE dbo.[SelectCustomers]
AS
    SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
    DROP PROCEDURE dbo.InsertCustomers
GO

CREATE PROCEDURE dbo.InsertCustomers
(
    @CustomerID nchar(5),
    @CompanyName nvarchar(40),
    @ContactName nvarchar(30),
    @ContactTitle nvarchar(30),
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Country nvarchar(15),
    @Phone nvarchar(24),
    @Fax nvarchar(24)
)
AS
    SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) _ 
VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')
    DROP PROCEDURE dbo.UpdateCustomers
GO

CREATE PROCEDURE dbo.UpdateCustomers
(
    @CustomerID nchar(5),
    @CompanyName nvarchar(40),
    @ContactName nvarchar(30),
    @ContactTitle nvarchar(30),
    @Address nvarchar(60),
    @City nvarchar(15),
    @Region nvarchar(15),
    @PostalCode nvarchar(10),
    @Country nvarchar(15),
    @Phone nvarchar(24),
    @Fax nvarchar(24),
    @Original_CustomerID nchar(5)
)
AS
    SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, _
 [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'DeleteCustomers' AND user_name(uid) = 'dbo')
    DROP PROCEDURE dbo.DeleteCustomers
GO

CREATE PROCEDURE dbo.DeleteCustomers
(
    @Original_CustomerID nchar(5)
)
AS
    SET NOCOUNT OFF;
DELETE FROM [dbo].[Customers] WHERE (([CustomerID] = @Original_CustomerID))
GO

 

Selecione todo o texto incluído  e clique com o botão direito do mouse sobre o editor selecionando a opção Run Selection. As stores procedures serão criadas no banco de dados.

Ao final da execução efetuando um refresh nos objetos iremos ver as stored procedures criadas:

Agora selecione a stored procedure UpdateCustomers, arraste-a e solte no Descritor O/R. Ela será incluída no painel Methods como um método DataContext.

Agora selecione a entidade Customer no descritor O/R e na janela de propriedades selecione o comando a ser sobreposto, no nosso caso vamos selecionar o comando Update.

Clique no botão contendo os 3 pontos ...e na janela Configure Behavior marque a opção Customize e selecione o método UpdateCustomers na lista;

Você vai notar que existe um método CustomerID mapeado para a classe CustomerID(Current) e outro chamado original_CustomerID que deverá ser mapeado para CustomerID(original). (Este comportamento ocorre para efetuar o tratamento da concorrência de dados.)

Nota: O comportamento padrão é mapear os métodos para propriedades de classes com o mesmo nome.

Agora basta executar a aplicação , efetuar qualquer alteração nos dados e verificar que a stored procedure criada está efetuando as alterações.

Da mesma forma podemos repetir o processo para os comandos Insert e Delete usando as stored procedures pertinentes. Depois é só criar consultas LINQ para efetuar as operações.

Até o próximo artigo ...

Pegue o projeto completo aqui: cadEmails.zip

referências:

José Carlos Macoratti