C# - Usando LINQ to SQL (o retorno)

Este artigo mostra como usar os recursos do LINQ to SQL em uma aplicação Windows Forms. Ele foi baseado no artigo de autoria de Scott Lysle.

Embora o Entity Framework tenha chegado para praticamente substituir o LINQ to SQL com muitas vantagens o LINQ to SQL ainda pode ser considerado com uma alternativa viável para agilizar o desenvolvimento de aplicações com acesso a dados.

O objetivo do artigo é mostrar como usar acessar tabelas, consultas e stored procedures de um banco de dados SQL Server (o LINQ to SQL só funciona com ele) e também como realizar operações de alteração, inclusão e exclusão de dados.

Para o exemplo do artigo eu vou usar o banco de dados Northwind.mdf e a ferramenta Visual C# 2008 Express Edition.

A estrutura do banco de dados Northwind.mdf exibindo suas tabelas, Views e Stored Procedures é exibida na figura abaixo:

Vamos criar uma aplicação Windows Forms e exibir contendo 3 formulários:

1-) O formulário form1.cs irá exibir o resultado das consultas às tabelas, querys e stored procedures em um controle DataGridView e deverá possuir um menu criado via controle MenuStrip conforme o leiaute abaixo:

Para cada item do menu vamos definir sub-itens conforme as figuras a seguir:

a- Consultas

b- Movimento

2-) O formulário form2.cs é exibido na figura abaixo e apresenta os controles Label e TextBox para realizar a alteração/inclusão na tabela Customer;

3-) O formulário form3.cs da figura abaixo é usado para excluir um registro da tabela Customer e possui o controle Label, TextBox e Button:

Criando o DataContext e as classes com o código LINQ to SQL

Agora vamos criar a infra-estrutura usando classes onde devemos criar o código usando LINQ to SQL antes devemos realizar o mapeamento objeto relacional para gerar as classes a partir das tabelas do Northwind.

No menu Project -> Add New Item , selecione o template LINQ to SQL Classes e informe o nome Northwind.dbml e clique em Add;

A seguir arraste e solte a partir da janela DataBase Explorer as tabelas do Northwind para o descritor LINQ afim de gerar as classes com o mapeamento ORM. Devemos arrastar também as stored procedures Sales_by_Year e Ten_Most_Expensive_Products na painel de métodos do descritor conforme a figura abaixo:

Com isso já temos todo o mapeamento objeto relacional gerado e já podemos trabalhar com as classes que representam as tabelas do nosso banco de dados.

Para isso eu poderia criar uma classe e nela definir os métodos para acessar as tabelas , consultas e stored procedures e os métodos para realizar alteração e inclusão mas vou fazer diferente, vou classes distintas para cada item assim teremos as seguintes classes:

1- Criando a classe para acessar as tabelas

Vamos iniciar então criando a classe para acessar as tabelas selecionando no menu Project -> Add Class e informando o nome acessoLinqTabelas.cs;

A seguir defina o código abaixo na classe:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqToSQL_Cshp1
{
    class acessoLinqTabelas
    {
        /// <summary>
        /// Obtem dados da tabela Customer 
        /// </summary> summary> obtem; < returns> return
        }
        /// <summary>
        /// Obtem dados da tabela Employee 
        /// </summary>
        /// <returns></returns>
        public static System.Data.Linq.Table<Employee> GetEmployeeTable()
        {
           NorthwindDataContext dc = new
           NorthwindDataContext();
           return dc.GetTable< return
        }
        /// <summary>
        /// Obtem dados da tabela Order 
        /// </summary>
        /// <returns></returns>
        public static System.Data.Linq.Table<Order> GetOrderTable()
        {
            NorthwindDataContext dc = new
            NorthwindDataContext();
            return dc.GetTable< return
        }
        /// <summary>
        /// Obtem dados da tabela Category 
        /// </summary>
        /// <returns></returns>
        public static System.Data.Linq.Table<Category> GetCategoryTable()
        {
            NorthwindDataContext dc = new
            NorthwindDataContext();
            return dc.GetTable< return
        } 
        /// <summary>
        /// Obtem dados da tabela Product 
        /// </summary>
        /// <returns></returns>
        public static System.Data.Linq.Table<Product> GetProductTable()
        {
            NorthwindDataContext dc = new
            NorthwindDataContext();
            return dc.GetTable< return
        }
    }
}

É isso mesmo que você esta vendo em cada método criamos apenas uma instância do nosso datacontext e acessamos o método mapeado GetTable<nome_da_tabela>();

Outro detalhe importante é que definimos os métodos como static, e, com isso não precisaremos criar uma instância da classe para usar os métodos.

2- criando a classe para acessar as consultas

Vamos agora criar a classe para acessar as consultas selecionando no menu project -> add class e informando o nome acessolinqconsultas.cs;

A seguir defina o código abaixo na classe:

using system; using system.Collections.Generic; using system.Linq; using system.Text; namespace linqtosql_cshp1
{
    class acessoLinqConsultas
    {
        /// <summary>
        /// Exemplo:  a cláusuala Where
        /// Retorna um funcionario onde o codigo coincinde com o valor
        /// passado na parâmetro empID
        /// </summary>
        /// <param name=" summary> < empid" > param> returns> o; return

        /// <summary>
        /// Exemplo:  Select para um único objeto
        /// usando a cláusula Where
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns>Retorna o primeiro pedido que atendo o critério definido</returns>
        public static Order GetOrderById(int orderId)
        {
            NorthwindDataContext dc = new
            NorthwindDataContext();

       return (from ord in dc.GetTable<Order>()
               where (ord.OrderID == orderId)
              select ord).SingleOrDefault<Order>();
        }

        /// <summary>
        /// Exemplo:  Select para uma lista tipada
        /// usando a cláusula Where
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public static List<Order> GetOrdersById(int orderId)
        {
            NorthwindDataContext dc = new
            NorthwindDataContext();

      return (from ord in dc.GetTable<Order>()
              where (ord.OrderID == orderId)
              select ord).ToList<Order>();
        }

        /// <summary>
        /// Exemplo:  Retorna uma lista ordeanda
        /// Converte o valor retornado para uma Lista
        /// do tipo Employee ordenada por data de admissão
        /// </summary>
        /// <returns></returns>
        public static List<Employee> GetEmployeesByHireDate()
        {
            NorthwindDataContext dc = new
            NorthwindDataContext();        

      return (from emp in dc.GetTable<Employee>()
              orderby emp.HireDate ascending
              select emp).ToList<Employee>();
        }

        /// <summary>
        /// Classe usada para definir o tipo de retorno
        /// para o método - OrdersAndDetails
        /// </summary>
        public class OrdersAndDetailsResult
        {
            public System.String CustomerID
            { get; public }
            public System.Nullable<System.DateTime> OrderDate
            { get; get }
            public System.Nullable<System.DateTime> RequiredDate
            { get; get }
            public System.String ShipAddress
            { get; get }
            public System.String ShipCity
            { get; get }
            public System.String ShipCountry
            { get; get }
            public System.String ShipZip
            { get; get }
            public System.String ShippedTo
            { get; get }
            public System.Int32 OrderID
            { get; get }
            public System.String NameOfProduct
            { get; get }
            public System.String QtyPerUnit
            { get; get }
            public System.Nullable<System.Decimal> Price
            { get; get }
            public System.Int16 QtyOrdered
            { get; get }
            public System.Single Discount
            { get; get }
        }

        /// <summary>
        /// Exemplo:  Joins
        /// Vinculando usando a palavra-chave join
        /// O valor retornado é convertido
        /// para uma lista do tipo especificado
        /// </summary>
        /// <returns></returns>
        public static List<OrdersAndDetailsResult> OrdersAndDetails()
        {
            NorthwindDataContext dc = new
            NorthwindDataContext(); 

      return (from ords in dc.GetTable<Order>()
              join dets in dc.GetTable<Order_Detail>()
              on ords.OrderID equals dets.OrderID
              orderby ords.CustomerID ascending
              select new OrdersAndDetailsResult
              {
                CustomerID = ords.CustomerID,
                OrderDate = ords.OrderDate,
                RequiredDate = ords.RequiredDate,
                ShipAddress = ords.ShipAddress,
                ShipCity = ords.ShipCity,
                ShipCountry = ords.ShipCountry,
                ShipZip = ords.ShipPostalCode,
                ShippedTo = ords.ShipName,
                OrderID = ords.OrderID,
                NameOfProduct = dets.Product.ProductName,
                QtyPerUnit = dets.Product.QuantityPerUnit,
                Price = dets.Product.UnitPrice,
                QtyOrdered = dets.Quantity,
                Discount = dets.Discount
               }
             ).ToList<OrdersAndDetailsResult>();
        }

        /// <summary>
        /// Exemplo:  Aggregation
        /// Retorna o total sa soma dos pedidos
        /// selecionado por ID
        /// computando o preco unitário multiplicado pela quantidade
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public static decimal? GetOrderValueByOrderId(int orderID)
        {
            NorthwindDataContext dc = new
            NorthwindDataContext();          

       var matches =
                    (from od in dc.GetTable<Order_Detail>()
                     where od.OrderID == orderID
                     select od.Product.UnitPrice * od.Quantity).Sum();
                     return matches;
        }

        /// <summary>
        /// Exemplo:  Usando Take para obter um número limitado
        /// de valores para exibir
        /// E usando Skip para sequencialmente mudar para um ponto distinto
        /// com os valores retornados
        /// </summary>
        /// <param name="SkipNumber"></param>
        /// <returns></returns>
        public static List<Order> GetTopFiveOrdersById(int SkipNumber)
        {
            NorthwindDataContext dc = new
            NorthwindDataContext();     

      return (from ord in dc.GetTable<Order>()
              orderby ord.OrderID ascending
             select ord).Skip(SkipNumber).Take(5).ToList<Order>();
        }
    }
}
acessolinqconsultas.cs

Nesta classe temos os métodos que também precisam do datacontext e usam as propriedades e cláusulas das consultas linq como where, skip, take, etc.

3- criando a classe para acessar as stored procedures

vamos agora criar a classe para acessar os procedimentos armazenados selecionando no menu project -> add class e informando o nome acessolinqstoredprocedures.cs;

a seguir defina o código abaixo na classe:

using system; using system.Collections.Generic; using system.Linq; using system.Text; namespace linqtosql_cshp1
{
    class acessoLinqStoredProcedure
    {
        /// <summary>
        /// Stored Procedure:  Sales By Year
        /// </summary>
        /// <param name="beginningYear" summary> < beginningyear" > param> endingyear" returns> return
       public static List<Sales_by_YearResult> SalesByYear(DateTime? beginningYear,DateTime? endingYear)
    {
                    NorthwindDataContext dc = new NorthwindDataContext();
                    return dc.Sales_by_Year(beginningYear,endingYear).ToList<Sales_by_YearResult>();

    }

        /// <summary>
        /// Stored Procedure:  Ten Most Expenisve Products
        /// </summary>
        /// <returns></returns>
        public static List<Ten_Most_Expensive_ProductsResult>
        TenMostExpensiveProducts()
        {
            NorthwindDataContext dc = new
            NorthwindDataContext();

      return dc.Ten_Most_Expensive_Products().ToList<Ten_Most_Expensive_ProductsResult>();
        }
    }
}

Nesta classe temos os métodos que usam os recursos do LINQ to SQL para acessar as stored procedures mapeadas através do DataContext.

4- Criando a classe para realizar as operações CRUD

Vamos agora criar a classe para realizar as operações CRUD selecionando no menu Project -> Add Class e informando o nome acessoLinqCRUD.cs;

A seguir defina o código abaixo na classe:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace LinqToSQL_Cshp1
{
    class acessoLinqCrud
    {

        /// <summary>
        /// Inserir ou Atualizar um registro da tabela Customer
        /// Se o ID do Cliente existir então o registro é atualizado
        /// caso contrário um novo registro será inserido na tabela
        /// </summary>
        /// <param name="customerId"></param>
        /// <param name="companyName"></param>
        /// <param name="contactName"></param>
        /// <param name="contactTitle"></param>
        /// <param name="address"></param>
        /// <param name="city"></param>
        /// <param name="region"></param>
        /// <param name="postalCode"></param>
        /// <param name="country"></param>
        /// <param name="phone"></param>
        /// <param name="fax"></param>
        public static void InsertOrUpdateCustomer(string customerId, string companyName,
            string contactName, string contactTitle, string address, string city,
            string region, string postalCode, string country, string phone, string fax)
        {

            NorthwindDataContext dc = new NorthwindDataContext();
            var matchedCustomer = (from c in dc.GetTable<Customer>()
                                   where c.CustomerID == customerId
                                   select c).SingleOrDefault();
            if (matchedCustomer == null)
            {
                try
                {
                    // cria um novo registro customer pois o ID não existe
                    Table<Customer> customers = acessoLinqTabelas.GetCustomerTable();
                    Customer cust = new Customer();

                    cust.CustomerID = customerId;
                    cust.CompanyName = companyName;
                    cust.ContactName = contactName;
                    cust.ContactTitle = contactTitle;
                    cust.Address = address;
                    cust.City = city;
                    cust.Region = region;
                    cust.PostalCode = postalCode;
                    cust.Country = country;
                    cust.Phone = phone;
                    cust.Fax = fax;

                    customers.InsertOnSubmit(cust);
                    customers.Context.SubmitChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                try
                {
                    matchedCustomer.CompanyName = companyName;
                    matchedCustomer.ContactName = contactName;
                    matchedCustomer.ContactTitle = contactTitle;
                    matchedCustomer.Address = address;
                    matchedCustomer.City = city;
                    matchedCustomer.Region = region;
                    matchedCustomer.PostalCode = postalCode;
                    matchedCustomer.Country = country;
                    matchedCustomer.Phone = phone;
                    matchedCustomer.Fax = fax;

                    dc.SubmitChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// Deleta um registro da tabela usando o ID do cliente
        /// </summary>
        /// <param name="customerID"></param>
        public static void DeleteCustomer(string customerID)
        {
            NorthwindDataContext dc = new NorthwindDataContext();

            var matchedCustomer = (from c in dc.GetTable<Customer>()
                                   where c.CustomerID == customerID
                                   select c).SingleOrDefault();
            try
            {
                dc.Customers.DeleteOnSubmit(matchedCustomer);
                dc.SubmitChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

O método InsertOrUpdateCustomer é usado para inserir ou atualizar um cliente e o método DeleteCustomer para excluir um cliente.

Criando a interface com o usuário

1- Definindo a chamada dos métodos pelas opções do menu no formulário form1.cs;

No formulário form1.cs vamos definir o código que irá acessar os respectivos métodos das classes contendo as consultas LINQ;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Linq;


namespace LinqToSQL_Cshp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void customerstoolStripMenuItem1_Click(object sender, EventArgs e)
        {
            Table<Customer> customers = acessoLinqTabelas.GetCustomerTable();
            dataGridView1.DataSource = customers;
        }

        private void orderToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Table<Order> orders = acessoLinqTabelas.GetOrderTable();
            dataGridView1.DataSource = orders;
        }

        private void employessToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Table<Employee> emp = acessoLinqTabelas.GetEmployeeTable();
            dataGridView1.DataSource = emp;
        }

        private void categoriesToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Table<Category> cats = acessoLinqTabelas.GetCategoryTable();
            dataGridView1.DataSource = cats;
        }

        private void employeesByToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Order ord = acessoLinqConsultas.GetOrderById(10248);

            StringBuilder sb = new StringBuilder();
            sb.Append("Order: " + Environment.NewLine);
            sb.Append("Order ID: " + ord.OrderID + Environment.NewLine);
            sb.Append("Date Shipped: " + ord.ShippedDate + Environment.NewLine);
            sb.Append("Shipping Address: " + ord.ShipAddress + Environment.NewLine);
            sb.Append("         City: " + ord.ShipCity + Environment.NewLine);
            sb.Append("         Region: " + ord.ShipRegion + Environment.NewLine);
            sb.Append("         Country: " + ord.ShipCountry + Environment.NewLine);
            sb.Append("         Postal Code: " + ord.ShipPostalCode +
            Environment.NewLine);

            sb.Append("Shipping Name: " + ord.ShipName + Environment.NewLine);
            MessageBox.Show(sb.ToString(), "Shipping Information");
        }

        private void salesByCategoryToolStripMenuItem_Click(object sender, EventArgs e)
        {
            List<acessoLinqConsultas.OrdersAndDetailsResult> oad = acessoLinqConsultas.OrdersAndDetails();
            dataGridView1.DataSource = oad;
        }

        private void employeeByIDToolStripMenuItem_Click(object sender, EventArgs e)
        {

            Employee emp = acessoLinqConsultas.GetEmployeeById(1);

            StringBuilder sb = new StringBuilder();
            sb.Append("Employee 1: " + Environment.NewLine);
            sb.Append("Name: " + emp.FirstName + " " + emp.LastName + Environment.NewLine);
            sb.Append("Hire Date: " + emp.HireDate + Environment.NewLine);
            sb.Append("Home Phone: " + emp.HomePhone + Environment.NewLine);

            MessageBox.Show(sb.ToString(), "Employee ID Search");
        }

        private void salesByYearToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DateTime start = new DateTime(1990, 1, 1);
            DateTime end = new DateTime(2000, 1, 1);

            List<Sales_by_YearResult> result = acessoLinqStoredProcedure.SalesByYear(start, end);
            dataGridView1.DataSource = result;
        }

        private void tenMostExpensiveProductsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            List<Ten_Most_Expensive_ProductsResult> result = acessoLinqStoredProcedure.TenMostExpensiveProducts();
            dataGridView1.DataSource = result;
        }

        private void atualizarOuIncluirClienteToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form2 frm2 = new Form2();
            frm2.ShowDialog();
        }

        private void excluirClienteToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form3 frm3 = new Form3();
            frm3.ShowDialog();
        }
    }
}
 

2- No formulário form2.cs temos a chamada dos métodos para incluir/atualizar um registro da tabela Customers passando os parâmetros informados no formulário pelo usuário;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace LinqToSQL_Cshp1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void btnAlterarIncluirCliente_Click(object sender, EventArgs e)
        {
            if(!customerIDTextBox.Text.Equals(""))
            {
                try
                {
                    acessoLinqCrud.InsertOrUpdateCustomer(customerIDTextBox.Text, companyNameTextBox.Text, contactNameTextBox.Text, 
                        contactTitleTextBox.Text, addressTextBox.Text, cityTextBox.Text, regionTextBox.Text, postalCodeTextBox.Text, 
                        countryTextBox.Text, phoneTextBox.Text, faxTextBox.Text);

                    MessageBox.Show("Cliente atualizado/incluído com sucesso !");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Erro");
                }
            }
            else
            {
                MessageBox.Show("Valores inválidos.");
            }
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            customerIDTextBox.Focus();
        }

        private void btnSair_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}
 

3- No formulário form3.cs temos a chamada do método para excluir registros passando o código do cliente informado pelo usuário no formulário;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace LinqToSQL_Cshp1
{
    public partial class Form3 : Form
    {
        public Form3()
        {
            InitializeComponent();
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            customerIDTextBox.Focus();
        }

        private void btnSair_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnExcluirCliente_Click(object sender, EventArgs e)
        {
            if (!customerIDTextBox.Text.Equals(""))
            {
                try
                {
                    acessoLinqCrud.DeleteCustomer(customerIDTextBox.Text);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Erro");
                }
            }
            else
            {
                MessageBox.Show("Valores inválidos.");
            }
        }
    }
}

Abaixo vemos o projeto em execução exibindo os dados da tabela Customers e o formulário para alterar/incluir um cliente;

Neste artigo mostrei como usar os recursos do LINQ to SQL para acessar tabelas, consultas, stored procedures e realizar operações CRUD em um banco de dados SQL Server. Creio que isso é tudo que um usuário com conhecimento básico sobre LINQ deve saber para poder tomar decisões e se virar sozinho.

Acredito que esse seja um dos últimos artigos que vou escrever sobre o LINQ to SQL visto que o Entity Framework pode substituí-lo com vantagens.

Pegue o projeto completo aqui: LinqToSQL_Cshp1.zip

Eu sei é apenas LINQ to SQL, mas eu gosto...

Referências:

José Carlos Macoratti