MVC5 + EF6 complete tutorial 10, mvc5ef6 getting started tutorial

MVC5 + EF6 complete tutorial 10, mvc5ef6 getting started tutorial

This is the end of the first phase.

After learning this article, you should be able to use MVC to develop a complete project.

This article describes how to update associated data of multiple tables and how to use native SQL.

Outline

• Update multi-Table associated data
• How to use native SQL
• Summary

Update multi-Table associated data

We have already mentioned data update in the fourth article, but it is for updating the single table structure.

This time we will talk about using EF to update associated data.

Two associated data updates are available:

1. One-to-many

2. many-to-many

In the first case, the associated table has a primary foreign key Association. simply update the foreign key value (equivalent to updating a single table). We will mainly explain the second case of many-to-many Association.

Use the familiar model:

We define a scenario:

A user can have any number of roles, and a role can have any number of users.

Next, complete the following operations:

When editing a user, the role of the user is displayed for editing.

Update a user (SysUser table) and its related roles (SysUserRole table ).

 

Detailed steps:

1. Add a ViewModel to indicate whether the role is assigned to a user.

2. Open UserRoleController and add an Edit Action to display the editing page.

There are two notes:

A. We use the model in the previous article and have an additional SysDepartment. The actual model is as follows:

 

B. PopulateAssigenedRoleData marks the role selected by a specific user.

 

3. Open Views \ UserRole \ Index. cshtml and add an edit button.

 

4. the Edit View is automatically generated based on the Edit Action.

Modify related content, mainly for two points:

A. Department

B. Role

A role is displayed through a set of checkpoints.

Checkbox displays all roles in the database. If you have assigned a role to the user, the selected status is displayed.

Check the checkbox option to update the user role.

Description

It's okay if there are fewer roles. If there are more typical practices, you can use two listboxes and move the options on both sides with arrows in the middle. This article mainly describes how to update associated tables. We will provide examples of better practices in subsequent articles.

Run the Index page.

Go to the editing page.

In this way, the editing display function is complete.

You can see that a set of checkboxes indicates whether or not the roles is selected.

 

5. Complete the Edit function of HttpPost.

First, update the SysUser table:

Update entity: userToUpdate with the value in model binder.

We can see that we use the whitelist to specify the fields to be updated in the database.

TryUpdateModel (userToUpdate ,"",

New string [] {"LoginName", "Email", "Password", "CreateDate", "sys1_mentid "})

 

Then update the SysUserRole table:

Compare the value of the database with the value after editing. The basic logic is:

If it is selected, it will not be added;

If it is not selected, some of them will be deleted.

UpdateUserRoles (selectedRoles, userToUpdate );

 

 

Note that in UpdateUserRoles, a new connection is created.

Using (AccountContext db2 = new AccountContext ())

If the database is used, the following error is reported:

DataReader associated with this Command already opened must be disabled first.

 

Run the Index again, as shown in the following figure. Now we can see that role editing has taken effect.

So far, the example of multi-Table update is introduced here. In other cases, we believe that you can deduce the practice by yourself.

 

 

Use native SQL

One advantage of using EF is that it automatically generates SQL for us, which is convenient in general cases, but in some cases it is not suitable for using EF.

For example, when we update the SysUserRole table above, each time we increase or decrease a piece of data, we need to cycle it many times.

In addition, some very complex statements are difficult to generate using EF.

EF provides a set of methods for executing native SQL statements.

There are three types:

1. DbSet. SqlQuery

2. Database. SqlQuery

3. Database. ExecuteSqlCommand

What are the differences between the three types? Let's look at the example.

Let's take each of the three forms as an example.

Example 1: DbSet. SqlQuery query and return Entities

Let's start Controllers \ AccountController. cs to do the experiment.

Find the Details Method

Change the comments to the box.

SysUser sysUser = db. SysUsers. Find (id) is exactly the same as the comment in the box.

The front-end display effect remains unchanged:

 

Note:

1. Construct SQL statements with parameters (good habits to prevent SQL injection, always using SQL statements with parameters)

2. DbSet <TEntity> is used to execute the SQL method. The returned result is Entity, which is the same as that of the LINQ query. If you are not familiar with LINQ for the moment, replacing it with this method (as a transition) will allow you to quickly use the new framework.

This situation has some defects, such

SELECT LoginName as UserName, * FROM [dbo]. [SysUser] where id = @ id

You can see that I have added LoginName as UserName, because Column Attribute is used in the Model, and the field stored in the database is LoginName.

In this way, if no conversion is performed, the model will fail to Find the matching field, and the database. SysUsers. Find (id) can be used for intelligent conversion.

 

Example 2 Database. SqlQuery returns other types

String query = "select loginName from SysUser ";

Var names = db. Database. SqlQuery <string> (query). ToList ();

The System. Collections. Generic. List <string> type is returned.

The biggest difference between this method and the first case is that the non-entity type is returned.

We can build the desired types as needed.

We can also customize an entity type for it to return, for example, in the previous example:

SysUser sysUser = db. Database. SqlQuery (query, paras). SingleOrDefault ();

In this way, you can also return the entity, but note that this method will not be used by context track. It does not matter after the return. If we use a Model in the View. if the XXX navigation property retrieves other associated data, an error is returned. For example, @ foreach (var item in Model. SysUserRoles). in this case, the error Model is null.

 

Example 3: Database. ExecuteSqlCommand executes the update statement.

The last one is updated. You can see the following example:

Context. Database. ExecuteSqlCommand ("UPDATE dbo. Posts SET Rating = 5 WHERE Author = @ author", new SqlParameter ("@ author", userSuppliedAuthor ));

 

The last mention of the execution of the stored procedure, also similar, I will not say much, the following MSDN (https://msdn.microsoft.com/en-us/data/jj592907 ).

 

Native SQL usage Summary

Native SQL query:

The object model needs to be returned. Use DbSet. SqlQuery (context will be tracked, equivalent to the LINQ method)

To return other types, use Database. SqlQuery

Native SQL Execution update:

Use Database. ExecuteSqlCommand

 

So far, the first phase of this series of articles (1 ~ 10) It's over. Goodbye in the next stage.

Thank you for your support and wish you a better learning experience!

P.S. For your convenience, list the series of articles:

 

• Full tutorial 10: Update multiple-to-multiple join tables & use native SQL @ 20150521
• Full tutorial 9: Multi-table data loading @ 20150212
• MVC5 + EF6 full tutorial 8: upgrading the database structure without losing data @ 20141215
• Full tutorial 7: sorting and filtering pages @ 20141201
• MVC5 + EF6 full tutorial 6: partition View @ 20141117
• MVC5 + EF6 full tutorial 5: UI transformation @ 20141113
• MVC5 + EF6 full tutorial 4: EF basic CRUD @ 20141104
• MVC5 + EF6 complete tutorial 3: EF complete development process @ 20141027
• MVC5 + EF6 full tutorial 2: Starting from the front-end UI @ 20141021
• MVC5 + EF6 full tutorial 1: Starting from 0