Galin Iliev

Software Architecture & Development

LINQ to SQL

LINQ to SQL е ORM (object relational mapping), който позволява работа с таблици в релационна база данни използвайки класове в .NET. С други думи LINQ to SQL дава възможност за се пишат заявки на предпочитан език на .NET, които да манипулират данни в SQL Server.

Забележка: LINQ to SQL работи само с MS SQL Server. За да се работи с други релационни бази данни е необходим допълнителен LINQ Provider. В уеб пространството вече се говори, че съществуват такива за водещите системи за релационни бази данни.

Създаване на модела на базата

За да използваме LINQ to SQL е необходимо да създадем модел на базата в нашия .NET проект. Този модел много наподобява DataSet, понеже в него се съдържат специфични класове, представящи конкретните таблици в базата.

1.       За целта отваряме нов проект във Visual Studio 2008

2.       От менюто Project -> Add New Item избираме Linq to SQL Classes и в полето Name въвеждаме Northwind.

За да увеличите снимката щракнете върху нея

3.      Добавяме нова връзка към базата Northwind в Server Explorer (от менюто View -> Server Explorer). За целите на демонстрацията ще използваме примерната база  Northwind, а на вас оставям инсталацията на MS SQL Server.

4.       Издърпайте последователно таблиците ( Categories, Products, Suppliers, Orders, OrderDetails ) докато се получи следната диаграма. Забележете, че обектите са в единствено число.

За да увеличите снимката щракнете върху нея

5.       От Server Explorer->servername.Northwind.dbo->Stored Procedures придърпайте процедурата "Ten Most Expensive Products" върху панела вдясно.

6.       Запишете промените и сме готови за действие.

С горната поредица от действия създадохме набор от класове за работа със съответните таблици и един специален клас за възка с базата – NorthwindDataContext наследник на DataContext. Този клас е основата на всички колекции извлечени от базата и затова преди да започнем за изпълняваме заявки към базата трябва да създадем негова инстанция. При генерирането на NorthwindDataContext се създавават няколко конструктора, които приемат указания за връзка към сървъра като текст (connection string) или инстанция на IDbConnection, а конструктура по подразбиране (този без параметри) използва връзката записана в конфигурационния файл. Това позволява голяма гъвкавост при посочване местоположението на базата.

Важно: Всички колекции извлечени от една инстанция на DataContext класа съдържат споделени инстанции на обектите. Например ако извлечем две колекции от продукти – едната с продуктите, започващи с буква “A”, а другата с 10-те най-скъпи продукта и се окаже, че 3 продукта ги има и в двете колекции, то ако вземем инстанцията на такъв продукт от колекцията с имената и му променим цената, то ще се промени и цената на обекта в колекцията с 10-те най-скъпи продукта.

Горните класове може да се генерират и с помощта на инструмента SqlMetal - http://blogs.msdn.com/sanjeets/archive/2007/06/07/how-to-use-sqlmetal-exe-to-create-a-class-from-xml-and-database.aspx

Извличане на данни

Имайки построеният модел и генерираните класове можем да извлечем данни от базата с LINQ заявка, така както бихме го направили с помощта на LINQ to Objects:

NorthwindDataContext db = new NorthwindDataContext();

var products = from p in db.Products
              
where
p.Category.CategoryName == "Seafood"
              
select
p;

foreach (var p in products) {
    
Console
.WriteLine("{0}\t\t{1}", p.ProductName, p.UnitPrice);
}

Забележка: Данните не се извличат на реда, започващ с var products. На този етап LINQ заявката се преобразува в SQL заявка, но се изпълнява едва когато се извлече някой елемент от резулата – в нашият случай – на реда foreach (var p in products).

Страниране на данни

Много често в практиката се налага да не извличаме всички редове от таблицата наведнъж, а използвайки страниране ни трябват X реда, от общо Y, започвайки от ред с № Z.  Затова нека да покажем как може да извлечем 50 продукта от базата, започвайки от продукт № 40:

NorthwindDataContext db = new NorthwindDataContext();

var paging = (from p in db.Products
              select
p).Skip(40).Take(50);

Изключително лесно и удобно, нали? А ето и каква заявка се генерира от

Промяната на данни също е толкова лесна – в следващият пример създаваме заявка, от която извличаме един елемент и му променяме някои от свойствата:

NorthwindDataContext db = new NorthwindDataContext();

var productChai = (from p in db.Products

               where p.ProductName == "Chai"

               select p).Single();

 

productChai.UnitsInStock = 55;

productChai.UnitPrice = 2.55;

 

db.SubmitChanges();

Забележете последният ред - db.SubmitChanges(); всъщност записва промените в базата. Преди този ред промените са само в паметта.

Вмъкване на данни

Вмъкването на данни става на два етапа – първо се създава инстанция на обекта, после се добавя в колекцията. ( Подобно на добавяне на DataRow в DataTable ). В следващият пример добавяме два нови продукта и един доставчик, като присвояваме доставчика на едниният продукт:

NorthwindDataContext db = new NorthwindDataContext();

Product bicycle = new Product();

bicycle.ProductName = "Bicycle";

bicycle.UnitPrice = 160.5M;

 

Supplier supp = new Supplier { CompanyName = "Supplier 1", ContactName = "Bai Spiro" };

 

Product chair = new Product { ProductName = "Chair", UnitsInStock = 56 };

chair.Supplier = supp;

 

db.Suppliers.Add(supp);

db.Products.Add(bicycle);

db.Products.Add(chair);

 

Изтриване на данни

Изтриването е подобно –премахваме елементи от колекцията. В примера избираме колекция от продукти, които после изтриваме с помощта на метода RemoveAll:

NorthwindDataContext db = new NorthwindDataContext();

var productForDelete = from p in db.Products

                       where p.ProductName.Contains("Chef")

                       select p;

 

db.Products.RemoveAll(productForDelete);

С това разгледахме CRUD (Create, Read, Update, Delete) операциите и видяхме по какъв начин се извършва всяка една от тях. С това, обаче, не се изчерпват възможностите на LINQ to SQL – същите операции могат да бъдат извършени по няколко начина, а тук разгледахме само по един. Може да разгледаде връзките в края на този документ. Те могат да бъдат доста полезни в изучаването на C# 3.0 и LINQ to SQL.

Извикване на съхранени процедури (stored procedures)

До момента разгледахме начините за работа с данни през код написан на C#, за самите SQL заявки се генерираха от LINQ. Функционалността на LINQ to SQL не би била пълна ако не предоставяше възможност за извикване на съхранени процедури. Дали е по-добре да се изпълняват заявки от кода или да се извикват съхранени  процедури? Има много изписано по темата, но накратко казано съхранените процедури са за предпочитане, защото са компилирани на SQL сървъра и той е подготвен за изпълнението им ( това е наистина кратко обяснение – ако се нуждаете от повече се обърнете към документацията на MS SQL Server ).

Забележете, че при създаване на модела на базата, в стъпка 5 добавихме съхранена процедура. Макар и малко прибързано това беше с цел да покажем къде се разполагат в диаграмата, но ще свърши работа, за да покажем как се извикват съхранени процедури.

Съхранените процедури се извикват като метод на DataContext класа, след като са добавени в модела на базата:

var spCall = db.Ten_Most_Expensive_Products();

Но нека да видим какво се получава при обхождането на резулата:

Но това не са свойствата на класа Product, които очакваме!

Това се получава по две причини: първата е самото тяло на съхранената процедура:

ALTER procedure "Ten Most Expensive Products" AS

SET ROWCOUNT 10

SELECT ProductName, UnitPrice

FROM Products

ORDER BY Products.UnitPrice DESC

Всяка съхранена процедура може да върне произволен брой полета от произволен брой таблици. Затова по подразбиране при пускането на съхранената процедура върху бялата повърхност на модела се генерира метод за извикването й, но и нов клас за резултата – погледнете на снимката на прозореца Class View по-горе.

За да получим резултат от тип, който вече имаме в диаграмата трябва да пуснем процедурата върху типа, от който желаем да е резулата. Нека да изтрием съдържанието на панела вдясно на модела (Northwind.dbml). Придърпваме Ten Most Expensive Productsвърху формата на класа Product и

Visual Studio 2008 проверява резултата на процедурата и ни уведомява, че схемата се различава и операцията не може да бъде изпълнена, както и че модела не е променен.

Налага се да променим връщаните полета от съхранената процедура:

ALTER procedure "Ten Most Expensive Products" AS

SET ROWCOUNT 10

SELECT *

FROM Products

ORDER BY Products.UnitPrice DESC

Записваме промените в процедурата, опитваме отново да я пуснем върху формата на класа Product и този път е успешно.

Извикване на потребителски функции (UDF)

Често в при работа данни създаваме потребителски функции, които могат да се ползват в SQL заявки и съхранени процедури. Въпреки, че пренасяме голяма част от работата с данни в C#, не е оправдано да изхвърляме или пренаписваме работещ код, още повече че той се изпълнява на сървъра и може да намали натоварването на клиента. Затова нека да разгледаме как става извикване на такава потребителска функция.

За целта ще създадем една проста функция, която умножава две числа:

CREATE FUNCTION dbo.udfMultiply (@num1 INT,  @num2 INT)

RETURNS INT

AS

BEGIN

RETURN (@num1 * @num2)

END

Подобно на добавянето на съхранена процедура я добавяме в модела на базата в нашия проект ( чрез влачене и пускане от Server Explorer върху  десния панел на Northwind.dbml)

След това можем да я извикваме като метод на наследника на DataContext класа, подобно на съхранената процедура. Нека да видим SQL кода, който се генерира за следната LINQ заявка (която връща имената на тези продукти, чието количество умножено по 2 е по-малка от 100):

NorthwindDataContext db = new NorthwindDataContext();

var udfCall = from p in db.Products

              where db.udfMultiply(p.UnitsInStock, 2) < 100

              select p.ProductName;

Стартираме проекта в режим на debug и показваме кода в SQL Server Query Visualizer:

За да увеличите снимката щракнете върху нея

Забележете извикването на функцията в where клаузата на заявката на последния ред.

 

 

Следваща част: LINQ to Entities

Content