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

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

Что такое БД

Реляционная база данных — база данных, основанная на реляционной модели данных. Реляционность – это отношения (связи) от англ. relation.

Таблицы

Это таблица:


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

Структуру таблицы можно записать в таком виде:
  • Имя столбца
  • Тип данных для этого столбца


Связи

Между таблицами существуют связи (relation). Для установки связи необходимо иметь следующее:
  • Первичный ключ – это набор столбцов (атрибутов) таблицы, однозначно определяющих уникальность строки. Обычно это одно поле, называется 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. Т.е. у одного пользователя может быть больше одной роли, и одна и та же роль может быть у нескольких пользователей.

SELECT, INSERT, UPDATE, DELETE.

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

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

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

INSERT — Добавление строк в таблицу
Пример:
INSERT INTO Role (Code, Name)
VALUES (“admin”, “Администратор”)

UPDATE – изменение значений в таблице
Пример:
UPDATE User
SET Password=”password1” 
WHERE ID=1

DELETE – удаление строк из таблицы
Пример:
DELETE FROM User
WHERE ID =1


Примечание: Подробнее можно изучить SQL по ссылкам:
http://www.w3schools.com/sql/
http://codingcraft.ru/sql_queries.php

LinqToSQL и Linq.

Создадим проект 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


И UserRole

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


Создадим консольный проект Lesson3 и подключим LessonProject.Model. Добавим сборку System.Configuration и System.Data.Linq. Проинициализируем context и выведем данные о ролях:
    class Program
    {
        static void Main(string[] args)
        {
            var context = new LessonProjectDbDataContext(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

     var roles = context.Roles.ToList();
            foreach (var role in roles)
            {
                Console.WriteLine("{0} {1} {2}", role.ID, role.Code, role.Name);
            }
            Console.ReadLine();
        }
    }


Для добавления строки в Role делаем так:
var newRole = new Role
            {
                Code = "manager", 
                Name = "Менеджер"
            };
            context.Roles.InsertOnSubmit(newRole);
            context.Roles.Context.SubmitChanges();

Для удаления строки в Role делаем так:
var role = context.Roles.Where(p => p.Name == "Менеджер").FirstOrDefault();
         if (role != null)
         {
            context.Roles.DeleteOnSubmit(role);
            context.Roles.Context.SubmitChanges();
         }

Для изменения данных делаем так:
var role = context.Roles.Where(p => p.Name == "Менеджер").FirstOrDefault();
       if (role != null)
       {
           role.Name = "Манагер";
           context.Roles.Context.SubmitChanges();
       }

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

  • .Where() – основная функция фильтрации. Возвращает тип IQueryable. Условие внутри должно возвращать булево значение (bool).
    var roles = context.Roles.Where(p => p.Name == "Менеджер") 
    

  • .FirstOrDefault() — .First(), .Single(), .SingleOrDefault() – получают первую или единственную запись. Если записи нет, то FirstOrDefault() или SingleOrDefault() возвращают null (на самом деле, значение по умолчанию этого типа [default(int)], например).
     var roles = context.Roles.Where(p => p.Name == "Менеджер").FirstOrDefault()
    
    – получаем первую (или не получаем) роль названную «Менеджер».
  • .Take() – выбирает N первых записей
    var roles = context.Roles.Where(p => p.Name == "Менеджер").Take(4) 
    
    – выберет 4 первые записи
  • .Skip() – пропускает выбор N первых записей
    var roles = context.Roles.Where(p => p.Name == "Менеджер"). Skip(2).Take(3)
    
    – пропустит первые 2 и выберет 3 следующие записи
  • .OrderBy() – сортирует по возрастанию. А также OrderByDescending(), ThenBy(), ThenByDescending(). Лямбда-выражение должно возвращать тип int, по которому и будет происходить сортировка.
    var roles = context.Roles.Where(p => p.Name == "Менеджер").OrderBy(p => p.ID) 
    
    – сортирует по порядку
  • .Count() – получает количество записей
    var rolesCount = context.Roles.Where(p => p.Name == "Менеджер").Count()
    
    – количество записей
  • .Any() – существует одна или больше записей по данному условию
    var rolesExist = context.Roles.Where(p => p.Name == «Менеджер»).Any() – есть ли запись такая
  • . Select() – возвращает IQueryable произвольного типа, может быть даже dynamic:
    var otherRole = context.Roles.Where(p => p.Name == "Менеджер").Select(p => new {
    ID = p.ID, Kod = p.Code}) 
    
    – получаем динамический тип, сформированный на основе Role.
  • .SelectMany() – возвращает объединение всех IQueryable типов внутри выборки:
    var otherRole = context.Roles.Where(p => p.Name == "Менеджер").SelectMany(p => p.UserRoles) 
    
    – получаем все UserRole из роли, названной «Менеджер»
  • .Distinct() – удаляет дубликаты
    var managers = context.Roles.Where(p => p.Name == "Менеджер").SelectMany(p => p.UserRoles).Select(p => p.User).Distinct()
    
    – все пользователи с ролью названной «Менеджер»

Примечание: First(), FirstOrDefault(), Single(), SingleOrDefault(), Any(), Count() – могут применять параметр, соответствующий Where(), тем самым, можно сокращать запись:
var roles = context.Roles.FirstOrDefault(p => p.Name == "Менеджер")


Больше примеров и вариантов использования linq вы сможете найти:
http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b


Создание репозитория IRepository, SqlRepository.


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

Для этого создадим интерфейс IRepository, где будет дан доступ к данным, а также выведены методы для создания, изменения и удаления этих данных.
    public interface IRepository
    {
        IQueryable<Role> Roles { get; }

        bool CreateRole(Role instance);

        bool UpdateRole(Role instance);

        bool RemoveRole(int idRole);

      	…  
    }


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


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

 public class SqlRepository : IRepository
    {
        [Inject]
        public LessonProjectDbDataContext Db { get; set; }

        public IQueryable<Role> Roles
        {
            get { throw new NotImplementedException(); }
        }

        public bool CreateRole(Role instance)
        {
            throw new NotImplementedException();
        }

        public bool UpdateRole(Role instance)
        {
            throw new NotImplementedException();
        }

        public bool RemoveRole(int idRole)
        {
            throw new NotImplementedException();
        }
    }


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

 public partial class SqlRepository
    {
        public IQueryable<Role> Roles
        {
            get
            {
                return Db.Roles;
            }
        }

        public bool CreateRole(Role instance)
        {
            if (instance.ID == 0)
            {
                Db.Roles.InsertOnSubmit(instance);
                Db.Roles.Context.SubmitChanges();
                return true;
            }

            return false;
        }

        public bool RemoveRole(int idRole)
        {
            Role instance = Db.Roles.FirstOrDefault(p => p.ID == idRole);
            if (instance != null)
            {
                Db.Roles.DeleteOnSubmit(instance);
                Db.Roles.Context.SubmitChanges();
                return true;
            }

            return false;
        }
    }


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

Сниппеты


Для IRepository таблиц, создадим table.snippet:
<CodeSnippets
    xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0" >
    <Header>
      <Title>
        Table
      </Title>
      <Shortcut>Table</Shortcut>
    </Header>
    <Snippet>
      <Declarations>
        <Literal>
          <ID>Table</ID>
          <ToolTip>Table name for create.</ToolTip>
          <Default>Table</Default>
        </Literal>
      </Declarations>

      <Code Language="CSharp">
        <![CDATA[
		#region $Table$

        IQueryable<$Table$> $Table$s { get; }

        bool Create$Table$($Table$ instance);

        bool Update$Table$($Table$ instance);

        bool Remove$Table$(int id$Table$);

        #endregion 
        ]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets> 


Для SqlRepository создадим сниппет sqlTable.snippet:
<CodeSnippets
    xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0" >
    <Header>
      <Title>
        Sql repository
      </Title>
      <Shortcut>sqltable</Shortcut>
    </Header>
    <Snippet>
      <Declarations>
        <Literal>
          <ID>Table</ID>
          <ToolTip>Table name for create.</ToolTip>
          <Default>Table</Default>
        </Literal>
      </Declarations>

      <Code Language="CSharp">
        <![CDATA[
        
        public IQueryable<$Table$> $Table$s
        {
            get
            {
                return Db.$Table$s;
            }
        }

        public bool Create$Table$($Table$ instance)
        {
            if (instance.ID == 0)
            {
                Db.$Table$s.InsertOnSubmit(instance);
                Db.$Table$s.Context.SubmitChanges();
                return true;
            }

            return false;
        }

        public bool Update$Table$($Table$ instance)
        {
            $Table$ cache = Db.$Table$s.Where(p => p.ID == instance.ID).FirstOrDefault();
            if (cache != null)
            {
                //TODO : Update fields for $Table$
                Db.$Table$s.Context.SubmitChanges();
                return true;
            }

            return false;
        }

        public bool Remove$Table$(int id$Table$)
        {
            $Table$ instance = Db.$Table$s.Where(p => p.ID == id$Table$).FirstOrDefault();
            if (instance != null)
            {
                Db.$Table$s.DeleteOnSubmit(instance);
                Db.$Table$s.Context.SubmitChanges();
                return true;
            }

            return false;
        }
        ]]>
      </Code>
    </Snippet>
  </CodeSnippet>
</CodeSnippets>


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



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





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

Proxy

Как видим, классы, которые мы используем, являются partial, поэтому их можно дополнить. Создадим, подобно SqlRepository, папку Proxy, где будем размещать partial классы. Например, для класса User создадим метод, который автоматически генерирует строку, требуемую для активации пользователя:
 public partial class User
    {
        public static string GetActivateUrl()
        {
            return Guid.NewGuid().ToString("N");
        }
    }

Используем это:
public bool CreateUser(User instance)
        {
            if (instance.ID == 0)
            {
                instance.AddedDate = DateTime.Now;
                instance.ActivatedLink = User.GetActivateUrl();
                Db.Users.InsertOnSubmit(instance);
                Db.Users.Context.SubmitChanges();
                return true;
            }

            return false;
        }


Использование БД в asp.net mvc

Добавим строку доступа к БД в web.Config:
<connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=SATURN-PC;Initial Catalog=LessonProject;Integrated Security=True;Pooling=False" providerName="System.Data.SqlClient" />
  </connectionStrings>


Проинициализируем работу с БД в Ninject:
  private static void RegisterServices(IKernel kernel)
        {
            kernel.Bind<LessonProjectDbDataContext>().ToMethod(c => new LessonProjectDbDataContext(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString));
            kernel.Bind<IRepository>().To<SqlRepository>().InRequestScope();
        }      

Применяем InRequestScope(). Т.е. каждый запрос будет использовать отдельный объект SqlRepository. Это позволит избежать коллизий при исполнении.Объявляем IRepository в контроллере:
  public class HomeController : Controller
    {
        [Inject]
        public IRepository Repository { get; set; }

        public ActionResult Index()
        {
            var roles = Repository.Roles.ToList();
            return View(roles);
        }
    }


И обновляем View (/Views/Home/Index.cshtml):
@model IList<LessonProject.Model.Role>
@{
    ViewBag.Title = "LessonProject";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>LessonProject</h2>

<p>
    @foreach (var role in Model)
    {
        <div class="item">
            <span class="id">
                @role.ID
            </span>
            <span class="name">
                @role.Name
            </span>
            <span class="Code">
                @role.Code
            </span>
        </div>
    }
</p>


Получаем хороший результат:



Все исходники находятся по адресу https://bitbucket.org/chernikov/lessons
Share post
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 22

    +6
    А не лучше\проще было бы воспользоваться Code First (EF, nHibernate, etc)?
      +1
      Можно, но мне удобнее LinqToSql пока. Там далее в разделе скаффолдинга мы автоматизируем процесс создания репозитория. Я пробовал использовать и Code First, по сути там нужны минимальные изменения. Я думаю, потом допишу по этой теме и реализацию с Code First.
        +1
        ну интерфейс и сниппеты тоже полезны, да, но CodeFirst чертовски прелестен…
          –3
          EF и nHibernate жутко медленны, поэтому не всегда пригодны для использования.
            0
            Без Linq2Entities уже не жизнь, nHibernate как не имеющий этого, автоматически отпадает.
            А еще у EF есть миграции, совокупно с CodeFirst — просто конфетка.
              0
              >>nHibernate как не имеющий этого, автоматически отпадает.
              Не понял. nhibernate уже давно нормально умеет LINQ.
                –1
                Может быть, новые версии не смотрел. У меня в наследство приехал видимо с шибко старой версией. Синтаксис всех этих СreateCriteria, Add Expression удручает. Не позволяет поднимать условия поиска выше инфраструктурного уровня, что заставляет сильно плодить количество методов, интерфейсы разрастаются вширь.
                  0
                  Это ж какой у Вас версии… NHibernate поддерживал Linq2Entities со второй версии отдельной либой, с третьей — уже встроенной реализацией.
                    0
                    Версия вроде бы вторая. Про отдельную либу не слышал.
                0
                Можно ли в одном проекте смешать EF DBFirst и CodeFirst?
                  0
                  Если это две разные модели — запросто.
                    0
                    Ситуация у меня такая: Примерно 70 таблиц связаны с приложением через DBFirst. Теперь я хочу дополнительный функционал проекта дописывать с использованием CodeFirst. Часть новых таблиц будет связано отношениями со старыми (например, таб. Users связана со многими другими таблицами). Вопрос: нужно ли мне в новой модели добавлять таблицу Users, и если да, то какие проблемы можно ожидать из-за такого двойственного сосуществования? И еще не совсем понятно, чего ждать от MigrationHistory: не удалит ли он мне мои данные при развертывании? А при добавлении столбцов в модель CF, видимо, придется обновлять и модель DBF.
              • UFO just landed and posted this here
                +4
                Разве Linq2SQL не устарел и более не поддерживается?
                • UFO just landed and posted this here
                  –6
                  Значит, чтобы вывести на экран браузера список из таблицы, мне нужно:
                  * 8к потратить на Microsoft Windows
                  * 13к потратить на MS VS
                  * 25к на MS SQL Server standard (поправьте, если ошибаюсь)
                  * прочитать 20!!! страниц туториала (20 страниц туториала мне нравятся еще меньше, чем стоимость окружения разработчика.)

                  Сервер
                  * Microsoft Windows Server web — тоже 25к?

                  При всем уважении к производительности .Net решений — перебор. При чем, выбрав такое решения, мне очень трудно будет сказать: «чота мне .Net » разонравился.

                    +1
                    * Можно тот же Linux с Mono.
                    * Есть бесплатные версии студии, есть SharpDevelop и MonoDevelop.
                    * Провайдеры EF есть не только к MS SQL Server, например. Кроме того, есть MS SQL Server Express, которого для многих мелких проектов будет за глаза.
                    * Большая часть туториала — картинки и куски кода. И еще репозитории, описание построения бд и т.п. Так что не аргумент. Но да, читать обычно надо много. Везде.

                    За IIS отдельно платить уже не надо. Ну и про никсы выше.
                      +1
                      Windows — 2400р(купил в юлмарте)
                      VS 2012 web express, MS SQL Server express, IIS 8 — бесплатно.
                        0
                        Рад, что времена меняются. Удаляюсь.
                          0
                          Это вы какую винду купили за 2400? Home, самая обрезанная? На ней точно будут работать все эти VS 2012, MS SQL, IIS 8?
                          Ну и серверную винду никто не отменял для развертывания проекта.
                          Хотя конечно как правило лицензия на сервер теряется в общем бюджете проекта, но тем не менее, она стоит не 10коп.
                            0
                            Вот такую www.ulmart.ru/goods/366511, только 64хбитную, почему то ее не видно тут в наличии сейчас.
                            Да, на ней все это работает.
                            Развертывание проекта, если конечно это не крупный какой то проект, будет делаться на хостинге или в облаке, там стоимость серверной винды непосредственно не видна. Хостинг для ASP.Net подороже конечно стоит, чем для PHP проектов, год назад последний раз интересовался, на сегодня не в курсе какие цены.
                            • UFO just landed and posted this here

                      Only users with full accounts can post comments. Log in, please.