MVC.Net and Linq to SQL

I was up until 4:30am Friday night working on a database thing for work. I spent most of the day Saturday, on and off, doing the same thing. The good news is I learned all that I needed to learn, solved the problem, and built a pretty thorough test harness and MbUnit tests for it. Swell. It violated my “don’t work on weekends” rule, but it was for a good cause. I had to learn new stuff, so it was to my benefit.

Anyhoo, that’s a different story for another day.

When Sunday evening rolled around, we sat down to watch an Indiana Jones movie or 2 (or 3). I’m mentally unable to simply sit there and watch a movie that I’ve already seen, unless I’m tired or uncharacteristically mentally lazy. Typically, I need something to work on at the same time. (Well, it depends on the movie too, I suppose).

Furthermore, I’ve been slacking for a long time. Over the last few months, I’ve played A LOT of Burnout Paradise. For a while, I was feeling guilty about gaming instead of something productive, but I quickly came to terms with it. If I want to take a break, I can take a break. This one just happened to be longer than usual. Oh well.

So, now that i’ve thoroughly beat Burnout Paradise, and I’ve done enough “work” work, its time for something new. I decided to jump on this MVC.NET thing that I’ve been hearing so much about. And, at the same time, I might as well start using LINQ for SQL. (I dabbled with LING for collections a bit, but nothing serious).

The MVC.NET web template gets you going pretty good. It creates the MODELS, VIEWS, and CONTROLLERS folders.

Create Database

I have a vague about the website I’m going to build from this stuff, but its not real important. I started with an empty database and created a single table: PEOPLE. It has the following fields;

– PeopleId (PK int identity)

– FirstName

– LastName

– EmailAddress (nullable)

– BirthDate (nullable)

Simple… no big deal.

Create DBML

I ended up using VS2008 to crate the DBML file. Before that, I used SQLMetal just for kicks. It worked swell.

I then ran a couple quick tests on the DBML file just to get a feel for ole betsy.

ClanDataContext db = new ClanDataContext();

People people = new People();

people.FirstName = “first”;

people.LastName = “last”;

people.EmailAddress = “”;

people.Birthday = new DateTime(1972, 7, 2);

//PeopleId deafults to 0



//PeopleId is now the new identity value

Sweet. LINQ is cool.

Modify the MasterPage

The MVC site template comes with a master page. I added an admin link to the left margin.

  • Parameter 1 is the text that appears in the link.

    Parameter 2 is the name of the action.

    Parameter 3 is the controller.

    The existing links on the page did not specify the controller, because there was only one to start with. Once I added my ClanController, I had to change the existing links to specify the third parameter.

    The link renders as: http://localhost:1653/ClanAdmin/Index (Controller/Action)

    Controller Class

    Next, create a controller class. The controller specified is ClanAdmin. The name of the specified action was Index, so create a method called Index.

    using System;

    using System.Collections.Generic;

    using System.Linq;

    using System.Web;

    using System.Web.Mvc;

    using MvcTest1.Models;

    namespace MvcTest1.Controllers


    public class ClanAdminController : Controller


    public void Index()


    ClanDataContext db = new ClanDataContext();List people = (from p in db.Peoples select p).ToList();

    RenderView("Index", people);}

    public void EditPerson(int peopleId)


    RenderView("EditPerson", peopleId);




    That’s the full Controller class. Index is the action we’ve already mentioned. It uses the linq entity class to get a list of all of the people. It passes it to the view via the second parameter of the RenderView method.

    The first parameter is the name of the view. So far, it seems that view names may commonly match action names, at least for navigation purposes. We have an action called index that loads a view called index.

    The second action, EditPerson, comes into play later.

    Index View

    Thew view page goes into VIEWS/CLANADMIN. VIEWS is a fixed name. CLANADMIN is the name of the controller. (What if you want multiple controllers to share the same view? Is that practical and/or possible?)

    By default, a view is just an ASPX page. However, you can change that behavior somewhere. (I’m not there yet).

    Here comes an inconvenience: When you add the MVC view page, it doesn’t give you the option to select a master page. You have to create the page, assign the master page, and lop off the junk you no longer need.

    If you look back to the controller code, you’ll see that the second parameter of RenderView is a List. That parameter is called ViewData, and it may be anything type you desire. In order to make the view page aware of it, you must make it a generic of that type.

    Controller / Action Method

    ClanDataContext db = new ClanDataContext();List people = (from p in db.Peoples select p).ToList();

    RenderView("Index", people);}

    NOTE: Will create a partial class to add a new method to ClanDataContext: GetPeople();

    View Page

    public partial class Index : ViewPage<list>



    Now, the ViewData property is a List.

    Lets recap. The controller retrieves the list of people and passes it to the view. Now, to keep the name VIEW honest, we must display the data.

    It looks like that while the use of ServerControls is allowed, its not encouraged. Instinct is to drop a DataGrid on the page and bind it to the list. But, all examples so far just use inline code. We end up with smaller more precise html, but we lose the flexibility of the grid control. I’m eager to see how that pans out. (I tried creating a Table object manually in code behind, but the namespace isn’t included by default. I took that as a hint to not use it. For now, I’m keeping it simple. Its my first night.)

    I went with the flow and added inline code to the ASPX page.


    I really don’t like that. It brings me back to ASP. The difference, though, is that it’ll all be compiled so you don’t have context switching like ASP, but its still ugly. Not very OOPish, is it?

    I had a problem with Html.ActionLink. The second parameter is the action. Nothing I’ve found via google shows that parameter being specified. The anonymous third parameter becomes a RouteValueCollection. All samples I’ve seen specify the action there. But, when I do it, I get a method overload exception. Its trying to use the (string, string) signature, which is invalid. (This may be due to the fact that I’m using Preview 2. Most samples are Preview 1).

    Edit View

    Now that we have a list of people from the database, I’d like to edit a person. Notice the Html.ActionLink in the last cell. It creates a link with text of “test”. It’d be better to use p.LastName or something, but its junk code. The second parameter is the name of the action. Earlier, we saw the action defined in the controller class as shown:

    public void EditPerson(int peopleId)


    RenderView(“EditPerson”, peopleId);


    That’s not how it will end up looking. The second parameter should be a PEOPLE object, but for now, I’m just passing the PEOPLEID to show that it works.

    To support this, I created a second view called Edit, then simply print the ID (stored in ViewData)

    Class: public partial class EditPerson : ViewPage

    ASPX Code:


    Super. But, as I expected, it didn’t work. The existing routes all treat ID as string, so there was a data type problem. I added a new route.

    routes.Add(new Route(“{controller}/{action}/{peopleId}”, new MvcRouteHandler())


    Defaults = new RouteValueDictionary(new { peopleId = -1 })


    I thought that would work, but it didn’t. Then I remembered that the first matching route found wins. So, I moved that route to the top of the list, and now it wins. (I’m not 100% convinced this is all correct yet. This new route may be getting picked up in other scenarios when it shouldn’t be. We’ll see.)

    Now, when you click the TEST link, it goes to the EDIT page and displays the PeopleId.

    The URL to the edit page is:


    The page then shows the #3.

    Because nothing is implemented yet, you can change 3 to any integer that you want, and it will display.

    That’s it for Tonight

    The next steps are:

    – Use Linq to retrieve the people object. (Create the partial class mentioned earlier, and add a GetPerson(int peopleId)

    – Create the edit page. MVC.NET has some stuff to make this easier. I need to learn the details.

    – Figure out how to save. When saving, do I have to load the People object, change values, then save? Does MVC have a standard approach to this? (The People object will most likely be persisted somewhere. I don’t know if that’s my task, or something MVC helps with. If we do the reload/modify/save, then we lose Linq’s conflict resolution capabilities. We want to keep that.)


    Leave a Reply

    Please log in using one of these methods to post your comment: Logo

    You are commenting using your account. Log Out /  Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )


    Connecting to %s

    %d bloggers like this: