Why not build your own, simpler interfaces? I built a set of interfaces,
with default implementations for Access and SQL Server. Talk to Access
with OleDbConnection, SQL with SqlConnection. Business objects implement
a higher-level interface [1] that lets them instantiate an object's
properties from a table row, which in turn works with the implementation
of this interface. If/when I have to support another DBMS, I'll just
write another implementation against the IFTDBServices interface, and
applications won't know any different. Obviously, the SQL syntax used
must be supported by the DBMS, but is really a separate problem IMHO.
Here's the core interface I use to talk to a DB:
using System;
namespace FinnaTech.Data.IFTDataServices
{
/// <summary>
/// FinnaTech database services interface.
/// </summary>
public interface IFTDBServices
{
string ConnectString{get; set;}
IFTDataServices.FTDBType DBType ();
void CloseConnection();
bool Connected();
void CreateConnection();
string DataRead(string SQL, string className); //Sends
back an XML doc as a string. Can easily be overloaded to send back a
stream, the doc itself, etc.
bool DataWrite (string SQL, System.Data.CommandType ct,
bool useTrans);
string GetSQLToken (IFTDataServices.FTSQLTokens sqt);
void TranBegin();
void TranCommit();
void TranRollback();
}
}
The GetSQLToken method provides limited support for building SQL strings
for different DBs by returning the appropriate delimiter for passing
strings within single quotes, dates in yyyymmdd format, a standard date
format for the DB, etc.
[1] This interface translates table columns to object properties, but
does not hard-code the columns to properties. Instead, a collection of
field objects is built based on the field names passed in. SQL
statements are generated based on the fields used by that particular
instance of the business object. Enormously less maintenance than
hard-coding columns to properties:).
-----Original Message-----
From: dotnet discussion [mailto:[EMAIL PROTECTED]] On Behalf Of
Murphy, James
Sent: Tuesday, April 30, 2002 2:18 PM
To: [EMAIL PROTECTED]
Subject: [DOTNET] How are you supporting multiple DB vendors?
I'm curious what folks are using to abstract the underlying DBMS from
your
.NET apps. I know lots of shops that just pick a RDBMS and run with it
-
and have simpler designs - that would be nice. But lets say you need to
support both ORACLE and SQL Server?
You can program to the ADO.NET interfaces so instead of SqlConnection or
OleDdConnection use IDbConnection or use OleDdConnection always and
sacrifice performance. But what about the actual SQL statements? How
do
you virtualize the variants? String tables in resource only assemblies?
Do
you put everything in stored procs and simplify the SQL embedded in your
code? Or Don't use stored procs because you have SQL statement
generation
code a la RogueWave's DBTools.
I'd love to hear all strategies big and small.
Thanks
Jim
You can read messages from the DOTNET archive, unsubscribe from DOTNET,
or
subscribe to other DevelopMentor lists at http://discuss.develop.com.
You can read messages from the DOTNET archive, unsubscribe from DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.
