- PVSM.RU - https://www.pvsm.ru -
Цель урока: Изучить основные принципы работы с базой данных. Краткое описание реляционной модели баз данных. Работа с базой данных (создание таблиц, связей в VS 2012). Команды INSERT, UPDATE, DELETE, SELECT. Использование LinqToSql и Linq. Создание репозитария, IRepository, SqlRepository.
Реляционная база данных — база данных, основанная на реляционной модели данных. Реляционность – это отношения (связи) от англ. relation.
Это таблица:
Таблица состоит из столбцов и строк. Столбцы имеют свойства – имя, тип данных.
Таблицы должны обладать следующими свойствами:
Структуру таблицы можно записать в таком виде:
Между таблицами существуют связи (relation). Для установки связи необходимо иметь следующее:
Связи бывают трех типов:
Сейчас разберемся, как это делать.
Создадим БД в VS 2012:
Назовем её LessonProject, и добавим 3 таблицы Role User и UserRole.
Создадим таблицу Role:
Для строковых значений используем тип nvarchar(n), где n – максимальная длина строки, обычно используется от 50 до 500. Для больших текстовых строк используется nvarchar(MAX).
Устанавливаем первичный ключ:
Задаем для ID автоинкремент:
Подобным образом создаем таблицу User:
Поле | Тип поля |
ID | int |
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 – для выбора данных и таблиц.
Пример:
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/ [1]
http://codingcraft.ru/sql_queries.php [2]
Создадим проект 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<>
var roles = context.Roles.Where(p => p.Name == "Менеджер")
var roles = context.Roles.Where(p => p.Name == "Менеджер").FirstOrDefault()
– получаем первую (или не получаем) роль названную «Менеджер».
var roles = context.Roles.Where(p => p.Name == "Менеджер").Take(4)
– выберет 4 первые записи
var roles = context.Roles.Where(p => p.Name == "Менеджер"). Skip(2).Take(3)
– пропустит первые 2 и выберет 3 следующие записи
var roles = context.Roles.Where(p => p.Name == "Менеджер").OrderBy(p => p.ID)
– сортирует по порядку
var rolesCount = context.Roles.Where(p => p.Name == "Менеджер").Count()
– количество записей
var otherRole = context.Roles.Where(p => p.Name == "Менеджер").Select(p => new {
ID = p.ID, Kod = p.Code})
– получаем динамический тип, сформированный на основе Role.
var otherRole = context.Roles.Where(p => p.Name == "Менеджер").SelectMany(p => p.UserRoles)
– получаем все UserRole из роли, названной «Менеджер»
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 [3]
Собственно с БД мы уже можем работать, только теперь нужно отделить модель данных от конкретной реализации, т.е. наши контроллеры про 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
[имя таблицы], но это уже меньше работы.
Как видим, классы, которые мы используем, являются 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;
}
Добавим строку доступа к БД в 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 [4]
Автор: chernikov
Источник [5]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/asp-net-mvc/31657
Ссылки в тексте:
[1] http://www.w3schools.com/sql/: http://www.w3schools.com/sql/
[2] http://codingcraft.ru/sql_queries.php: http://codingcraft.ru/sql_queries.php
[3] http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b: http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b
[4] https://bitbucket.org/chernikov/lessons: https://bitbucket.org/chernikov/lessons
[5] Источник: http://habrahabr.ru/post/176017/
Нажмите здесь для печати.