none
Architecture to support multiple db RRS feed

  • Question

  •  

    Hi,

    We are in the midst of designing ASP.NET application using Visual Studio 2005(vb) . At the moment, it's being developed using Oracle 10g. One of the main requirement is to be database INDEPENDENT. Normally we also use triggers, stored procedures. the projects are separated into 3 layers.

     

    At the minimum it should support at least Oracle db and MS SQL server 2005.  How would be the architecture would be in this scenario? The software may be deployed at place where the end customer may already have license for Oracle or SQL server 2005. So, we cannot ask them to get addtional license for the moment.

     

    Also, some of the SQL syntax for both oracle and sql server may be different? So, how to tackle this issue?

    Even the stored proc's syntax different?? Also, any main issues when developing Transaction screens??

     

    Any suggestions/recommendations would be a great help...


     http://www.c-sharpcorner.com/UploadFile/leonpere/EnterpriseLibrary11142005021940AM/EnterpriseLibrary.aspx

    thanks.

    rama arumugam

    Friday, May 23, 2008 9:41 AM

Answers

  • Hi Rama,

     

    Enterprise Library Data Access Application Block would be the best in your scenario (as you mentioned in the link). You can create two different versions of "database create script" and run appropriate version during deployment. I believe it would be pretty hard to create one common script that would be applicable for both MS SQL and Oracle databases (also in this case you wan't have an ability to use specific features of each one).

     

    Friday, May 23, 2008 12:51 PM
  • I agree with Vitaliy's comments on using Enterprise library's DAAB.  It would shield you from having to

    create custom code.

     

    Also, if you want to have stored procedures that would work both on sql server and oracle (or mysql, etc.,) then stick to ANSI SQL statements rather than database specific sql statements.  I know it is hard to resist the urge and flexibility when you use vendor specific sqls, but hey if you want to switch between different databases, then this approach would fit.

     

    On the other hand, look at NHibernateThis is a well matured ORM tool, that shields you from using different databases.  You map the database tables to .NET objects and interact with the objects like any other .NET object.  NHibernate would generate sqls on the fly, so yes you wont be using stored procedures.  If you ask me, I would pick NHibernate approach (It is a mature open source ORM, very popular in JEE world).

     

    Hope this helps.

     

    Gaja

    Friday, May 23, 2008 2:29 PM
  • Its nice to see a mention of NHibernate instead of the usual posts here that mention just the items from the enterprise library, other options available are Subsonic and Castle ActiveRecord (which uses NHibernate)

    Friday, May 23, 2008 7:30 PM
  • You could also look at LINQ?

     

    It's a technology, so make sure that it's right for what you want though.

     

    Martin.

     

    Thursday, June 5, 2008 10:39 PM

All replies

  • Hi Rama,

     

    Enterprise Library Data Access Application Block would be the best in your scenario (as you mentioned in the link). You can create two different versions of "database create script" and run appropriate version during deployment. I believe it would be pretty hard to create one common script that would be applicable for both MS SQL and Oracle databases (also in this case you wan't have an ability to use specific features of each one).

     

    Friday, May 23, 2008 12:51 PM
  • I agree with Vitaliy's comments on using Enterprise library's DAAB.  It would shield you from having to

    create custom code.

     

    Also, if you want to have stored procedures that would work both on sql server and oracle (or mysql, etc.,) then stick to ANSI SQL statements rather than database specific sql statements.  I know it is hard to resist the urge and flexibility when you use vendor specific sqls, but hey if you want to switch between different databases, then this approach would fit.

     

    On the other hand, look at NHibernateThis is a well matured ORM tool, that shields you from using different databases.  You map the database tables to .NET objects and interact with the objects like any other .NET object.  NHibernate would generate sqls on the fly, so yes you wont be using stored procedures.  If you ask me, I would pick NHibernate approach (It is a mature open source ORM, very popular in JEE world).

     

    Hope this helps.

     

    Gaja

    Friday, May 23, 2008 2:29 PM
  • Its nice to see a mention of NHibernate instead of the usual posts here that mention just the items from the enterprise library, other options available are Subsonic and Castle ActiveRecord (which uses NHibernate)

    Friday, May 23, 2008 7:30 PM
  •  

    Depending on the size and complexity of the application you may want to look at using Reflection to load the data access assembly at runtime.  The general theory is to create a static data access object in the business object itself, then call a data access factory that uses the application configuration file to load the correct data access assembly (i.e.  You create an assembly for SqlServer, Oracle, etc... and load them at runtime).  Many people will tell you the overhead is too high; however, this works very well for most applications.  Only very high transaction systems should have a problem with it.  The best place to see this method in practice is in Microsoft's PetShop application for .Net.  This way you have one code base and a web.config change is all it takes to changes sources.
    Wednesday, June 4, 2008 12:48 AM
  • You could also look at LINQ?

     

    It's a technology, so make sure that it's right for what you want though.

     

    Martin.

     

    Thursday, June 5, 2008 10:39 PM