ASP NET.MVC Lesson 3. Working with the database

The purpose of the lesson : To learn the basic principles of working with the database. A brief description of the relational database model. Work with the database (creating tables, links in VS 2012). Commands INSERT, UPDATE, DELETE, SELECT. Using LinqToSql and Linq. Create Repository, IRepository, SqlRepository.

What is a DB

A relational database is a database based on a relational data model. Relationality is a relationship (relationship) from the English. relation.

Tables

This is a table:


A table consists of columns and rows. Columns have properties - name, data type.
Tables should have the following properties:

• the table has a name (unique)
• no two lines are alike
• columns have different names (there are no two identical columns)
• the order of the rows in the table is arbitrary (i.e. do not take into account the row order if sorting is not specified)

The structure of the table can be written in the following form:

• Column name
• Data type for this column

Communications

Between tables there are relations (relation). To establish a connection, you must have the following:

• A primary key is a set of columns (attributes) of a table that uniquely determine the uniqueness of a row. This is usually a single field called an ID. It is auto-increment, i.e. when you try to add an entry, 1, 2, 3, 4 ... n + 1 is automatically inserted there, where n is the value of the last added ID.
• A foreign key is a set of columns (attributes) of a table that uniquely determine the uniqueness of a row in another table. Again this is usually a single field called [Table Name] ID. But it is not auto-incrementing.
• The relationship between the primary key and the foreign key is registered.

There are three types of connections:

• One to one. Those. one row in the table corresponds to one row in another table. It is rarely used, but used. For example, in one table data about the user, and in another - additional data about the same user. This option is necessary to manipulate, if necessary, less data.
• One-to-many. One row in table A corresponds to one or more rows in table B. But only one row in table A corresponds to one row in table B. In this case, there is a foreign key in table B that uniquely identifies the entry in table A.
• Many-to-many. One row in table A corresponds to one or more rows in table B, which is true in the opposite. In this case, an additional table is created with its primary key, and two foreign keys to table A and B.

Now let's figure out how to do it.

Creating a simple schema in the database

Создадим БД в 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 links:



Add a new link by clicking Add. Adding relationships takes place in the table where the foreign keys are located. We open the Tables and Columns tab and set the table with the primary key, and select the foreign key in the current UserRole table.



In the INSERT And UPDATE Specification properties, set the Cascade properties to On Update / On Delete:



This is necessary so that when changing / deleting a column from the Role table, all rows associated with this row in the UserRole table must be changed or deleted.

We establish a similar relationship with the User table.

Thus, Role and User tables have many to many relationships through the UserRole table. Those. one user can have more than one role, and several users can have the same role.

SELECT, INSERT, UPDATE, DELETE.

Relational databases use the SQL query language.

There are 4 main commands for manipulating data - SELECT, INSERT, UPDATE, DELETE

SELECT - for selecting data and tables.
Example:

SELECT * FROM User

INSERT - Adding Rows to a Table
Example:

INSERT INTO Role (Code, Name)
VALUES (“admin”, “Администратор”)

UPDATE - changing values ​​in a table
Example:

UPDATE User
SET Password=”password1” 
WHERE ID=1

DELETE - deleting rows from a table
Example:

DELETE FROM User
WHERE ID =1

Note: You can learn more about SQL at the links:
http://www.w3schools.com/sql/
http://codingcraft.ru/sql_queries.php

LinqToSQL and 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();
       }

For data manipulation, the Linq query language is used. We will consider only some of the basic functions of Linq. Linq is applied to types implementing the IQueryable <> interface

• .Where () is the main filtering function. Returns an IQueryable type. The condition inside should return a boolean value (bool).
  

  var roles = context.Roles.Where(p => p.Name == "Менеджер") 
  

  
  
• .FirstOrDefault () - .First (), .Single (), .SingleOrDefault () - get the first or only record. If there is no record, then FirstOrDefault () or SingleOrDefault () will return null (in fact, the default value of this type is [default (int)], for example).
  

   var roles = context.Roles.Where(p => p.Name == "Менеджер").FirstOrDefault()
  

  - we get the first (or do not get) the role called "Manager".
  
• .Take () - selects N first records
  

  var roles = context.Roles.Where(p => p.Name == "Менеджер").Take(4) 
  

  - selects the first 4 entries
  
• .Skip () - skips the selection of N first records
  

  var roles = context.Roles.Where(p => p.Name == "Менеджер"). Skip(2).Take(3)
  

  - skips the first 2 and selects 3 next entries
  
• .OrderBy () - Sorts ascending. And also OrderByDescending (), ThenBy (), ThenByDescending (). A lambda expression should return an int type, according to which sorting will occur.
  

  var roles = context.Roles.Where(p => p.Name == "Менеджер").OrderBy(p => p.ID) 
  

  - sorts in order
  
• .Count () - gets the number of records
  

  var rolesCount = context.Roles.Where(p => p.Name == "Менеджер").Count()
  

  - number of records
  
• .Any () - there is one or more records under this condition
  var rolesExist = context.Roles.Where (p => p.Name == "Manager"). Any () - is there such a record
  
• . Select () - returns an IQueryable of arbitrary type, maybe even dynamic:
  

  var otherRole = context.Roles.Where(p => p.Name == "Менеджер").Select(p => new {
  ID = p.ID, Kod = p.Code}) 
  

  - we get a dynamic type formed on the basis of Role.
  
• .SelectMany () - returns the union of all IQueryable types within the selection:
  

  var otherRole = context.Roles.Where(p => p.Name == "Менеджер").SelectMany(p => p.UserRoles) 
  

  - we get all UserRole from the role called "Manager"
  
• .Distinct () - removes duplicates
  

  var managers = context.Roles.Where(p => p.Name == "Менеджер").SelectMany(p => p.UserRoles).Select(p => p.User).Distinct()
  

  - all users with a role called "Manager"
  

Note: First(), FirstOrDefault(), Single(), SingleOrDefault(), Any(), Count() - they can apply the parameter corresponding Where(), thus, you can shorten the record:

var roles = context.Roles.FirstOrDefault(p => p.Name == "Менеджер")

You can find more examples and use cases of linq:
http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

Creating an IRepository Repository, SqlRepository.

Actually, we can already work with the database, only now we need to separate the data model from a specific implementation, i.e. our controllers about context and System.Data.Linq do not need to know anything at all.

To do this, create an IRepository interface, where access to the data will be given, and methods for creating, modifying, and deleting this data will be derived.

    public interface IRepository
    {
        IQueryable Roles { get; }
        bool CreateRole(Role instance);
        bool UpdateRole(Role instance);
        bool RemoveRole(int idRole);
      	…  
    }

We call the implementation SqlRepository. Since we don’t particularly want to associate SqlRepository with this context, we will add the Ninject module to the LessonProject.Model project:

Install-Package Ninject

Create the SqlRepository class:

 public class SqlRepository : IRepository
    {
        [Inject]
        public LessonProjectDbDataContext Db { get; set; }
        public IQueryable 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();
        }
    }

Before realizing access to all tables, creating, deleting and changing, let us think that this file will look huge and awkward. Such a code will be physically difficult to manage. So we will make a separate folder SqlRepository and SqlRepository class we will make partial, and in the folder we will create implementations of the IRepository interface, broken down for each table. Let's name the Role file:

 public partial class SqlRepository
    {
        public IQueryable 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;
        }
    }

A small project contains from 10 to 40 tables, a large project from 40, and I would like to somehow automate all this. Let's create some snippets, for IRepository and for SqlRepository. Snippets are predefined code templates that are called using intelliSence and allow you to quickly create code.

Snippets

For IRepository tables, create table.snippet:

Table
TableTable name for create.Table
         $Table$s { get; }
        bool Create$Table$($Table$ instance);
        bool Update$Table$($Table$ instance);
        bool Remove$Table$(int id$Table$);
        #endregion 
        ]]>
      

For SqlRepository, create the sqlTable.snippet snippet:

sqltable
TableTable name for create.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;
        }
        ]]>
      

To add code-snippet. open TOOLS -> Code Snippet Manager ... (Ctrl-K, B). In the window, click Import and import both snippets into My Code snippet:



Finish, OK.
We use for tables User and UserRole.





It remains to register only the fields for Update[table name], but this is less work.

Proxy

As you can see, the classes we use are partial, so they can be supplemented. Let's create, like SqlRepository, the Proxy folder where we will place partial classes. For example, for the User class, create a method that automatically generates the string required to activate the user:

 public partial class User
    {
        public static string GetActivateUrl()
        {
            return Guid.NewGuid().ToString("N");
        }
    }

Use this:

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;
        }

Using DB in asp.net mvc

Add the database access line to web.Config:

We initialize the work with the database in Ninject:

  private static void RegisterServices(IKernel kernel)
        {
            kernel.Bind().ToMethod(c => new LessonProjectDbDataContext(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString));
            kernel.Bind().To().InRequestScope();
        }      

Apply InRequestScope (). Those. each request will use a separate SqlRepository object. This will avoid collisions during execution. We declare an IRepository in the controller:

  public class HomeController : Controller
    {
        [Inject]
        public IRepository Repository { get; set; }
        public ActionResult Index()
        {
            var roles = Repository.Roles.ToList();
            return View(roles);
        }
    }

And update View (/Views/Home/Index.cshtml):

@model IList
@{
    ViewBag.Title = "LessonProject";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
LessonProject

    @foreach (var role in Model)
    {
        

                @role.ID
            
                @role.Name
            
                @role.Code
            
    }

We get a good result:



All sources are located at https://bitbucket.org/chernikov/lessons