Paweł Łukasiewicz
2015-10-25
Paweł Łukasiewicz
2015-10-25
Udostępnij Udostępnij Kontakt
Wstęp

Artykuł ten tłumaczy czym jest LINQ to SQL oraz wyjaśnia podstawową funkcjonalność. Funkcjonalność ta ułatwia programiście debuggowanie kodu oraz oferuje wiele nowych sposobów pisania aplikacji.

W artykule będę używał przykładowej bazy danych AdventureWorks udostępnionej przez Microsoft. Możecie ją pobrać z poniższego linka -> http://msftdbprodsamples.codeplex.com/


Czym jest 'LINQ to SQL'?

Technologia LINQ jest zestawem rozszerzeń dla platformy .NET, który obejmuje zintegrowany język zapytań wraz ze zbiorem możliwych operacji. Rozszerza język C# o natywny język zapytań. Zapewnia ponadto biblioteki, która pozwalają skorzystać z powyższych możliwości. Więcej informacji możecie znaleźć na stronie projektu -> https://msdn.microsoft.com/library/bb397926.aspx

LINQ to SQL pozwala w łatwy sposób na mapowanie tabeli, widoków, procedur składowanych z serwera SQL. Dodatkowo, technologia ta pomaga programistom przy mapowaniu i pobieraniu danych z bazy danych w sposób podoby jak ma to miejsce w języku SQL. Nie jest to zastąpienie ADO.NET a raczej coś na kształt rozszerzenia, które oferuje nowe funkcje.

LINQ to SQL - architektura


Jak używać LINQ to SQL?

W sekcji tej zaprezentuje jak używać LINQ to SQL od samego początku, tj. od utworzenia projektu.

  1. Tworzymy nową aplikację konsolową
  2. Tworzymy nowe połączenie do wspomnianej wyżej bazy danych:

    LINQ to SQL - modyfikacja połączenia

  3. Dodajemy nowy element do naszego projektu, wybieramy: LINQ to SQL Classes. Zmieniamy jego nazwę na: AdventureWorks.dbml. Ten nowo utworzony plik będzie mapował tabele z naszej bazy danych na klasy w języku C#:

    LINQ to SQL - mapowanie tabeli do klasy w języku C#
Utworzony został tzw. Object Relational Designer, który odwozrowuje tabele z bazy danych na klasy w języku C#. Aby to zrobić wystarczy przeciągnąć i upuścić tabelę z naszej bazy danych. Projekt automatycznie wyświetli tabele w notacji UML oraz przedstawi relacje pomiędzy nimi. Na poniższym diagramie możecie zobaczyć relacje pomiędzy 4 tabelami, tj. Product, ProductCostHistory, ProductSubCategory, ProductCategory:


Wewnątrz klasy AdventureWorks.designer.cs znajdziesz definicje wszystkich klas w postaci tabeli przedstawionej poniżej:

SQL LINQ to SQL O/R Designer
Nazwa tabeli Nazwa klasy
Kolumny Atrybuty
Relacje EntitySet lub EntityRef
Procedury składowane Metody


Zrozumieć DataContext

DataContext jest klasą, która daje bezpośredni dostep do klas języka C#, połączeń z bazami danych, itd. Klasa ta jest generowana kiedy designer jest zapisywany. Dla pliku o nazwie AdventureWorks.dbml, klasa AdventureWorksDataContext jest tworzona automatycznie. Zawiera definicję tabel oraz procedur składowanych.


Odpytywanie bazy danych

Kiedy model bazy danych został przygotowany przy użyciu designera można rozpoczać odpytywanie bazy danych. Poniżej zostanie przedstawione kilka z przykładów a na końcu artykuły dostępny będzie cały kod wraz ze szczegółowym objaśnieniem.

W poniższym przykładzie znajdzie się cały kod dla pierwszego przykładu. Pozwoli to Wam zorientować się w jego strukturze. Kolejne przykłady będą zawierały jedynie wowołania kolejnych metod.

Zwracanie danych z tabeli Product:

using System;
using System.Linq;
namespace LINQtoSQLConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            // Wspomniana w artykule automatycznie utworzona klasa
            AdventureWorksDataContext dc = new AdventureWorksDataContext();
            Console.WriteLine("Podaj Id kodu do wywołania: ");
            Console.WriteLine("1 : zwracanie danych z tabeli Product");
            Console.WriteLine();
            Console.Write("Twój wybór: ");
            // Zczytujemy wybór użytkownika
            int liczba = Convert.ToInt32(Console.ReadLine());
            switch (liczba)
            {
                case 1:
                    DaneZtabeliProduct(dc);
                    break;
                default:
                    break;
            }
        }
        static void DaneZtabeliProduct(AdventureWorksDataContext dc)
        {
            // Zwracamy wszystkie dane z tabeli Product
            var data = from p in dc.Products
                       select p;
            foreach (var item in data)
            {
                // Wypisujemy podstawowe informacje
                Console.WriteLine("Id: {0}, Numer: {1}, Nazwa: {2}", item.ProductID, item.ProductNumber, item.Name);
            }
            Console.ReadKey();
        }
    }
}
Zwracanie danych z tabeli Product wraz z klauzulą Where:
static void DaneZtabeliProductWhere(AdventureWorksDataContext dc)
{
    // zwracamy dane z tabeli Product gdzie cena jest większa niż 300
    var data = from p in dc.Products
                where p.ListPrice > 300
                select p;
    foreach (var item in data)
    {
        // Wypisujemy podstawowe informacje
        Console.WriteLine("Id: {0}, Nazwa: {1}, Cena: {2}", item.ProductID, item.Name, item.ListPrice);
    }
    Console.ReadKey();
}
Zwracanie określonego typu danych z tabeli Product
W pierwszej kolejności zdefiniujemy własną klasę, która będzie służyła do przechowywania zwracanych danych:
public class TwoSpecifiedColumns
{
    // definiujemy pola publiczne
    public string style;
    public string color;
        
    // w konstruktorze ustalamy ich wartości
    public TwoSpecifiedColumns(string Style, string Color)
    {
        this.style = Style;
        this.color = Color;
    }
}
A następnie przygotujemy metodę do zwracania zdefiniowanego wcześniej typu danych:
static void DaneZokreslonychKolumnZwracamyWlasnyTyp(AdventureWorksDataContext dc)
{
    // zwracamy określonych typ danych z tabeli Product,
    // gdzie cena jest większa niż 250
    var data = from p in dc.Products
                where p.ListPrice > 250
                select new TwoSpecifiedColumns(p.Style, p.Color);
    // Wypisujemy dostępne informacje (zgodne z def. klasy TwoSpecifiedColumns)
    foreach (TwoSpecifiedColumns item in data)
    {
        Console.WriteLine("Styl: {0}, Kolor: {1}", item.style, item.color);
    }
    Console.ReadKey();
}
Zwracanie niezdefiniowanego typu danych z tabeli Product
static void NieokreslonyTypDanych(AdventureWorksDataContext dc)
{
    // zwracamy nieokreślony typ danych z tabeli Product,
    // gdzie cena jest większa niż 500
    var data = from p in dc.Products
                where p.ListPrice > 500
                select new
                { 
                    // tworzymy nazwę dla kolumny Name
                    Nazwa_produktu = p.Name,
                    // tworzymy nazwę dla kolumny ProductLine
                    Linia_produktu = p.ProductLine
                };
    // Wypisujemy dostępne informacje (zgodne z naszą definicją)
    foreach (var item in data)
    {
        Console.WriteLine("Nazwa: {0}, Linia: {1}", item.Nazwa_produktu, item.Linia_produktu);
    }
    Console.ReadKey();
}
Powyższy przykład pokazuje jak możemy używać anonimowej klasy, która w przykładzie składa się z dwóch pól. Celem takiego podejścia jest utworzenie nowej klasy do czasowego przechowywania danych, kiedy deweloper nie chce lub nie ma potrzeby tworzenia nowej deklaracji takiej klasy. Podejście takie może być przydatne w przypadku, gdy deklaracja klasy jest wykorzystywana tylko do przechowywania danych.

W przykładzie z określonym typem danych nowa klasa używana jest tylko, jako swojego rodzaju interfejs pomiędzy danymi zwracanymi z tabeli a wyświeteniem ich w konsoli. Nie jest stosowana nigdzie indziej. Dzięki takiemu podejściu programista może utworzyć klasę tymczasową z nieograniczoną liczbą atrybutów. Każdy atrybut ma nazwę. Warto zapamiętać, że IntelliSense współpracuje z anonimowymi klasami.

Przeszukiwanie wielu tabel jednocześnie
static void PrzeszukiwanieWieluTabel(AdventureWorksDataContext dc)
{
    // poniższe zapytanie zwraca dane, których cena jest większa niż 300
    // oraz nazwa kategorii sprawdzana w innej tabeli to "Clothing"
    // zapytanie to jest zapytaniem krzyżowym, tzn. powstała kolekcja
    // jest krzyżowym połączeniem pomiędzy wszystkimi produktami o cenie wyższej niż 300
    // oraz wszystkimi kategoriami o nazwie "Clothing"
    var data = from p in dc.Products
                from pc in dc.ProductCategories
                where p.ListPrice > 300 && pc.Name == "Clothing"
                select new
                {
                    Nazwa_produktu = p.Name,
                    Nazwa_kategorii = pc.Name
                };
    // Wypisujemy dostępne informacje (zgodne z naszą definicją)
    foreach (var item in data)
    {
        Console.WriteLine("Nazwa produktu: {0}, Nazwa kategorii: {1}", item.Nazwa_produktu, item.Nazwa_kategorii);
    }
    Console.ReadKey();
}
Przeszukiwanie połączonych tabel
static void PrzeszukiwaniePolaczonychTabel(AdventureWorksDataContext dc)
{
    // poniższe zapytania zwraca nam dane z tabeli Product oraz ProductSubcategory
    // które mają doładnie takie samo 'ProductSubcategoryID'
    var data = from p in dc.Products
                from psc in dc.ProductSubcategories
                where p.ProductSubcategoryID == psc.ProductSubcategoryID
                select new { p.ProductID, p.ProductSubcategoryID, psc.Name};
    // Wypisujemy dostępne informacje (zgodne z naszą definicją)
    foreach (var item in data)
    {
        Console.WriteLine("Id: {0}, Id Subkategorii: {1}, Nazwa: {2}", item.ProductID, item.ProductSubcategoryID, item.Name);
    }
    Console.ReadKey();
}
Przeszukiwanie tabel połączonych przez entityref
static void PrzeszukiwaniePolaczonychTabelEntityRef(AdventureWorksDataContext dc)
{
    // Przeszukiwanie połączonych tabel przy użyciu EntityRef - wyjaśnienie a artykule
    var data = from p in dc.Products
                select new
                {
                    SubcategoryName = p.ProductSubcategory.Name,
                    ProductId = p.ProductID,
                    ProductName = p.Name
                };
    // Wypisujemy dostępne informacje (zgodne z naszą definicją)
    foreach (var item in data)
    {
        Console.WriteLine("Nazwa Subkategorii: {0}, Id Produktu: {1}, Nazwa: {2}", item.SubcategoryName, item.ProductId, item.ProductName);
    }
    Console.ReadKey();
}
W powyższym przykładzie została użyta właściwość entityref. Klasa Produkt posiada referencję do klasy ProductSubcategory. Daje nam to bezpośredni dostęp do właściwości tej drugiej klasy. Zaletą entityref jest to, że programista nie musi dokładnie wiedzieć jak tabele są połączane a dostęp do tych danych jest natychmiastowy. Warto zapamiętać, że entityset odzwierciedla relację jeden do wielu lub wiele do wielu podczas, gdy entityref jest relacją jeden do jednego.


Insert, Update oraz Delete

LINQ to SQL pozwala na zarządzanie bazą danych. Trzy najważniejsze operacje zostały zaimplementowane, tzn. INSERT, DELETE oraz UPDATE ale ich użycie jest niewidoczne.

UPDATE:

static void UPDATE(AdventureWorksDataContext dc)
{
    // dokonamy update wszystkich rekordow w ktorych nazwa produktu zaiwiera "Tube"
    var update = from p in dc.Products
                    where p.Name.Contains("Tube")
                    select p;
    // Zmieniamy nazwę na inną, przy czym nazwa musi być unikalna
    // wg. projektu tej konkretnej bazy danych
    int i = 0;
    foreach (var item in update)
    {
        item.Name = "tuuube" + i.ToString();
        i++;
    }
    // Zapisujemy zmiany
    dc.SubmitChanges();
    // a teraz sprawdzimy czy update się powiódł
    var data = from p in dc.Products
                where p.Name.Contains("tuuube")
                select p;
    foreach (var item in data)
    {
        Console.WriteLine("Id: {0}, Nazwa: {1}", item.ProductID, item.Name);
    }
    Console.ReadKey();
}
Aby dokonać zmian w bazie danych należy wywołać metodę SumbitChanges().

INSERT: Aby dokonać wstawienia nowego rekordu do bazy danych należy utworzyć nowy obiekt danej klasy a następnie przypisać do niej konkretne wartości.
static void INSERT(AdventureWorksDataContext dc)
{
    // tworzymy nowy obiekt klasy ProductCategory 
    ProductCategory prod = new ProductCategory();
    prod.Name = "Prosty Test";
    prod.ModifiedDate = DateTime.Now;
    // Wywołujemy metode InsertOnSubmit oraz zaspisujemy dane
    dc.ProductCategories.InsertOnSubmit(prod);
    dc.SubmitChanges();
    // zwracamy ostatni rekord w celu sprawdzenia powyższego kodu
    var lastrow = (from p in dc.ProductCategories
                    orderby p.ProductCategoryID descending
                    select p).First();
    // Wyświetlamy ten element w konsoli
    Console.WriteLine("Id: {0}, Nazwa: {1}", lastrow.ProductCategoryID, lastrow.Name);
    Console.ReadKey();
}
DELETE: Usuwanie rekordów jest proste. W pierwszej kolejności zwracamy dane przy użyciu klauzuli Select a następnie wywołujemy metodę DeleteOnSumbit() lub DeleteAllOnSumbit, aby usunąć wskazane elementy.
static void DELETE(AdventureWorksDataContext dc)
{
    // w pierwszej kolejności zwracamy dane, które chcemy usuanąć
    var data = from p in dc.ProductCategories
                where p.Name.Contains("Prosty Test")
                select p;
    // kasujemy zwrócone dane z naszej tabeli
    dc.ProductCategories.DeleteAllOnSubmit(data);
    // zapisujemy zmiany
    dc.SubmitChanges();
    // dla testu zwracamy wszystkie rekordy z tabeli
    var afterChanges = from p in dc.ProductCategories
                        select p;
    foreach (var item in afterChanges)
    {
        Console.WriteLine("Id: {0}, Nazwa: {1}", item.ProductCategoryID, item.Name);
    }
    Console.ReadKey();
}
Oraz zgodnie z wcześniejszą informacją poniżej komplenty kod tego artykułu:
using System;
using System.Linq;
namespace LINQtoSQLConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            // Wspomniana w artykule automatycznie utworzona klasa
            AdventureWorksDataContext dc = new AdventureWorksDataContext();
            Console.WriteLine("Podaj Id kodu do wywołania: ");
            Console.WriteLine("1 : zwracanie danych z tabeli Product");
            Console.WriteLine("2 : zwracanie danych z tabeli Product w klauzulą Where");
            Console.WriteLine("3 : zwracanie określonego typu danych z tabeli Product");
            Console.WriteLine("4 : zwracanie niezdefiniowanego typu danych z tabeli Product");
            Console.WriteLine("5 : przeszukiwanie wielu tabel jednocześnie");
            Console.WriteLine("6 : przeszukiwanie połączonych tabel");
            Console.WriteLine("7 : przeszukiwanie połączonych tabel - EntityRef");
            Console.WriteLine("8 : UPDATE");
            Console.WriteLine("9 : INSERT");
            Console.WriteLine("10 : DELETE");
            Console.WriteLine();
            Console.Write("Twój wybór: ");
            // Zczytujemy wybór użytkownika
            int liczba = Convert.ToInt32(Console.ReadLine());
            switch (liczba)
            {
                case 1:
                    DaneZtabeliProduct(dc);
                    break;
                case 2:
                    DaneZtabeliProductWhere(dc);
                    break;
                case 3:
                    DaneZokreslonychKolumnZwracamyWlasnyTyp(dc);
                    break;
                case 4:
                    NieokreslonyTypDanych(dc);
                    break;
                case 5:
                    PrzeszukiwanieWieluTabel(dc);
                    break;
                case 6:
                    PrzeszukiwaniePolaczonychTabel(dc);
                    break;
                case 7:
                    PrzeszukiwaniePolaczonychTabelEntityRef(dc);
                    break;
                case 8:
                    UPDATE(dc);
                    break;
                case 9:
                    INSERT(dc);
                    break;
                case 10:
                    DELETE(dc);
                    break;
                default:
                    break;
            }
        }
        static void DaneZtabeliProduct(AdventureWorksDataContext dc)
        {
            // Zwracamy wszystkie dane z tabeli Product
            var data = from p in dc.Products
                       select p;
            foreach (var item in data)
            {
                // Wypisujemy podstawowe informacje
                Console.WriteLine("Id: {0}, Numer: {1}, Nazwa: {2}", item.ProductID, item.ProductNumber, item.Name);
            }
            Console.ReadKey();
        }
        static void DaneZtabeliProductWhere(AdventureWorksDataContext dc)
        {
            // zwracamy dane z tabeli Product gdzie cena jest większa niż 300
            var data = from p in dc.Products
                       where p.ListPrice > 300
                       select p;
            foreach (var item in data)
            {
                // Wypisujemy podstawowe informacje
                Console.WriteLine("Id: {0}, Nazwa: {1}, Cena: {2}", item.ProductID, item.Name, item.ListPrice);
            }
            Console.ReadKey();
        }
        static void DaneZokreslonychKolumnZwracamyWlasnyTyp(AdventureWorksDataContext dc)
        {
            // zwracamy określonych typ danych z tabeli Product,
            // gdzie cena jest większa niż 250
            var data = from p in dc.Products
                       where p.ListPrice > 250
                       select new TwoSpecifiedColumns(p.Style, p.Color);
            // Wypisujemy dostępne informacje (zgodne z def. klasy TwoSpecifiedColumns)
            foreach (TwoSpecifiedColumns item in data)
            {
                Console.WriteLine("Styl: {0}, Kolor: {1}", item.style, item.color);
            }
            Console.ReadKey();
        }
        static void NieokreslonyTypDanych(AdventureWorksDataContext dc)
        {
            // zwracamy nieokreślony typ danych z tabeli Product,
            // gdzie cena jest większa niż 500
            var data = from p in dc.Products
                       where p.ListPrice > 500
                       select new
                       {
                           // tworzymy nazwę dla kolumny Name
                           Nazwa_produktu = p.Name,
                           // tworzymy nazwę dla kolumny ProductLine
                           Linia_produktu = p.ProductLine
                       };
            // Wypisujemy dostępne informacje (zgodne z naszą definicją)
            foreach (var item in data)
            {
                Console.WriteLine("Nazwa: {0}, Linia: {1}", item.Nazwa_produktu, item.Linia_produktu);
            }
            Console.ReadKey();
        }
        static void PrzeszukiwanieWieluTabel(AdventureWorksDataContext dc)
        {
            // poniższe zapytanie zwraca dane, których cena jest większa niż 300
            // oraz nazwa kategorii sprawdzana w innej tabeli to "Clothing"
            // zapytanie to jest zapytaniem krzyżowym, tzn. powstała kolekcja
            // jest krzyżowym połączeniem pomiędzy wszystkimi produktami o cenie wyższej niż 300
            // oraz wszystkimi kategoriami o nazwie "Clothing"
            var data = from p in dc.Products
                       from pc in dc.ProductCategories
                       where p.ListPrice > 300 && pc.Name == "Clothing"
                       select new
                       {
                           Nazwa_produktu = p.Name,
                           Nazwa_kategorii = pc.Name
                       };
            // Wypisujemy dostępne informacje (zgodne z naszą definicją)
            foreach (var item in data)
            {
                Console.WriteLine("Nazwa produktu: {0}, Nazwa kategorii: {1}", item.Nazwa_produktu, item.Nazwa_kategorii);
            }
            Console.ReadKey();
        }
        static void PrzeszukiwaniePolaczonychTabel(AdventureWorksDataContext dc)
        {
            // poniższe zapytania zwraca nam dane z tabeli Product oraz ProductSubcategory
            // które mają doładnie takie samo 'ProductSubcategoryID'
            var data = from p in dc.Products
                       from psc in dc.ProductSubcategories
                       where p.ProductSubcategoryID == psc.ProductSubcategoryID
                       select new { p.ProductID, p.ProductSubcategoryID, psc.Name };
            // Wypisujemy dostępne informacje (zgodne z naszą definicją)
            foreach (var item in data)
            {
                Console.WriteLine("Id: {0}, Id Subkategorii: {1}, Nazwa: {2}", item.ProductID, item.ProductSubcategoryID, item.Name);
            }
            Console.ReadKey();
        }
        static void PrzeszukiwaniePolaczonychTabelEntityRef(AdventureWorksDataContext dc)
        {
            // Przeszukiwanie połączonych tabel przy użyciu EntityRef - wyjaśnienie a artykule
            var data = from p in dc.Products
                       select new
                       {
                           SubcategoryName = p.ProductSubcategory.Name,
                           ProductId = p.ProductID,
                           ProductName = p.Name
                       };
            // Wypisujemy dostępne informacje (zgodne z naszą definicją)
            foreach (var item in data)
            {
                Console.WriteLine("Nazwa Subkategorii: {0}, Id Produktu: {1}, Nazwa: {2}", item.SubcategoryName, item.ProductId, item.ProductName);
            }
            Console.ReadKey();
        }
        static void UPDATE(AdventureWorksDataContext dc)
        {
            // dokonamy update wszystkich rekordow w ktorych nazwa produktu zaiwiera "Tube"
            var update = from p in dc.Products
                         where p.Name.Contains("Tube")
                         select p;
            // Zmieniamy nazwę na inną, przy czym nazwa musi być unikalna
            // wg. projektu tej konkretnej bazy danych
            int i = 0;
            foreach (var item in update)
            {
                item.Name = "tuuube" + i.ToString();
                i++;
            }
            // Zapisujemy zmiany
            dc.SubmitChanges();
            // a teraz sprawdzimy czy update się powiódł
            var data = from p in dc.Products
                       where p.Name.Contains("tuuube")
                       select p;
            foreach (var item in data)
            {
                Console.WriteLine("Id: {0}, Nazwa: {1}", item.ProductID, item.Name);
            }
            Console.ReadKey();
        }
        static void INSERT(AdventureWorksDataContext dc)
        {
            // tworzymy nowy obiekt klasy ProductCategory 
            ProductCategory prod = new ProductCategory();
            prod.Name = "Prosty Test";
            prod.ModifiedDate = DateTime.Now;
            // Wywołujemy metode InsertOnSubmit oraz zaspisujemy dane
            dc.ProductCategories.InsertOnSubmit(prod);
            dc.SubmitChanges();
            // zwracamy ostatni rekord w celu sprawdzenia powyższego kodu
            var lastrow = (from p in dc.ProductCategories
                           orderby p.ProductCategoryID descending
                           select p).First();
            // Wyświetlamy ten element w konsoli
            Console.WriteLine("Id: {0}, Nazwa: {1}", lastrow.ProductCategoryID, lastrow.Name);
            Console.ReadKey();
        }
        static void DELETE(AdventureWorksDataContext dc)
        {
            // w pierwszej kolejności zwracamy dane, które chcemy usuanąć
            var data = from p in dc.ProductCategories
                       where p.Name.Contains("Prosty Test")
                       select p;
            // kasujemy zwrócone dane z naszej tabeli
            dc.ProductCategories.DeleteAllOnSubmit(data);
            // zapisujemy zmiany
            dc.SubmitChanges();
            // dla testu zwracamy wszystkie rekordy z tabeli
            var afterChanges = from p in dc.ProductCategories
                               select p;
            foreach (var item in afterChanges)
            {
                Console.WriteLine("Id: {0}, Nazwa: {1}", item.ProductCategoryID, item.Name);
            }
            Console.ReadKey();
        }
    }
    public class TwoSpecifiedColumns
    {
        // definiujemy pola publiczne
        public string style;
        public string color;
        // w konstruktorze ustalamy ich wartości
        public TwoSpecifiedColumns(string Style, string Color)
        {
            this.style = Style;
            this.color = Color;
        }
    }
}