Zack Owens

I was recently told by a client of mine that the ASP.NET app I developed was WAY TOO SLOW! I had to agree; the site was pinging the database twice on every load of the home page. So I said, "Give me a week... I'll make it work better". I went home feeling bad... my app was slow and I really didn't know where to begin. I had a lot of code that depended on pinging the database and I didn't want to sift through it all. What I ended up doing was using the cache object.

I started to look at other open source projects and their approaches to caching. Than DotNetKicks' cache manager caught my eye! I went off that idea and created my own way of implementing an object to interface with the cache object, making it more manageable.

The Cache manager I wrote has 3 methods (Grab, insert, clear), a constructor and 2 properties (CacheKey, CacheDuration). So here's my code in VB and C#:

using System.Web;
using System.Web.Caching;
namespace MainSite.Cache
{
  public class CacheManager<T>
  {
    private string m_cachekey = "";
    public string CacheKey {
      get { return m_cachekey; }
      set { m_cachekey = value; }
    }

    private int m_cacheduration;
    public int CacheDuration {
      get { return m_cacheduration; }
      set { m_cacheduration = value; }
    }

    public CacheManager(string Key, int duration)
    {
      this.CacheKey = Key;
      this.CacheDuration = duration;
    }

    public T Grab()
    {
      return (T)HttpContext.Current.Cache(this.CacheKey);
    }

    public void Insert(T obj, System.Web.Caching.CacheItemPriority priority)
    {
      DateTime expiration = DateTime.Now.AddMinutes(this.CacheDuration);
      HttpContext.Current.Cache.Add(this.CacheKey, obj, null, expiration, TimeSpan.Zero, priority, null);
    }

    public void Clear()
    {
      HttpContext.Current.Cache.Remove(this.CacheKey);
    }

  }
}

Imports System.Web
Imports System.Web.Caching

Namespace MainSite.Cache
    Public Class CacheManager(Of T)

        Private m_cachekey As String = ""
        Public Property CacheKey() As String
            Get
                Return m_cachekey
            End Get
            Set(ByVal value As String)
                m_cachekey = value
            End Set
        End Property

        Private m_cacheduration As Integer
        Public Property CacheDuration() As Integer
            Get
                Return m_cacheduration
            End Get
            Set(ByVal value As Integer)
                m_cacheduration = value
            End Set
        End Property

        Public Sub New(ByVal Key As String, ByVal duration As Integer)
            CacheKey() = Key
            CacheDuration() = duration
        End Sub

        Public Function Grab() As T
            Return CType(HttpContext.Current.Cache(CacheKey()), T)
        End Function

        Public Sub Insert(ByVal obj As T, ByVal priority As System.Web.Caching.CacheItemPriority)
            Dim expiration As DateTime = DateTime.Now.AddMinutes(CacheDuration())
            HttpContext.Current.Cache.Add(CacheKey(), obj, Nothing, expiration, TimeSpan.Zero, priority, Nothing)
        End Sub

        Public Sub Clear()
            HttpContext.Current.Cache.Remove(CacheKey())
        End Sub

    End Class
End Namespace

So what I do is create a class and have a grab method that makes an instance of the cache manager object and calls the cache manager class' grab method. If the cache returns null, I have a private method in my class that does the nesessary things to put the info into the cache object. Here's an example:

namespace MainSite.Cache
{
  public class ReviewsCache
  {
    public static ReviewsCollection Grab()
    {
      CacheManager<ReviewsCollection> man = new CacheManager<ReviewsCollection>(GetKey(), 90);

      ReviewsCollection cont = man.Grab();

      if (cont == null) cont = Insert(man); 

      return cont;
    }

    private static ReviewsCollection Insert(CacheManager<ReviewsCollection> man)
    {
      ReviewsCollection cont = MainSite.Logic.Reviews.GetAll();
      man.Insert(cont, Web.Caching.CacheItemPriority.Default);
      return cont;
    }

    public static void Delete(string sectionname)
    {
      CacheManager<ReviewsCollection> man = new CacheManager<ReviewsCollection>(GetKey(), 90);
      man.Clear();
    }

    private static string GetKey()
    {
      return "Reviews";
    }

  }
}

Namespace MainSite.Cache
    Public Class ReviewsCache

        Public Shared Function Grab() As ReviewsCollection
            Dim man As New CacheManager(Of ReviewsCollection)(GetKey(), 90)

            Dim cont As ReviewsCollection = man.Grab()

            If cont Is Nothing Then cont = Insert(man)

            Return cont
        End Function

        Private Shared Function Insert(ByVal man As CacheManager(Of ReviewsCollection)) As ReviewsCollection
            Dim cont As ReviewsCollection = MainSite.Logic.Reviews.GetAll()
            man.Insert(cont, Web.Caching.CacheItemPriority.Default)
            Return cont
        End Function

        Public Shared Sub Delete(ByVal sectionname As String)
            Dim man As New CacheManager(Of ReviewsCollection)(GetKey(), 90)
            man.Clear()
        End Sub

        Private Shared Function GetKey() As String
            Return "Reviews"
        End Function

    End Class
End Namespace

The cache object is a great thing to utilize in ASP.NET. But to make the caching manageable, you need to have a structure for managing the cache object and add logic to delete, grab, etc. Interfacing with a class is easier and follows the MVC pattern better than just saying Cache["MyKey"] in your logic or UI.

One of the most common things I see on the SubSonic forums is people asking about the Membership and Role providers running SubSonic. And another common thing I see are people saying they are going to write those providers... but it doesn't happen. So, I did it :) I started writing the Membership and Role providers a few days ago... and I finally finished coding the providers today. And I'm ready to publish!

I chose to do this project on Google Code (http://code.google.com/p/subsonicproviders/).

So I am announcing Beta 1! The providers should just work (requires a little config). I think we should incorporate some feedback from the SubSonic user-base before calling it "Version 1".

The one thing I am EXTREAMLY proud of is that we are going to ALWAYS put out the code in C# AND VB. I am a VB dev at heart that is starting to fall in love with C#! (I'm taking the BIG step in developing the SubSonic ASP.NET providers in C#!)

Hop on over to the Google Code site for the beta download. Don't forget to report your feedback!!!!!

(Send me an email at zowens@eagleenvision.net if you would like to contribute. SubSonic guys would be a plus :)   )

By now, you have probably heard of LINQ. LINQ, as you probably know, stands for Language Integrated Query. The LINQ queries you build inside of C# or VB are baked into the .NET 3.5 framework.

SubSonic takes a more traditional approach (right now) and uses the Query as an object. Instead of doing weird things like var myQ = from.... and Dim myQ = Select..... you create an object and manipulate a query rather than have a language do the querying for you.

Query Types

So to understand the query object, one of the first things you need to understand is the four types of SQL queries: select, update, delete

and insert. Each do different things. Select pulls out rows from a table. Update updates the rows in a table. Delete deletes the row (or rows) in a table. Insert adds rows to a table. See... that wasn't THAT hard :)

Make the Query!

OK... you've learned enough... now let's get to some Query action!

'VB

Dim qry as New Query(Tables.Product)

qry.QueryType = QueryType.Select

qry.AddWhere(Product.Columns.UnitPrice, Comparison.GreaterOrEquals, 5)

Dim myReader as IDataReader = qry.ExecuteReader()

'READER MAGIC

myReader.Close() 'YOU NEED TO ALWAYS CLOSE THE READERS!

//C#

Query qry = new Query(Tables.Product);

qry.QueryType = QueryType.Select;

qry.AddWhere(Product.Columns.UnitPrice, Comparison.GreaterOrEquals, 5);

IDataReader myReader = qry.ExecuteReader();

//READER MAGIC

myReader.Close(); //YOU NEED TO ALWAYS CLOSE THE READERS!

The query above is an example of Select Query qry grabbing the records from the Products table where the unit price is greater than or equal to 5. It then creates an IDataReader (myReader) that holds the executed Query.

Where

So the example above shows off an EXTREMELY useful feature: the Where. Where (in SQL) is good to use to filter out records you don't need. I'm sure you can think of MANY examples. There are really ENDLESS possibilities when it comes to where's in the SubSonic Query.

Top

Say you only want the top 5 records, just append this to the query above (before the myReader declaration):

'VB

qry.Top = "5"

//C#

qry.Top = "5";

You can also use percentages:

'VB

qry.Top = "5%"

//C#

qry.Top = "5%";

This is similar to the Paging feature (to be detailed later in this post), but this is quicker and has a lower execution time as compared to Server Side paging.

OrderBy

Another EXCELLENT feature of the SubSonic query tool is the OrderBy property. Here is an example. Just add this before the myReader declaration.

'VB

qry.OrderBy = OrderBy.Asc(Product.Columns.ProductName)

//C#

qry.OrderBy = OrderBy.Asc(Product.Columns.ProductName);

That OrderBy goes ascending, but you can also do the same thing using descending by substituting Desc for Asc.

So think of OrderBy as a "sort" mechanism.

Select List

To keep your execution times to a minimum, you should ONLY grab the necessary columns (unless you are working with a Collection). Once again, I will build off my example above and add a SelectList property to the query BEFORE it is executed.

'VB

qry.SelectList = Product.Columns.ProductName & "," & Product.Columns.ID

//C#

qry.SelectList = Product.Columns.ProductName + "," + Product.Columns.ID;

So you should format the SelectList to have the column name and append a comma without a space between column names.

This will DRAMATICALLY decrease your query execution times if you only need a few columns.

Add Update Setting

If you are going to go ahead and execute an update query, you will want to add 1 or more AddUpdateSetting properties to your query. This will tell your database what the update and what to update it to.

The "Others"

You can also do a AND, OR, BETWEEN, BETWEEN_AND, BETWEEN_OR, IN, and DISTINCT to your query... but I'm not really a typical user of those functions... I need to play with those a little more and tell you about them later ;)

Server-Side paging

Paging is a great way to decrease query execution times if you have a table with a fair number of records. What Server-Side paging does is only grabs a number of records with the specified page size and page number. It then returns the rows that fit that criteria. Here is an example of paging in action:

'VB

Dim q as new Query(Tables.Product)

q.AddWhere(Product.Columns.UnitPrice, Comparison.GreaterOrEquals, 5)

q.PageIndex = 2

q.PageSize = 10

Dim rdr as IDataReader = q.ExecuteReader()

'READER MAGIC

rdr.Close() 'YOU NEED TO ALWAYS CLOSE THE READERS!

//C#

Query q = new Query(Tables.Product);

q.AddWhere(Product.Columns.UnitPrice, Comparison.GreaterOfEquals, 5);

q.PageIndex = 2;

q.PageSide = 10;

IDataReader rdr = q.ExecuteReader();

//READER MAGIC

rdr.Close(); //YOU NEED TO ALWAYS CLOSE THE READERS!

So the example above executes a reader with the page size of 10 records and page 2.

Although the PageIndex property says "Index", it isn't really an index... page 1 is really 1 for the PageIndex property of the query.

The Getters (OK... they're called Aggregates)

A "getter" (as I call them) in the context of SubSonic are basically methods that call the database for SOMETHING. There are a few that I use most often: GetAverage(), GetRecordCount(), GetMax(), GetMin(), and GetSum(). All of these really do what they mean, they get the count or whatever the method does. The most frequent one that I use is GetRecordCount() since it directly reflects the things I put into the query above the execution of the GetRecordCount() function, which returns an integer.

What time is it? Execution time!

There are 4 main methods of executing a query (or going out and fetching data).

ExecuteReader()

Returns data in a System.Data.IDataReader. REMEMBER: ALWAYS CLOSE YOUR READERS!

Execute()

Just does a "one-way" with the database. You would usually do this if you are updating or inserting.

ExecuteDataSet()

Returns data in System.Data.DataSet format

ExecuteScalar()

Return an object... which you must cast to get into the format you want. You would use this if you want to get a single value from a single record.

All of these do different things... so it basically does anything you want.

Use with a Collection

A really cool thing about SubSonic is that everything works together. I mentioned that you can use a query to load up a collection. Here is how you do it:

'VB

Dim qry as new Query(Tables.Product)

'DO YOUR QUERY MAGIC

Dim col as ProductCollection = new ProductCollection().LoadAndCloseReader(qry.ExecuteReader())

//C#

Query qry = new Query(Tables.Product);

//DO YOUR QUERY MAGIC

ProductCollection col = new ProductCollection().LoadAndCloseReader(qry.ExecuteReader());

What you HAVE to remember is that you CANNOT CHANGE THE COLUMNS IN THE SELECT LIST OF THE QUERY. If you do, you will see an error that says that the column that the collection is looking for doesn't exist in the reader.

The coolest feature of all

The absolute coolest feature of the Query tool is the Inspect() method. What is does is executes the query and outputs it into HTML. The HTML the Inspect method generates gives you the SQL, the execution time, parameters, and the data. It spits out a string, so put a label onto an ASP.NET page and set the label's text property to be qry.Inspect().

Food for Thought

There are just a few things you need to know when it comes to where and where NOT to use a query.

If you can use a collection that loads records instead of executing a query USE THE COLLECTION AND DITCH THE QUERY. It looks better if you have all your code on 1 or 2 lines rather than 7. Plus the Collection is typed and the query isn't. Grabbing column values from IDataReaders is not fun:

'VB

Dim rdr as IDataReader = qry.ExecuteReader()

Dim myGuid as Guid = rdr.GetGuid("myColumnName")

//C#

IDataReader rdr = qry.ExecuteReader();

Guid myGuid = rdr.GetGuid("myColumnName");

Yeah... ouch! It's best to stick with the collection on this one! It does the work for you.

I usually use the generated objects for updates, deletes, and inserts... they tend to be easier to use since I am using the properties of the generated objects and not executing an ad-hoc query. That's my preference, but both ways work!

I know for a FACT that I am missing a few feature the query tool offers, such as the parser for Where's (you can do a sort of "inline SQL" thing and do a .AddWhere("UnitPrice > 5") thing). But these are basically my favorites and the ones that I think most mainstream users of SubSonic will enjoy.