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.