Page 1 of 1

Razor template using DB Connection and WebGrid

#1 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Posted 29 March 2011 - 09:55 PM

This tutorial is going to introduce us to creating a simple database connection and putting some data into a simple grid using the Razor template engine. I'm going to just build off of the Default.cshtml page that is generated when you create a new web site that uses Razor. It should look something like this.

@{  
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Welcome to my Web Site!";
}
<p>
    ASP.NET Web Pages make it easy to build powerful .NET based applications for the web.
</p>



I'm going to leave this alone for the purpose of this tutorial and just go right into adding code. I'm also going to assume you already have a database available to make a connection to. The first thing we want to do is add a connection to our web.config file. So in the <connectionStrings> section we'll create a new connection string that should follow this basic format:

<add name="pubsConnection"
     connectionString="Server=ServerName;Database=DatabaseName;Uid=Username;pwd=Password"    
     providerName="System.Data.SqlClient"/>




Now that we have our connection string defined in our web.config we can go ahead and create our connection to the database. So in the beginning code block we'll use the Database.Open method and provide it the name of our connection string to accomplish this.

@{  
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Welcome to my Web Site!";
    var DB = Database.Open("pubsConnection");
}



We can now use the variable DB to query our database anywhere in the page. Extremely simple stuff huh. Now what if we want to display a table of data from the database? Fortunately we have a nice little WebGrid that makes this extremely simple. Once again in our opening code block we'll add one more line of code.

@{  
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Welcome to my Web Site!";
    var DB = Database.Open("pubsConnection");
    var Grid = new WebGrid(DB.Query("SELECT * FROM authors"));
}



We create a new instance of a WebGrid and fill it using the DB.Query method. The query method takes a string representation of the database query you want to run. Now that we have defined our Grid we still need to display it. This is done very easily by calling the GetHtml() method of the WebGrid. This method can be called without any parameters which will produce a table without any formatting whatsoever. Functional but not much to look at. However, the GetHtml() method can also take additional parameters to define the style for the table as a whole, the headers, alternating row, and so on. You can also pass a parameter to tell the grid which columns to display and what to use for the header text.

    @Grid.GetHtml(
        tableStyle: "MyTable",
        headerStyle: "columnHead",
        alternatingRowStyle: "altRow",
        columns: Grid.Columns(
        Grid.Column("au_lname", "Last Name"),
                    Grid.Column("au_fname", "First Name"),
                    Grid.Column("address", "Address")
        )
    )



So here we are calling the GetHtml method and setting the table style, the header style and the alternating row style. We are also telling the grid to only display three columns, the last name, first name and address columns, as well as changing the header text for those columns. Now you just need to create the css classes to correspond to the style values in the grid and you will have a nicely formatted WebGrid with very little code. If you are using a layout file you can add your styles to the top of that file or include an external css file. Here is the final code for the Default.cshtml page.

@{  
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Welcome to my Web Site!";
    var DB = Database.Open("pubsConnection");
    var Grid = new WebGrid(DB.Query("SELECT * FROM authors"));
}
<p>
    ASP.NET Web Pages make it easy to build powerful .NET based applications for the web.
</p>

<div>
    @Grid.GetHtml(
        tableStyle: "MyTable",
        headerStyle: "columnHead",
        alternatingRowStyle: "altRow",
        columns: Grid.Columns(
        Grid.Column("au_lname", "Last Name"),
                    Grid.Column("au_fname", "First Name"),
                    Grid.Column("address", "Address")
        )
    )
</div>



Also, here is the code from my layout file which includes the css styles I used.
Spoiler


And here is the final result:

Attached Image

This post has been edited by Nakor: 03 April 2011 - 04:19 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Razor template using DB Connection and WebGrid

#2 drdan18  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 15-August 12

Posted 22 August 2012 - 07:39 AM

I am looking for a tutorial just like this but making the connection to a MySQL database. Can you show me what would be different if using a MySQL database?

Thanks.

Dan
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8911
  • View blog
  • Posts: 33,424
  • Joined: 12-June 08

Posted 22 August 2012 - 07:45 AM

Have you investigated other tutorials?

Example:
Use VB.NET to connect to MySQL
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1