ASP NET.MVC Урок 3. Работа с БД

Цель урока: Изучить основные принципы работы с базой данных. Краткое описание реляционной модели баз данных. Работа с базой данных (создание таблиц, связей в VS 2012). Команды INSERT, UPDATE, DELETE, SELECT. Использование LinqToSql и Linq. Создание репозитария, IRepository, SqlRepository.

• у таблицы есть имя (уникальное)
• нет двух одинаковых строк
• столбцы имеют разные наименования (нет двух одинаковых столбцов)
• порядок строк в таблице произвольный (т.е. не надо учитывать порядок строк, если не задана сортировка)

• Имя столбца
• Тип данных для этого столбца

• Первичный ключ – это набор столбцов (атрибутов) таблицы, однозначно определяющих уникальность строки. Обычно это одно поле, называется ID. Оно является автоикрементным, т.е. при попытке добавления записи, там автоматически вставляется 1, 2, 3, 4… n+1, где n – это значение последнего добавленного ID.
• Внешний ключ – это набор столбцов (атрибутов) таблицы, которые однозначно определяют уникальность строки в другой таблице. Опять же это обычно одно поле, названное [Имя таблицы]ID. Но не является автоинкрементным.
• Прописана связь между первичным ключом и внешним ключом.

• Один-к-одному. Т.е. одной строке в таблице соответствует одна строка в другой таблице. Это редко используется, но используется. Например, в одной таблице данные о пользователе, а в другой — дополнительные данные о том же пользователе. Такой вариант необходим, чтобы манипулировать, по необходимости, меньшим количеством данных.
• Один-ко-многим. Одной строк в таблице A соответствует одна или несколько строк в таблице B. Но одной строке в таблице B соответствует только одна строка в таблице A. В этом случае в таблице B существует внешний ключ, который однозначно определяет запись в таблице A.
• Многие-ко-многим. Одной строке в таблице А соответствует одна или несколько строк в таблице В, что истинно и в обратном. В данном случае создается дополнительная таблица со своим первичным ключом, и двумя внешними ключами к таблице A и B.

Создадим БД в VS 2012:

Назовем её LessonProject, и добавим 3 таблицы Role User и UserRole. Создадим таблицу Role:

Для строковых значений используем тип nvarchar(n), где n – максимальная длина строки, обычно используется от 50 до 500. Для больших текстовых строк используется nvarchar(MAX).

Устанавливаем первичный ключ:

Задаем для ID автоинкремент:

Подобным образом создаем таблицу User: Поле Тип поля ID int Email nvarchar(150) Password nvarchar(50) AddedDate datetime ActivatedDate datetime (null) ActivatedLink nvarchar(50) LastVisitDate datetime (null) AvatarPath nvarchar(150) (null)

Создаем таблицу UserRole: Поле Тип поля ID int UserID int RoleID int

Добавляем новую связь, нажав Add. Добавление связей происходит в таблице, где находятся внешние ключи. Раскрываем вкладку Tables and Columns и выставляем таблицу с первичным ключом, и выбираем внешний ключ в текущей таблице UserRole.

В свойствах INSERT And UPDATE Specification выставляем On Update/On Delete свойства Cascade:

Это необходимо для того, чтобы при изменении/удалении столбца из таблицы Role все связанные с этой строкой строки таблицы UserRole должны быть изменены или удалены.

Аналогичную связь мы устанавливаем с таблицей User.

Таким образом, таблицы Role и User имеют отношения многие ко многим через таблицу UserRole. Т.е. у одного пользователя может быть больше одной роли, и одна и та же роль может быть у нескольких пользователей.

В реляционных базах данных используется язык запросов SQL.

Есть 4 основные команды для манипулирования данными — SELECT, INSERT, UPDATE, DELETE

SELECT – для выбора данных и таблиц. Пример:

INSERT — Добавление строк в таблицу Пример:

UPDATE – изменение значений в таблице Пример:

DELETE – удаление строк из таблицы Пример:

Создадим проект LessonProject.Model для работы с БД типа ClassLibrary. Добавляем LINQ to SQL Classes тип, называем LessonProejctDb.dbml

Открываем объект, выделяем все таблицы и мышкой переносим на холст:

• классы, готовые к использованию в работе с БД
• визуальное отображение таблиц и связей

Добавим несколько данных в таблицу Role и User:

1 admin Админ 2 customer Заказчик

1 chernikov@gmail.com 123456 1/1/2012 12:00:00 AM NULL 123456 NULL NULL 2 chernikov2@gmail.com 123456 1/1/2012 12:00:00 AM NULL 123456 NULL NULL

ID UserID RoleID 1 1 1 2 1 2 3 2 2

Создадим консольный проект Lesson3 и подключим LessonProject.Model. Добавим сборку System.Configuration и System.Data.Linq. Проинициализируем context и выведем данные о ролях:

Для добавления строки в Role делаем так:

Для удаления строки в Role делаем так:

Для изменения данных делаем так:

Для манипуляции данных используется язык запросов Linq. Мы рассмотрим только некоторые основные функции Linq. Linq применяется для типов реализующий интерфейс IQueryable<>

.Where() – основная функция фильтрации. Возвращает тип IQueryable. Условие внутри должно возвращать булево значение (bool).

Собственно с БД мы уже можем работать, только теперь нужно отделить модель данных от конкретной реализации, т.е. наши контроллеры про context и System.Data.Linq вообще не должны ничего знать.

Для этого создадим интерфейс IRepository, где будет дан доступ к данным, а также выведены методы для создания, изменения и удаления этих данных.

Реализацию назовем SqlRepository. Так как мы с данным контекстом SqlRepository не хотим особо связывать, то добавим Ninject модуль в проект LessonProject.Model:

Создадим класс SqlRepository:

Прежде, чем реализовать доступ ко всем таблицам, создание, удаление и изменение, подумаем о том, что файл этот будет выглядеть громадным и неуклюжим. Таким кодом будет управлять тяжело физически. Так что сделаем отдельную папку SqlRepository и SqlRepository класс сделаем partial, а в папке создадим реализации интерфейса IRepository, разбитые по каждой таблице. Назовем файл Role:

Небольшой проект содержит от 10 до 40 таблиц, большой проект от 40, и всё это хотелось бы как-то автоматизировать. Создадим несколько сниппетов, для IRepository и для SqlRepository. Сниппеты – это уже готовые шаблоны кода, которые вызываются с помощью intelliSence, и позволяют быстро создавать код.

Для IRepository таблиц, создадим table.snippet:

Для SqlRepository создадим сниппет sqlTable.snippet:

Для того, чтобы добавить code-snippet. откроем TOOLS -> Code Snippet Manager… (Ctrl-K, B). В окне нажимаем Import и импортируем оба сниппета в My Code snippet:

Finish, OK. Используем для таблиц User и UserRole.

Осталось прописать только поля для Update [имя таблицы], но это уже меньше работы.

Как видим, классы, которые мы используем, являются partial, поэтому их можно дополнить. Создадим, подобно SqlRepository, папку Proxy, где будем размещать partial классы. Например, для класса User создадим метод, который автоматически генерирует строку, требуемую для активации пользователя:

Добавим строку доступа к БД в web.Config:

Проинициализируем работу с БД в Ninject:

Применяем InRequestScope(). Т.е. каждый запрос будет использовать отдельный объект SqlRepository. Это позволит избежать коллизий при исполнении.Объявляем IRepository в контроллере: