Высокоуровневые методы информатики и программирования Лекция 30

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

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

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

4. Работа с не типизированным DataSet string cnStr = @"Data 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"]);

5. 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);

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

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

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

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

10. Классы сущностей (entityclass) • Классы сущностей (entityclasses) это классы программы, которые представляют данные содержащиеся в реляционной БД, с которой выполняется работа. • С программной точки зрения классы сущностей это описания классов, которые аннотированы с помощью специальных атрибутов технологии «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. • В VisualStudio 2008 (и SDK Framework 3.5)включены специальные программы, которые автоматически создают классы сущностей, требуемые для работы приложения.

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

12. Пример описания класса сущностей [Table(Name = "Customers")] // <- атрибут таблицы public class Customer { [Column (IsPrimaryKey=true)] // <- атрибут поля public string CustomerID { get; set; } [Column] // <- атрибут поля public string City { get; set; } public override string ToString() { return CustomerID + "\t" + City; } }

13. Пример описания класса сущностей [Table(Name = "Person.Contact")] public class Contact { [Column(DBType = "int not null", IsPrimaryKey=true, IsDBGenerated=true)] public intContactID; [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 EmailAddress; [Column(DBType = "int")] public intEmailPromotion; [Column(DBType = "bit")] public byte NameStyle; [Column(DBType = "varchar(40)")] public string PasswordHash; [Column(DBType = "varchar(40)")] public string PasswordSalt; }

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

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

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

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

18. Получение одного объекта таблицы • С помощью метода SingleOrDefaultкласса Table<> (если объектов больше 1, то генерируется исключение) DataContextdcn = new DataContext(cnStr); Table<MyCustomer> customers = dcn.GetTable<MyCustomer>(); 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<>: varrow = (from c incustomers where c.City == “Moscow” select c).First();

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

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

21. Описание класса сущностей [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; } }

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

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

24. Строго типизированный DataContext • Описание classNorthwindDatabase : DataContext { public Table<Products> Products { get {return this.GetTable<Products>}; } publicNorthwindDatabase(stringconnectionString): base(connectionString) {. . .} } • Использование // Содание объекта NorthwindDatabase NorthwindDatabasedb = newNorthwindDatabase(cnStr); // Теперь можно использовать поле Products foreach (varcarinfrom c indb.Productsselect c) Console.WriteLine(car.ToString());

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

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

27. Дизайнер LINQtoSQL

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

30. Связь между объектами классов • Запрос к архиву работы сотрудников в подразделениях компании • Все отношения между таблицами реализованы с помощью связи между объектами классов: • Например:dep.Employee.Contact.LastName // Use a variant to hold the EmployeeDepartmentHistories // Use LINQ to query the database, passing in the last name varDepartmentHistories = 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 (EmployeeDepartmentHistoryedh in DepartmentHistories) { StringBuildersb = 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()); }

31. ХМL файл соответствия(*.dbml) • Создается вручную дизайнером типизированного DataContext • Используется только дизайнером для создания файлов с описаниями классов <?xml version="1.0" encoding="utf-8"?> <Database Name="AdventureWorks" Class="AdventureWorksDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007"> <Connection Mode="AppSettings" ConnectionString= "Data Source=MASTER\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True“ SettingsObjectName="TypedDataContext.Properties.Settings" SettingsPropertyName="AdventureWorksConnectionString" Provider="System.Data.SqlClient" /> <Table Name="HumanResources.Department" Member="Departments"> <Type Name="Department"> <Column Name="DepartmentID“ Type="System.Int16" DbType="SmallInt NOT NULL IDENTITY“ IsPrimaryKey="true" IsDbGenerated="true“ CanBeNull="false" /> <Column Name="Name“ Type="System.String“ DbType="NVarChar(50) NOT NULL“ CanBeNull="false" /> <Column Name="GroupName“ Type="System.String" DbType="NVarChar(50) NOT NULL" CanBeNull="false" /> <Column Name="ModifiedDate“ Type="System.DateTime" DbType="DateTime NOT NULL" CanBeNull="false" /> </Type> </Table> </Database>

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

33. Конструкторы типизированного класса 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);

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

35. Пример LINQ запроса к типизированному DataContext string cnStr = @"Data 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);

36. Запрос к нескольким таблицам • 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);

37. Работа с хранимыми процедурами <Function Name="dbo.uspGetEmployeeManagers" Method="uspGetEmployeeManagers"> <Parameter Name="EmployeeID" Parameter="employeeID”Type="System.Int32" DbType="Int" /> <ElementType Name="uspGetEmployeeManagersResult"> <Column Name="RecursionLevel" Type="System.Int32"DbType="Int" CanBeNull="true" /> <Column Name="EmployeeID" Type="System.Int32”DbType="Int" CanBeNull="true" /> <Column Name="FirstName" Type="System.String”DbType="NVarChar(50)" CanBeNull="true" /> <Column Name="LastName" Type="System.String"DbType="NVarChar(50)" CanBeNull="true" /> <Column Name="ManagerID" Type="System.Int32" DbType="Int"CanBeNull="true" /> <Column Name="ManagerFirstName" Type="System.String"DbType="NVarChar(50) NOT NULL" CanBeNull="false" /> <Column Name="ManagerLastName" Type="System.String"DbType="NVarChar(50) NOT NULL" CanBeNull="false" /> </ElementType> </Function>

38. Содержание хранимой процедуры ALTER PROCEDURE [dbo].[uspGetEmployeeManagers] @EmployeeID [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] = @EmployeeID UNION ALL SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 1 -- 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;

39. ADO.NET Entity Framework • ADO.NET EntityFramework (EF) является более обширной технологией, чем ObjectRelationMapping (ORM). • Целью EF является выполнение более сложных задач, чем стандартное ORM. Она включает концептуальную модель (conceptualmodel) как нечто конкретное, с помощью специальной среды (framework) для создания абстрактной модели на основе реляционной модели, тем самым решая задачу несоответствия между ними (impedancemismatch). • Основным элементом EF является уровень абстракции, который разделен на концептуальный, согласующий (mapping) и логический уровни, которые составляют EntityDataModel (EDM). • Кроме этого, EF включает: • два прикладных интерфейса APIs, • объектные сервисы (objectservices) и • клиента сущностей (entityclient), для работы с EDM, • две конструкции для работы с данными: • Entity SQL (ESQL) и • LINQ toEntities. • Очень похожа на LINQ to SQL. Отличие: • может работать с разными реляционными БД; • в отличие от LINQ to SQL поддерживает не только прямое соответствие между классами и таблицами; • поддерживает наследие классов; • Для сложных приложений уровня Предприятия. • Другие ORM технологии: NHibernate, EntitySpacesиLLBLGen Pro.

40. Создание 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.EmailAddress); }

42. Модель сущностей данных - 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)между сущностями приложения с базой данных.

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

44. ADO.NET Entity Framework