Common Database Interface

  • Thread starter Thread starter Lourenço Teodoro
  • Start date Start date
L

Lourenço Teodoro

I am trying to develop a program that would work with any database using
ADO.NET. I am having a hard time because of the differences in SQL syntax
between databases.

One example is when I try to specify the column name, some databases require
that I put the name between square brackets, others between quotes, etc. I
had a similar problem with data/time fields, but I could solve it by using
an algorithm with reflection and tags in the SQL sentences. I would really
appreciate if anyone could tell me if there is a standard syntax to specify
column names that all the databases should accept. At this point I concluded
that I will have to create settings in my program to enable the user to
enter with this format, but something automatic (as I found for date) would
be much better.

My deadline for this project is next week, so I would appreciate if anyone
could send me feedback as soon as possible.

Regards,
Lourenço.
 
Hello Lourenco,

try QuotePrefix-Property and QuoteSuffix-Property of the Command-Object.

myCommand.QuotePrefix = "["
myCommand.QuoteSuffix = "]"

Steffi
 
I don't think you can get away with something as simple as a different "" or
{} or [].

I use a set of classes to return the correct SQL string for a given database
type.
Now that Oracle (finally!!) supports the Inner Join syntax the number of
queries that are different is really reduced.

The classes contain the CRUD methods as well as many other common methods.
Inheritance allows me to re-generate the code for the base level and add
custom code to the child level.
 
I think that your solution is good. The only problem is that the
property that you mentioned is in CommandBuilder and not in Command.
Unfortunately, CommandBuilder is not derived from a commom interface
like Command is (IDbCommand) (by the way, does anyone have any idea why
there is no interface such as IDbCommandBuilder?). This will cause me a
problem when trying to make something generic that would work with any
provider.
The solution that I found is to use reflection to create the command
builder and use InvokeMethod instead of the interface to perform the
method calls. Could you please let me know what do you thing about this
solution? Is there anything easier that I could do?
 
Hi

I have written a couple of articles about genereic database programming
with .NET using our framework Mimer Provider Manager. You might want to
check out http://www.codeproject.com/cs/database/MpmCodeproject.asp for
example.

Mimer Provider Manager is a free open source framework that sits on top
of ADO.NET and lets you define data sources in a Mimer Provider Manager
Administrator outside of the application. You don't have to chagne a
single line of code in your application to swith database and there is a
feature called SQL Filter that handles the different ways to parameter
markers.

You can find Mimer Provider Manager at http://developer.mimer.com/mpm or
http://www.sourceforge.net/projects/mimerpm

Regards,
Fredrik
 
Back
Top