SubSonic, LINQ and Datalayers

So in the past when I've needed to connect to a database I rolled my own code. First it was an inherited application in VB6 and was riddled with inline SQL which I would convert to stored procedures when maintenance was needed, or new code was added. After that it was .Net 1.1 and I used the provided libraries for connections, implement parametrized stored procedures for everything, and write my own CReate/Update/Delete (CRUD) methods for my own classes.

When that company was purchased and I was repurposed to being a VB6 supporter, .Net supporter and part time Java developer, I was introduced vaguely to Hibernate. I didn't have any knowledge of it other than it was there, but knew it created the classes used for data access and provided the data persistence. Everything of the workings was nestled inside some Ant functionality that was all Greek to me. I just learned how to work in the system and did such.

After I left there and started my current job I was introduced to SubSonic. At the time it was SubSonic 2.x, and we utilized a Visual Studio integration where we could setup the web.config, run a tool, and boom, it generated all the classes needed to interface with the database. Now SubSonic 3.x is out, it requires .Net 3.5 and takes advantage of LINQ and T4 templates. This shit is now getting fun.

As an example, if a table was:
Person
{
Integer: Id
String: FirstName
String: LastName
}

In SubSonic 2.x we would have been given a class named Person with three attributes of Id, FirstName and LastName that had built in Save methods as well as a PersonCollection class to represent a group of people. To query this and get a list, we would have a method similar to:

public PersonCollection GetPeople()
{
PersonCollection toReturn = new PersonCollection();
toReturn.Load();

return toReturn;
}

If we wanted to filter we could add a line of:
toReturn.Where("FirstName", "David");
to get all people with the first name of David.

Now in SubSonic 3.0 we get to use LINQ, so the same method would look like..

public List GetPeople()
{
var qry = from p in Person.All()
select p;

return qry.ToList();
}

and to add the filter we can add a line to the LINQ select of:
where p.FirstName == "David"

I'm loving it!