Доступ к данным SQL Server с помощью LINQ

Благодаря языку запросов Language Integrated Query (LINQ), реализованному в Microsoft Visual Studio 2008, разработчики могут использовать собственный синтаксис в сочетании с традиционными языками программирования, такими как C# и Visual Basic (VB), для ссылки на объекты базы данных как на собственные объекты языка и создания запросов к этим объектам. Запросы Visual Studio 2008 и LINQ преобразуют код процедур в вызовы базы данных на основе T-SQL, направляемые в SQL Server. LINQ - отличный инструмент для быстрой разработки приложений, с помощью которого можно формировать довольно эффективные запросы, хотя во многих случаях квалифицированный администратор базы данных может оптимизировать их. На сегодня существует три версии LINQ: LINQ for SQL, LINQ for XML и LINQ for Objects. В данной статье рассматривается LINQ for SQL, именуемый просто LINQ.

Как новая технология .NET, LINQ является частью библиотек Microsoft .NET Framework 3.5. Для использования LINQ в проектах Visual Studio 2008 нужно располагать платформой .NET Framework 3.5. У LINQ есть ряд особых требований к типам данных, которые не поддерживаются предыдущими версиями .NET, что заставило разработчиков Microsoft внести существенные изменения в основные языки .NET. Например, в C# появился тип var, а в VB - соответствующие типы. С помощью этих типов объектов .NET можно указать такой тип, при котором тип данных результата запроса неизвестен до выполнения запроса.

Основы LINQ

Коротко расскажу о принципах действия LINQ. Разработчикам Visual Studio 2008 предоставляется интегрированный инструментарий для технологий доступа к данным в SQL Server 2005. Обратите внимание, что компания Microsoft не выпустила пакет обновления SQL Server 2005 для поддержки NET Framework 3.5 и LINQ, так как во внутренних механизмах LINQ используются ADO. NET и существующие методы доступа к данным, реализованные в .NET Framework 2.0.

В LINQ объектная модель представляет источник данных. Затем LINQ ссылается на этот источник данных как на объект DataContext (например, System.Data.Linq.DataContext). Объект DataContext инкапсулирует строку соединения ADO. NET для базы данных. Затем объект DataContext используется с набором определений объекта (например, System.Data.Linq.Mapping.TableAttribute) для таблиц, хранимых процедур и функций в базе данных. Для каждого объекта базы данных, которому дано определение, требуется объект DataContext.

Создание классов с использованием SQLMetal

Существует два варианта создания классов, необходимых для использования LINQ в исходном тексте прикладных программ. Можно вручную ввести все необходимые классы с помощью функции объектно-реляционного сопоставления Visual Studio. Другой способ - применить инструменты типизированных наборов данных для управления доступом и извлечением данных, а затем использовать LINQ для запроса наборов результатов, созданных типизированными наборами данных. Конечно, при использовании объектно-реляционного сопоставления связи между таблицами в базе данных и создаваемыми объектами могут быть только 1:1. Как показывает метод Load, связи 1:1 не всегда удовлетворяют требованиям разработчиков. Поэтому в данной статье будет рассказано о том, как использовать SQLMetal для формирования классов сущностей для объектов базы данных.

SQLMetal.exe - бесплатная утилита сопоставления базы данных, поставляемая вместе с Visual Studio 2008. Этот инструмент командной строки находится в каталоге Program FilesMicrosoft SDKsWindowsV6.0ain. SQLMetal формирует необходимую сущность данных и объект DataContext для LINQ в исходном файле .vb или .cs. Чтобы создать исходные файлы VB для базы данных на локальном SQL Server и включить в них хранимые процедуры, нужно открыть командное окно, перейти в каталог установки (или дать ссылку на инструмент SQLMetal в этом каталоге) и запустить следующую команду:

SQLMetal.exe/server:.SQLEXPRESS
/database: AdventureWorks/
sprocs/functions/language: vb/
code: AdventureWorks.vb

Эта команда создает файл AdventureWorks.vb в текущем каталоге. Обратите внимание, что нужно изменить ссылку сервера, так как .SQLEXPRESS ссылается на экземпляр SQL Express на моем локальном сервере. Этот параметр должен содержать ссылку для локального компьютера или имя сервера базы данных. Параметр database: AdventureWorks указывает обрабатываемую базу данных. Параметры sprocs и function указывают, что SQLMetal должен сформировать файлы сущностей для поддержки хранимых процедур и функций в базе данных. Параметры language: vb и code: AdventureWorks.vb указывают соответственно язык программирования и целевой исходный файл. Чтобы создать файл AdventureWorks в текущем каталоге на языке C#, необходимо заменить параметр language: vb на language: cs, а расширение vb на cs. После того как эта команда была выполнена на тестовом компьютере, полученный файл содержал около 20 тыс. строк исходного текста.

Использование LINQ

После того как сформированы сущности базы данных, файл AdventureWorks.vb можно добавить в любой проект. Для этого требуется подготовить новый проект VB Windows Forms. Щелкните на проекте правой кнопкой мыши в Solution Explorer и на пункте Add Existing Item в контекстном меню, чтобы открыть диалоговое окно Add Existing Item, с помощью которого можно перейти к файлу AdventureWorks.vb. Затем добавьте этот файл к проекту.

После того как файл AdventureWorks.vb добавлен к проекту, фоновый компилятор VB выдает сотни ошибок, так как Visual Studio 2008 не вводит ссылки, необходимые для разбора LINQ в SQL. Чтобы избавиться от ошибок, перейдите к свойствам проекта, щелкните на вкладке References и добавьте System.Data.LINQ в качестве ссылки на проект. Затем щелкните на вкладке Load Web Settings и добавьте параметр строки соединения AdvWorksDB для базы данных. В этом параметре строки соединения нужно использовать те же значения, что и в SQLMetal для создания исходного файла. Обратите внимание, что нужно указать режим безопасности (например, Integrated Security) в параметре строки соединения.

Затем следует подготовить простую форму. В данном примере элемент управления DataGridView помещен в верхней части формы, а ниже расположены четыре кнопки команд. Расположение кнопок не имеет большого значения, но их нужно назвать ButtonLoad, ButtonAdd, ButtonEdit и ButtonDelete. Дважды щелкните на каждой кнопке конструктора, чтобы Visual Studio 2008 автоматически сформировал обработчик событий Click для каждой кнопки.

Затем нужно дважды щелкнуть в области конструктора формы, чтобы подготовить событие Load для формы. Затем измените событие Load с использованием исходного текста, показанного в листинге 1.

Фрагмент A в листинге 1 показывает определение объекта DataContext с именем AdvWorksDC, который использует строку соединения базы данных, определенную ранее. Этот объект DataContext определен в области формы, которая обеспечивает повторное использование объекта DataContext в обработчиках событий в форме. Вторая строка исходного текста во фрагменте A (листинг 1) определяет объект сущности (т. е. Department) для таблицы HumanResources_Department из базы данных AdventureWorks, которая также используется в нескольких обработчиках событий.

Исходный текст во фрагменте B листинга 1 определяет событие Form Load для показа. В событии Load во фрагменте B листинга 1 отключаются кнопки Add, Edit и Delete. Событие Load происходит только один раз, поэтому наступает удобный момент, чтобы создать начальный запрос данных для заполнения таблицы. Вместо простого запроса, который можно легко добавить, обновить или удалить, этот сложный запрос лучше иллюстрирует формат запросов LINQ. Отличие запросов LINQ от запросов SQL заключаются в том, что они начинаются с предложения FROM. Это предложение позволяет задать целевую таблицу в оперативной памяти, в которой содержится определение запроса. В разделе In предложения FROM указывается, где в объекте DataContext будет сделан запрос. После того как определен контекст запроса, механизм LINQ может предоставить технологию IntelliSense для таблиц и столбцов, доступных в запросе.

Запрос во фрагменте B листинга 1 использует инструкцию Join, которая называет вторую таблицу и указывает, какие столбцы будут использоваться для соединения двух таблиц. Запрос также содержит предложение WHERE, чтобы ограничить число возвращаемых результатов. Результат - объект DS, созданный как объект запроса на основе типа интерфейса. Этот объект запроса обслуживает базовый запрос и предоставляет перечислитель, позволяющий извлечь каждую строку результатов. Затем объект запроса назначается как источник данных для таблицы данных. Результирующая таблица (показанная на экране 1) не обеспечивает редактирования или добавления элементов на этапе выполнения. Требуется создать функциональный эквивалент инструкции T-SQL, в котором результаты назначаются набору данных.

Добавление строк в таблицу

Теперь можно скомпилировать и запустить приложение, которое выполнит запрос, определенный в событии Load. Эти данные загружаются в таблицу данных. Однако запрос ссылается на таблицу с несколькими связями. Чтобы проиллюстрировать операции Insert, Update и Delete, будет использован запрос, который ссылается на таблицу без связей.

В листинге 2 показана реализация трех ключевых методов процесса: ButtonLoad_Click, Bind- Grid и ButtonAdd_Click. Метод ButtonLoad_Click перезагружает таблицу с другой таблицей (т. е. таблицей HumanResources_Department). Метод BindGrid создает собственно запрос LINQ, направленный к объекту DataContext для записей в таблице, а затем обновляет источник данных DataGridView с помощью нового запроса. Запрос демонстрирует предложение ORDER BY, знакомое разработчикам T-SQL. Наконец, метод ButtonAdd_Click активизирует кнопку Add (см. экран 2).

Кнопка Add вызывает метод ButtonAdd_Click в листинге 2, чтобы добавить новую запись в текущую таблицу. Этот метод использует объект сущности, созданный как часть определения формы в листинге 1. Затем объект сущности ассоциируется с новым созданным экземпляром подразделения, как показано в первой строке метода ButtonAdd_Click в листинге 2. В этой строке исходного текста используется один из новых синтаксических элементов VB, который задает значения свойствам объекта при его создании. Синтаксис With {.PropertyName = value} позволяет задавать значения свойств объекта при его создании с использованием инструкции New. В строке показано создание новой сущности Department, которая будет отражать строку, которая обновляется в базе данных.

Затем нужно вставить новую сущность подразделения в таблицу. Обновление таблицы - двухэтапный процесс: во-первых, нужно связать новый объект подразделения со списком поставленных в очередь инструкций вставки объекта базы данных с помощью метода InsertOnUpdate. Этот метод указывает LINQ, что объект будет вставлен в таблицу, и позволяет определить несколько новых объектов перед обновлением базы данных. При использовании LINQ эти и другие обновления остаются локальными, пока выполняется метод SubmitChanges. Во-вторых, метод SubmitChanges обеспечивает получение кэшированных обновлений данных и применение сформированного программного кода T-SQL, связанного с каждым из них, к источнику данных SQL. Если нужно добавить коллекцию сущностей, можно отложить обновление базы данных до тех пор, пока не будут созданы все сущности. После щелчка на кнопке Add дисплей обновляется, и вновь созданное подразделение появляется в начале списка, показанного на экране 3.

Обновление и удаление строк

После того как строка вставлена, рассмотрим, как ее обновить. Поскольку объект сущности был создан из текущего объекта DataContext, требуется лишь обновить одно из свойств объекта сущности, указав значение, которое нужно поместить в базу данных. В нашем примере название подразделения изменится с Bike Computers на Fitness Computers.

Для обновления базы данных вызовите метод SubmitChanges объекта DataContext. На экране 4 показано, как название подразделения меняется с Bike Computers на Fitness Computers после выполнения метода SubmitChanges. Новое название подразделения заменяет первоначальное. Теперь доступна только кнопка Delete.

Теоретически удалить строку так же легко, как обновить; однако внутренний механизм сложнее. По умолчанию LINQ использует оптимистическую схему блокировки. Если LINQ полагает, что базовые данные объекта изменились, то объект не будет обновлен и возвращается сообщение об ошибке с указанием, что строка не найдена или изменилась. Это сообщение об ошибке будет отображаться также при многократном редактировании сущности или попытке удалить сущность после редактирования.

Одно из решений этой проблемы - заменить экземпляр объекта DataContext новым экземпляром DataContext. Первые две строки обработчика событий ButtonDelete_Click (см. листинг 3) воссоздают объект DataContext и связывают объект entity в оперативной памяти со вновь созданным объектом DataContext. Но это решение может повлиять на производительность, так как требует создать новое соединение с базой данных.

После того как получен вызов для обновления объекта DataContext на месте, вызывается метод DeleteOnSubmit, чтобы удалить текущую сущность из таблицы, а затем передать изменения для действительного удаления строки сущности из таблицы. После щелчка на кнопке Delete вновь появляется картина, подобная показанной на экране 2, и процесс можно повторить. Обратите внимание, что метод DeleteOnSubmit заменяет метод Remove из предшествующих версий LINQ.

Взгляд разработчиков на LINQ

С точки зрения разработчика, ориентированного на серверный компонент и знакомого с T-SQL, LINQ - еще один интерфейс запросов, который нужно освоить, но вряд ли его удастся использовать с большой выгодой. Для разработчика интерфейса пользователя, лучше знакомого с привязкой данных и управлением данными, LINQ - превосходный инструмент, который упрощает доступ к данным.

Эти два представления суммируют оценку LINQ. Как инструмент RAD, LINQ - мощное дополнение к арсеналу разработчика .NET. Расширяемый интерфейс автоматизирует потенциально сложные запросы. Однако для профессионала, желающего оптимизировать доступ к данным, LINQ вряд ли повлияет на выполнение повседневных задач.