In this post I would like to show with a hands-on example on how to retrieve data from an sql server database table using C# and ASP.Net MVC 3.

This post will not (and cannot be) a thorough introduction to ASP.Net MVC. I suggest that you go through some of the videos and other learning resources from the official ASP.Net MVC site.  Download all the necessary tools needed to build and run mvc applications from the same website.

I will say a few words on how I understand ASP.Net MVC and what its main benefits/design goals are.

Obviously the first paradigm on bulding web applications on the web is Web Forms.

Web forms was the first and only way to build web application when ASP.Net was introduced to the world, nine years ago.

It replaced the classic ASP model by providing us with a strongly typed code that replaced scripting.We had/have languages that are compiled.Web forms feels like a form that you programmed with VB 6.0 for the desktop.

The main idea was to abstract the WEB.By that I mean HTML is abstarcted in a way.Click events replaced "Post" operations.Since that time, web standards have strengthened and client side programming is on the rise. Developers wanted to have more control on the HTML.Web forms , as I said before handles HTML in an abstract way and is not the best paradigm for allowing full control on the HTML rendering.

ASP.Net MVC provide us with a new way of writing ASP.Net applications.It does not replace web forms. It is just an alternative project type.It still runs on ASP.Net and supports caching,sesions and master pages.In ASP.Net MVC applications we have no viewstate or page lifecycle. For more information on understanding the MVC application execution process have a look at this link .It is a highly extensible and testable model.

In order to see what features of ASP.Net are compatible in both Models have a look here.

MVC pattern has been around for decades and it has been used across many technologies as a design pattern to use when building UI. It is based on an architecture model that embraces the so called "seperation of concern pattern".

There are three main building blocks in the MVC pattern. The View talks to the Model. The Model has the data that the View needs to display.The View does not have much logic in them at all.

The Controller orchestrates everything.When we have an HTTP request coming in, that request is routed to the Controller . It is up to the Controller to talk to the file system,database and build the model.The routing mechanism in MVC is implemented through the System.Web.Routing assembly. Routes are defined during application startup.Have a look at the Global.asax file,when building an MVC application.

The Controller will select which View to use to display the Model to the client.It is clear that we have now a model that fully supports "seperation of concerns".The Controller is responsible for building the Model and selecting the View.

The Controller does not save any data or state. The Model is responsible for that.The Controller selects the View but has nothing to do with displaying data to the client.This is the View's job.

The Controller component is basically a class file that we can write VB.Net or C# code. We do not have Page_Load event handler routines, just simple methods which are easy to test.No code behind files are associated with the Controller classes.All these classes should be under the Controllers folder in your application.Controller type name must end with Controller (e.g ProductController).

In the Views folder we should place the files responsible for displaying content to the client.Create subfolders for every Controller. Shared folder contains views used by multiple controllers.

In this post I will use the Razor View engine rather than the WebForms View. Razor View engine is designed with MVC in mind and it is the way (as far as I am concerned) to work with ASP.Net MVC.

Let's move on to our hands on example. It will be a very simple application. I will retrieve data from a Sql Server express database and output this data on an html page.

1) Launch Visual Studio and select ASP.Net MVC 3 Web application from the available templates.Choose C# as the development language.Give your application a meaningful name.

 2) When you click OK, then a new dialog box will appear. I have selected the following options (Empty template,Razor View Engine). Press OK when you select the options

3) Now you have an empty ASP.Net MVC 3 application. Browse the folders and the files, until you have a good feeling regarding the stucture of your application.

4) Add a ASP.Net folder in your application (App_Data). Select that folder, right click and choose to add a new item, a SQL Server database.

5) Select the .mdf file and click Open.In the Server Explorer window , select the .mdf file and then select Tables. Right-click on Tables and select the option Add a new Table.

I have named my table "Footballers". Have a look below for the table definition.

6) Right-click on the Footballers table (or any table name you gave) from the Server Explorer and select ShowTable Data.I will populate the table with some data.

7) Select the Models folder and add a new item. An ADO.Net Entity data model.Click Add button when you finish.

8) Then in the wizzard that pops up, select the option  Generate from database and click Next.

 9) In the next step of the wizzard select the data connection,make a note of the entity connection string and the name it has in the web.config file. Click Next

 10) In this final step of the wizzard, select the database objects (just the table in this case) to be included in the model.Click Finish

11)  Add a new class in the Models folder. I will name it Footballers.cs.Inside this class file I will write some code that will retrieve the data available from the model.

So I will have just a simple static class with a static method that simply returns a list of Footballer objects.

The code below is very easy to follow. I am creating an instance of the FootballersEntities class and then just use this object (ctx) to return my objects.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
 
namespace SimpleDataRetrievalMVC3.Models
{
    public static class Footballers
    {
        public static List<Footballer> GetFootballers()
        
        {
 
            var ctx = new FootballersEntities();
 
            return ctx.Footballers.ToList();
 
        }
 
    }
}

12) Now I need to write my new Controller class. In the Controllers folder, right click it and then from the context menu add a new controller class. I will name it FootballController.

Inside there I will add another action , ListFootballers.This action just passes the model to the View (which I have not created yet.)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using SimpleDataRetrievalMVC3.Models;
 
namespace SimpleDataRetrievalMVC3.Controllers
{
    public class FootballController : Controller
    {
        //
        // GET: /Football/
 
        public ActionResult Index()
        {
            return View();
        }
 
        public ActionResult ListFootballers()
        {
            return View(Footballers.GetFootballers());
        }
 
    }
}

13) Now I need to create the View. The way I choose to do it is by right-clicking on this line of code in the FootballController.cs file,

public ActionResult ListFootballers()

and from the context menu I select Add View...In the popup window select the options as seen below, and click Add

Have a look in the Views folder and see that a new folder called Football (it depends on how you named the controller class) has been created. Inside this folder a ListFootballers.cshtml file is created. Spend some time looking into this file. The code is very easy to follow and understand.We have all the markup ready for us.

For example, this line of code (inside the ListFootballers.cshtml file), declares that we retrieve a specific list of objects Footballer type.

@model IEnumerable<SimpleDataRetrievalMVC3.Models.Footballer>

14) Build and run your application. In my case the url is this one. Did you notice the clean URL? That is one of the things ASP.Net MVC brings to our web applications.

15) Right-click on the page and select View->Source . One thing you will notice for sure is the much cleaner HTML that is produced.

To recap, in this post I wanted to explain what ASP.Net MVC is. I also wanted to explain the differences between ASP.Net web forms and ASP.Net MVC.Finally I wanted to explain what the building blocks of ASP.Net MVC are and how to use them in a way that it could be possible to retrieve data from a sql server table.

Email me if you need the source code.

Hope it helps!!!!