Monday, September 10, 2007

Extending a SubSonic Generated Class

I kept running into this issue, so I finally wrote up a little theory code to see what was possible, and what might be an elegant solution.

The issue: I am returning one of my SubSonic objects through a stored procedure. Normally, pretty straight forward. My test object for this was called "TestTable", and represents the same-named table in the database.

Column Name Type
TestID int
TestName varchar(50)
TestValue int

So, Subsonic generates a class that has all the properties representing these columns, and all the useful ActiveRecord methods.

Now, I make a stored procedure to get elements of this type from the database - normally I'd skip using a stored procedure unless I needed to do some special filtering or business logic, but for the sake of testing / demonstration:

ALTER PROCEDURE spTest    
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT TestTable.*,
CAST((CASE WHEN TestTable.TestValue > 2 THEN 1 ELSE 0 END) AS BIT) AS GreaterThanTwo
FROM TestTable

END

If you look at the SP, you'll see where things start to get interesting. Not only am I returning all of the columns that exist in our Table (or this could be a View too, same theory applies), but I am also returning an extra BIT field. Here, I'm just setting it to true if the number is greater than 2, but you can use your imagination for how this might be useful. Examples might be returning true if an item is editable by the user viewing it or returning some sort of derived field (a SUM total, MIN, MAX, etc.). When Subsonic loads the DataReader object you pull from this SP in code, it will simply ignore the fact that the Reader has a "GreaterThanTwo" field -- Subsonic uses the Table schema only when pulling the columns from the reader.


So, let's add an extra property to our TestTable generated class, and then make sure that, if present, this information is pulled from the IDataReader.


 using System;

namespace Protos
{

public partial class TestTable
{
public bool GreaterThanTwo { get; set; }

public override void Load(System.Data.IDataReader rdr)
{
base.Load(rdr);
if (rdr.FieldCount > TestTable.Schema.Columns.Count)
{
try
{
GreaterThanTwo =
(bool)(rdr["GreaterThanTwo"] ?? false);
}
catch (Exception)
{
GreaterThanTwo =
false;
}
}
}
}

}

You'll notice that the conditional, and the try-catch block set the GreaterThanTwo property to false if it isn't present in the IDataReader. You could modify this to set it to null by making the property nullable (bool?).


Results


Here are two gridviews loaded up with some sample data. The first one uses the Stored procedure shown above. The second one is a simple Subsonic collection Load(), which will not use the stored procedure or extra column, but does internally make a call to TestTable.Load(IDataReader rdr).


More


You could also extend this to loading DataRows and DataTables by overriding those methods (e.g. public override void Load(System.Data.DataRow dr)). That should be even simpler, since the DataSet classes seem to offer more information about what is in them compared to the IDataReader (which I gather is swifter, and has less overhead).

No comments:

Disqus for A Nofsinger's Blog