SubSonic – Extending The Zero Code DAL With A "Schema Provider Service" For Kicks

For the past couple of days I have been playing with SubSonic – The Zero Code DAL, which is a really sweet code generation tool by Rob Conery that generates ActiveRecord Classes, a Stored Procedures Wrapper, a Query Object, etc. for the data access layer of your web applications. You can go the build provider route which creates the classes and collections in the background automagically, or run the provided code generation templates that will make the class files for you to import into your application.

I wrote a brief tutorial on it that you can read for more information:

ActiveRecord and Code Generation – Data Access Layer RAD Tools – SubSonic: The Zero Code DAL

but I suggest watching the webcast which is excellent. I haven't listened to it yet, but I noticed that Rob talked about SubSonic earlier this month on .NET Rocks! as well ( Oct 4th show ). Kudos to Rob for his generosity in providing this great learning and productivity tool to the .NET community.


Table Schemas and Getting Database Schema Information

As you play with the code generated by SubSonic as well as dive into the source code, you will notice it has to go out and get schema information about each table in the database that you choose for inclusion into your data access layer. SubSonic queries the INFORMATION_SCHEMA Tables that hold the metadata for your database, allowing it to get the list of tables in the database as well as the column, primary key, and foreign key information for each table. Cool stuff. Here is an example of querying the INFORMATION_SCHEMA tables to get a list of tables in a SQL Server Database:

Get List of Tables in a Database – Query INFORMATION_SCHEMA.Tables – ADO.NET

You can also do this kind of thing using GetSchema in ADO.NET 2.0 as well as SQL Server Management Objects. The source code has a number of interesting queries to get database metadata which is worth checking out if you are interested.


Reading Table Schema Information from XML File Instead of Querying Database

For kicks, late last night I wanted to have SubSonic read the database schema from an XML file instead of going to the database. No real reason for doing this other than wondering how quickly I could pull it off. Although I could see some performance and security benefits from having it in a local repository as opposed to querying the database in real-time.

I envisioned separating the current data providers into 2 different providers: 1) DataProvider and 2) SchemaProvider. By treating them separately, one could get a little more flexibility about where to get the schema information as well as offer DDL services to create the database and tables based on the schema.

I decided to take a bit of a shortcut and piggyback on the current DataProvider because I needed to accomplish this in an hour :)


Extracting Our New ISchemaProvider Interface

We are going to extract a new SchemaProvider Interface from the current data providers by grabbing the interface that only has to do with Schema Related Stuff…


interface ISchemaProvider
    string GetForeignKeyTableName(string fkColumnName, string tableName);
    string GetForeignKeyTableName(string fkColumnName);
    string[] GetSPList();
    IDataReader GetSPParams(string spName);
    string[] GetTableList();
    TableSchema.Table GetTableSchema(string tableName);
    string[] GetViewList();
    string ScriptData(string tableName);
    string ScriptSchema();


Now, because the current DataProviders implement this interface, we can use them as Schema Providers. Note we could also make an IDataProvider interface out of the leftover methods, etc., but I am not concerned with that now.


public class SqlDataProvider : DataProvider, ISchemaProvider 


Modifying DataService Class to Use ISchemaProvider

The DataService Class handles all data services, handing them off to the proper Data Provider. This is beautiful, because here is where we are going to delegate schema related activities to our schema provider.


public class DataService {
    public static ISchemaProvider _schemaProvider = null;
    // ...
    static ISchemaProvider SchemaProviderInstance
            return _schemaProvider;
    internal static void LoadProviders() {
        // ...
        SubSonicConfig.SchemaFile = section.SchemaFile;
        // ...
        // If no XML Specified, use DataProvider
        // else use XmlSchemaProvider.
        if (string.IsNullOrEmpty(SubSonicConfig.SchemaFile))
            _schemaProvider = _provider as ISchemaProvider;
            _schemaProvider = new XmlSchemaProvider(SubSonicConfig.SchemaFile);
    // ...
    public static TableSchema.Table GetTableSchema(string tableName) {
        return SchemaProviderInstance.GetTableSchema(tableName);


I have set this all up using the provider model as used by SubSonic.


Specifying the XML File and the XMLSchemaProvider Class

I specify the path to the XML File in web.config using schemaFile="BlogTable.xml". Obviously this is just a shortcut as opposed to a complete IConfigurationSource type of idea.


<SubSonicService defaultProvider="SqlDataProvider" schemaFile="BlogTable.xml">
    <add name="SqlDataProvider" ... />


 I added the property to SubSonicConfig for completeness:


public static class SubSonicConfig {

    // ...
    private static string _schemaFile = string.Empty;
    public static string SchemaFile
        get { return _schemaFile; }
        set { _schemaFile = value; }


 And here is just a quick XmlSchemaProvider Class I created to test the idea that only handles the single method, GetTableSchema, by passing back the same schema for all tables as read from the XML file.


public class XmlSchemaProvider : ISchemaProvider
    TableSchema.Table _blogsTable;

    public XmlSchemaProvider(string xmlFile)
        XmlSerializer serializer = new XmlSerializer(typeof(TableSchema.Table));

        using (Stream fs = new FileStream(xmlFile, FileMode.Open))
            _blogsTable = (TableSchema.Table)serializer.Deserialize(fs);
    // ...
    // Just for test. Returns a single table based on any name.
    public TableSchema.Table GetTableSchema(string tableName)
        return _blogsTable;
    // ...


Obviously my XmlSchemaProvider is lacking :), but it worked. I was able to read the schema from an XML File as opposed to the database based on information provided in web.config and the simple addition of a Schema Provider Service which is separate than a data service.



This showed a little about the ease of extending SubSonic to include a separate Schema Provider Service from the Data Provider Service. Although a real solution would be different, this provided some architectural value in how that might look and what you can do in an hour :)

by David Hayden


This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>