Home .NET ASP NET.MVC Lesson 3: Working with the DB

ASP NET.MVC Lesson 3: Working with the DB

by admin

Lesson Objective : To learn the basic principles of working with a database. Brief description of the relational model of databases. Working with the database (creating tables, relationships in VS 2012). INSERT, UPDATE, DELETE, SELECT commands. Using LinqToSql and Linq. Creating a repository, IRepository, SqlRepository.

What is a DB

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

Tables

This is a table :
ASP NET.MVC Lesson 3: Working with the DB
A table consists of columns and rows. Columns have properties – name, data type.
Tables must have the following properties :

  • table has a name (unique)
  • no two rows 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., the order of the rows should not be taken into account if sorting is not set)

The table structure can be written in this form :

  • Column name
  • The data type for this column

Links

There are relations between tables. To set up a relation, you must have the following :

  • The primary key is a set of columns (attributes) of a table that uniquely determines the uniqueness of a row. Usually it is a single field, called ID. It is auto-incremental, i.e. when you try to add a record, 1, 2, 3, 4… n+1are automatically inserted there, where n is the value of the last IDadded.
  • The foreign key is a set of columns (attributes) of a table that uniquely identifies the uniqueness of a row in another table. Again, this is usually a single field called [Table Name]ID. But it is not autoincremental.
  • The relationship between the primary key and the foreign key is prescribed.

Connections come in three types :

  • One-to-one. That is, one row in a table corresponds to one row in another table. This is rarely used, but it is used. For example, one table has data about a user, and another table has additional data about the same user. This option is necessary to manipulate less data as needed.
  • One-to-many. One row in table A corresponds to one or more rows in table B. But one row in table B corresponds to only one row in table A. In this case, table B has a foreign key 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 reverse. 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 this.

Creating a simple schema in the database

Let’s create a database in VS 2012:
ASP NET.MVC Lesson 3: Working with the DB
Let’s call it LessonProject, and add 3tables Role User and UserRole.
Let’s create a Role table:
ASP NET.MVC Lesson 3: Working with the DB
ASP NET.MVC Lesson 3: Working with the DB
For string values we use the type nvarchar(n), where n is the maximum length of the string, usually used from 50 to 500. For large text strings, nvarchar(MAX) is used.
Set primary key :
ASP NET.MVC Lesson 3: Working with the DB
Set Auto Increment for ID:
ASP NET.MVC Lesson 3: Working with the DB
Similarly, create a table User:

Field Type of field
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 table UserRole:

Field Type of field
ID int
UserID int
RoleID int

Let’s add communications :
ASP NET.MVC Lesson 3: Working with the DB
Add a new relationship by clicking Add. Adding links takes place in the table where the foreign keys are located. Open the Tables and Columns tab and select the table with the primary key, and select the foreign key in the current UserRole table.
ASP NET.MVC Lesson 3: Working with the DB
In the INSERT And UPDATE Specification properties, set On Update/On Delete of the Cascade property:
ASP NET.MVC Lesson 3: Working with the DB
This is to ensure that when a column is changed/deleted from the Role table, all rows in the UserRole table associated with that row must be changed or deleted.
We make a similar connection to the User table.
So the Role and User tables have a many-to-many relationship through the UserRole table. That is, one user can have more than one role, and the same role can have multiple users.

SELECT, INSERT, UPDATE, DELETE.

Relational databases use the SQL query language.
There are 4 basic 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“, ”Administrator")

UPDATE – change values in a table
Example :

UPDATE UserSET Password=”password1”WHERE ID=1

DELETE – deletes rows from the table
Example :

DELETE FROM UserWHERE ID =1

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

LinqToSQL and Linq.

Create a LessonProject.Model project to work with the ClassLibrary database type.
Add LINQ to SQL Classes type, call it LessonProejctDb.dbml
ASP NET.MVC Lesson 3: Working with the DB
Open the object, select all the tables and transfer them to the canvas with the mouse:
ASP NET.MVC Lesson 3: Working with the DB
Actually, with such simple actions we get :

  • DB-ready classes
  • visual representation of tables and links

Let’s add some data to the Role and User table:

1 admin Admin
2 customer Customer

1 chernikov@gmail.com 123456 1/1/201212:00:00 AM NULL 123456 NULL NULL
2 chernikov2@gmail.com 123456 1/1/201212:00:00 AM NULL 123456 NULL NULL

And UserRole

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

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

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 the Role do this :

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

To delete a line in Role do the following :

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

To change the data do the following :

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

The Linq query language is used to manipulate data. We will consider only some basic Linq functions. Linq is used for types that implement IQueryable<> interface

  • Where() – The main filtering function. Returns an IQueryable type. The condition inside must return a boolean value (bool).
    var roles = context.Roles.Where(p =>p.Name == "Manager")

  • FirstOrDefault() – .First(), .Single(), .SingleOrDefault() – Get the first or only record. If there is no record, 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 == "Manager").FirstOrDefault()

    – Get the first (or don’t get) the role named "Manager".

  • Take() – takes the first N records
    var roles = context.Roles.Where(p => p.Name == "Manager").Take(4)

    – will take the first 4 records

  • Skip() – Skip selection of the first N records
    var roles = context.Roles.Where(p =>p.Name == "Manager"). Skip(2).Take(3)

    – Skip the first 2 and take the next 3 entries

  • OrderBy() – sorts in ascending order. And also OrderByDescending(), ThenBy(), ThenByDescending(). The lambda expression should return int type, which will be used for sorting.
    var roles = context.Roles.Where(p => p.Name == "Manager").OrderBy(p => p.ID)

    – sorts in order

  • Count() – gets the number of records
    var rolesCount = context.Roles.Where(p => p.Name == "Manager").Count()

    – number of records

  • Any() – there is one or more records with this condition
    var rolesExist = context.Roles.Where(p => p.Name == "Manager").Any() – whether there is such a record
  • Select() – Returns an IQueryable of arbitrary type, maybe even dynamic:
    var otherRole = context.Roles.Where(p => p.Name == "Manager").Select(p => new {ID = p.ID, Kod = p.Code})

    – we get a dynamic type generated from Role.

  • SelectMany() – Returns the union of all IQueryable types within a selection :
    var otherRole = context.Roles.Where(p => p.Name == "Manager").SelectMany(p => p.UserRoles)

    – Get all the UserRoles from the role named "Manager"

  • Distinct() – removes duplicates
    var managers = context.Roles.Where(p =>p.Name == "Manager").SelectMany(p => p.UserRoles).Select(p => p.User).Distinct()

    – all users with the role named "Manager"

Note : First(), FirstOrDefault(), Single(), SingleOrDefault(), Any(), Count() – can apply the parameter corresponding to Where() , thereby, it is possible to shorten the entry :

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

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

Creating IRepository, SqlRepository.

Actually we can already work with the database, but now we need to separate the data model from the specific implementation, i.e. our controllers shouldn’t know anything about context and System.Data.Linq at all.
To do this, let’s create an IRepository interface that will give you access to the data, as well as display methods to create, modify, and delete that data.

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

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

Install-Package Ninject

Let’s create a 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 we implement all table access, creation, deletion and modification, think about the fact that this file will look huge and clumsy. Such code will be physically hard to manage. So let’s make a separate folder SqlRepository and SqlRepository class make partial, and in the folder create an implementation of the IRepository interface, broken down for each table. Let’s 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 we want to automate it all somehow. Let’s create several snippets, for IRepository and for SqlRepository. Snippets are ready-made code templates, which are called with intelliSence, and allow you to quickly create code.

Snippets

For IRepository tables, let’s create a table.snippet:

<CodeSnippetsxmlns="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>

Create a sqlTable.snippet for the SqlRepository:

<CodeSnippetsxmlns="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 : Updatefields 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:
ASP NET.MVC Lesson 3: Working with the DB
Finish, OK.
Use for User and UserRole tables.
ASP NET.MVC Lesson 3: Working with the DB
ASP NET.MVC Lesson 3: Working with the DB
All that remains is to prescribe the fields for Update [table name], but that’s less work.

Proxy

As you can see, the classes we’re using are partial, so they can be extended. Let’s create, like SqlRepository, a folder Proxy, where we will put the partial classes. For example, we will create a method for the User class that will automatically generate the string we need 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 the database in asp.net mvc

Let’s add a 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>

Initializing the database operation 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(). I.e. each query will use separate SqlRepository object. This way we avoid collisions in execution.Declare IRepository in 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>

We get a good result :
ASP NET.MVC Lesson 3: Working with the DB
All the sources are at https://bitbucket.org/chernikov/lessons

You may also like