SubSonic-based app that connects to multiple databases

subsonicsubsonic2.2

I currently developed an app that connects to SQL Server 2005 database, so my DAL objects where generated using information from that DB.

It will also be possible to connect to an Oracle and MySQL db, all with the same table structures (aside from the normal differences in fields, such as varbinary(max) in SQL Server and BLOB in Oracle, and so on). For this purpose, I already defined multiple connection strings and multiple SubSonic providers for the different DB's the app will run on.

My question is, if I generated my objects using a SQL Server database, should the generated objects work transparently with the other DB's or do I need to generate a different DAL for each database engine I use? Should I be aware of any possible bugs I may encounter while performing these operations?

Thanks in advance for any advice on this issue.

Best Answer

I'm using SubSonic 2.2 by the way....

From what I've been able to test so far, I can't see an easy way to achieve what I'm trying to do.

The ideal situation for me would have been to generate SubSonic objects using SQL Server for example, and just be able to switch dynamically to MySQL by just creating at runtime the correct Provider for it along with its connection string. I got to a point where my app would correctly connect from SQL Server to a MySQL DB, but there's a point where the app fails since SubSonic internally generates queries of the form

SELECT * FROM dbo.MyTable

which MySQL doesn't support obviously. I also noticed queries that enclosed table names with brackets ([]), so it seems that there are a number of factors that would limit the use of one Provider along multiple DB engines.

I guess my only other option is to sort it out with multiple generated providers, although I must admit it does not make me comfortable knowing that I'll have N copies of basically the same classes along my project.

I would really love to hear from anyone else if they've had similar experiences. I'll be sure to post my results once I get everything sorted out and working for my project.

Has any of this changed in 3.0? This would definitely be a worthy reason for me to upgrade if life is any easier on this matter...

Related Solutions

Build Error: ‘System.Array’ does not contain a definition for ‘Columns’,

It sounds to me like a naming conflict with one of the namespaces that's already been declared. Perhaps the error your seeing isn't telling you the whole truth. You may need to use regexDictionaryReplace in the web.config to rename the columns to non-conflicting values.

Sql – SubSonic 2.2 SqlQuery object generates very different sql for WHERE…IN statement for SQL Server 2008 and SQL Server 2005

This may have been fixed already in the source? I suggest you try the most recent source from github to see if the problem has been addressed.

Basically, it's as you said -- the 2005 generator overrides the BuildPagedSelectStatement() method provided by ANSISqlGenerator. The 2008 generator inherits from 2005, so it should use the same paging method as 2005.

This can be seen by comparing these two files:

I believe you would also see the behavior you're describing on a SQL 2000 database since it doesn't override the ANSISqlGenerator.BuildPagedSelectStatement() method.

Also, I don't think compatability level is used to determine the SQL generator:

    public static bool IsSql2008(DataProvider provider)
    {
        return provider.DatabaseVersion.IndexOf("2008 - 10.") > -1 ||
        provider.DatabaseVersion.IndexOf("2008 (RTM) - 10.") > -1;
    }

And, assuming the ANSI generator is what's actually being used, the problem with the AND may be caused by this line in BuildPagedSqlStatement():

    //have to doctor the wheres, since we're using a WHERE in the paging
    //bits. So change all "WHERE" to "AND"
    string tweakedWheres = wheres.Replace("WHERE", "AND");

It needs to be a little smarter than that, although the point you raise about the duplicated clause also needs to be addressed.

Best Answer

Related Solutions

Build Error: ‘System.Array’ does not contain a definition for ‘Columns’,

Sql – SubSonic 2.2 SqlQuery object generates very different sql for WHERE…IN statement for SQL Server 2008 and SQL Server 2005

Related Topic