Mulitple DB Vendors in distributed application

Guppy posted on Thursday, August 30, 2007

Hi

I'm currently in the process of reading the C# 2005 book and only up to chapter six, so there is a chance this is answered in a future chapter, but my mind is wondering off and has a query that I was hoping someone could point me in the right direction of.

The system I would like to use CSLA for would run on the desktop and a web server, with the master DB being behind the web server. The desktop client can work remotely and then sych through a web service as needed.

On the desktop I had planned to use the embedded MS Sql Express option, but doubt very much if we'll have SQL Server on the web server (more likely MySQL or Informix).

The query I had was how should I be structuring my project so that I can have the same (by same I mean version) business objects running on both platforms and support different DB backends easily?

I appreciate that I will be effectively providing three interfaces, web, web service and windows forms, however I clearly want to have a common business object library which uses a different DB depending on where it is.

I am not sure yet (as I've not got to the web services chapter) if I will have implement the web service myself or follow Rockford's approach.

If anyone has any guidance on where I should be looking for explinations on this, or how they would layout the project I would be very greatful.

Thank you.

Glenn

LiamGrossmann replied on Thursday, August 30, 2007

The standard CSLA templates do not support databases from other vendors - they are geared towards SQL Server.

If you want to support multiple databases (from differenct vendors) you should look to extending the CSLA templates to use the Microsoft Data Access Application Block - part of the Enterprise Patterns and Practices (http://msdn2.microsoft.com/en-us/library/aa480453.aspx)

I'm not too sure how well the MS DAAB will cope with MySQL or INformix but it does work for DB2 and Oracle and does abstract your DAL so that it is database vendor independent.

Guppy replied on Thursday, August 30, 2007

That sounds like it could be more work that I would have hoped for. We're only considering another product for the central database instead of sql server as we have in-house management skills for it.

I assume we wouldn't have any problems if we had sql server express on the clients and then sql server on the central db? I assume there will be a way of telling the software which db server to use.

Clayton replied on Thursday, August 30, 2007

MS's Enterprise Library Data block should serve your needs. With it you can write your data access code one way in your business objects, and use the configuration files in each location to determine the database to use.

The other trick will be to use your stored procedures as the interface into your database. If you write your stored procedures with the same names, parameters, etc., I believe you may be able to get the result your looking for. I'm not familiar enough with non-MS databases to say for sure though.

It should be easy enough to write a small test harness to verify and the results of a successful test will be worth the effort.

JoeFallon1 replied on Thursday, August 30, 2007

The MS DAAB is a nice piece of code.

I wrote something similar before it was published and then just reviewed it to ensure I hadn't overlooked anything major.

It basically abstracts all the ADO.Net commands so you can do stuff like:

DAL.ExecuteScalar("SomeQuery") and get back a result in your BO.

You also have access to all the other ADO commands - you just don't have to bother setting each one up in your DataPortal_Fetch anymore.

The key thing is that it returns the Interfaces for each object (like IDataReader, ICommand, I...etc)

This allows you to use any provider at runtime. I simply set a value in my config file and I can switch databases.

If you have a choice though - use the same DB server everywhere to keep things simple and to leverage the power of that DB server. I always get stuck with the lowest common functionality.