WebMatrix Advanced Tutorial (5): How to use a database in a Web page

Guide: Microsoft WebMatrix is a free tool that you can use to create, customize, and publish Web sites on the Internet.

WebMatrix makes it easy for you to create sites. You can start with an open source application (such as WordPress, Joomla, DotNetNuke, or Orchard), and WebMatrix will handle the tasks of downloading, installing, and configuring these applications for you. Or you can use many of the built-in templates to write your own code that will help you get started quickly. Whatever your choice, WebMatrix provides everything you need to run your site, including Web servers, databases, and frameworks. By using the same stack that you will use on the web host on your development desktop, the process of getting your site online is easy and smooth.
You can download it from Http://web.ms/webmatrix.
Now you can learn to use WebMatrix, CSS, HTML, HTML5, ASP, SQL, database, and more, and how to write simple Web applications in just a few hours. The contents are as follows:
So far you've learned how to use WebMatrix to create HTML pages, how to use cascading style sheets (CSS) to style pages effectively, and how to use the built-in layout features and "Razor" syntax in WebMatrix to focus your attention on the content of your Web page. Instead of spreading to other chores.

In this chapter, we'll show you how to transform the static movie list that you're already using, and we'll make it dynamic. Actually, there's no need to manually write a movie list using HTML, we'll put them in a database, and we'll let WebMatrix read the database and generate HTML for us. In this way, we can easily change the database and automatically update the page.

Creating a database in WebMatrix

First, find the "Databases" workspace in WebMatrix and open it. In the center of the window, you will see the option "Add a Database to your site".

Select this option and WebMatrix will create a new database named "Movies.sdf". If your site has a different name, such as "foo", WebMatrix will create a database (or FOO.SDF) based on that name.

You will see the database in the Database Explorer on the left side of the window:

To add a table to a database

At the top of the window, you will see a tool ribbon that shows the different actions you can perform on the database, including creating tables and queries, and migrating to other databases. From this ribbon, select the New Table tool. If nothing happens when you select it, make sure that you select Movies.sdf in Database Explorer.

WebMatrix will create the table for you and open the Column editor. This allows you to create a new column in a database table. In a database vocabulary, a record refers to all the data for a particular entity. So, for example, a person's data may be his name, age, address, and phone number record. Columns are the values of individual data parts, regardless of which record they reside in. So, in the example above, name will be a column, and age is.

So, for our movie, we're going to create a database similar to this:

First, we create the ID. ID is the identifier for a particular record. You don't need to have an ID, especially for a simple database like this, but creating an ID is a good practice. As you build more complex databases, you will find them very important for tracking and querying data.

In the column editor, complete the details as shown:

This provides the column with the name "id" and specifies it as a number (bigint), which must have a value (set allow Nulls to false). It then specifies that the field is an ID, which means we have told the database that we use this field as an ID. The advantage of this is that as soon as we add a new record to the database, it automatically updates the ID, so we don't have to worry about how to keep track of the latest additions, get its ID, and calculate a new record.

Now we have shown that this field is the primary key. Again, "PRIMARY key" is a database vocabulary. A key is a column in a database that has a special value, usually the main content you want to use when you select a record. The database uses them to simplify the lookup of the data. For example, your workplace might have assigned you an employee number. This number makes it easy to track you because the search number "333102" may be much simpler than searching for employee "Nizhoni benally", especially if you have a large number of employees in your organization. This makes your employee number a key for finding your keys. You can use many keys in your data, and the primary key should be considered the most important one.

So, when a movie is recorded in the database, we will provide an ID for each movie, and we'll set the ID as the primary key.

Use the New column button in the Ribbon to create another 3 columns. You will see 3 unnamed blank columns in the table.

Select the first blank line, name it "name", and set the data type to "ntext". Make sure "is Identity" and "is Primary Key" are False.

For the second blank line, name it "Releaseyear" and keep its data type as "bigint".

For the third blank line, name it "Genre" and keep its data type as "ntext".

Click the Save button in the WebMatrix title bar

Pop-up dialog to enter the table name, named Favorites

Click OK and you'll see that the new table appears on the left side of the form

Add data

Double-click the Favorites table to open the empty table with no data

Enter data in the appropriate fields, ID does not need data

Now we've entered 4 records.

Create a Web page that uses data

In front, you see that your site uses a layout that includes HTML

Replace datamovies.cshtml content with static content

<div id="movieslist">  
      <ol>  
        <li><a href="#">It's a wonderful life</a></li>  
        <li><a href="#">Lord of the Rings</a></li>  
        <li><a href="#">The Fourth World</a></li>  
        <li><a href="#">The Lion King</a></li>  
      </ol>  
    </div>

The static list has only 4 items, and if we want 5 items, we need to add one. When the data in the database is pushed to the page, the page does not know how many records are in the database, then it generates N <li>, so it needs to loop to add

Let's first tell the page about the database, at the top of datamovies.cshtml, add the following code:

@{  
      var db= Database.Open("Movies");  
      var sqlQ = "SELECT * FROM Favorites";  
      var data = db.Query(sqlQ);  
    }

"@" represents the code that the razor engine needs to execute, where the syntax is familiar in programming languages such as Java, C + +, C, or C #

At this point, our code looks like this:

@{  
       var db= Database.Open("Movies");  
       var sqlQ = "SELECT * FROM Favorites";  
       var data = db.Query(sqlQ);  
     }  
     <div id="movieslist">  
       <ol>  
         <li><a href="#">It's a wonderful life</a></li>  
         <li><a href="#">Lord of the Rings</a></li>  
         <li><a href="#">The Fourth World</a></li>  
         <li><a href="#">The Lion King</a></li>  
       </ol>  
     </div>

We get the data from the database, but the page shows the static content, first we delete the rest of the <li>

<ol>         <li><a href= "#" >it ' s a wonderful life</a></li>       </ol>

Use the Razor engine to add data loops to the page using the @foreach

<ol>  
     @foreach(var row in data)  
       {  
         <li><a href="#">It's a wonderful life</a></li>  
       }  
     </ol>

Now we see the code, that's the effect, there are 4 records in the database, it's been looped 4 times.

But we don't get the content in the database, we need to change the code to:

<ol>  
       @foreach(var row in data)  
       {  
         <li><a href="#">@row.Name</a></li>  
       }  
     </ol>

So that you can get the data you're querying.

Now we add another piece of data to the database, and there's a total of 5 data

We open the page and present a list of 5 data

The above is WebMatrix Advanced Tutorial (5): How to use the content of the database in the Web page, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!

