Subsonic, MySQL, and DAL DLL

Introduction

In this article, I attempt to tackle some of the peculiarities in using SubSonic to generate a DAL in a DLL, while using MySQL as our database. Why you might ask? Simply as most code samples on the WWW cover SQL Server as well as fail to show the problems that occur when using SubSonic itself.

Contents

1. What is Subsonic?
2. Configuring Subsonic for MySQL
3. Configuring Subsonic in Visual Studio 2008
4. Generating and including the output into your project
5. Managing the configuration file (basic compilation problems)
6. Dynamic custom providers (multiple dynamic providers)

1. What is SubSonic?

Subsonic is a toolset that helps a website build itself. At its core, it's:

• A Data Access Layer (DAL) builder that requires no code on your part, it builds itself at compile-time with a full object layer and strongly-typed collections
• A complete utility toolset, complete with Rails-like scaffolding, migrations (DB versioning), and code generators
• A dynamic query tool that lets you use SQL Server and the Enterprise Library without having to know SQL
• An OR Mapper that extends to Views and Stored Procedures so you're not locked into the OR/M thing

With that being said, Subsonic is a tightly-coupled DAL builder that allows for an almost SQL like interaction into data classes that are a representation of your database. But is this not what LINQ provides? But there is no stable provider for MySQL as yet.

Moving on ...

2. Configuring SubSonic for MySQL

The Beta works - 2.1 Beta 2 (March 13 2008)

I've tried and tried but was unable to get the current stable version of Subsonic to work as I kept getting the following error:

"System.InvalidOperationException: Connection must be valid and open"

This, from my investigations, seemed to involve updates to the MySql.Data.dll and SubCommander utility found within Subsonic. My point, use the Beta, it works!

Download, install

You can download and install the beta from the official website, here is the link.

Start a new project

Create your new project and choose Class Library, name it appropriately.

Add DLLs

Next, add the reference for Subsonic - they can be found in your Program Files directory.

Only the following will be required:

1. MySql.Data.dll (use only this copy found in the Subsonic sub-directory)
2. SubSonic.dll

Great start, now we just need to setup our configuration file to build our class libraries or DAL. But before we do that, let's make Subsonic (Commander) part of an external tool in Visual Studio 2008 for ease of use.

3. Configuring SubSonic in Visual Studio (2008)

Configuring an external tool

1. Under the "Tools" menu, click External Tools.
2. Now click "Add" to create a new menu Contents.



3. Name the "Title" anything of your choice. In my case, the Title is "Subsonic DAL".
4. Secondly, make the "Command" textbox point to the "SubCommander" executable found in the SubSonic Program Files folder. The name of the file is "Sonic.exe".
5. Once complete, add in the initial arguments. Please note, "/out Generated" will dump all output to the newly created folder "Generated" within the specified "Initial Directory" location.
6. The "Initial Directory" should be pointed at your project's Main directory. Expand the arrowed tab to find other Environment Variables within VS2008.
7. There are other arguments available, so feel free to read up on them at the Subsonic main page. Once the last step is complete, click OK and there should be a new button found on one of your tabbed Workspaces within Visual Studio 2008.

Clicking on this button would automatically initiate the Build/generation process for your DLL, but first we need a configuration file.

4. Generating and including output

We're not there yet, but once you have generated the DAL successfully, it will generate a folder in your project which won't be visible. To bring this folder into your project for inclusion, click on the "Project" menu and select "Show All Files".

You will then notice the "Generated" folder (provided that was your desired output folder), right-click and then say "Include in Project", and it's done.

5. Managing the configuration file

Basic configuration file

Configuration files are more or less self-explanatory and as such there is no need to go into too much detail around the configuration itself. The configuration file for Subsonic requires three parts:

Configuration section service definition

Configuration for Connection Strings

This section is merely the definition of your databases from which you would like to generate the DAL. The provider name is important for Subsonic, to ensure the correct library is used to gain access to the MySQL databases. In the above situation, I've excluded the "Password", you might have a need for it.

Configuration settings for providers relative to Connection Strings

This is the actual definition of the Subsonic service and its providers, which point back to the Connection Strings specified above. The entire configuration as a whole points to a multiple database configuration option, I only mention this as I struggled for some time just to be able to get the configuration correct.

Compilation problems (namespace, multiple DAL)

6. Dynamic custom providers

Using multiple providers

For being able to use multiple providers, you need to be able to fetch the schema for the table or object you are going to use and then actually make the call for the data. To access multiple data providers specified in your configuration (e.g., Helpdesk and Super), check out this code:

Instead of accessing the table objects directly, we would need to use the Query class for building and accessing data. In the above situation, the first argument is our table name within our database, and the second is the provider name defined in the configuration file above (App.config).

Now the second line makes the query possible, as Subsonic requires the table schema before it can manipulate and fetch data from the database.

Building dynamic providers with Connection Strings from a MySQL database

Now if you have an environment that has multiple databases, then maintaining an App.config with all the connection strings is by no means practical. So as we developers often do, we place connection strings in a database table. Here is the code to build an in-memory data provider:

The first step is creating a provider which we can use, and in our case, this is created using the SubSonic API class DataProvider. Notice that there are two parameters in creating a new instance of the data provider with a MySqlDataProvider type.

The next interesting point is the use of the format of the Connection Strings. Subsonic expects them to look like that specified in the configuration file above; this being different from the usual "Driver={Driver...}" connection string. The code is shown below (thanks Andre):

Finally we just create our new Query class, instantiate with our new in-memory provider, and we are done.

There you go!!!!