Mvc5+ef6 Getting Started complete tutorial Ten

This is the end of the first phase of the article.

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

This article focuses on updating multi-table associated data and how to use native SQL.

Article outline

• Multi-table associated data update
• How to use native SQL
• Summarize

Multi-table associated data update

We have already talked about the update of the data in the fourth article, but that is an update for the single-table structure.

This time we are talking about updating the associated data using EF.

There are two cases of associated data updates:

1. One-to-many

2. Many-to-many

The first case correlation table has the primary foreign Key association, as long as the simple update foreign key value can be (equivalent to update a single table), we mainly explain the second kind of many-to-many cases.

Use a model you were familiar with earlier:

We define a scenario:

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

Let's do this next:

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

That is to update a user (Sysuser table) and its associated roles (Sysuserrole table).

Detailed steps:

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

2. Open Userrolecontroller and add an edit action to display the edit page.

There are two points to explain:

A. We use the model of the previous article, one more sysdepartment, the actual model is as follows:

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

3. Open views\userrole\index.cshtml, add an Edit button

4. Automatically generate edit View based on edit action

Modify the relevant content, mainly two points:

A. Department

B. Role

A role is displayed through a set of checkboxes.

The checkbox displays all roles in the database, and the selected status is displayed for the user.

User role updates are implemented by checking the checkbox.

Description

Fewer characters like this, if more classic practice, you can use two ListBox, the middle with arrows to the left and right side of the options to move. This article mainly explains the updates to the associated tables, and we will provide examples of better practices in subsequent articles.

Run the next index page.

Go to the edit page.

The display function of the editor is completed.

As you can see, use a set of checkboxes to indicate whether roles is selected.

5. Finally finish the HttpPost edit function.

First update the Sysuser table:

Update entity:usertoupdate with the values in model binder.

As you can see, we used a whitelist to specify the fields in the database that need to be updated.

TryUpdateModel (Usertoupdate, "",

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

Re-update the Sysuserrole table:

Compare the values in the database with the edited values, the basic logic is:

If it is selected, the original is not to be added;

If not selected, the original has to be deleted.

Updateuserroles (Selectedroles, usertoupdate);

Note In Updateuserroles, I've created a new connection

using (Accountcontext db2=new accountcontext ())

If you use the previous DB, the following error is reported:

There is already an open DataReader associated with this Command, which must be closed first.

Rerun index, such as next photo, when we see the role edit has already worked.

At this point, examples of multi-table updates are presented here, and others believe you can extrapolate yourself out of the way.

Using native SQL

One of the advantages of using EF is that it automatically generates SQL for us, which is convenient in general, but in some cases it is not appropriate to use EF.

For example, when we update the Sysuserrole table above, we have to loop a number of times each time we add or subtract one piece of data.

There are also some particularly complex statements that can be difficult to generate with EF.

EF provides a set of methods to execute native SQL.

The following three types are available:

1.dbset.sqlquery

2.database.sqlquery

3.database.executesqlcommand

What's the difference between these three types? Let's take a look at examples.

We give one example to three forms.

Example 1:dbset.sqlquery Query and return entities

We open the Controllers\accountcontroller.cs and do the experiment.

Find the Details method

Change the part of the comment to the Solutionkeys box.

The contents of the box and the commented out Sysuser sysuser=db. Sysusers.find (ID) is exactly the same.

The front-end display effect is unchanged:

Note two points:

1. Construct SQL statements with parameters (develop good habits, prevent SQL injection, always use SQL statements with parameters)

2. The dbset<tentity> Execute SQL method is used here, and the returned entity is directly the same as the LINQ query. If you are unfamiliar with LINQ, replacing it (as a transition) in this way allows you to quickly use a new frame.

This situation has some drawbacks, such as

SELECT LoginName as username,* from [dbo]. [Sysuser] WHERE [email protected]

As you can see, I added LoginName as UserName because the model uses column Attribute, and the fields stored in the database are LoginName

So if I do not convert, the model will not find a matching field and error, if using DB. Sysusers.find (ID) can be converted intelligently.

Example 2 Database.sqlquery return other types

string query = "Select LoginName from Sysuser";

var names=db. Database.sqlquery<string> (query). ToList ();

The above will return a system.collections.generic.list<string> type.

The biggest difference between this and the first case is the return non-entity type.

We can build the types we need, as needed.

We can also customize an entity type to let it return, for example like our previous example:

Sysuser sysuser = db. Database.sqlquery (query, paras). Singleordefault ();

This also returns the entity, but be aware that this will not be done by the context track, and will not matter if we get other associated data in the view using similar model.xxx navigation properties. For example, @foreach (var item in Model.sysuserroles), which in this case will report the Model null error.

Example 3:database.executesqlcommand executing an UPDATE statement

The last one is updated, just look at the example and understand:

Context. Database.executesqlcommand ("UPDATE dbo. Posts SET Rating = 5 WHERE Author = @author ", New SqlParameter (" @author ", Usersuppliedauthor));

Finally, the execution of the stored procedure is similar, I will not say more, the following MSDN (https://msdn.microsoft.com/en-us/data/jj592907).

Summary of native SQL usage

Native SQL execution Query:

Need to return entity model, use Dbset.sqlquery (context Trace, equivalent to LINQ mode)

Need to return other types, using Database.sqlquery

Native SQL to perform the update:

Using Database.executesqlcommand

At this point, the first phase of this series (1~10) is over, and the next step is goodbye.

Thanks for the support, I wish to learn progress!

P.S. Easy to watch, list the series of articles address:

• Mvc5+ef6 Getting Started complete tutorial 11--the application of storage mode in MVC
• Mvc5+ef6 Getting Started complete tutorial 10: Many-to-many association table updates & using native sql@20150521
• Mvc5+ef6 Getting Started complete Tutorial 9: Multi-table Data loading @20150212
• Mvc5+ef6 Getting Started complete tutorial 8: Database structure upgrade without data loss @20141215
• Mvc5+ef6 Getting Started complete tutorial 7: Sorting Filtering paging @20141201
• Mvc5+ef6 Getting Started complete Tutorial 6: Partial View @20141117
• Mvc5+ef6 Getting Started complete tutorial 5:ui some makeover @20141113
• Mvc5+ef6 Getting Started complete tutorial 4:ef basic crud @20141104
• Mvc5+ef6 Getting Started complete tutorial 3:ef complete development process @20141027
• Mvc5+ef6 Getting Started complete Tutorial 2: The front-end UI begins @20141021
• Mvc5+ef6 Getting Started complete Tutorial 1: Starting from 0

Mvc5+ef6 Getting Started complete tutorial Ten