A way to transfer a query string for SQL Server to one for another database?

[Quentin Huo]

Hi:

If I have a query string for retrieving data from SQL Server database, is
there a way to transfer it to a query string for retrieving data from Oracle
or any other database like mySQL...? I mean by a program. I am working on
C#.

Thanks

Q.

 

[Peter Rilling]

SQL is a standards language and therefore you should be able to use the same
query on any relational database, assuming you are not using any MS
proprietary extensions. All you have to do is to use the SqlCommand object
on a different IConnection object.

For instance, "select * from myTable" should work for all database.

 

[Pete Davis]

If you're using the OleDb or Odbc classes instead of the Sql classes, then
you can use the same code and simply change the connection strings for
different database types. Your SQL needs to be compatible with all of the
databases, though. For example, you can't use SQL Server specific functions
in your query.

Pete

 

[Joe Fallon]

If you store your query string in a Shared method of a class you can
retrieve it by the method name (and use any parameters you might need.). You
could also pass in the DBtype (or read it from config) and the return a
different string from the same method for the other DB type. If the syntax
is identical you just return the same string.

e.g.

Public Shared Function GetMySQL() As String
Return "SELECT fld1 FROM table1"
End Sub

Public Shared Function GetMySpecialSQL(DBType As String) As String
If DBType="SQL Server" Then
Return "SELECT Top1 fld1 FROM table1"
ElseIfDBType="Oracle" Then
Return "SELECT fld1 FROM table1 WHERE rownum=1"
End If
End Sub