SubSonic of the Day - The Query tool
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.
