Display data from a database with WebMatrix and asp.net web pages

In this post I would like to show a hands on example on how to get data back from a database using web pages with WebMatrix.

If you want to find more information about WebMatrix,web pages and Razor syntax have a look in this older post of mine.

The main WebMatrix site is this one.

The main goal of this post is to show you how to get the data from a database table using web pages and Razor syntax.I assume you know a little bit about Razor syntax and C#.

I assume that you have access to a version of SQL Server.

If not, you can download and install the free SQL Server Express edition from here. 

We must create the database first and then the table. It is a simple table with some data regarding to football. The script follows:

 

Create database football

USE football

CREATE TABLE footballers
(
id INT PRIMARY KEY IDENTITY,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
isActive BIT

)

INSERT INTO footballers (firstname, lastname,isActive)
VALUES
 ('Robbie', 'Fowler',1),
 ('Steven', 'Gerrard',1),
 ('Kenny', 'Dalglish', 0)

 

1) Launch WebMatrix and select the "Site From Template" option. Then select Starter site. Give your site a reasonable name and Press OK.

2)  When you see the main screen in WebMatrix, click the Databases Option from the menu on the right.

3)  We need to create a connection to the existing database. Click New Connection from the ribbon.Fill in the popup window with the relevant information and click OK. Have a look at the picture below

 

 4) Click the Files option in the menu on the right hand site and click on the web.config file. You will see a new connectionString element added with all the relevant information.

<configuration>
    <connectionStrings>
        <add connectionString="Trusted_Connection=True;Server=.;Database=football" name="thefootballdemo" providerName="System.Data.SqlClient" />
    </connectionStrings>


5) Add a new file to your project and name it footballers.cshtml

The code I have inside this file follows

@{
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Footballers";
   
        var mydb = Database.Open("thefootballdemo");
        var footballers = mydb.Query ("Select * from Footballers");
             

}


@foreach (var row in footballers)

        {
   
        <div>
           
            <input type="checkbox" @if (row.isActive){<text>checked="checked"</text>} />
            @row.firstname
            @row.lastname
           
        </div>
       
       
}

 6) I will explain what the code does.

snippet1

@{
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Footballers";

This tells the new page to follow the layout(colors,structure) from the SiteLayout.cshtml file.Then I simply give the page a new title.

Now I want to bring the data into this page. That is what I do with those 2 lines of code, below.

snippet2

     var mydb = Database.Open("thefootballdemo");
     var footballers = mydb.Query ("Select * from Footballers");

 Now we need to get this data to be dislayed on the page.

I am using a foreach statement for each row in the footballers variable.

I need to dispaly the isActive column from the database. I will use a checkbox control and some razor syntax with an If statement.

So if the footballer is active then the checkbox will be checked.Finally, I will output the firstname and  lastname. See the snippet below

snippet3


@foreach (var row in footballers)

        {
   
        <div>
           
            <input type="checkbox" @if (row.isActive){<text>checked="checked"</text>} />
            @row.firstname
            @row.lastname
           
        </div>
       
       
} 

 

7) Run your application from the Run option from the Ribbon. Bear in mind that it runs under the IIS Express built-in application server. If you followed everything correctly up to this point, your new page will display the footballers data.

 

More posts on data access with WebMatrix and asp.net web pages will follow.

Hope it helps.

No Comments