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