Subsonic, MySQL and DAL DLL

Introduction

In this article I attempt to tackle some of the peculiarities in using SubSonic to generate a DAL into 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 failing to show the problems that lie in 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 say someone……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 working 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 beta from the Official Website, here is the link anyways.

Start a New Project

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

Add DLLs

Next add the reference for Subsonic – they will 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 into Visual Studio 2008 for ease of use.

c)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” to 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, the “ /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 Projects 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 that 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.

d)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.

e)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. 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 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 make mention of this as I struggled for some time just to be able to get the configuration correct.

Tip. Now what you might notice different from the Subsonic Documentation in the “Provider Section” above (c), is that the “type” has been renamed from "ActionPak" to "SubSonic". Why? It was previously named ActionPak and now the Namespace has been changed.

Compilation Problems (namespace, multiple DAL)

Tip. Points worth noting, is that Subsonic will not generate the DAL for you unless all your tables in your Database contain “PRIMARY KEYS”. This is a design decision as far as I know, and revolves around some standard of integrity as opposed to none.

Tip. In you have intentions of using different databases then just that of a singleton, from which you used to generate the DAL itself; it’s important to know that you need to at least fetch the schema from that built-against Database, so that you can manipulate the Subsonic API. Huh? I’ll explain further in the Multiple Provider section, just keep this in your head.

Tip. Sometimes you might have compile issues once you have included your Generated class libraries, and the two that I encountered revolved conflicts with:

a) A generated class from a table called System, which conflicted with the .NET System namespace. Thus you can exclude that Table from the Project, Delete it, or rename you database table.

b) This one is relative to .NET, it does not allow variables to have names that begin with numbers, and in this case, you might have fields in a database that are called “1stEscalation” or “2ndEscalation” for example. The fix behind this, is to just insert a character or letter before these variables using the “Replace” functionality found under the “Find” popup menu in Visual Studio. In my case, everything ‘1st” I renamed to “_1st”.

f)Dynamic Custom Providers

Using Multiple Providers

In being able to use multiple providers, one needs to be able to fetch the schema for the table or object one is going to use, and then to actually make the call for the data. To access the multiple data providers specified in our configuration (i.e. Helpdesk and Super), check out the code:

Instead of accessing the Table Objects directly, one 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, 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 the 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, which can be accessed:

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 2 parameters in creating a new instance of Data Provider, with a MySqlDataProvider type.

The next interesting point is the use that 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. Code is below (thanks Andre)

Finally we just create our new Query class, instantiate with our new in Memory Provider and you’re done.

There you go!!!!

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 7 of 7 (Total in Forum: 7) (Refresh) FirstPrevNext

What to do next? Member 4119496 4:09 3 May '09   Hi Dane, I found this good tutorial for newbie like me. I have couple questions, what to do next after successfully generated the dll? Suppose for simple example i want to bind a table to datagrid/grifview or something similar like that. Thanks for your answer, I have reviewed many orms out there, seem they have great support for (mostly) ms sqlserver, but not mysql. Sign In·View Thread·PermaLink Re: What to do next? Dane-Garrin Balia 5:26 7 May '09   Hi It's been ages since I've used subsonic; but I found an old project, and here is some of it's source which might be able to help you. Forms_Load #region Populate the Combo Box with the Customer Names DataSet ds = dm.PopulateCustomerCombo(); try { comboBox1.DataSource = ds.Tables[0]; comboBox1.DisplayMember = ds.Tables[0].Columns["Description"].Caption; } catch (Exception err) { throw new Exception("Failed to get DataSet Field Name" + err.ToString()); } #endregion #region Populate the ComboBox with Fields from the DB comboBox2.Items.AddRange(dm.PopulateFieldsCombo()); #endregion dm is an instance of my Data Manager (that uses Subsonic) #region Get the Schema /// <summary> /// Populates the customer combo. /// </summary> /// <returns></returns> public DataSet PopulateCustomerCombo() { DataSet ds = new DataSet(); try { dbQuerier.SetSelectList("Description"); dbQuerier.ORDER_BY("Description"); ds = dbQuerier.ExecuteDataSet(); } catch (Exception e) { throw new Exception("Failed to get Schema" + e.ToString()); } return ds; } #endregion HtH Sign In·View Thread·PermaLink Provider creation? mikesomething 21:01 20 Aug '08   Nice article. Quick question... What's the advantage of using Activator.CreateInstance over directly created the derived object you need as in SqlDataProvider oldDB = new SqlDataProvider(); oldDB.DefaultConnectionString = oldDBConnection; oldDB.Initialize("oldDB", new System.Collections.Specialized.NameValueCollection()); DataService.Providers.Add(oldDB); Sign In·View Thread·PermaLink How do you enable Transactions here? Chua Wen Ching 2:44 4 Jul '08   Hi I have 2 questions on using subsonic with MySQL. I faced this problem with MySQL Connector. Wonder Subsonic can solve this problem. a. 1 transaction - multiple insert into the same MySQL // Error: Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported. b. 1 transaction - a) insert to SQL Server, then b) insert to MySQL // MySQL Connector/Net does not currently support distributed transactions. Any help? Thanks. Best Regards, Chua Wen Ching Sign In·View Thread·PermaLink 1.00/5 (1 vote) Re: How do you enable Transactions here? Dane-Garrin Balia 2:57 7 Jul '08   Hi Good question. I don't think Subsonic could solve (a), as it opens and closes connections based on the object of choice I would assume. Seeing that an individual table is an object...make's me wonder. Also I believe, 2 connections used for getting the table defition and secondly to get the actually data relative to the now gotten metadata. On your second point, I think it should be possible using SubSonic to switch between 2 Providers on the fly, as defined in the configuration file (web.config). Hope I answered your question. Sign In·View Thread·PermaLink Nice article.... bt 1 question 4 u... Krunal Mevada 0:53 17 May '08   hi, very good article for subsonic. but when we use subsonic and generate classes for objectdatasource. these classes does not support the sorting for gridview. sorting is not available in built in subsonic generated classes. we have to write custom code for that. pls suggest something on this. thanks, krunal Sign In·View Thread·PermaLink Re: Nice article.... bt 1 question 4 u... Dane-Garrin Balia 10:48 18 May '08   Good question, will do some work on it when I get back to the office. Thanks Sign In·View Thread·PermaLink

Last Visit: 6:30 21 Nov '09     Last Update: 6:30 21 Nov '09 1