Высокоуровневые методы информатики и программирования Лекция 30 Работа с БД с использованием технологий связывания

Технология LINQ LINQ to DataSet LINQ to SQL

LINQ to DataSet «LINQ to DataSet» это новая технология ADO.NET. Позволяет выполнять LINQ запросы к объектам DataSet. Можно выполнять LINQ запросы к типизированным DataSet Для работы с не типизированным DataSet нужно использовать метод запрос LINQ сделать нельзя!!!

Работа с не типизированным DataSet string cnStr Source=(local)\SQLEXPRESS;Initial Catalog=Northwind; Integrated Security=True"; // создаем адаптер SqlDataAdapter da = new SqlDataAdapter("select * from Customers", cnStr); SqlCommandBuilder cmb = new SqlCommandBuilder(da); // создаем другие команды адаптера DataSet ds = new DataSet();// создаем DataSet da.Fill(ds, Customers); // заносим из БД таблицу в DataSet DataTable tbl = ds.Tables[Customers];// получаем ссылку на таблицу (не обязательно) // выводим данные записей на печать foreach(DataRow r in tbl.Rows) Console.WriteLine("id = {0}, name = {1};",r["CustomerID"],r["ContactName"]); Console.WriteLine("============"); // пример LINQ запроса к нетипизированной таблице var set = from c in tbl.AsEnumerable() where ((string)c["City"] == "London") select c; foreach (var r in set) Console.WriteLine("id = {0}, city = {1};", r["CustomerID"], r["City"]);

LINQ запрос типизированному DataSet using AdoSample.NorthwindDSTableAdapters; // AdoSample – название проекта … CustomersTableAdapter ca = new CustomersTableAdapter(); NorthwindDS ds = new NorthwindDS(); ca.Fill(ds.Customers); var results = from c in ds.Customers where c.City == "London select c; foreach (var c in results) Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);

Получение ссылки на одну запись в типизированном DataSet try { NorthwindDataSet.CustomersRow cr = ds1.Customers.SingleOrDefault(c => c.City == "ЛОНДОН"); if (cr != null) cr.City = "London"; } catch (Exception ex) {// более чем одна запись MessageBox.Show(ex.Message); }

Пояснение LINQ запроса Запрос следующего вида: var results = from c in ds.Customers where c.City == "London" select c; Можно прочитать как: Из объектов с входящих во множество Customers, которые удовлетворяют условию с.City == London выбрать объекты с

LINQ to SQL «LINQ to SQL» это новая технология семейства технологий ADO.NET. Она работает только с Microsoft SQL Server. Основная цель LINQ to SQL является обеспечение согласованности между реляционными БД и программной логикой взаимодействия с ними. LINQ to SQL позволяет встроить доступ к данным в код программы. При программировании с помощью LINQ to SQL скрываются множество типов ADO.NET, таких как SqlConnection, SqlCommand, or SqlDataAdapter. Вместо того, чтобы обрабатывать реляционную БД в виде потока записей, можно рассматривать их в виде коллекций объектов определенного класса – класса сущностей.

Для работы с технологией LINQ to SQL требуется Добавить к проекту ссылку (Reference) на компонент: System.Data.Linq.dll Задать в программе используемые пространства имен using System.Data.Linq; using System.Data.Linq.Mapping;

Классы сущностей (entity class) Классы сущностей (entity classes) это классы программы, которые представляют данные содержащиеся в реляционной БД, с которой выполняется работа. С программной точки зрения классы сущностей это описания классов, которые аннотированы с помощью специальных атрибутов технологии «LINQ to SQL» (таких, как [Table] и [Column]), которые связывают их с физическими таблицами в БД. Например, класс сущностей для таблицы Customers: [Table(Name = "Customers")] public class Customer { public string CustomerID; //... public string City; } Большинство атрибутов «LINQ to SQL» определены в пространстве имен System.Data.Linq.Mapping. В Visual Studio 2008 (и SDK Framework 3.5) включены специальные программы, которые автоматически создают классы сущностей, требуемые для работы приложения.

Атрибуты класса сущностей Атрибут Table. С ним можно задавать следующие параметры: – Name – имя таблицы, которой соответствует, описываемый класс сущностей. – Например: [Table (Name = xxxxx)] Атрибут Column. С ним можно задавать следующие параметры: – Name – имя соответствующей колонки в таблице; – DbType – тип поля записи; – CanBeNull – может ли быть значение null у поля записи. – IsPrimaryKey – указание, что поле является первичным ключом (!!! Если первичный ключ не указан, то таблицу будет только для чтения ReadOnly !!!). – Например: [Column (IsPrimaryKey=true)]

Пример описания класса сущностей [Table(Name = "Customers")] //

Пример описания класса сущностей [Table(Name = "Person.Contact")] public class Contact { [Column(DBType = "int not null", IsPrimaryKey=true, IsDBGenerated=true)] public int ContactID; [Column(DBType = "nvarchar(8) not null")] public string Title; [Column(DBType = "nvarchar(50) not null")] public string FirstName; [Column(DBType = "nvarchar(50) not null")] public string MiddleName; [Column(DBType = "nvarchar(50) not null")] public string LastName; [Column(DBType = "nvarchar(50) not null")] public string Address; [Column(DBType = "int")] public int Promotion; [Column(DBType = "bit")] public byte NameStyle; [Column(DBType = "varchar(40)")] public string PasswordHash; [Column(DBType = "varchar(40)")] public string PasswordSalt; }

Класс DataContext После того, как описан класс сущностей запросы к СУБД передаются с помощью класса DataContext. Данный класс отвечает за трансляцию LINQ запросов в соответствующие SQL запросы и передачу их конкретной БД. В некотором смысле DataContext похож на объект Connection, так как он также требует строку соединения. Однако, в отличии от класса Connection, класс DataContext имеет методы, которые связывают результаты выполнения запроса (выборку записей) с описанными классами сущностей. Класс DataContext описывает способ получения экземпляров класса сущностей, которые могут использоваться в программе. После получения экземпляров сущностей можно менять их состояние любым желательным способом (добавлять, изменять и т.п.) и предоставлять измененный объект назад для последующей обработки. В этом смысле класс DataContext похож на класс DataAdapter.

Методы DataContext Конструктор: DataContext ctx = new DataContext( ) Методы: – проверки соединения с базой данных DatabaseExists() – если true, то соединение выполнено успешно. – получение таблицу Table GetTable () Например: Table invTable = ctx.GetTable (); – Метод сохранения изменений SubmitChanges() ctx.SubmitChanges();

Класс Table Описывает таблицу указанного типа в базе данных. Типизированный класс, для которого задается используемый им тип данных Table tbc; Хранит объекты классов сущностей, того класса, который указан в угловых скобках. Предоставляет методы для работы LINQ запросов. Свойство IsReadOnly возвращает true если таблица описана только для чтения из БД.

Методы класса Table Для получения ссылки на объект класса Table используется метод GetTable класса DataContext: DataContext cnt = new DtaContext(strconn) ; Table tbl = cnt.GetTable ; Добавления новой записи в таблицу InsertOnSubmit() tbl.InsertOnSubmit(object); Удаление записи из таблицы DeleteOnSubmit() tbl.DeleteOnSubmit(object); Пример: …

Получение одного объекта таблицы С помощью метода SingleOrDefault класса Table (если объектов больше 1, то генерируется исключение) DataContext dcn = new DataContext(cnStr); Table customers = dcn.GetTable (); MyCustomer mc = customers. SingleOrDefault(c => c.CustomerID == "CONSH"); if(mc != null) mc.City = "ЛОНДОН"; dcn.SubmitChanges(); C помощью метода FirstOrDefault класса Table var first = dcx.Customers.FirstOrDefault(c => (c.City == "London")); C помощью метода Fitst() коллекции IEnumerable: var row = (from c in customers where c.City == Moscow select c).First();

Пример работы с таблицей // Создаем объект класса DataContext DataContext db = new DataContext(cnStr); // получаем объект класса Table для класса // сущностей Inventory Table invTable = db.GetTable (); // создаем выборку всех элементов класса LINQ запрос var inv = from c in invTable select c; // выводим на экран элементы выборки foreach (var car in inv) Console.WriteLine(car.ToString());

LINQ запрос к DataContext var db = new DataContext Source=.\sqlexpress;Initial Catalog=Northwind"); var results = from c in db.GetTable () where c.City == "London" select c; foreach (var c in results) Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);

Описание класса сущностей [Table(Name = "Customers")] public class Customer { [Column] public string CustomerID { get; set; } [Column] public string City { get; set; } public override string ToString() { return CustomerID + "\t" + City; }

Добавление записи в БД // создаем новый экземпляр класса сущностей Table tbc = ctx.GetTable () Customer newCust = new Customer (); // задаем его свойства newCust.ID = 1111; newCust.City = Moscow"; // заносим созданный объект в контекст tbc.InsertOnSubmit(newCust); // заносим изменения в БД ctx.SubmitChanges();

Изменение колонок записи // находим нужную запись var row = (from c in ctx.Customers where c.City == Moscow" select c).First(); // меняем значение полей row.City = Berlin"; // переносим изменения в БД ctx.SubmitChanges();

Строго типизированный DataContext Описание class NorthwindDatabase : DataContext { public Table Products { get {return this.GetTable }; } public NorthwindDatabase(string connectionString): base(connectionString) {...} } Использование // Содание объекта NorthwindDatabase NorthwindDatabase db = new NorthwindDatabase(cnStr); // Теперь можно использовать поле Products foreach (var car in from c in db.Products select c) Console.WriteLine(car.ToString());

Удаление записи // ищем запись var row = (from c in ctx.Customers where (c.CustomerID == 1212) select c).First()); // удаляем из контекста ctx.Inventories.DeleteOnSubmit(row); // удаляем из базы данных ctx.SubmitChanges();

Автоматическое создание типизированного DataContext

Дизайнер LINQtoSQL

Автоматически формируемые классы Класс типизированного контекста – содержит свойства соответствующие таблицам. Классы сущностей, объекты которых соответствуют записям всех таблиц, включенных в типизированный контекст. Классы сущностей содержат: – Свойства соответствующие полям таблицы. – Связи один-ко-многим со стороны «многие» реализуются в виде свойства, которое возвращает соответствующую им запись из связанной таблицы. – Связи один-ко-многим со стороны «один» реализуются в виде свойств – коллекций, которые содержат соответствующие им записи из связанной таблицы.

Связь между объектами классов Запрос к архиву работы сотрудников в подразделениях компании Все отношения между таблицами реализованы с помощью связи между объектами классов: – Например: dep.Employee.Contact.LastName // Use a variant to hold the EmployeeDepartmentHistories // Use LINQ to query the database, passing in the last name var DepartmentHistories = from dep in db.EmployeeDepartmentHistories where dep.Employee.Contact.LastName == txtLastName.Text select dep; // Loop through the values and display in the list box foreach (EmployeeDepartmentHistory edh in DepartmentHistories) { StringBuilder sb = new StringBuilder(edh.Employee.Contact.FirstName); sb.Append("\t"); sb.Append(edh.Employee.Contact.LastName); sb.Append("\t"); sb.Append(edh.Department.Name); sb.Append("\t"); sb.Append(edh.Employee.VacationHours.ToString()); lstResults.Items.Add(sb.ToString()); }

ХМL файл соответствия (*.dbml) Создается вручную дизайнером типизированного DataContext Используется только дизайнером для создания файлов с описаниями классов

SqlMetal файл *.dbml файл *.desiner.cs с описаниями классов

Конструкторы типизированного класса DataContext // По умолчанию - Default Constructor AdventureWorksDataContext db = new AdventureWorksDataContext(); // С объектом Connection AdventureWorksDataContext db = new AdventureWorksDataContext(IDbConnection); // с именем исходного файла соответствия - mapping file AdventureWorksDataContext db = new AdventureWorksDataContext(string); // С объектом Connection и именем файла задания соответствия AdventureWorksDataContext db = new AdventureWorksDataContext(IDbConnection, MappingSource); // с именем файла соответствия и MappingSource классом AdventureWorksDataContext db = new AdventureWorksDataContext(String, MappingSource);

Методы типизированного DataContext Типизированный DataContext содержит специальные методы – для вставки записей (Insert ) Например: partial void InsertDepartment(Department instance); – для изменения записей (Update ) Например: partial void UpdateDepartment(Department instance); для удаления записей (Update ) Например: partial void DeleteDepartment(Department instance);

Пример LINQ запроса к типизированному DataContext string cnStr Source=(local)\SQLEXPRESS;Initial Catalog=Northwind; Integrated Security=True"; NortwindDCDataContext dcx = new NortwindDCDataContext(cnStr); var results = from c in dcx.Customers where c.City == "London" select c; foreach (var c in results) Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);

Запрос к нескольким таблицам LINQ запрос к двум таблица, включенным в типизированный DataContext: var labels = (from c in dcx.Customers join o in dcx.Orders on c.CustomerID equals o.CustomerID select new { name = c.ContactName, address = o.ShipAddress }).Distinct(); foreach (var c in labels) Console.WriteLine("contact name = {0}\taddress = {1}", c.name, c.address);

Работа с хранимыми процедурами

Содержание хранимой процедуры ALTER PROCEDURE [int] AS BEGIN SET NOCOUNT ON; -- Use recursive query to list out all Employees required for a particular Manager WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [Title], [RecursionLevel]) -- CTE name and columns AS ( SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], 0 -- Get the initial Employee FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID] WHERE e.[EmployeeID] UNION ALL SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] Join recursive member to anchor FROM [HumanResources].[Employee] e INNER JOIN [EMP_cte] ON e.[EmployeeID] = [EMP_cte].[ManagerID] INNER JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID] ) -- Join back to Employee to return the manager name SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName], [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName' -- Outer select from the CTE FROM [EMP_cte] INNER JOIN [HumanResources].[Employee] e ON [EMP_cte].[ManagerID] = e.[EmployeeID] INNER JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID] ORDER BY [RecursionLevel], [ManagerID], [EmployeeID] OPTION (MAXRECURSION 25) END;

Example: Writing and Reading XML Data - 1 Wolfgang Beer - 2 Dietrich Birngruber 1 Dietrich Birngruber Didi Wolfgang Beer... 1 ds.writeXML("personcontact.xml"); Write data to XML file DataSet ds = new DataSet(); ds.readXML("personcontact.xml", XmlReadMode.Auto); Read data from XML –with XmlReadMode.Auto a schema is generated automatically

ADO.NET Entity Framework ADO.NET Entity Framework (EF) является более обширной технологией, чем Object Relation Mapping (ORM). Целью EF является выполнение более сложных задач, чем стандартное ORM. Она включает концептуальную модель (conceptual model) как нечто конкретное, с помощью специальной среды (framework) для создания абстрактной модели на основе реляционной модели, тем самым решая задачу несоответствия между ними (impedance mismatch). Основным элементом EF является уровень абстракции, который разделен на концептуальный, согласующий (mapping) и логический уровни, которые составляют Entity Data Model (EDM). Кроме этого, EF включает: – два прикладных интерфейса APIs, – объектные сервисы (object services) и – клиента сущностей (entity client), для работы с EDM, – две конструкции для работы с данными: Entity SQL (ESQL) и LINQ to Entities. Очень похожа на LINQ to SQL. Отличие: – может работать с разными реляционными БД; – в отличие от LINQ to SQL поддерживает не только прямое соответствие между классами и таблицами; – поддерживает наследие классов; – Для сложных приложений уровня Предприятия. Другие ORM технологии: NHibernate, EntitySpaces и LLBLGen Pro.

Создание EDM модели в проекте Добавить в проект новый элемент - «ADO.NET Entity Data Model» Работа с EDM: var departmentHistories = from dep in aw.EmployeeDepartmentHistory select dep; foreach (var edh in departmentHistories) { Console.WriteLine(edh.Employee.Contact. Address); }

Модель сущностей данных - Entity Data Model (EDM) это спецификация для описания данных, которые используются приложениями разрабатываемыми с помощью Entity Framework. Модель EDM включает три уровня (структуры метаданных): – концептуальный уровень; – уровень согласования – логический уровень. Данные уровни (структуры) определяются, как схема проектирования (design schema, ваш.edmx файл) и включают следующие языки: – Язык Описания Концептуальной Схемы (Conceptual Schema Definition Language, CSDL): Язык CSDL описывает концептуальную модель используемых в приложении данных (классов). Данная XML структура включает типы (сущности) используемые в программе и их отношения и описывает объектный код. – Язык Описания Хранимых Схем (Stored Schema Definition Language, SSDL): Язык SSDL описывает структуру базы данных и данных, содержащихся в ней. Данная XML структура используется для описания логического уровня разрабатываемого приложения. – Язык Описания Соответствия (Mapping Specification Language, MSL): Язык MSL является языком описания метаданных, которые задают соответствие концептуальной схемы, описанной на языке CSDL с логической моделью, описанной на языке SSDL. Данная XML структура является информацией, задающей соответствие (mapping information) между сущностями приложения с базой данных.

Приложение База данных Уровень Описания Хранимых Схем Уровень Описания Соответствия Уровень Описания Концептуальной схемы Модель сущностей данных - Entity Data Model (EDM)

ADO.NET Entity Framework