ActiveRecord and Code Generation - Data Access Layer RAD Tools - SubSonic: The Zero Code DAL
by David Hayden ( .NET C# Developer ), Filed: Code Generation
There is nothing I love talking about more than ActiveRecord and Code Generation :)
ActiveRecord is my default architecture of choice unless requirements dictate otherwise, and I talk about it frequently:
Rob Conery, who I have met through email via the Commerce Starter Kit Project, continues to impress me with his generosity to the .NET Community. Rob has started a project on CodePlex, called SubSonic - The Zero Code DAL, where he has put together an impressive library and code generation templates that allows one to create a data access layer using ActiveRecord for your ASP.NET websites with almost no effort. I believe it was modeled after Ruby on Rails with perhaps a little inspiration from my Hayden.ActiveRecord and Castle Project's ActiveRecord :) Rob has taken the ActiveRecord to a new level, however, offering a cool Query Object, wrapper for stored procedures, a build provider, and code generation templates in addition to the very rich ActiveRecord Class that you can use in your projects. Nicely done!
The best way to experience the power of SubSonic is to view the screencast, which walks you through all the cool features.
SubSonic - The Zero Code DAL
After downloading the source code, I was able to start using SubSonic in my website in about 15 minutes with little effort. The key is the settings in the web.config that you can copy and paste from the sample web application that is part of the download. Below I am just pointing it to a test Blog Database for use with SQL Server and using the SubSonic SqlDataProvider.
<configSections>
<section name="SubSonicService"
type="SubSonic.SubSonicSection, SubSonic"
allowDefinition="MachineToApplication"
restartOnExternalChanges="true"
requirePermission="false"/>
</configSections>
<SubSonicService defaultProvider="SqlDataProvider"
spClassName="SPs" fixPluralClassNames="true">
<providers>
<add name="SqlDataProvider" type="SubSonic.SqlDataProvider,
SubSonic" connectionStringName="BlogConnection"/>
</providers>
</SubSonicService>
<connectionStrings>
<add name="BlogConnection"
connectionString="Data Source=.; Database=Blog;
Integrated Security=true;"/>
</connectionStrings>
Generating ActiveRecord Classes and Collections
You can certainly use the build providers which generate classes in the background ( check out the screencast mentioned above ), but I went the way of using the class generators that are part of the sample web application. The class generators are ASP.NET pages that will generate ActiveRecord Classes for you based on the tables you select in your database.
As mentioned, I pointed it to a Blog Database and chose it to generate classes for 2 tables in the Database: Blogs and BlogArticles. I imported the generated files into a test web application and referenced the SubSonic Assembly:
Using the ActiveRecord Classes
Using the generated classes is just as easy as generating them. Below I grab a single blog record from the Blogs Table and grab a page of blog articles for the same blog.
// Get 1 Blog
Blog blog = Blog.FetchByID(1);
// Get Page of Articles for a Blog
Query query = new Query("BlogArticles");
query.AddWhere("BlogId", Comparison.Equals, 1);
query.PageIndex = 1;
query.PageSize = 5;
BlogArticleCollection articles = new BlogArticleCollection();
articles.Load(query);
Sweet! Note the paging uses the Row_Number function only available in SQL Server 2005 as I have talked about here:
Reading Table Schemas in Real-Time
SubSonic is reading table schema in real-time for objects the first-time you use them. Hence, the first time I use the Blog Class in my application, SubSonic sends a query to the INFORMATION_SCHEMA Tables in my blog database to get column and key information about the Blogs Table. It only happens once for the class, not for every instance of the class. I talk about INFORMATION_SCHEMA Tables here-
Rob has some pretty interesting queries in the source code if you are interested in how you can capture METADATA about your database.
Conclusion
I will talk about SubSonic more in the near future, but for now I just wanted to introduce you to the project and the wonderful code generation possibilities it provides for your applications. Another great contribution to the community by Rob!
Source: David Hayden ( .NET C# Developer )
Filed: Code Generation