Mike van der Meulen's Blog

Today I was struggling to get a fairly simple query to work in my current project. While I was using LINQ to SQL for this portion, in other parts I use my own home-grown model to SQL translation, based on stored procedures, generated from the model's DataAnnotations. LINQ to SQL gave me a bit of a problem with the last grid display before the project was completed.

I had a fairly benign query involving 4 tables (all inner join), using group by, order by and some aggregate operators (mainly Count()). While I had no problem expressing this with LINQ, introducing an anonymous type here or there, the runtime really was not all that good (well, that's being polite - in fact I was cursing quite a bit).

Remembering Mitch Labrador's BigfootSQL that he presented at one of the recent ODUG meetings, I started to investigate it as I always was partial to the approach embodied by BigfootSQL. Rather than being yet another API layer, BigfootSQL is nothing more than a set of "convenience functions" giving direct access to the SQL query. While this description really doesn't do its usefulness justice, it handles some of the "administrative" tasks for you, like parameter handling, and it can hand you a full set of objects, retrieved through a query, while you (the developer) have complete control over the query.

Here is a small example of a query that I'm using. BigfootSQL  even has support for paged record retrieval. As I was debugging my application (and learning BigfootSQL at the same time), it became obvious that this is the way to go, retaining complete control over the generated SQL, enhanced with some handy helper functions, like ExecuteCollection() in this example, that reads the requested records and returns a list of objects (List<UserSiteSpellingError> in this example).

public static List<UserSiteSpellingError> GetSpellingErrors(out int totalRecs, long reportID, 
                     ref int currPage, int pageSize = 20, 
                     string sortField = null, string sortOrder = null) {
           
  DefaultSortAttribute.GetSortOrder<UserSiteSpellingError>(ref sortField, ref sortOrder);

  BigfootSQL.SqlHelper DB = new BigfootSQL.SqlHelper(YourConnectionString);
  DB.Clear();
  List<UserSiteSpellingError> list =
    DB.SELECTPAGED("ReportErrors.Data1 As Word, Count(*) As Count, 2 As MessageType", 
         sortField + " " + sortOrder)
      .FROM("UserReportErrors")
      .INNERJOIN("ReportErrors ON UserReportErrors._ID = ReportErrors._ID")
      .INNERJOIN("Messages ON ReportErrors.MessageID = Messages.MessageID")
      .INNERJOIN("ReportItems ON ReportErrors.ReportItemID = ReportItems.ReportItemID")
      .WHERE("Messages.MessageKind", 2)
        .AND("UserReportErrors.Suppressed", 0)
        .AND("ReportItems.ReportID", reportID)
      .GROUPBY("ReportErrors.Data1")
      .PAGE(currPage - 1, pageSize)
      .ExecuteCollection<UserSiteSpellingError>();
    totalRecs = DB.GetParamValue<int>("SELECTPAGED_TOTALRECORDS");
    currPage = DB.GetParamValue<int>("SELECTPAGED_PAGE") + 1;
    return list;
}

I added some functionality to BigfootSQL (and fixed a thing here and there as the source code seems a bit behind the dll?). For example, my paging requires that I receive the total number of records and the page actually displayed (important as data is subject to outside changes). Since BigfootSQL includes the source code (thanks Mitch) it's easy to adapt it to your requirements. In this case, I added bi-directional support for parameters (like "SELECTPAGED_PAGE") so I can retrieve updated values after a query.

If you are like me and like to retain control, BigfootSQL may well be the way to go!