Patrick Steele's .NET Blog

Generate ActiveRecord Classes from Existing DB Schema

GeneratorStudio is a project on CodePlex used to automate the creation of Castle ActiveRecord classes from existing database schema.  I haven't downloaded it yet to play around with it, but it looks like it would be a nice thing to have if you have a large (or even medium) schema that's already in production and you'd like to start using Castle ActiveRecord for DB access.

More info here and here.

Mock Databases and ActiveRecord

Fellow SRT employee Jay Harris has a great post on using SQLite's in-memory database feature to mock his database.  He points out a few of the factors that drove him to investigate such an approach:

• I did not want a SQL Server installation to be a requirement for me, the other developers, and my Continuous Integration server.
• I wanted something fast. I didn't want to have to wait for SQL Server to build / tear down my schema.
• I wanted something isolated, so the other developers, and my CI server, and I wouldn't have contention over the same database, but didn't want to have to deal with independent SQL Server instances for everyone.

Check it out.

SRT to host PDC Keynote Remote Viewing

Register now.  Seating is limited.  Thanks to Microsoft for sponsoring lunch!  Here's the details:

SRT Solutions will be hosting a remote viewing of the Ray Ozzie Keynotes, live from the PDC conference. Join SRT staff and other .NET developers from the community to watch the keynote live and to discuss it afterward.

Microsoft is sponsoring lunch.

Pre-registration is required.

Please note that there are two keynote events, one on Monday and one on Tuesday.  You must register for each one you plan on attending.  And since seating is limited, please only register if you know you can make it.

Tweaking my color settings

Like many developers, I spend a lot of time reading and writing code every day.  After hearing so many developers praise the "dark background/light text" colors that have become popular, I decided to switch too.  I did some research and found a theme I really liked.  I tweaked it very slightly and have been using it ever since.  It is much easier on the eyes.

However, one thing has been bothering me.  When Visual Studio stops in an exception or when an exception is thrown and Visual Studio highlights the catch block, the coloring is almost unreadable.  Here's a quick sample:

I remember doing a quick run through trying to find what this type of code block was called so I could change the color, but had no luck.

Today, I couldn't stand it anymore.  I methodically searched through every color option and finally found the one that corresponds to this case:

Read-Only Region

Looking in the Options dialog, the "Sample" shown looks okay:

But that's because the "Item Foreground" is set to "Default" and VS assumes black.  Since my default text color is White, this combination is horrible.  I picked a new color from the background color dropdown (Olvie) and now my eyes are much happier:

ActiveRecord and a Custom NHibernate PrimitiveType

I was intrigued by Steve Smith's blog post yesterday about reducing SQL Lookup tables in nHibernate.  He gave an example of a WorkOrderStatus class the exposed the actual status as a POCO object that wasn't stored in the database.  What really piqued my interest was the following comment:

NHibernate can map this status directly if you create a WorkOrderStatusType class that inherits from NHibernate.Type.PrimitiveType and overrides its methods.

I never knew nHibernate supported this type of feature (never needed it or thought about it).  As I'm an avid ActiveRecord user, I decided to see how I would implement a custom nHibernate PrimitiveType and utilize it via ActiveRecord.  Turns out it was pretty easy!  The full source is available from my GoogleCode page either through SVN or simply a ZIP download.

A quick note before we begin: I didn't find a whole lot of documentation on extending PrimitiveType and implementing your own.  I reviewed some nHibernate code and I think I got the general implementation right, but can't be sure it'll work 100% of the time.  It was a proof-of-concept project.

ActiveRecord Setup

I decided I'd use SQLite for this sample since it's perfect for this type of job -- small, compact and no install required.  I can poke around the database to check schema and data using the SQLite addon for Firefox.

Instead of stealing Steve's WorkOrderStatus, I decided to go with a schema that has a simple Company object, and that Company object has a CompanyType defined.  Instead of defining a lookup table just for company types, I'll create a CompanyType class that derives from NHibernate.Type.PrimitiveType and let nHibernate do the loading/saving.

First, the CompanyType.  For this demo, it's a simple object with a Description (string) and a Value (integer).  The Value is what is actually saved to the database (note: this isn't the entire class -- just the basics):

public class CompanyType : NHibernate.Type.PrimitiveType

{

    public static readonly CompanyType Software = new CompanyType() { Description = "Software", Value = 1 };

    public static readonly CompanyType Manufacturing = new CompanyType() { Description = "Manufacturing", Value = 2 };

    public static readonly CompanyType Insurance = new CompanyType() { Description = "Insurance", Value = 3 };

    private static readonly CompanyType[] AllTypes = new CompanyType[] { Software, Manufacturing, Insurance };

    public string Description { get; set; }

    public int Value { get; set; }

    public override string ToString()

    {

        return this.Description;

    }

    public CompanyType()

        : base(SqlTypeFactory.Int32)

    {

    }

}

I've defined an AllTypes[] that I'll use to find the matching CompanyType when nHibernate reads the integer from the database.  The ctor calls the base class ctor and tells nHibernate what data type this new PrimitiveType is based on (the schema in the database will be an integer).  I also overrode ToString() to return the Description property to make debugging easier.

The Company record is pretty simple too.  When we get to the CompanyType, we tell ActiveRecord (which works through nHibernate) the column type for the column (our custom PrimitiveType):

[ActiveRecord]

public class Company : ActiveRecordBase<Company>

{

    [PrimaryKey(Generator = PrimaryKeyType.Identity)]

    public int Id { get; set; }

    [Property]

    public string Name { get; set; }

    [Property]

    public DateTime InceptionDate { get; set; }

    [Property(ColumnType = "ARPrimitiveType.Model.CompanyType, ARPrimitiveType")]

    public CompanyType CompanyType { get; set; }

    public override string ToString()

    {

        return this.Name;

    }

}

Implementing the required methods in CompanyType was pretty easy.  I'm not sure when DefaultValue is used, so I just return a CompanyType of Software:

public override object DefaultValue

{

    get { return CompanyType.Software; }

}

ObjectToSQLString seems to want to convert your PrimitiveType (CompanyType) to a string value that can be used by the database.  So we'll convert our Value property to a string:

public override string ObjectToSQLString(object value, NHibernate.Dialect.Dialect dialect)

{

    CompanyType type = value as CompanyType;

    return type.Value.ToString();

}

The PrimitiveType class indicated the actual type of data stored in the database (a 32-bit integer for our CompanyType):

public override Type PrimitiveClass

{

    get { return typeof(Int32); }

}

The FromStringValue and two Get overloads both need to do the same thing: Take a representation of the database value and convert it to our PrimitiveType (a CompanyType).  For this I created a single method that converts the database integer back to a CompanyType instance using a LINQ query on the AllTypes array:

public override object FromStringValue(string xml)

{

    return GetCompanyType(xml);

}

public override object Get(System.Data.IDataReader rs, string name)

{

    return GetCompanyType(rs[name]);

}

public override object Get(System.Data.IDataReader rs, int index)

{

    return GetCompanyType(rs[index]);

}

private object GetCompanyType(object val)

{

    if (val == null)

    {

        return DefaultValue;

    }

    int v = Int32.Parse(val.ToString());

    return AllTypes.First(x => x.Value == v);

}

Next, nHibernate needs to know how to stick a CompanyType into the database.  In the Set method, we take our "Value" property and place it in the IDbCommand.Parameters collection:

public override void Set(System.Data.IDbCommand cmd, object value, int index)

{

    CompanyType type = value as CompanyType;

    DbParameter param = cmd.Parameters[index] as DbParameter;

    param.Value = type.Value;

}

Finally, nHibernate wants to know that type of data this PrimitiveType is exposing to the outside world (our application):

public override Type ReturnedClass

{

    get { return typeof(CompanyType); }

}

We've now implemented a PrimitiveType that will allow our application to program against a CompanyType object while the database deals with an integer.

Sample Code

Now we can create Company objects like this:

Company c = new Company();

c.Name = "ABC Software";

c.InceptionDate = new DateTime(2008, 1, 1);

c.CompanyType = CompanyType.Insurance;

c.Save();

If you look in the database, the Company table has a field called "CompanyType".  It's an integer and the record above will populate the CompanyType with a value of 3.

You can use this object in HQL queries too:

private static void HQLTest()

{

    SimpleQuery<Company> query = new SimpleQuery<Company>(

        "from Company c where c.CompanyType = ?", CompanyType.Manufacturing);

    Company[] results = query.Execute();

}

Or, if you prefer, you can use nHibernate's DetachedCriteria:

private static void CriteriaTest()

{

    DetachedCriteria query = DetachedCriteria.For<Company>("Company")

        .Add(Expression.Eq("CompanyType", CompanyType.Manufacturing));

    Company[] results = Company.FindAll(query);

}

Conclusion

This was a fun demo project to create.  It's always nice to learn something new about a tool you use often (ActiveRecord/nHibernate).  This was a good proof-of-concept, but needs a lot more testing and error handling before it could be used in production.  You're free to use the code as-is, but there are no guarantees as to its correctness.

If anyone is aware of any errors I've made in implementing a custom PrimitiveType, please feel free to let me know.

Project Eurler #12

I see that Bill did Euler #11 earlier this week so I thought I'd tackle #12.

The first thing I wanted to do was write a routine to generate a triangle number.  As we've seen throughout this series, LINQ can come in very handy:

static int TriangleOf(int number)

{

    return Enumerable.Range(1, number).Sum();

}

Now I needed to find all the divisors of a number.  I have to "eat crow" and admit the first time I did this, I used the brute force method:

static IEnumerable<int> FindAllDivisors(int number)

{

    return from d in Enumerable.Range(1, number)

           where number % d == 0

           select d;

}

I didn't like it.  I knew I could use the square root to reduce the number of iterations in this loop, but that would mean every "select" of my LINQ query would need to return more than one result -- the divisor and the number / divisor.  I couldn't see any easy way to create two results from a query so I just left this as-is and moved on to actually solving the problem.

Again, LINQ made this extremely easy:

var answer = (from d in Enumerable.Range(1, int.MaxValue)

          where FindAllDivisors(TriangleOf(d)).Count() > 500

          select d).Take(1);

As you might expect, this wasn't very quick.  In fact, I let it run overnight since I didn't start on this till later in the evening.  It ended up taking 5 hours and 15 minutes.  NOT A SOLUTION!

So I started researching how I could return multiple items from a single LINQ query.  The answer was embarassingly simple -- create an array!

var values = from d in Enumerable.Range(1, (int)Math.Sqrt(number))

         where number % d == 0

         select new int[] { d, number / d };

But now I've got an IEnumeable<int[]> and I want all the divisors together in an IEnumerable<int>.  I looked around at the extension methods on IEnumerable<T> and found SelectMany.  This method will project each element into an IEnumerable<T> and flatten the result into a single sequence.

So my revised FindAllDivisors now looks like this:

static IEnumerable<int> FindAllDivisors(int number)

{

    var values = from d in Enumerable.Range(1, (int)Math.Sqrt(number))

             where number % d == 0

             select new int[] { d, number / d };

    return values.SelectMany(x => x);

}

Now this completes in a respectable 5 - 6 seconds.  MUCH better than my first attempt.  Sometimes, KISS only gets you so far.

Playing around with ASP.NET MVC

As I'm a big fan of Castle Project's MonoRail, I often get asked my opinion of the ASP.NET MVC stuff Microsoft is working on.  And I always have the same answer -- I've seen some demos but haven't actually played around with it.  So I took some time tonight and installed it.

Installation

You can download the ASP.NET MVC Preview 5 release here.  Double-click on the MSI and you get the usual welcome screen.

As always, there's a EULA to accept.

After that, you're ready to install.

And then you're done!  Painless and easy.

My First Project

After installation completed, I started up Visual Studio 2008.  At this point, the GhostDoc configuration screen appeared.  I had to repeat my GhostDoc set up.  That was weird!  Don't know if that had anything to do with the ASP.NET MVC install, but I hadn't done anything else with my VS2008 installation recently.

You'll now have a new Web project type called "ASP.NET MVC Web Application".

After selecting this I was asked if I wanted to add a unit test project as well.  Very nice!  Obviously, I selected "Yes".  :)

My solution was now all set up and ready to go:

Notice the project is pre-populated with folders for my controllers, models and views.  The web.config is also fully configured.  At this point, I clicked "Run" to see what would happen.

By default, the web.config is not set up for debugging.  I took the default and let VS.NET set up my config for debugging.

And now my first ASP.NET MVC project was up and running!

Comparison to MonoRail

Now I started poking around the directory structure.  I noticed that instead of a "layouts" folder they place their master pages ("layouts" in MonoRail) inside a "shared" folder.  Sounds similar to MonoRail's shared views.

I noticed that the view pages are still ASPX pages and contain a code-behind file.  This seems kind of odd to me as I'm accustomed to MonoRail's view files (mostly NVelocity) -- a single file that contains everything needed to render the view; no more, no less.  I could see that without discipline, the code-behind files could be bloated with business logic when it should only contain view logic.  Be careful!

Poking around the pre-built pages I noticed most of the "ViewData" (PropertyBag for you MonoRail people) output was wrapped in Html.Encode.  It would seem to me that you'd want to, by default, always HTML encode your output (like MonoRail does).  I think raw output of view data is the exception rather than the rule.  Others think it should be this way too.  Again, be careful!

Adding a New View

So it's time to start adding a little bit of my own code to this sample app.  I started with adding a new view.  When you do this, make sure you add a new "MVC View Page" and not the usual "Web Form":

The page popped up and I noticed something right away: There's no way to pick your master page when creating a new MVC View Page.  I'm sure this is just one of those things that don't exist yet.  I know that this selection step is available for web forms so it's probably just a matter of time before the ASP.NET MVC stuff supports this.

But, since it doesn't, you'll need to add the MasterPageFile attribute yourself as well as any ContentPlaceholder tags.  I grabbed them from one of the sample pages.

So now I just dumped some code real quick into the view.  As I typed, I noticed the Visual Studio was complaining about what I was typing:

It didn't like my "Html.Encode" nor my "ViewData" references.  I copied these right from another page?!  What was I missing?  An import perhaps?  Nope, the imports on the sample pages are the same as mine.  Then it hit me -- these view pages (like web forms pages) inherit from a base class.  That base class probably exposes the Html and ViewData objects.  Since I just created this page (and haven't compiled), the intellisense wasn't picking up the proper settings.  So even though I had the red squiggle's, I hit the build button.  Everything built fine and my page errors went away.

I put a line of code in one of the controllers to stick my name in the ViewData.  Then I added my new page (action) to the menu:

I ran the project and clicked on my link:

Ok -- I admit it.  Not too exciting!  And it barely scratches the surface of what you can do with ASP.NET MVC.  But I think I've showed it's pretty easy to install it and start using it right away -- even if you're not familiar with the MVC pattern.

I'll continue to use MonoRail for my web projects as it's more mature than the ASP.NET MVC stuff.  But I'll definitely be coming back to this stuff from time to time and playing around with it.  I think Microsoft has made the right decision in creating a whole new architecture for implementation of the MVC pattern and not trying to jam it into the Web Forms engine.  That would have been a huge mistake!

ActiveRecord + HQL and an "IN" clause

Late last year as I was using MonoRail and ActiveRecord for a simple web application.  I was helping my local church find volunteers with various skills to teach some basic computer courses to the the church staff.  I wanted to keep track of the volunteers along with the skills they had.  I used this as another opportunity to learn more about MonoRail and ActiveRecord.

The Database

The database couldn't be any simpler: I had a Trainer table, a Skill table and a join table to keep track of the 1:M relationship between a Trainer and their skills.

ActiveRecord

The ActiveRecord classes were equally easy to define (in fact, I created the ActiveRecord objects first and then used schema generation to generate the actual database tables).

The Problem Query

I used MonoRail to put together a couple of web pages for editing of the data.  Then I created a "Report" page that allowed me to pick an arbritrary set of skills and get a list of all Trainers that had that particular skill.

In SQL, I'd use an "IN" clause like this:

select t.*

from Trainer t

inner join TrainerSkills ts on ts.TrainerId = t.id and ts.SkillId in (2,6)

In the query above, the IN clause of (2,6) contains the primary keys of the two Skill records selected by the user.  Pretty simple SQL.  I needed to figure out how to get this in HQL (Hibernate Query Language).

My first attempt was an almost exact port of the SQL syntax (HQL is very similar to SQL anyway):

public static Trainer[] FindBySkillset(Skill[] skills)

{

    SimpleQuery<Trainer> q = new SimpleQuery<Trainer>("from Trainer t where t.Skills in (?)", skills);

    return q.Execute();

}

That didn't work.  I got some cryptic error about having an "unindexed collection before []".  So then I tried a variation of the above query where I used a named parameter and specifically indicated the parameter was a list:

public static Trainer[] FindBySkillset(Skill[] skills)

{

    SimpleQuery<Trainer> q = new SimpleQuery<Trainer>("from Trainer t where t.Skills in (:skills)");

    q.SetParameterList("skills", skills);

    return q.Execute();

}

That gave me another odd error.  After digging around in the HQL docs as well as finding a forum post somewhere that showed a slightly different IN clause, I found out that I needed to "flip" the way I use the IN clause.  In SQL, you'd say "WHERE xxx IN (values...)".  In HQL, you give the list of values first and use the "elements" keyword to indicate which collection to match up those values to.  The final working query:

public static Trainer[] FindBySkillset(Skill[] skills)

{

    SimpleQuery<Trainer> q = new SimpleQuery<Trainer>("from Trainer t where ? in elements (t.Skills)", skills);

    return q.Execute();

}

Hopefully this helps out someone else.