Hey - this is great. The interface is a little flat for me but I could imagine extending it slightly to return lower level objects if I needed them. Like an IDataReader for things that return rowsets.
I'd be interested to learn a little more about GetSQLToken (IFTDataServices.FTSQLTokens sqt). The SQL syntax used to generate the queries is a large part of what I'm talking about. Bridging Object-Relational gap is something I'm looking into as a follow-up. Your method [1] looks really promising. Do you provide a schema generation capability with this approach too? Jim > -----Original Message----- > From: Dan Souk [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 30, 2002 4:46 PM > To: [EMAIL PROTECTED] > Subject: Re: [DOTNET] How are you supporting multiple DB vendors? > > > 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. > You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
