Posted by: Granville Barnett, on 18 Apr 2007 | View original | Bookmarked: 0 time(s)

Every example of using LINQ I have seen (including my own articles!) have used inline queries based upon the standard query operators that ship as part of C# 3.0, to be more exact .NET 3.5.  While I have used this approach simply for convenience i.e. I couldn't be bothered writing more code than needed just to slap something on my blog, however, I have noticed that this ad-hoc query composition seems to of found itself being used everywhere!  What are you people doing?!  My preference is to define sprocs at the db layer then call those using LINQ from my DAL - by composing ad-hoc queries in your code you run the enormous risk of allowing those who aren't SQL profecient to generate their own SQL, and let's be honest the SQL generated by the DataContext will not be the best in many a scenario!

Solution:  Get your SQL guys to code up your sprocs.  Why?  Inefficient SQL code (T-SQL in this case) is a show stopper, I hear about cases on a weekly basis!  Don't be silly and just treat the DataContext as a black box and take the attitude of those who use designers for everything - yes, the SQL it generates is fairly decent for basic scenarios but when doing anything half complex code your own sprocs up!  Remember - calling sprocs using .NET 3.5 is as simple as creating your own ad-hoc queries.  OK - so you need to know some SQL, and?  I cannot emphasise this enough, people get paid a lot of money for coding up efficient SQL just like you would do any other language (well not java :-)), theres a good reason for this - it saves cash in the long run!!!

Now the rant is over...

I'm going to create a simple application that just drags back a list of book titles (thats it!) just to show you how easy it is to create a 3 tier application using LINQ.  I'm not going to go through the whole CRUD ops, as its the same way you would go about it (logically) before (.NET 2.0) - you simply call a sproc from your DAL, throw in your params, execute the sproc and bang!  Exactly the same here...but we don't have to compose our DAL methods using several different types (SqlConnection, SqlCommand, SqlDataReader) and then perform the OR mapping manually and have left something which isn't flexible i.e. not having the ability to query objects and compose new ones from queries (ah thank you anonymous types!!).

I'm using ASP.NET for this demo - I'll use the ObjectDataSource also (I will show you guys an advanced way to support sorting etc later in the future).

In this sample code I'm not going to define any business logic (the comment says it all), and the classes will be locted in the App_Code folder of my ASP.NET app - in a real world solution you would componentiz these both into seperate class libraries (dll's).

With that said...

Sproc:

ALTER procedure GetBooks
as
select BookID, PublisherID, Title 
from Books 
order by Title

DAL method:

[System.Diagnostics.DebuggerNonUserCodeAttribute()]
[System.Data.Linq.StoredProcedure(Name="dbo.GetBooks")]
public global::System.Collections.Generic.IEnumerable<Book> GetBooks() {
    System.Data.Linq.Provider.IQueryResults<Book> result = this.ExecuteMethodCall<Book>(this, ((System.Reflection.MethodInfo)
(System.Reflection.MethodInfo.GetCurrentMethod())));
    return ((global::System.Collections.Generic.IEnumerable<Book>)(result));
}

BLL method:

using System;
using System.Collections.Generic;
using System.Linq;

public static class BooksBll
{

    public static List<Book> GetBooks()
    {
        // bll logic here
        using (BookShopDataContext db = new BookShopDataContext())
        {
            return (from b in db.GetBooks() select b).ToList<Book>();
        }
    }

}

ASP.NET:

<asp:GridView ID="gv" runat="server" DataSourceID="odsBooks" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="BookID" HeaderText="BookID" />
        <asp:BoundField DataField="Title" HeaderText="Title" />
    </Columns>
</asp:GridView>

<asp:ObjectDataSource ID="odsBooks" runat="server" TypeName="BooksBll" SelectMethod="GetBooks" />

Results in:

Keep an eye out for future parts of my Introduction to LINQ series over at DotNetSlackers for more on this!

Category: XLinq | Other Posts: View all posts by this blogger | Report as irrelevant | View bloggers stats | Views: 7387 | Hits: 157