ASP.NET WP - Database

In this chapter, we will be covering how to create a database in WebMatrix using ASP.NET Web Pages (Razor) and how to display database data in a page.

  • A database contains one or more tables that contain information, like table for Customers information or table for Students.

  • In any given table you have several pieces of information, for example in Customers table there will be their first name, last name, and address, etc.

  • In most database tables, there is a column that contains a unique identifier which is also known as primary key, like a CustomerID, or StudentID, etc.

  • The primary key identifies each row in the table.

Create a Database

WebMatrix provides tools in which you can easily create a database and then can add tables in that database. The structure of a database is referred to as the database's schema. Now let’s open the WebMatrix and create a new empty site.

Database

Enter WebPagesCustomers in the Site Name field and click Next.

In the left pane, click Database as highlighted in the following screenshot.

WebPages Customers

Now you will see that it opens the database related options in the ribbon.

New Database

Click on the New Database option.

Page Customers

You will see that WebMatrix creates a SQL Server database which is a *.sdf file that has the same name as your site WebPagesCustomers.sdf and you can also rename this file as well.

Create Table

You can easily create a table in the database either by right clicking on Tables in the left pane and then selecting a New Table or you can click on the New Table option in the ribbon.

New Table

Now you can see that WebMatrix has opened the table designer.

Table Designer

Enter the Table Name and then some columns and press Ctrl+S to save as shown in the following screenshot.

Table Name

For ID row set, the Is Primary Key? and Is Identify? options to be changed to Yes (if they are not).

Now let’s enter some raw data to work with by clicking on the Data option and then entering some data as shown in the following screenshot.

Primary Key

Display Database Data

As we have a database and a Customers table and we also have some data in the database. Now we need to display that on the web page from the database. Let’s create a new CSHTML file.

Display Database

Enter ListCustomers.cshtml in the Name field and click OK. Now to retrieve all the customers from the database let’s replace all the code in ListCustomers.cshtml file as shown in the following program.

@{
   var db = Database.Open("WebPagesCustomers");
   var selectQueryString = "SELECT * FROM Customers ORDER BY FirstName";
}

<!DOCTYPE html>
<html>
   
   <head>
      <title>Customers List</title>
      <style>
         table, th, td {
            border: solid 1px #bbbbbb;
            border-collapse: collapse;
            padding: 2px;
         }
      </style>
   </head>
   
   <body>
      <h1>Customers List</h1>
      <table>
         <thead>
            <tr>
               <th>Id</th>
               <th>First Name</th>
               <th>Last Name</th>
               <th>Address</th>
            </tr>
         </thead>
         
         <tbody>
            @foreach(var row in db.Query(selectQueryString)){
               <tr>
                  <td>@row.ID</td>
                  <td>@row.FirstName</td>
                  <td>@row.LastName</td>
                  <td>@row.Address</td>
               </tr>
            }
         </tbody>
      </table>
   
   </body>
</html>

Now let’s run the application and specify the following url − http://localhost:36905/ListCustomers and you will see the list of customers on the web page as shown in the following screenshot.

List of Customers
Advertisements