Building an N-Layer ASP.NET Application with LINQ, Lambdas and Stored Procedures (Updated)

Update:  I refactored some of the code and also did a better job ensuring Dispose() is called everywhere so that the DataContext object gets cleaned up properly.

Download the Application Here

.NET 3.5 has a lot of great new features that can significantly enhance developer productivity.  I've been spending some time lately working on a little sample application that demonstrates how an N-Layer ASP.NET 3.5 application can be built using LINQ, lambdas and LINQ with stored procedures.  The application is for a talk I'll be giving at DevConnections in April discussing how LINQ technologies can be used in an N-Layer architecture.  In a previous post comparing different LINQ options I mentioned that I'd be posting the code download as soon as it was ready. 

The application provides a presentation layer, business layer, data layer and model layer through separate projects as shown next:

image

It also demonstrates how the new ListView control can be used to display data, perform insert, update and delete operations and nest other controls such as the GridView.  Databinding on the presentation layer is mainly done using the ObjectDataSource control.

Application Example

All of the queries performed in the application go against an object model created using the Visual Studio 2008 LINQ to SQL Designer. 

Note:  The included Northwind SQL Express database has been modified slightly to add a TimeStamp field into the Customer and Orders tables and contains several custom stored procedures (sprocs aren't required unless using that portion of the application).  Adding TimeStamp fields simplifies updates so be aware that if you change the connection string to point to a standard Northwind database you'll get an error since the TimeStamp fields will be missing.


3 Options for Data Access

Rather than focusing solely on LINQ, I wanted to show different options for data access that .NET 3.5 offers so that developers can get a feel for what's available in addition to standard LINQ queries that seem to get most of the attention these days.  I ended up creating six main data layer classes as shown next:

Customer Query Classes:

  • CustomerDBLINQ - Executes customer related queries using inline LINQ
  • CustomerDBLambda - Executes customer related queries using lambda expressions
  • CustomerDBSprocs - Executes customer related queries using stored procedures and LINQ

Order Query Classes:

  • OrderDBLINQ - Executes order related queries using inline LINQ
  • OrderDBLambda - Executes order related queries using lambda expressions
  • OrderDBSprocs - Executes order related queries using stored procedures and LINQ

I still lean toward using stored procedures due to the security and maintenance benefits they offer in more enterprise environments, but for small queries I actually prefer lambda expressions over LINQ (not sure why...just feels more object oriented I guess).  If you currently use stored procedures in your applications and haven't checked out the new LINQ to SQL Designer you'll be impressed with how easy it is to call stored procedures and pass parameters.  You never have to see or create another SqlCommand or SqlParameter object again (well...in many cases anyway).


Switching Between Data Access Classes

By changing a value in web.config you can switch between the different data layer classes and see which option you prefer (LINQ, lambdas or LINQ with sprocs).  All of the data access classes perform the same overall tasks, they just use different techniques to do it.

<appSettings>
  <!-- 
    Used to define which DB layer class should be loaded and used. 
    Valid customer values include:  Data.CustomerDBSprocs, Data.CustomerDBLINQ, Data.CustomerDBLambda
    Valid order values include: Data.OrderDBSprocs, Data.OrderDBLINQ, Data.OrderDBLambda
  -->
  <add key="CustomerDBType" value="Data.CustomerDBLINQ" />
  <add key="OrderDBType" value="Data.OrderDBLINQ" />
  <!-- When the following key is set to "true" ensure that 
       EnablePartialRendering is set to false on the Default.aspx ScriptManager control -->
  <add key="EnableDataContextLogging" value="false" />
</appSettings>

Over the next few weeks I'm hoping to make some time to walk through the application pieces.  I may create some video tutorials about it as well....we'll see how time goes.

comments powered by Disqus

24 Comments

  • I changed the attached code to point to my version of northwind which hasnt changed and the code will break due to your attached version of the Northwind database having extra columns (DateTimeStamp) on a couple of the tables - just a note incase anyone tries to change the connection string as I did and wonders why the code compiles but wont run.

    This leads me to the thought that if someone changes a table definition within SQL Server you might not realise that the code wont run unless you had to check the pages - at least with stored procs youd get a compile error ( at least I imagien you would as I havent tried it yet).

  • Gregor,

    Thanks for mentioning that...I updated the post to note that I did add TimeStamp fields into the Customers and Orders tables.

    For those wondering why, it's something I typically do for tables anyway and is recommended for versioning. Ultimately it simplifies updates done through LINQ (or normal queries) since the SQL can use the TimeStamp field in the WHERE class to see if any concurrency issues have occurred.

  • One last thing - I changed it to use stored procs and had to add them in from your attached db also - once thats done it worked fine.

    Great sample and one that I will play around a bit with.

    Thanks again
    Gregor

  • Thanks Gregor. I updated the post to mention the custom sprocs as well since I can see people switching to their own Northwind database like you did. Thanks for taking the time to mention that.

  • Id be interested in seeing an article comparing a typical data layer vs a LINQ data layer epsecially about the performance and time take to write each?

    Right now we are looking at using LINQ to aid rapid development but we can't see anyone comparing the two approaches

    Thanks

  • Hello sorry for my english. I am brazilian :)
    I try create n-tier linq sample, but this error ocours in Attach method.An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy. I find in your code this method:
    public static CustomerDBBase Create()
    {
    string typeString = ConfigurationManager.AppSettings["CustomerDBType"];
    Type dbType = Type.GetType(typeString);
    return (CustomerDBBase)Activator.CreateInstance(dbType);
    }
    This resolve the issue?

    Thankx.

  • Hi Marcio,

    I'm not sure if you're seeing this error in the sample app or in a sample you personally created, but it's typically caused by not having a TimeStamp field defined in your LINQ to SQL classes. Looking at the sample application you'll see that a TimeStamp field is defined (named DateTimeStamp) for objects that can be updated. I had to modify the normal Northwind database somewhat to do this. If you don't use a TimeStamp in each object that is updated then the application has to track all of the original values (which can be done of course) and then use them in the Update statement's WHERE clause. Hope that helps.

    P.S. You're English is a lot better then my Portuguese. :-)

  • heuheue my english is english of Master Yoda heuehue.

    I create de column TimeStamp in database em drag table in datacontext, now show other error: Row not found or changed. :)

    Thank you for attention.

  • Yoda's good he is.&nbsp;:-)
    I haven't seen that specific error unfortunately. &nbsp;I'd compare what you have to the sample code in the application posted here to make sure things match up. &nbsp;I'm guessing you've already done that...but it's hard to say why you're seeing that based on what I've heard to this point.

  • Dan how come you are not disposing of the DataContext in a using block every time you use it? I have noticed in multiple examples that this is done.

  • Darren,

    Short answer...I should've been but was lazy. I updated the app though since it really should be in there. :-) Thanks for commenting.

  • Maybe a short screencast on this would great, if you have the time.

  • Maulik,

    Good suggestion....but, since the modified database is included in the download I'll leave that as an exercise for the reader. :-)

  • Tyrone,

    I'll see if I can create a video tutorial on that topic...just need more hours in the day. :-)

  • I enjoy your video tutorials. I'd buy a series on linq or silverlight 2 on disk or delivered via Internet. Thanks for the previous screencasts.

  • Hello!

    I've had a look through the sample app, and you've created a custom object 'OrderDescription' by adding it in the LINQ to SQL designer. Is this generally the accepted way to create and use custom objects with LINQ?

  • Marty,

    You could create the object from scratch of course, but since we can create custom objects directly on the designer and assign the desired namespace I went that route. Either way you do it you'll get the same class in the end so it's really personal preference.

  • Brilliant - thanks. Your sample app was very helpful by the way!

  • One more question! If I wanted to return the custom object from more than one method (eg return OrderDescription from GetOrderDetails, and return a list of them from GetAllOrderDetails, or return a list of OrderDescriptions for a user's orders) would I have to duplicate the select part of the query that assigns values to the object for all places I wanted to return the object? Would I be better off in this case creating the object as a view in the database and adding the view through the designer?

  • Marty,

    Good question...I think having a method that returns the custom objects (but leaves the query dynamic) would be good because related methods could call the base method and then add additional filters, sorts, etc. But, having a View is a nice idea in that case since the view could handle filtering out the unwanted fields. I personally lean toward doing as much in the database as possible (where appropriate of course) which is why I also added sprocs into the sample app. It's really your call though.

  • Hi Dan,
    When I open the solution in VWD 2008 express, projects Biz, Data, and Model fail to get loaded. I get the following error message:
    "The project system components are not installed on this computer. Please re-install Visual Studio."
    Any reason why I am getting this message!

  • KC,
    I haven't tried WWD 2008 Express unfortunately (haven't tried the 2005 version in years either) so I'm not sure what it's capable of handling as far as projects go. &nbsp;If you find that it can't load the multiple projects you could copy the .cs files into the App_Code folder of the Website and that shouldn't give it a problem since I know it's definitely capable of working with that type of setup.&nbsp;
    I did a quick Google search on the error message and found the following as well which makes it sound like you may just need to copy the various project .cs files into the Website's App_Code folder:
    &nbsp;http://www.cuyahoga-project.org/13/view.aspx?g=posts&amp;t=912

  • Anyone using n-layer architecture w/ typed datasets & custom business objects? Could you provide examples or point me to resources that correctly utilize this architecture?

  • I posted an example of using typed DataSets in an N-layer situation at the following URL awhile back if you're interested:

    http://weblogs.asp.net/dwahlin/archive/2006/04/06/442115.aspx

Comments have been disabled for this content.