Generating data layer using Subsonic.

• Download SubSonic.zip - 2.16 MB

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

• Support BusinessObject pattern.
• Must have code generator support for reverse engineering existing database.
• Can be incorporated inside the build environment, to generate business objects on fly and SQL scripts from existing database structure.
• Must be intelligent enough to detect object relationship and generate code in 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 prefer to setup environment. Very first thing to do is download the SubSonic from http://subsonicproject.com/ This is open source toolkit and one can use in their project.

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

Now, open a command prompt, and proceed to location : {Program Files}\SubSonic\SubSonic<Version>\SubCommander. And fire the command sonic. You will 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 file
• scriptdata: Scripts the data to file for your database
• scriptschema: Scripts your Database schema to file
• generate: Generates output code for tables, views, and SPs
• generatetables: Generates output code for your tables
• generateODS: Generates and ObjectDataSource controller for each table
• generateviews: Generates output code for your views
• generatesps: Generates output code for your SPs
• 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 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 DB (blank = use SSPI)
• /out - the output directory for generated items. (default = current)
• /lang - 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 your App/Web.Config - used to instance SubSonic
• /excludeTableList the opposite of tablelist. These tables will NOT be
• used to generate classes

The useful options from above tons of output are :

• generate : It is used to specify the option for generating code of all database exist in database.
• /server: It is used to specify the server name, where the database reside i.e. localhost.
• /db: It is used to specify the database we are interested into. i.e. 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 option of Sonic. If yes then execute the following command:

sonic generate /server localhost /db EntLibQuickStarts /out BO.

Above command will generate 12 code files as follows :

• AllStructs.cs :- It contains structures for holding name of all tables, views, database.
• Credit.cs:- It contains the implementation of business object for Credit table.
• CreditController.cs:- It contains the implementation of Factory Controller for Credit table.
• Customer.cs:- It contains the implementation of business object for Customer table.
• CustomerController.cs:- It contains the implementation of Factory Controller for Customer table.
• Debit.cs:- It contains the implementation of business object for Debit table.
• DebitController.cs:- It contains the implementation of Factory Controller for Debit table.
• Order.cs:- It contains the implementation of business object for Order table.
• OrderController.cs:- It contains the implementation of Factory Controller for Order table.
• Product.cs:- It contains the implementation of business object for Product table.
• ProductController.cs:- It contains the implementation of Factory Controller for Product table.
• StoredProcedures.cs:- It contains the wrapper for all stored procedures.

For creating Business layer, Open your IDE VS or SharpDevelop and Create new class library project named as EntLibQuickStarts. Add above generated code files to the newly created project and add reference to Subsonic.dll from installed location then Build the project.

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

Add App.config file and add 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 ActiveRecord Pattern means, an object instance represents a record in table. Hence to simulate the same behavior as table, we need to use the collection of objects and bind the collection to UI controls. Don’t get overwhelm by these terminologies, because subsonic is smart enough it generate Business Objects as well Object collection in generated code so you only need to use the instance of generated collection and fetch the records using the BusinessObjectControllers FetchAll method.

The first thing that need to done is create object for BusinessObjectController, BusinessObjectCollection i.e.

ProductController pc=null;

ProductCollection pr=null;

Next step is to add Button with caption Load and add the following code into 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 application is ready to run. Some more finishing is needed to support CRUD oprations.

To Support all operation add UI buttons for all operation 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 of business layer and data layer powered by subsonic.