1

I have an application written in C# which is currently using a mixture of datasets and some hardcoded SQL statements/stored procedures which uses Advantage Database Server.

I now want my application to also support MS-SQL.

I have a few options :-

1) Branch the source code and manually make the syntactical alterations to enable MS-SQL support. The downside of this is obviously multiple codebases.

2) Move all the DB code into 2 separate libraries (one for ADS, one for MS-SQL) and give the end user the correct library depending on the DB. I am not sure how to set this up, ideally I would like to have both library projects in my VS solution and be able to switch between using them, preferably at debug and run time. Is this possible?

3) Start using a DAL which supports both db backends. If I was to start using EF, would it be possible to generate the schema initially based on the existing ADS database, then switch the connection at run time to a MS-SQL db using the same schema, or would it be better to create two contexts/schemas for each database and somehow switch them at runtime i.e. is it possible to create a method that returns the relevant context via a configuration setting e.g.

dbcontext = new GetDBContext();

DbContext  GetDBContext()
{
   if( configsetting == "ADS")

      return new AdsDBContext();

   else

     return new MSSQLDBContext();

}

What I want to do is to make incremental changes so the the program continues to run on the existing ADS DB whilst slowly adapting it for MS-SQL/other db backends.

Regards

Mike

Ace Grace
  • 631
  • 1
  • 7
  • 21

2 Answers2

1

If your application is not too complex, I would suggest using DbProviderFactoryclass. Here you will find step by step examples on how to register .NET providers and how to use them. Hope it helps.

darkArk
  • 144
  • 1
  • 11
  • @AceGrace Unfortunately I can't comment only on my own posts, so I will put this here. Entity Framework is a tricky beast :). You said that you wanted to generate the database schema and at runtime you wanted to change the connection string according to your needs, please see [this](http://stackoverflow.com/questions/20216147/entity-framework-change-connection-at-runtime) and [this](http://stackoverflow.com/questions/18841164/change-database-during-runtime-in-entity-framework-without-changing-the-connect). – darkArk Feb 27 '15 at 09:26
  • Hi, Thanks for the links. I don't need to change the connection string at runtime so I suppose (if it works) I could just change the connection string in the app.config file based on whether a customer is using ADS or MS-SQL? – Ace Grace Feb 27 '15 at 16:39
1

The first case is a bad choice. The best way would be the third case but at first you have to convert all your raw statements/queries to ORM. This may be difficult. It depends on their complexity, existing models structure and limitations of the ORM. Most likely you will fail with this way because eventually you need to rewrite significant part of your project (again it depends on structure the existing code). If not, then go this way, otherwise the second case is a golden mean.

neleus
  • 2,230
  • 21
  • 36
  • In addition to darkArk link there is a nice post http://stackoverflow.com/questions/3080601/keeping-an-application-database-agnostic-ado-net-vs-encapsulating-db-logic – neleus Feb 26 '15 at 13:11
  • I am still mulling over whether option 3 is worth the agravation although for parts of my app I am using an AdsExtendedReader class for navigational access to the data. This needs to be re-written regardless in order to function on MS-SQL. – Ace Grace Feb 26 '15 at 15:25
  • Can an entity model created from say my ADS database be switched to using an MS-SQL db just by changing the connection strings as long as the MS-SQL db has been created the same? – Ace Grace Feb 26 '15 at 16:18
  • Unfortunately Im not familiar with ADS. Even if you create "the same" database you may face the problem of difference in SQL dialects unless all your SQL statements/queries are simple and don't use ADS-specific features. – neleus Feb 27 '15 at 08:23
  • I am not intending to use any ADS-specific features except for a few SP's but I would change to work with MS-SQL. Wouldn't EF handle the generation of SQL? – Ace Grace Feb 27 '15 at 14:13
  • After thinking about it a lot I think I will go with Option 2. There is too much code and data access (using Datasets and TableAdapters) to consider levering in the Entity Framework. I will look at it for adding future functionality though. So option 2 it is. Is it possible to have two identical project libraries in a solution that I can switch between when building the application? – Ace Grace Mar 03 '15 at 15:54
  • I would suggest to pick a desired assembly in runtime based on configuration option like connection string or even better some flag. Also please feel free to mark the answer accepted. – neleus Mar 03 '15 at 17:10