Generating a data layer using SubSonic

• Download source code - 1.12 MB

Introduction

In this article, I would like to explore a very robust and feature rich Data Access Layer toolkit, SubSonic. The inception for this article started when I was hunting for some Open Source toolkits to support my database access code. I was looking at a toolkit that would be Open Source and would have some ground features such as:

• Supports the BusinessObject pattern.
• Must have code generator support for reverse engineering an existing database.
• Can be incorporated inside the build environment, to generate business objects on the fly and SQL scripts from an existing database structure.
• Must be intelligent enough to detect object relationships and generate code in the form of get<related_object_name>.
• Can be configured from app.config or web.config.

As a conclusion of my hunting, I finally got affectionate with SubSonic. Before proceeding, I would like to setup the environment. The very first thing to do is download SubSonic from here. This is an Open Source toolkit.

After downloading, install SubSonic in the default location, i.e., {Program Files}\SubSonic.

Now, open a command prompt, and proceed to: {Program Files}\SubSonic\SubSonic<Version>\SubCommander. Now, fire the command sonic. You will get a very rich output as follows:

• sonic.exe v2.0.3.0 - Command Line Interface to SubSonic v2.0.3.0
• Usage: sonic command [options]
• Sample: sonic generate /server localhost /db EntLibQuickStarts /out GeneratedFiles
• Help: sonic help
• Tip: SubSonic will read your App.Config or Web.Config - just select the project, and run your command.

Commands

• version: Scripts out the schema/data of your DB to a file
• scriptdata: Scripts the data to file for your database
• scriptschema: Scripts your database schema to a file
• generate: Generates the output code for tables, Views, and Stored Procedures
• generatetables: Generates the output code for your tables
• generateODS: Generates an ObjectDataSource controller for each table
• gienerateviews: Generates the output code for your Views
• generatesps: Generates the output code for your Stored Procedures
• editor: Creates an editor for a particular table.

Argument list

• #######: Required for all commands (these can be read from config files); if you don't have a Web.Config or App.config, these need to be set
• /override: SubCommander won't try to find a config - instead, it will use what you pass in
• /server - the database server - always required
• /db - the database to use

Other commands (some may be required for specific commands)

• /userid - the user ID for your database (blank = use SSPI)
• /password - the password for your database (blank = use SSPI)
• /out - the output directory for the generated items (default = current)
• /lang - the generated code language: cs or vb (default = cs)
• /provider - the name of the provider to use
• /includeTableList - used for generating classes, a comma-delimited list that defines which tables should be used to generate classes
• /config - the path to your App/Web.Config - used to instantiate SubSonic
• /excludeTableList - the opposite of /includeTableList; these tables will not be used to generate classes

The useful options from the above tons of output are:

• generate: It is used to specify the option for generating the code for all the tables in the database.
• /server: It is used to specify the server name where the database resides, e.g., localhost.
• /db: It is used to specify the database we are interested in, e.g., Northwind.
• /out: It is used to specify the output directory for generating the code files.
• /lang: It is used to specify the code generation language.

I guess now you are familiar with the options of SubSonic. If yes, then execute the following command:

sonic generate /server localhost /db EntLibQuickStarts /out BO

The above command will generate twelve code files as follows:

• AllStructs.cs: It contains the structures for holding the name of all the tables and Views in the database.
• Credit.cs: It contains the implementation of the business objects for the Credit table.
• CreditController.cs: It contains the implementation for the Factory Controller of the Credit table.
• Customer.cs: It contains the implementation for the business objects for the Customer table.
• CustomerController.cs: It contains the implementation for the Factory Controller for the Customer table.
• Debit.cs: It contains the implementation for the business object for the Debit table.
• DebitController.cs: It contains the implementation for the Factory Controller for the Debit table.
• Order.cs: It contains the implementation for the business object for the Order table.
• OrderController.cs: It contains the implementation for the Factory Controller for the Order table.
• Product.cs:- It contains the implementation for the business object for the Product table.
• ProductController.cs:- It contains the implementation for the Factory Controller for the Product table.
• StoredProcedures.cs: It contains the wrapper for all the Stored Procedures.

For creating the Business Layer, open your IDE, VS or SharpDevelop, and create a new Class Library Project named EntLibQuickStarts. Add the above generated code files to the newly created project, and add a reference to Subsonic.dll from the installed location, and then build the project.

For testing the business layer, create one more Windows Application project in the same solution, named TestApp. Create some UI layouts to show and edit the data in the database, e.g., datagridviews and textboxes.

Add the App.config file and add the following section into that:

<configSections>

<section name="SubSonicService" type="SubSonic.SubSonicSection, 
    SubSonic" allowDefinition="MachineToApplication"
    restartOnExternalChanges="true" requirePermission="false"/>

</configSections>

<appSettings/>

<connectionStrings>

<add name="EntLibQuickStarts" 
    connectionString="Data Source=localhost; 
    Database=EntLibQuickStarts; Integrated Security=true;"/>

</connectionStrings>

<SubSonicService defaultProvider="EntLib" >

<providers>

<clear/>

<add name="default" type="SubSonic.SqlDataProvider,
   SubSonic" connectionStringName="EntLib" 
   generatedNamespace="aicl.data" /> 

<add name="EntLib" type="SubSonic.SqlDataProvider, SubSonic" 
    connectionStringName="EntLib" generatedNamespace="aicl.data" /> 

</providers>

</SubSonicService>

Basically, SubSonic supports the ActiveRecord Pattern, which means an object instance represents a record in a table. Hence, to simulate the same behavior as a table, we need to use the collection of objects and bind the collection to the UI controls. Don’t get overwhelmed by these terminologies, because SubSonic is smart enough and it generate Business Objects as well as the object collection in the generated code, so you only need to use the instance of the generated collection and fetch the records using the BusinessObjectControllers FetchAll method.

The first thing that needs to be done is create an object for BusinessObjectController and BusinessObjectCollection.

ProductController pc=null;

ProductCollection pr=null;

The next step is to add a button with the caption Load, and add the following code into the click handler:

pr=pc.FetchAll();

this.bindingSource1.DataSource=pr;

dataGridView1.DataSource=bindingSource1;

this.comboBox1.DataSource=pr;

this.comboBox1.DisplayMember="ProductName";

this.comboBox1.ValueMember="ProductID";

Now, the application is ready to run. Some more finishing work is needed to support CRUD operations.

To support all operation, add UI buttons for all operations, and code as follows:

void BtnNewClick(object sender, EventArgs e)
{
    try
    {
        pc.Insert("Shower to Shower",1,45,DateTime.Now);
        BtnLoadClick(null,null);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString(),ex.Message);
    }

}
void BtnDeleteClick(object sender, EventArgs e)
{
    try
    {
        pc.Delete((object)5);
        BtnLoadClick(null,null);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString(),ex.Message);
    }
}

void DataGridView1Selection(object sender,EventArgs e)
{
    if (dataGridView1.SelectedRows.Count>0)
    {
        Product p=pr[dataGridView1.SelectedRows[0].Index];
        or=p.Orders();
        this.dataGridView2.DataSource=or;
    }
}

void BtnUpdateClick(object sender, EventArgs e)
{
    try
        {
            pr.SaveAll();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message,ex.Source);
        }
}

void BtnSortClick(object sender, EventArgs e)
{
    try
    {
        pr=pr.OrderByDesc("CategoryID");
        BtnLoadClick(null,null);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message,ex.Source);
    }
}

Now, we have a full fledged application running with support for business layer and data layer, powered by SubSonic.