Click here to Skip to main content
15,181,193 members
Articles / Database Development / SQL Server
Posted 8 Nov 2007


42 bookmarked

Creating a DAL with SubSonic

Rate me:
Please Sign up or sign in to vote.
3.94/5 (22 votes)
8 Nov 2007CPOL4 min read
Creating a DAL with SubSonic.


In a typical data driven application, you can expect to spend at least 40-50% of your time writing the "CRUD" code (Create, Read, Update, and Delete). In order to loosely couple your application code with the database, you will probably want to create a DAL (Data Access Layer). Unfortunately, writing a DAL can be very time consuming. Especially if you are working on a project where the data model is constantly evolving.

Well, lucky for us, there is SubSonic. SubSonic is an Open Source tool built for .NET that will automatically build your DAL with a few simple steps.

Building the DAL

  1. The first task is to download and install SubSonic from
  2. Open up Visual Studio 2005 and create a new project. Choose Visual C# --> Windows --> Windows Control Library.
  3. Once the project is created, you need to add three references. The first is to SubSonic.dll. If you did a default install, then this should be located at C:\Program Files\SubSonic\SubSonic 2.0.3\SubSonic.dll. You will also need a reference to System.Web (unfortunately, this is a requirement) and System.Configuration.
  4. Now, you need to add an application configuration file. In the config file, you will need to add a connection string, a config section, and a provider section. Here is sample "SubSonic" configuration file:
  5. XML
    <?xml version="1.0" encoding="utf-8" ?>
          <section name="SubSonicService" 
                   type="SubSonic.SubSonicSection, SubSonic" requirePermission="false"/>
          <add name="AdventureWorks" 
            connectionString="Data Source=.;Database=AdventureWorks;
                              Integrated Security=true;"/>
       <SubSonicService defaultProvider="AdventureWorks">
             <add name="AdventureWorks" type="SubSonic.SqlDataProvider, SubSonic" 

    Note: You can change the default namespace by modifying the generatedNamespace attribute.

  6. Add a new folder to the solution with the name "Generated". This is where all of the generated code will get created. Although it is not required to add this folder, it is definitely recommended. The folder is nice to have because once you are happy with the data model and the resulting code, you can move it into a separate folder that will be your "production ready" code. Since SubSonic is a code generation tool, you may choose to generate the files a few times and keep overwriting the source files in the "Generated" folder without affecting the "production ready" files. When the files are generated, they do not get automatically included into the library, so having this extra folder filled with files will not bloat your DLL. This is because Visual Studio will ignore files that are not included in the project, and therefore those files will not get compiled into the assembly.
  7. Now, it is time to run sonic.exe. If you did a default install, sonic.exe will be located at C:\Program Files\SubSonic\SubSonic 2.0.3\SubCommander\sonic.exe. The easiest way to run sonic.exe is to add it as an external tool to your VS2005 environment. This can be done by going to Tools-->External Tools and clicking on the "Add" button. In the Title field, I entered "SubSonic DAL", but you can name it whatever you like. In the command field, enter in the path to the sonic.exe. In the Arguments field, enter in "generate /out Generated". This means that all generated code will get created in the folder named Generated that we created in the previous step. Finally, set the Initial Directory to "$(ProjectDir)". Also check the "Use Output Window" and the "Prompt for Arguments" checkboxes. Click the "OK" button to close out the dialog.
  8. Screenshot - external_tool_dlg.jpg

  9. Now, for the moment of truth. Execute sonic.exe by clicking on the "SubSonic DAL" menu item under the "Tools" menu. Keep an eye on the output window so you can see the execution status. If you setup something wrong, the output window will usually give you a clue of how to fix the error.
  10. If everything went well, you should see a bunch of new files in the Generated folder. If you do not see any files, then you may need to click on the Show all files button located at the top of Solution Explorer. Select all of the files, and right click and choose "Include In Project". Now compile, and voila! You have just created your first SubSonic DAL!
  11. Screenshot - show_all_files.jpg

Using the DAL

You have officially created your first SubSonic DAL. Now, you are probably wondering... how do I use this thing? Well, here are some sample code snippets.

Sample No. 1 - Fetching all of the products that have a ListPrice greater than $50.00, a Class value of L, and is the Color Yellow.
Query qry = Product.CreateQuery().WHERE("ListPrice > 50.00")
            .AND("Class = L").AND("Color = Yellow");
IDataReader rdr = qry.ExecuteReader();

while (rdr.Read()) {

Notice how I chained the where conditions!

Sample No. 2 - Adding a new product.
Currency c = new Currency();
c.CurrencyCode = "CPD";
c.Name = "Code Project Dollar";

Tips & Thoughts

If you need to extend the functionality of a class, then use partial classes. This way, when you re-generate the files, you won't have to worry about having your custom code overwritten.

This product states that it works with other database systems. This library could be very useful if you are creating an application that runs against multiple database systems. In theory, with a little work and some basic know-how, you should be able to move your application from SQL Server to Oracle without changing your code. You would simply just recompile your DAL against the new RDBMS (Remote Database Management System). Remember, I said in theory! Don't send me hate emails if this does not work.

Finally, SubSonic is not only a big time saver, but it is free, it is Open Source, and it is very easy to use. What more could you ask for?


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Michael Ceranski
Software Developer (Senior) Concepts2Code
United States United States
Michael is the co-founder and master consultant for Concepts2Code, a software consulting company based in Buffalo, New York. He's been programming since the early 1990's. His vast programming experience includes VB, Delphi, C#, ASP, ASP.NET, Ruby on Rails, Coldfusion and PHP. Michael also is a Microsoft Certified Application Developer and a Certified Technology Specialist for SQL Server.

Visit his blog.

Comments and Discussions

QuestionStored procedure class is containing only namespaces Pin
ROBINKB22-Jan-14 23:16
MemberROBINKB22-Jan-14 23:16 
GeneralSubSonic is by far the cost-effective DAL tool. Pin
Pham Dinh Truong29-Apr-13 0:16
professionalPham Dinh Truong29-Apr-13 0:16 
GeneralUnable to find sonic.exe Pin
Member 210546522-Jul-10 9:46
MemberMember 210546522-Jul-10 9:46 
Questioni can not use subsonic? Pin
yusufkaratoprak24-Mar-10 23:41
Memberyusufkaratoprak24-Mar-10 23:41 
AnswerRe: i can not use subsonic? Pin
Michael Ceranski25-Mar-10 3:03
MemberMichael Ceranski25-Mar-10 3:03 
GeneralRe: i can not use subsonic? Pin
yusufkaratoprak25-Mar-10 4:31
Memberyusufkaratoprak25-Mar-10 4:31 
GeneralRe: i can not use subsonic? Pin
yusufkaratoprak25-Mar-10 4:45
Memberyusufkaratoprak25-Mar-10 4:45 
Questionhelp Pin
frank.d.zu20-Nov-07 22:01
Memberfrank.d.zu20-Nov-07 22:01 
QuestionLINQ? Pin
Mike Lang9-Nov-07 4:59
MemberMike Lang9-Nov-07 4:59 
AnswerRe: LINQ? Pin
Michael Ceranski9-Nov-07 5:07
MemberMichael Ceranski9-Nov-07 5:07 
GeneralWHERE("ListPrice > 50.00") Pin
Thanks for all the fish9-Nov-07 4:25
MemberThanks for all the fish9-Nov-07 4:25 
GeneralRe: WHERE("ListPrice > 50.00") Pin
Michael Ceranski9-Nov-07 4:37
MemberMichael Ceranski9-Nov-07 4:37 
GeneralRe: WHERE("ListPrice > 50.00") Pin
jonnii9-Nov-07 4:47
Memberjonnii9-Nov-07 4:47 
GeneralRe: WHERE("ListPrice > 50.00") Pin
Thanks for all the fish9-Nov-07 5:04
MemberThanks for all the fish9-Nov-07 5:04 
GeneralRe: WHERE("ListPrice > 50.00") Pin
Michael Ceranski9-Nov-07 5:09
MemberMichael Ceranski9-Nov-07 5:09 
GeneralRe: WHERE("ListPrice > 50.00") Pin
Thanks for all the fish9-Nov-07 5:31
MemberThanks for all the fish9-Nov-07 5:31 
GeneralRe: WHERE("ListPrice > 50.00") Pin
miies9-Nov-07 5:22
Membermiies9-Nov-07 5:22 
GeneralRe: WHERE("ListPrice > 50.00") Pin
Michael Ceranski9-Nov-07 5:32
MemberMichael Ceranski9-Nov-07 5:32 
GeneralRe: WHERE("ListPrice > 50.00") Pin
miies9-Nov-07 6:15
Membermiies9-Nov-07 6:15 
GeneralRe: WHERE("ListPrice > 50.00") Pin
Thanks for all the fish9-Nov-07 5:41
MemberThanks for all the fish9-Nov-07 5:41 
GeneralRe: WHERE("ListPrice > 50.00") Pin
Michael Ceranski9-Nov-07 5:46
MemberMichael Ceranski9-Nov-07 5:46 
GeneralRe: WHERE("ListPrice > 50.00") Pin
miies9-Nov-07 6:11
Membermiies9-Nov-07 6:11 
GeneralRe: WHERE("ListPrice > 50.00") Pin
chyde7412-Nov-07 16:53
Memberchyde7412-Nov-07 16:53 

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

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