ASP. Basic Learning Notes (7): Data query page

Objective

The front cushion so much, today we use MVC5 + EF6 + BOOTSTRAP3 to formally create a basic query page.

Why start with the query page? Hey, because of my little brother. NET career is to start from the query page, remember that the first month of formal work is a variety of modules query. What are the benefits of doing a query first? Read-only data is queried first, and no data is written. The data security of the database itself is not affected or the trouble of writing dirty data. The second addition and deletion to check the four operations inside, the difficulty of the query should be minimal, do not consider the transaction process and other aspects of the problem.

The following page will be generated at the end of this section:

Create model

About model, database context, Entity Framework, which is described earlier in this tutorial series

Create a class under the Models folder named Worker.cs, which is the model we are going to use here. The code is as follows:

namespace slarkinc.models{public    enum Sex    {        Male, Female    } public    class Worker    {        public int ID {get; set;}        public string LastName {get; set;}        public string FirstName {get; set;}        Public sex Sex {get; set;}        Public double? Rating {get; set;}}}    

For convenience, add line numbers, and if you want to copy the code, click the small icon in the upper-left corner and the code will appear without the line number. On the top of the worker class, line 3rd creates a sex enumeration type that guarantees that sex has only male and female two of values. 9 to 13 rows The worker class uses the method of automatic attributes to define 5 properties, where the rating property is preceded by a question mark indicating that the property is nullable and can be null.

Create a database context DbContext

Here we create a database table with the code first method. The simple point of this method is to create the model and then build the database table from the model.

For the sake of convenience, the database used here is the localdb that comes with Visual Studio.

The database context, which is our data access layer, operates differently from the model on the database table. So we first create a data Access Layer folder in the project named Dal. Then, in the Dal folder, create the model corresponding database access context class named Companycontext. Write the following code:

Using system.data.entity;using slarkinc.models;using System.data.entity.modelconfiguration.conventions;namespace slarkinc.dal{public    class Companycontext:dbcontext    {public        companycontext ()  : Base (" Companycontext ")        {        } public        dbset<worker> Workers {get;set;}        protected override void Onmodelcreating (Dbmodelbuilder modelBuilder)        {            modelbuilder.conventions.remove< Pluralizingtablenameconvention> ();}}}    

Because we have database access here to use the Entity Framework, the 1th line is to introduce System.Data.Entity. Line 7th, our database context inherits the DbContext base class from EF. Line 9th indicates

Companycontext inherits the constructor of the base class DbContext. Line 13th indicates that our model worker corresponds to the data set workers in Companycontext. Line 15th to 18th defines an event

Onmodelcreating, this event is triggered when we use the code first method to create a data table in the database. The purpose of the code in line 17th is that the names of the database tables we create are singular, not plural. This means that future database table names are workers rather than workers. This is also the customary naming method for creating database tables.

Then add the database connection string that the database context uses in the Web. config file. Find the <configuration> element in the Web. config file as shown in the following code. Then find the <connectionStrings> element within the <configuration> element. Finally, add the following <add> elements within the <connectionStrings> element.

<configuration>  <connectionStrings>    <add name= "Companycontext" connectionstring= "Data Source= (LocalDb) \v11.0;initial catalog=company;integrated Security=sspi; attachdbfilename=| Datadirectory|\company.mdf "providername=" System.Data.SqlClient "/>  </connectionStrings>< Configuration>

This allows our database context Companycontext to use this connection string to access the database. Explain the contents of the <add> element. Name= "Companycontext" is the name of the connection string, which is the same as the parameter of the base ("Companycontext") function in CompanyContext.cs, so that the string can be read when the database context is initialized. Data source= (LOCALDB) \v11.0 represents the kind and version of the database used. Catalog=company is the name of the database. Integrated Security=sspi is the authentication method. attachdbfilename=| Datadirectory|\company.mdf means that in our case, the database file exists ~\app_data\company.mdf. Providername= "System.Data.SqlClient" means the database connector we're going to use here, or it's called a database driver.

Then we also declare the database context in Web. config. Find <entityFramework> elements in <configuration>. Find the <contexts> element in the <entityFramework> element. Write the following <context> elements in the <contexts> element.

<entityFramework>    <contexts>      <context type= "SlarkInc.DAL.CompanyContext, Slarkinc" >      </context>    </contexts></entityFramework>

The SlarkInc.DAL.CompanyContext in type= "SlarkInc.DAL.CompanyContext, Slarkinc" represents this Companycontext namespace and the class name. The slarkinc after the comma indicates that the Companycontext is in SlarkInc.dll.

This allows us to add companycontext to the context of the Entity Framework.

Creating initialization data

To facilitate testing, we need to add some data to the database after it is created. So let's create a data initialization class to do the work. Create a class in the Dal folder named CompanyInitializer.cs. Write the following code:

Using system.collections.generic;using slarkinc.models;namespace slarkinc.dal{public    class Companyinitializer: system.data.entity.dropcreatedatabasealways<companycontext>    {        protected override void Seed ( Companycontext context)        {            var students = new list<worker>            {                new worker{firstname= "Andy", Lastname= "George", sex = Sex.male},                new Worker{firstname= "Laura", lastname= "Smith", sex = Sex.female},                new Worker{firstname= "Jason", lastname= "Black", sex = Sex.male},                new Worker{firstname= "Linda", lastname= "Queen", sex = Sex.female},                new Worker{firstname= "James", lastname= "Brown", Sex = Sex.male}            };            Students. ForEach (s = = context. Workers.add (s));            Context. SaveChanges ();}}}    

Line 6th, our data initialization class inherits a generic class named Dropcreatedatabasealways, which acts like its name, and deletes and re-creates the database every time the program runs, so that we can test it easily. Line 8th to 20th, the function that initializes the data, overrides the parent class's seed function, and accepts Companycontext as the database context for us to manipulate. Line 18th adds students data to the dataset in the database context using LINQ syntax. Line 19th, commit the data to the database.

You then configure the initializer in the Web. config file. In the <context> that you just configured, write the Databaseinitializer as shown below.

<contexts>   <context type= "SlarkInc.DAL.CompanyContext, Slarkinc" >      <databaseinitializer Type= "SlarkInc.DAL.CompanyInitializer, slarkinc"/>   </context></contexts>

Type= "SlarkInc.DAL.CompanyInitializer, Slarkinc" The SlarkInc.DAL.CompanyInitializer in the Companyinitializer represents the namespace and class name of this. The slarkinc after the comma indicates that the Companyinitializer is in SlarkInc.dll.

So our initializer is done.

Create a Controller

Controller did not learn the classmate please poke me!

Create a controller under the Controllers folder Select "MVC 5 controller-NULL", named CompanyController.cs. Write the following code:

Using system.linq;using system.web.mvc;using slarkinc.dal;namespace slarkinc.controllers{Public    class Companycontroller:controller    {        private Companycontext db = new Companycontext ();        Public ViewResult Index ()        {            return View (db. Workers.tolist ());}}}    

In order to use the model and database context created earlier, the Dal namespace is introduced in line 3rd and the Companycontext is initialized on line 9th. Creating the Index action return type on line 12th is Viewresult, then the user request returns a view page. Then pass all the data to view in line 12th. The 12th line ToList method is to output the data in the form of a list.

CREATE view

To create a view of the Companycontroller's index action, first create the company folder under the Views folder. Then create an empty view under the Company folder and choose to use the layout page. Name is index.cshtml. Write the following code:

@model ienumerable<slarkinc.models.worker><table class= "table" >    <tr>        <th>@ Html.displaynamefor (model = model). FirstName) </th>        <th> @Html. displaynamefor (model = model. LastName) </th>        <th> @Html. displaynamefor (model = model. Sex) </th>        <th> @Html. displaynamefor (model = model. Rating) </th>    </tr>    @foreach (var item in Model)    {        <tr>            <td>                @ Html.displayfor (ModelItem = Item. FirstName)            </td>            <td>                @Html. displayfor (ModelItem = Item. LastName)            </td>            <td>                @Html. displayfor (ModelItem = Item. Sex)            </td>            <td>                @Html. displayfor (ModelItem = Item. Rating)            </td>        </tr>    }</table>

Line 1th, indicates that the model used for this page is the worker class we created. The following code creates a table to display the data from the controller. 4-7 lines through

Displaynamefor gets the property name of the model as a column header display. The 第9-25 line displays the data through the Displayfor function.

View results page

Select View in Browser. You can see the results shown below.

The default layout of ~/views/shared/_layout.cshtml is applied to the head and tail of the page. The middle section is the table generated in the index.cshtml file. 4 of these columns are the 4 columns of data in the model worker other than the ID. The five-figure data in the table is the data we initialize in ~/dal/companyinitializer.cs.

Right-click on any of the data in the page table and select the check element as shown in.

This will have CSS styles for the corresponding elements on the right side of the F12 Developer Tool window.

Because we set the <table class= "table" in Index.cshtml > so all the elements of this table will apply the bootstrap table style as shown below.

View Database

In Solution Explorer, click the Show All Files button as shown to make all the files appear. A companycontext.mdf database file appears under App_Data, as shown in.

Double-clicking this file will appear in the Server Explorer window as shown in:

You can see two database connections, do not worry, the first is the connection we generated after running the program, the second is just double-click the MDF file generated connection. Two connection effects are the same. Double-click the Worker table under the Tables folder for the second connection. You can see the tables we generated using the EF Code first method. Shown below:

The top left is the table structure, the top right is the table's other properties, and below is the SQL statement used to generate the table.

Right-click on the Worker table in Server Explorer and select "Show Table Data" to display all the data, is it the same as the page display?

End

In this section we apply MVC5 + EF6 + BOOTSTRAP3 to create the first formal data query page. Later, we will add keyword query, pagination, sorting and other functions to this page. To implement these features, we'll cover more EF functions and how LINQ is used. Please look forward to it.

ASP. Basic Learning Notes (7): Data query page