WebMatrix Advanced Tutorial (5): How to use databases in web pages

Introduction: Microsoft WebMatrix is ​​a free tool that can be used to create, customize, and publish websites on the Internet.

WebMatrix enables you to create websites easily. You can start with an open source application (such as WordPress, Joomla, DotNetNuke or Orchard) and WebMatrix handles the task of downloading, installing and configuring the application for you. Or you can write the code yourself using the many built-in templates that will help you get started quickly. Whatever you choose, WebMatrix provides everything your website needs to run, including web servers, databases, and frameworks. By using the same stack on your development desktop that you would use on your web host, the process of bringing your website 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.NET, SQL, databases, and how to write simple web applications in just a few hours. The content is as follows:
So far you have learned how to use WebMatrix to create HTML web pages, how to use Cascading Style Sheets (CSS) to effectively style web pages, and how to use the built-in layout functions and "Razor" syntax in WebMatrix to combine your Focus your attention on the content of the web page instead of being distracted by other chores.

In this chapter we'll cover how to transform the static movie list you're already using into something dynamic. Practically speaking, instead of manually writing the list of movies in HTML, we will put them in a database and we will let WebMatrix read that database and generate the HTML for us. This way we can easily make changes to the database, as well as automatically update web pages.

Create 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 website has a different name, such as "foo", WebMatrix will create a database based on that name (i.e. foo.sdf).

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

Add tables to the database

In the window At the top, you'll see a tools ribbon that shows the different operations you can perform on your 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 you select Movies.sdf in Database Explorer.

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

So, for our movie, we will create a database similar to the one below:

First, we create the ID. ID is the identifier of a specific record. You don't need to have an ID, especially with a simple database like this, but it's good practice to create one. As you build more complex databases, you'll find that they are important for tracking and querying your data.

In the column editor, fill in the details as shown below:

This will create the column Provide the name "id" and specify it as a number (bigint) that must have a value (set Allow Nulls to False). It then specifies that field as an ID, which means we're telling the database that we're using this field as an ID. The advantage of this is that whenever we add a new record to the database, it automatically updates the ID, so we don't have to worry about tracking the latest addition, getting its ID, and figuring out the new record.

Now we indicate that this field is the primary key. Again, "primary key" is a database term. A key is a column in a database that has a special value, usually the main thing you want to use when picking out records. Databases use them to simplify the lookup of data. For example, your workplace may have assigned you an employee number. This number makes it easy to track you, as searching for the number "333102" may be much simpler than searching for the employee "Nizhoni Benally," especially if you have a large number of employees in your business. This makes your employee number the key used to find you. You can use many keys in your data, and the primary key should be considered the most important one.

So, when recording movies in the database, we will provide each movie with an ID, which we set as the primary key.

Create 3 more columns using the "New Column" button in the ribbon. You will see 3 unnamed blank columns in the table.

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

For the second blank row, 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

A dialog box will pop up to enter the table name and name it Favorites

Click OK and you will see the newly created table appear on the left side of the form

Add data

Double-click the Favorites table to open an empty table, there is no data

Enter data in the corresponding field, no data is required for id

Now a total of 4 records have been entered

Create a web page and use the data

In front, you see your The website uses a layout including HTML

Replace datamovies.cshtml content with static content

<div id="movieslist">  
      <ol>  
        <li><a href="#">It&#39;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 only has 4 pieces of content. If we want 5 pieces of content, then 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, N number of

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

@{  
       var db= Database.Open("Movies");  
       var sqlQ = "SELECT * FROM Favorites";  
       var data = db.Query(sqlQ);  
     }  
     <div id="movieslist">  
       <ol>  
         <li><a href="#">It&#39;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>

<ol>  
       <li><a href="#">It&#39;s a wonderful life</a></li>  
     </ol>

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

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