Working With Database in ASP.Net Web Pages 2

Introduction

We've done some programming in ASP.NET WEB Pages 2 in the Programming in Web Pages part. Now we'll create an important part for the programming, the integration of the database with our site.

In that context, you will learn here how to create a database in the site and how to display data in the site using Razor syntax. We'll use the WebMatrix tool to create the database and database tables also.

So, let's proceed with the following sections:

Creating Database
Creating Table
Adding Data into Table
Displaying Data by Page
WebGrid Modification
Apply Paging

Creating Database

Now we create the database in this section. There is a very efficient tool in the WebMatrix with which we can create the database easily. Use the following procedure to do that.

Step 1: Open the site in WebMatrix.

Step 2: Click on the databases.

Creating Databases in WebMatrix

Step 3: Click on "New Database" to generate the database.

Create New Database in WebMatrix

Creating Table

Since we've created the database, we need to create the tables in it. Use the following procedure to do that.

Step 1: Click on "New Table" in the database.

Create New Table in WebMatrix

Step 2: Now enter the name for the table and create some fields in it like ID, Name and so on. Look at the screenshot below of my new Cricketers table:

Table Definition in WebMatrix

Step 3: Press Ctrl+S to save the table and close the database designer also.

Adding Data into Table

We've successfully created the table, therefore now we add the data into the table manually. We'll add the data from the browser in future articles.

Use the following procedure to do that.

Step 1: Open the database workspace in WebMatrix and open the "Table" section node.

Step 2: Choose the "Data" option by right-clicking on your table.

Open Table Data

Step 3: Enter the table records.

Enter Table Data

Step 4: Enter more records and it'll look as follows:

Table Records in WebMatrix

Displaying Data by Page

Now we create the page in the site and in the page we'll view our database table data using "WebGrid" helper. This helper produces a display in a grid or table. So, use the following procedure to do it.

Step 1: Create a new "CSHTML" file in the site named Cricketers.

Create CSHTML file

Step 2: Modify your code with the highlighted code below:

@{
   var CricDb= Database.Open("Cricketer Site");
   var CricData = CricDb.Query("Select * from Cricketers");
   var CricGrid = new WebGrid(source: CricData);
}
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>Best Cricketers</title>
    </head>
    <body>
        <h1>Best Cricketers</h1>
        <div>
            @CricGrid.GetHtml()
            )
        </div>
    </body>
</html>

In the code above you can see that the database is set to be open. This is always the first step before performing anything to the database. We use the Open method that assumes that it's looking for an ".sdf" file. The Query method is used to fetch the data as per query. We used here "Select * from Cricketers" which means that it will display all the records from the table named Cricketers.

Step 3: Now launch your site in the browser.

Display Site in Browser

You can also click on the headings to sort that column. It is already built into the WebGrid helper.

WebGrid Modification

As we can see, the grid view is plain, therefore in this section we modify the look of the webgrid. Use the following procedure to do it.

Individual Column

Step 1: Replace GetHtml() with the following code:

@CricGrid.GetHtml(
      columns: CricGrid.Columns(
            CricGrid.Column("Name"),
            CricGrid.Column("Team"),
            CricGrid.Column("Grade")
      )
)

In the code above, we'll use the columns parameter for the GetHtml method and pass in a collection of columns. We specify each column to include and we can include individual column by the Column property.

Step 2: Launch the site in the browser

Table Data in WebMatrix

Better Look

Step 3: Modify the code with the highlighted code below:

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>Best Cricketers</title>
        <style type="text/css">
            .CricGrid { margin: 3px; border-collapse: collapse; width: 450px; }
            .CricGrid th, .CricGrid td { border: 1px solid #C0C0C0; padding: 5px; }
            .head { background-color:#0ff; font-weight: bold; color: #FFF; }
            .alt { background-color: #0ff; color: #000; } 
        </style>
    </head>
    <body>
        <h1>Best Cricketers</h1>
        <div>
            @CricGrid.GetHtml(
                tableStyle: "CricGrid",
                headerStyle: "head",
                alternatingRowStyle: "alt",
                columns: CricGrid.Columns(
                    CricGrid.Column("Name"),
                    CricGrid.Column("Team"),
                    CricGrid.Column("Grade")
                )
            )
        </div>
    </body>
</html>

In the code above, we've specified some style for the webgrid and told the webgrid to follow the style.

Step 4: Now launch the site in the browser

Modified Table

Apply Paging

Now we apply paging in our code to make the look good and feel better of the web grid. Use the following procedure to do that.

Step 1: Modify the page code with the highlighted code below:

@{
   var CricDb= Database.Open("Cricketer Site");
   var CricData = CricDb.Query("Select * from Cricketers");
   var CricGrid = new WebGrid(source: CricData, rowsPerPage: 4);
}

Step 2: Launch the site in the browser.

Paging in WebGrid

Summary

This article has shown how to create a database and table using the tools in WebMatrix. You can also apply Razor syntax and paging in the WebGrid to make it look and feel better. In the next article, we'll perform the searching. Thanks for reading.