Click here to Skip to main content
Click here to Skip to main content

Generating a data layer using SubSonic

, 20 May 2008 Ms-PL
Rate this:
Please Sign up or sign in to vote.
This article will demonstrate how to generate a data access layer using SubSonic.

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.

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)

Share

About the Author

Ashutosh Phoujdar
Architect Infosys Technologies Ltd.
India India
An Software Architect having work experience of 8+ years in Architecture, Design and Development of Enterprise class application for Analytic s and Business Intelligence. Having hands-on experience primarily in Microsoft Technology Stack including(but not limited to) .NET, SQL Server, SharePoint Server, Biztalk, Withal know to program in Java, Perl and Shell. Currently working as Product Technical Architect in Infosys Technologies Ltd. Products R&D division. He has worked on development of best in class Supply Chain Optimization System and Spend Classification Software for optimizing indirect spend. An avid programmer by heart and passionate for solving any challenging and complex business problems. He is advocate of Open source and love to share solutions with open source communities like
1.Stack Overflow
2. nHibernate
.
 
Award's :
Prize winner in Competition "Best article of May 2008"
 
Articles :
Click to see my CodeProject Articles
 
Open Source Contributions:
Click to see my Open Source Project
 
Blog :
Share a solution | Explore the .NET world
 
Link'd :
LinkedIn Profile
 
His Favorite(s) :
nHibernate - The best ORM.
nHibernate Contributed Tools
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralFORMATTING!! Pinmemberleckey20-May-08 3:38 
AnswerRe: FORMATTING!! Pinmemberashu fouzdar20-May-08 6:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 20 May 2008
Article Copyright 2008 by Ashutosh Phoujdar
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid