Parameterized queries to different data providers

[Dino Buljubasic]

Hi,

I would like to build my app so it can connect to SQL Server, Oracle
or mySQL and query data.
Data queries should be parameterized to reduce impact of SQL
Injection.

SQLServer Dataprovider uses named parameters (i.e. @name)
OracleClient Dataprovider uses named parameters (i.e. :name)
mySQL ODBC Dataprovider uses question marks (i.e. ?) on the place of a
parameter (so order of question marks is important)

How do I write my queries easily so they will work with all 3 of these
data providers?

For example how do I write this to work with all tree:

SELECT a1, a2 FROM table1 WHERE a1 = 'blah' AND a2 = 'blahblah'

thank you
_dino_

 

[Miha Markic [MVP C#]]

Hi Dino,

I don't think that there is an easy solution other than doing naming based
on provider manually.
Perhaps you might look into ORM products that do that part for you?

 

[Marina]

This is why it is virtually impossible to write one code base that works for
multiple database engines. There are just too many differences among them,
and invariable there has to be provider specific code.

 

[Martin Robins]

I have written a utility function that corrects parameter names based upon
the xxxCommand object being used; my data access layer then pre-processes
each command object before executing it.

For info: SqlServer requires "@name" - correct; Oracle requires "name" or
":name" - the colon being optional and Odbc ignores parameter names instead
relying on the parameter order being correct so providing that you always
order your parameters in the sequence that they are used, you can then
simply use names and then pre-process to add "@" for SqlServer (I found
regular expressions were the easiest way for this).

 

[IUnknown]

You may need OleDBPro for dotNet at http://www.udaparts.com/oledbpronet.htm

For sure, it will work for all of databases using one copy of source code
only!