ASP NET.MVC Lesson 3. Working with the Database

The purpose of the lesson : Learn the basic principles of working with a database. 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 a repository, IRepository, SqlRepository.

What is a DB?

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

Tables

This is the table:

The 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 the same
columns have different names (no two columns are the same)
the order of the rows in the table is arbitrary (i.e. it is not necessary to take into account the order of the rows if sorting is not specified)

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

Column name
The data type for this column

Connections

Relationships exist between tables. To establish a connection, you must have the following:

A primary key is a set of columns (attributes) of a table that uniquely identify the uniqueness of a row. This is usually one field called ID. It is auto-incremental, i.e. when trying to add a record, 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 one field called the [Table Name] ID. But it is not auto-increment.
A link is established between the primary key and the foreign key.

Links are of three types:

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, a smaller amount of 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 an entry in table A.
Many-to-many. One line in table A corresponds to one or several lines 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

Let's create a database in VS 2012:

Let's call it LessonProject, and add 3 tables Role User and UserRole.
Create a Role table:

For string values, we use the type nvarchar (n), where n is the maximum length of the string, usually between 50 and 500. For large text lines, nvarchar (MAX) is used.

Install the primary key:

Set the ID autoincrement:

Similarly, create a User table:

Field	Field type
ID	int
Email	nvarchar (150)
Password	nvarchar (50)
AddedDate	datetime
Activateddate	datetime (null)
Activatedlink	nvarchar (50)
LastVisitDate	datetime (null)
Avatarpath	nvarchar (150) (null)

Create a UserRole table:

Field	Field type
ID	int
Userid	int
RoleID	int

Add links:

Add a new link by clicking Add. Adding links occurs in the table where the foreign keys are located. Expand 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 properties of the INSERT And UPDATE Specification, we set the On Update / On Delete properties of the Cascade:

This is necessary in order to change / delete a column from the Role table, all the rows of the UserRole table associated with this row must be changed or deleted.

We establish a similar connection with the User table.

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

SELECT, INSERT, UPDATE, DELETE.

Relational databases use SQL query language.

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

SELECT - to select data and tables.
Example:

SELECT * FROM User

INSERT - Adding rows to a table
Example:

 INSERT INTO Role (Code, Name) VALUES (“admin”, “”)

UPDATE - change the values in the table
Example:

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

DELETE - delete rows from a table.
Example:

 DELETE FROM User WHERE ID =1

Note: You can learn more about SQL by reference:
http://www.w3schools.com/sql/
http://codingcraft.ru/sql_queries.php

LinqToSQL and Linq.

Create a project LessonProject.Model to work with the database of type ClassLibrary.
Add LINQ to SQL Classes type, call LessonProejctDb.dbml

Open the object, select all the tables and drag it to the canvas with the mouse:

Actually, with the help of such simple actions we get:

classes ready for use in working with the database
visual display of tables and links

Add some data to the Role and User table:

one	admin	Admin
2	customer	Customer

one	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

And userole

ID	Userid	RoleID
one	one	one
2	one	2
3	2	2

Create a console project Lesson3 and connect LessonProject.Model. Add the System.Configuration and System.Data.Linq assembly. Initialize the context and display data about the roles:

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

To add a line to Role, do this:

 var newRole = new Role { Code = "manager", Name = "" }; context.Roles.InsertOnSubmit(newRole); context.Roles.Context.SubmitChanges();

To delete a line in Role, do this:

 var role = context.Roles.Where(p => p.Name == "").FirstOrDefault(); if (role != null) { context.Roles.DeleteOnSubmit(role); context.Roles.Context.SubmitChanges(); }

To change the data, do this:

 var role = context.Roles.Where(p => p.Name == "").FirstOrDefault(); if (role != null) { role.Name = ""; context.Roles.Context.SubmitChanges(); }

Linq query language is used to manipulate data. We will cover only some basic Linq functions. Linq is used for types implementing IQueryable interface <>

.Where () is the main filtering function. Returns the type of IQueryable. The condition inside must 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 entry, then FirstOrDefault () or SingleOrDefault () returns null (in fact, the default value of this type is [default (int)], for example).
```
  var roles = context.Roles.Where(p => p.Name == "").FirstOrDefault() 
```
- we receive the first (or we do not receive) the role named "Manager".

.Take () - selects N first records

 var roles = context.Roles.Where(p => p.Name == "").Take(4)

- selects 4 first entries

.Skip () - skips the selection of the first N records
```
 var roles = context.Roles.Where(p => p.Name == ""). Skip(2).Take(3) 
```
- skip the first 2 and select the following 3 entries
.OrderBy () - sorts in ascending order. And also OrderByDescending (), ThenBy (), ThenByDescending (). The lambda expression must return the type int, by which the 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 for this condition
var rolesExist = context.Roles.Where (p => p.Name == "Manager"). Any () - is there such a record
. Select () - returns IQueryable of any type, maybe even dynamic:
```
 var otherRole = context.Roles.Where(p => p.Name == "").Select(p => new { ID = p.ID, Kod = p.Code}) 
```
- we receive the dynamic type generated on the basis of Role.
.SelectMany () - returns the union of all IQueryable types within the sample:
```
 var otherRole = context.Roles.Where(p => p.Name == "").SelectMany(p => p.UserRoles) 
```
- we get all UserRole from the role named “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() - can use the parameter corresponding to Where() , thus, you can reduce the record:

 var roles = context.Roles.FirstOrDefault(p => p.Name == "")

More examples and uses of linq can be found:
http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

Creating an IRepository, SqlRepository Repository.

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

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

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

The implementation is called the SqlRepository. Since we don’t want to specifically link to this SqlRepository context, we will add a 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<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(); } }

Before implementing access to all tables, creation, deletion and modification, we will think that this file will look huge and clumsy. This code will be hard to manage physically. So let's make a separate SqlRepository folder and the SqlRepository class will be partial, and in the folder we will create implementations of the IRepository interface, broken down into each table. Name the file 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; } }

A small project contains from 10 to 40 tables, a large project from 40, and I would like to automate all this. Create several snippets for the IRepository and for the SqlRepository. Snippets are ready-made code templates that are invoked with intelliSence and allow you to quickly create code.

Snipples

For IRepository tables, create a 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>

For the SqlRepository, create a sqlTable.snippet 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>

To add a 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.
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 that we use are partial, so you can add them. Let's create, like SqlRepository, a folder Proxy, where we will allocate partial classes. For example, for the User class, create a method that automatically generates the string required for user activation:

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

We 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 string to web.Config:

 <connectionStrings> <add name="ConnectionString" connectionString="Data Source=SATURN-PC;Initial Catalog=LessonProject;Integrated Security=True;Pooling=False" providerName="System.Data.SqlClient" /> </connectionStrings>

Initialize the work with the database in Ninject:

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

Apply InRequestScope (). Those. each query will use a separate SqlRepository object. This will avoid collisions during execution. We declare the 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 the 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>