Re: How to do non dependence on database vendor?


"Brett" wrote...
> If I want to easily swap the database I'm using, what is
> the best method for developing that tier in my application?
>
> I'll have basically a 4 tier app:
> 1. presentation
> 2. business logic
> 3. data layer containing standard SQL compliant queries
> 4. any database
>
> I'm looking for the most efficient way to design tier 3. I can't use
> stored procedures because those are specific two a particular database.
> Using queries in an app layer is less efficient than SPs but there isn't
> much choice at this point.
>
> Any comments in general on this design are welcome.

"Any comments"... ;-)

Alright then...

>From your specifications I would split the "data layer" really into two.

3a. data layer containing standard SQL compliant queries
3b. A "pluggable" DB-proxy

This would mean that the proxy would be specific for a database, but would
be easy enough to implement for other DBs as you'd need them, so you could
exchange it, even "on the fly".

Using reflection you could even make some of the implementations enough
flexible to be able to be used in e "generic" way, i.e. for more than one
database...

The key to this solution is to *not* expose any DB-specific classes from the
proxy, only the necessary interfaces (IDbConnection, IDbCommand, etc).

But as I said, this was based "from your specifications", and you said you
accepted "any comments"... ;-)

My first thought was actually to include yet another layer between the
business logic and the data layer, on a higher abstraction level than to use
SQL queries, as not all databases even support standard SQL...

// Bjorn A





.

Relevant Pages

  • Re: Structuring of large application
    ... Have you thought about possibly writing the business logic & data layer in ... one application as a web service, and writing the UI in your regular web ... Again your central login database could be another web services ... > At the centre is the User login facility - everyone using the site must ...
    (microsoft.public.dotnet.framework.aspnet)
  • How to do non dependence on database vendor?
    ... If I want to easily swap the database I'm using, what is the best method for ... I'll have basically a 4 tier app: ... data layer containing standard SQL compliant queries ... I'm looking for the most efficient way to design tier 3. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQL2000/ASP.Net performance question
    ... functionality is not currently supported by the existing data layer however ... >> from the web form will generate a record in a single table in the ... >> Given my current data layer I'm connecting to the database for each ...
    (microsoft.public.sqlserver.programming)
  • Re: Creating user friendly error messages for SqlExceptions
    ... The message you get back from SqlException would say something like 'attempt to delete row failed because of FK product_category'. ... This data layer would handle the error and then could throw another error with a more user friendly message. ... The database is unavailable for whatever reason like network issues or server problems. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SQL2000/ASP.Net performance question
    ... > I'm building an ASP.Net app that will process a web form containing a few ... the results going into a SQL database. ... This would require modifying the data layer ... letters of the control name were the field name it would be stored in the ...
    (microsoft.public.sqlserver.programming)