Создание БД и контекста данных

Создаем базу данных

После определения всех моделей создадим базу данных. Поскольку многие модели взаимосвязаны, о нам придется это учитывать и при создании таблиц, указывая соответствующие внешние ключи.

Итак, добавим в папку App_Data проекта базу данных. Назовем ее, например, helpdesk.mdf.

Затем откроем базу данных в окне Database Explorer. Начнем добавлять таблицы для тех сущностей, которые не требуют внешних ключей.

Создадим таблицу для модели Category:

Далее создадим таблицы для моделей Department и Activ. Модель Department:

Модель Activ:

Так как модель Activ связана через внешний ключ с моделью Department, то при определении таблицы также прописываем этот внешний ключ и указываем действие, которое будет происходить при удалении связанной модели Department. В данном случае поле DepartmentId будет принимать значение null.

Теперь добавим таблицы для ролей и пользователей. Таблица для класса Role:

Также тут же можно и внести все возможные роли:

Определение таблицы пользователей будет выглядеть следующим образом:

Здесь у нас уже два внешних ключа. В принципе можно уже определить одного пользователя - администратора:

Этот администратор затем уже будет добавлять всех остальных пользователей в приложении.

Теперь перейдем к системе заявок. Вначале добавим таблицу для сущности Lifecycle, описывающей жизненный цикл заявки. Скрипт создания таблицы будет выглядеть следующим образом:

CREATE TABLE [dbo].[Lifecycles]
(
	[Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Opened] DATETIME NOT NULL, 
    [Distributed] DATETIME NULL, 
    [Proccesing] DATETIME NULL, 
    [Checking] DATETIME NULL, 
    [Closed] DATETIME NULL
)

И скрипт создания таблицы заявок будет выглядеть так:

CREATE TABLE [dbo].[Requests] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (50)  NOT NULL,
    [Description] NVARCHAR (200) NULL,
    [Comment]     NVARCHAR (200) NULL,
    [Status]      INT            NOT NULL,
    [Priority]    INT            NOT NULL,
    [ActivId]     INT            NULL,
    [File]        NVARCHAR (200) NULL,
    [CategoryId]  INT            NULL,
    [UserId]      INT            NULL,
    [ExecutorId]  INT            NULL,
    [LifecycleId] INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Requests_ToCategories] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[Categories] ([Id]) ON DELETE SET NULL,
    CONSTRAINT [FK_Requests_ToUsers] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id]) ON DELETE SET NULL,
    CONSTRAINT [FK_Requests_ToLifecycles] FOREIGN KEY ([LifecycleId]) REFERENCES [dbo].[Lifecycles] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_Requests_ToActivs] FOREIGN KEY ([ActivId]) REFERENCES [dbo].[Activs] ([Id]) ON DELETE SET NULL
);

GO

CREATE TRIGGER [dbo].[Trigger_Requests]
    ON [dbo].[Users]
    FOR DELETE
    AS
    BEGIN
       UPDATE Requests SET ExecutorId = NULL 
     WHERE Requests.ExecutorId IN (SELECT [Id] FROM [deleted]);
    END

GO

CREATE TRIGGER [dbo].[Trigger_Requests2]
    ON [dbo].[Users]
    FOR UPDATE
    AS
    BEGIN
        UPDATE Requests SET ExecutorId = NULL 
     WHERE Requests.ExecutorId IN (SELECT [Id] FROM [inserted]) AND (SELECT [Name] FROM Roles WHERE Id=(SELECT [RoleId] FROM [inserted])) <> 'Исполнитель';
    END

Здесь у нас куча внешних ключей и, кроме того, еще не очень понятная конструкция в конце под названием триггер, точнее два триггера. Общее действие у нас таково: у нас два поля - пользователь, создавший заявку, и исполнитель этой заявки - ссылаются на таблицу Users. Мы хотим, чтобы в случае удаления объекта из таблицы Users соответствующие поля в таблице Requests устанавливались в NULL. Ну например в реальности пользователь отписался о какой-то проблеме, и вдруг его уволили (на уровне кода - удалили), но проблема могла остаться, поэтому мы устанавливаем null. Однако вы также можете использовать здесь каскадное удаление (ON DELETE CASCADE)

В случае с другим полем ExecutorId установить с помощью внешнего ключа в null уже не получится, так как среда нам не позволяет задать два внешних ключа на одно поле другой таблицы. Но мы можем выйти из этой ситуации, задав триггер.

По сути весь текст начиная с GO и до слова END представляет собой определение триггера, который делает практически то же самое, только для поля ExecutorId. В данном случае используется специфическое объявление триггера, о котором вы можете справиться в специальных руководствах по языку запросов SQL.

Второй триггер у нас определен для той ситуации, если какой-то пользователь перестает быть исполнителем, но остается в системе, то есть меняет статус. И тогда мы также устанавливаем для поля ExecutorId значение NULL.

Если в общих словах, то создание триггера начинается со слов CREATE TRIGGER, после которых идет название триггера. Далее указываем таблицу, за которой триггер будет следить и операцию после слова AFTER, при проведении которой триггер будет срабатывать. В обоих случаях таблицей является Users. А вот операции у нас используется две: удаление для первого триггера, и обновление для второго.

Само действие производит следующий код UPDATE Requests SET ExecutorId = NULL - обычное выражение SQL, устанавливающее значение NULL. А дальше следует условие, при котором происходит такое присвоение.

При удалении условие WHERE Requests.ExecutorId IN (SELECT [Id] FROM [deleted]) говорит удалить те строки, где значения полей Requests.ExecutorId попадают в набор (SELECT [Id] FROM [deleted]). Слово deleted указывает на набор удаленных объектов из таблицы Users.

При обновлении чуть более сложное решение, так как нам надо проверить, является ли обновленный объект в таблице Users по прежнему исполнителем.

Благодаря использованию триггера удаление исполнителя, даже в том случае, если является и создателем заявки, пройдет вполне спокойно и без ошибок.

Создание контекста данных

Итак, мы создали весь необходимый набор данных, который сейчас выглядит следующим образом:

Нас осталось создать контекст данных, через который мы будем получать все необходимые сведения из базы данных. Так, добавим в папку Models класс HelpdeskContext:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace HelpDeskTrain.Models
{
    public class HelpdeskContext : DbContext
    {
        public DbSet<Role> Roles { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<Request> Requests { get; set; }
        public DbSet<Lifecycle> Lifecycles { get; set; }
        public DbSet<Category> Categories { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<Activ> Activs { get; set; }
    }
}

Теперь надо связать контекст с базой данных. Откроем файл web.config изменим в нем стандартное определение строки подключения в секции connectionStrings на следующее:

<connectionStrings>
   <add name="HelpdeskContext" providerName="System.Data.SqlClient" 
		connectionString="Data Source=(LocalDb)\v11.0;AttachDBFilename=|DataDirectory|\helpdesk.mdf" />
</connectionStrings>

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