Published: 16 Oct 2009
By: Andrew Siemer

In this article series we are going to discuss some architectural concepts.

Contents [hide]

The Stack Overflow Inspired Knowledge Exchange Series

  • TOC Checkout the project homepage of this series to follow our journey from the creation of the famous StackOverFlow website.
  • Introduction

    In the last article we took a look at some team collaboration aspects of SketchFlow in the form of getting feedback from the users of our prototype. Then we built a reusable component for our header and navigation controls that all of the screens in our prototype project could use. Once we had a header through out our entire project we then implemented a navigation in our top and primary navigation buttons. Then we created the guts of a couple of pages to show how easy it was to quickly mock up a page. Finally we discussed managing the various states of a page with SketchFlow's state management tools.

    With our prototype concepts demonstrated we can start to build our application while discussing some architectural concepts. In this article series we are going to discuss some architectural concepts. The plan is to start off creating the most simple project possible - a web page that dips directly into the database to get a list of questions for our Knowledge Exchange. Once we have that working we will walk through several refactorings of our code to slowly get it to a point that it is ultimately flexible and scalable. With each type of refactoring we will discuss the pros and cons involved with that decision. By the end of this mini-series we should have a very strong, flexible, and scalable baseline to start building our Knowledge Exchange on top of. More important you will know what someone means when they talk about a tiered application and be able to pick apart the various forms of this very generic term!

    A simple ASP.NET MVC application

    We have all seen the demos performed in the various web casts and conferences. You know the one where someone opens up a page and steps through some fancy wizard style interfaces to create their page. Before long they hit F5 and show you some data on the web page. WOW...that was magic. But how realistic is this scenario? Generally speaking I suggest that you don't build anything this way. It has so many problems with it (that we will discuss in a second). Let's walk through the creation of this type of development real quick.

    To get started we first need to define what it is that we plan to build. Since we are working towards building a Knowledge Exchange lets do the most basic thing that a Knowledge Exchange does. When you first go to what do you see? A list of questions. Let's focus on this most basic concept by creating a table that will hold the QuestionID, Title, Body, CreateDate, etc.

    Do we need a Questions table or a Posts table?

    Actually, so that we don't get to far off track in this discussion we will create a Posts table as questions and answers have very similar structures. The only real difference is that a question is the parent and the answer is not. Other than that they have owners, revision histories, etc.

    Getting the database up and running

    As we are not yet really worried about anything other than posts we will create a simple posts schema that will only care about questions at this point.

    Listing 1: Posts table

    Create this table, then generate the create script for that table (right click, script table as, create..). Then save that script into a .sql file and name it 00001_CreatePosts.sql and save it into the db/updateScripts directory. This way when you run your local build the database will be built back up including this table.

    Now let's generate some quick test data. I like to use Excel or some other spreadsheet program to do this as it is very quick. Then right click on your table in SQL Server Management Studio and Script Table as Insert To. This will generate a quick insert statement for us. Quickly remove all the formatting (line breaks and tabs). Then plug in fake data for the actual insert portion of the statement. Then copy that statement to your spreadsheet. Then be sure to put the actual values of your insert statement into their own field. This means that the value is in a column, then another column with a comma, then the next value, etc. What this will allow us to do is quick generate sql insert statements with generated values for us. Hundreds of them if need be.

    Once you have your columns configured appropriately you can then go to the next column to create the concatenated sql insert statement. If you have the same number of columns a I do the function for the following field will look something like this:

    Ensure that your single quotes look right in the generated sql insert statement. You may have to use two single quotes to trick your spreadsheet into giving your one single quote. In my image above for column D the value is actually '',' which is then translated to ','. Now comes the magic. Select all of your cells to include the script generation field. Then in the lower right corner of the last selected column you should see a dark black square. Select that and drag it down to repeat the selection. Notice that your numbers and dates are incrementing for you. We now have a bunch of insert statements scripted out for us. And to make changes to all of them just go back to the first line and reformat it then regenerate all the scripts again. This is not an exact may need to weak your formula a bit to get the data just the way you want it! Also, for large bodies of text you can use to generate text in various ways.

    Add your generated insert statements to a new 00002_GenerateDataForPosts.sql and toss all your insert statements in there.

    LINQ to SQL

    The next thing we need to do to spit data out onto our web page is add a LINQ to SQL file to our Knowledge Exchange project. Open up your Knowledge Exchange solution (created in previous articles). Then add a new LINQ to SQL Classes item to the Models folder of your ASP.NET MVC web project. Name it KE.dbml (short of Knowledge Exchange). Click Add. Once the wizard that sits behind the addition of that file is completed you should be faced with a design surface.

    Does it matter what I name my LINQ to SQL Classes .dbml file?

    Good question! Yes. Whatever you name this file will by default be named {yourname}DataContext. So if you name your file KnowledgeExchangeDataContext then when you reference your data context in code it will actually come up as KnowledgeExchangeDataContextDataContext! Yes... For that reason I prefer to name it something fairly short such as KE in this case. This will translate to KEDataContext in code which is very workable for me.

    Now that we have the LINQ to SQL design surface open we need to go to the server explorer window (ctrl+alt+s). In there you will see a Data Connections section that looks like a database on top of a database. Expand that section and then right click on the Data Connections text and select Add Connection. Then enter the server name for your database and select the appropriate method for logging on to your server. If you are working locally this might look like server:localhost\sqlexpress and you should be able to use Windows Authentication. Then in the connect to a database section choose to select or enter a database name. If things are configured correctly you should be able to drop down the list and choose your database.

    My list doesn't show a KnowledgeExchange database?

    If you don't see the KnowledgeExchange database make sure that you read through the local build automation article which discusses initializing your system in an automated way. This includes installing and configuring your local database.

    Once you have selected the KnowledgeExchange database you can click the test connection button. If your test was successful then click ok and then ok again. You should now have a KnowledgeExchange database connection in your data connections section. Expand connection node. Then expand the tables node. You should see the Posts table we just created. Drag it to the design surface. Magic!

    If you haven't used LINQ to SQL yet you might wander what just happened The LINQ to SQL wizard just create KEDataContext class for you that has a Post class defined in it as well as a bunch of helper methods for you to use. With the KEDataContext in hand we can now create, read, update, and delete Post objects till our heart is content!

    Any gotchas when using LINQ to SQL?

    There are a few things that will get you when using LINQ to SQL which we will trip over as we build our KE. The most important thing to remember is that what is reflected in the LINQ to SQL designer is directly tied to your database structure. For this reason I like to name my tables using plural names. Some people frown on this naming style but here is why I do it. When you create a Posts table it is translated to a Post class. If you name your table Post it is not translated to a Post get something else. You can always change the name in the LINQ to SQL design surface. However, this is generated code. You should never change generated code if you can help it as each time that you re-generate your generated code you will have to remember to go in and make all the same changes you did initially. Also, if you are in a Posts table (which will be translated to a Post class) you want to be sure that you don't create a Posts.Post field in the table. This will work fine and read well on the database end. But when you drag the Posts table to the LINQ to SQL designer you will have a Post class with a Post1 property which is sort of ugly! We will address more later.

    Creating our first page

    Since we already have a default ASP.NET MVC project loaded up in our solution let's start tinkering on the home page. This is in the Views/Home directory and called Index.aspx. To spit out data on our home page all we need to do is open the controller for our home page (HomeController in controllers directory). In the Index action we can then specify our connection string, define a generic list of posts to hold the posts we get from the database, spin up an instance of our data context, perform a query, and then pass out our results to the view.

    Listing 2: Controllers/HomeController.cs

    To consume this data we need to open the Index view and add some script to iterate through the passed in data.

    Listing 3: Views/Home/Index.aspx

    With that completed we can then run our page and see our data.


    In this projects case it is hard to see any clear lines of dependency as everything is so directly coupled to one another. The UI talks directly to the database through a generated data access layer (DAL) totally bypassing the non-existent business layer (BL or BLL: business layer logic).


    As you might guess the biggest advantage to this style of coding (cowboy coding or duct tape programming) is that it is super fast! You literally design a table, update the data context, and query the data to the page. Done!


    As with anything that is this fast there are obvious down sides to this style of programming. Where to begin? The only thing that this way of programming does right is that it is quick to market. For this reason it is only really good for proof of concept as long as your proof of concept is neither big nor complex.

    Comparison Chart

    Throughout this series we will do our best to slowly tweak our code so that it complies with most if not all of the concepts in the following chart. Be aware that while all of these concepts are great in spirit there will become a point in time where your project may not require the added complexity or overhead that some of these ideas will inflict on your code and your team. This discussion will move us down the path towards code utopia and along the way we will see the pros and cons of each approach.

    Table 1: Comparison Chart

    Coding Concepts



    Fast/Easy to develop: Can we generate the end product quickly?


    Testable: Can we write tests around the majority of the projects code?


    Flexible for refactoring: Can we easily refactor the code base to add new concepts?


    Well abstracted: Do the users of your code only know what they need too?


    Well encapsulated: Can you change the internals of code without impacting the users of that code?


    Separation of concerns? Is your code well compartmentalized and only doing what it needs to?


    DRY? Does your code follow the "Don't repeat yourself motto?"


    Swappable? Can you swap out an entire layer down the road?


    Distributable? Can you push complex processing of logical areas to a separate server easily to offload computing cycles and scale?



    In this article we got some things ready for our upcoming article series. Part of this was to create a Posts table to hold our questions and answers down the road. We then added a Posts create script to our build process. Next we looked at how we could use a spreadsheet to generate some test data which we also added to our build process. Once all of the database tasks were complete we were then able to configure a LINQ to SQL datacontext in our Knowledge Exchange project. It was downhill from there. We were able to quickly add a query into our home page controller and force feed our view with a list of Post objects. With the home page displaying data we then took a look at the pros and cons of this approach. The majority of popular concepts were of course not met with this design approach!

    In the next article we will refactor this application to a slightly different design. We will see what it will take to push this code into a pseudo logical tiered environment. This means that we will put business logic into its own namespace and data access into its own namespace. This will help our code comply with more aspects of our coding concepts list. But it should also help bring to light some areas of our code that will still need further attention.

    <<  Previous Article Continue reading and see our next or previous articles Next Article >>

    About Andrew Siemer

    I am a 33 year old, ex-Army Ranger, father of 6, geeky software engineer that loves to code, teach, and write. In my spare time (ha!) I like playing with my 6 kids, horses, and various other animals.

    This author has published 29 articles on DotNetSlackers. View other articles or the complete profile here.

    Other articles in this category

    Code First Approach using Entity Framework 4.1, Inversion of Control, Unity Framework, Repository and Unit of Work Patterns, and MVC3 Razor View
    A detailed introduction about the code first approach using Entity Framework 4.1, Inversion of Contr...
    Exception Handling and .Net (A practical approach)
    Error Handling has always been crucial for an application in a number of ways. It may affect the exe...
    jQuery Mobile ListView
    In this article, we're going to look at what JQuery Mobile uses to represent lists, and how capable ...
    Book Review: SignalR: Real-time Application Development
    A book review of SignalR by Simone.
    JQuery Mobile Widgets Overview
    An overview of widgets in jQuery Mobile.

    You might also be interested in the following related blog posts

    Joel Spolsky: Indigo won't enable a new class of applications read more


    Subject Author Date
    placeholder Great Article Robert Williams 10/16/2009 3:46 PM
    RE: Great Article Andrew Siemer 10/16/2009 5:45 PM

    Please login to rate or to leave a comment.