It is a bit flat, but you could definitely extend it however you needed.
Actually, you could just overload DataRead and add a ref parameter to
return the IDataReader interface you need. Or another overload for
another interface, etc. etc. They're all doing basically the same thing
(reading data out of a db), so I'd try to just use the DataRead method.
GetSQLToken is my way of avoiding hard-coding ' and % all over my SQL.
It also helps support talking to different DBMS at run time, even
reading from one and updating to another.
The different delimiters/tokens are represented in this enumeration:
public enum FTSQLTokens
{
deSQLTk_Concat = 1, //Concatenation character; SQL
= +, Access = &
deSQLTk_DelimQuote = 2, //String delimiter; SQL = ',
Access = "
deSQLTk_DelimDate = 3, //Date delimiter
deSQLTk_DateFmt = 4, //Date format
deSQLTk_TimeFmt = 5, //Time format
deSQLTk_EmbeddedQuote = 6,//Embedded quote
deSQLTk_WildcardDel = 7, //Wildcard used in DELETE
statment
deSQLTk_WildcardSel = 8, //Wildcard used in SELECT
statment
deSQLTk_WildcardLike = 9, //Wildcard used by LIKE
operator
deSQLTk_DateTimeFmt = 10 //Complete DateTime format
};
string IFTDBServices.GetSQLToken
(IFTDataServices.FTSQLTokens sqt)
{
//Return the appropriate token for the database
back-end specified in the connect string
//Only SQL Server in this implementation
string strTmp = "";
string strDF = "yyyyMMdd"; //Case-sensitive
string strTF = "HHmmss"; //Case-sensitive
try
{
switch (mifcDS.DBType()) //SQL Server
{
case
IFTDataServices.FTDBType.deSQL:
switch (sqt)
{
case
IFTDataServices.FTSQLTokens.deSQLTk_Concat:
strTmp = "+";
break;
case
IFTDataServices.FTSQLTokens.deSQLTk_DateFmt:
strTmp = strDF;
break;
case
IFTDataServices.FTSQLTokens.deSQLTk_DelimDate:
strTmp = "'";
break;
case
IFTDataServices.FTSQLTokens.deSQLTk_DelimQuote:
strTmp = "'";
break;
case
IFTDataServices.FTSQLTokens.deSQLTk_EmbeddedQuote:
strTmp = "''";
break;
case
IFTDataServices.FTSQLTokens.deSQLTk_TimeFmt:
strTmp = strTF;
break;
case
IFTDataServices.FTSQLTokens.deSQLTk_WildcardDel:
strTmp = "";
break;
case
IFTDataServices.FTSQLTokens.deSQLTk_WildcardLike:
strTmp = "%";
break;
case
IFTDataServices.FTSQLTokens.deSQLTk_WildcardSel:
strTmp = "*";
break;
case
IFTDataServices.FTSQLTokens.deSQLTk_DateTimeFmt:
strTmp = strDF +
" " + strTF;
break;
}
break;
}
}
catch(System.Exception ex)
{
throw ex;
}
return strTmp;
}
-----Original Message-----
From: dotnet discussion [mailto:[EMAIL PROTECTED]] On Behalf Of
Murphy, James
Sent: Tuesday, April 30, 2002 4:13 PM
To: [EMAIL PROTECTED]
Subject: Re: [DOTNET] How are you supporting multiple DB vendors?
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.
You can read messages from the DOTNET archive, unsubscribe from DOTNET, or
subscribe to other DevelopMentor lists at http://discuss.develop.com.
