Oracle vs MSSQL Parameters

  • Thread starter Thread starter Adam Clauss
  • Start date Start date
A

Adam Clauss

So, we originally wrote our application to use Oracle. As such, we used
specifically declared OracleConnection and use ":" for naming parameters.

Ex:
SELECT * FROM sometable where somefield = :fieldValue

Now, we have a need to port the code to SQL server for another client. I'm
thinking we should do this the "right" way this time, and abstract things
back to IDbConnection rather than OracleConnection or SqlConnection. Then,
we can simply change what type of objects get created, and not the
declarations of usage.

This is good.

What is bad is parameters - Oracle parameters begin wtih ":" whereas SQL
server requires them to be "@". But there does not seem to be a "generic"
way of handling this if we were to abstract back to the interfaces and have
the SQL be compatible with different types of servers. Am I missing a way
to do this?

Thanks!
 
Adam, I believe you *can* (not want but can) just use OleDb and use the ?
for the param marker - I'm not positive on this though. But you really
probably want to at most, use the interfaces and use a factory pattern to
create the specific providers which pretty much leaves you in the same place
if I understand you correctly. If I were in your shoes, I'd use the
Enterprise library and just write the specific stuff out for whichever db I
was using at the time. I know this sounds like a bit of a cop out, however
you probably don't switch back ends too often and to fully take advantage of
the features of a new db, you'll have some porting to do anyway. I'd take a
look at the Enterprise library - I think it'll get you a long way down the
path.
 
W.G. Ryan - MVP said:
Adam, I believe you *can* (not want but can) just use OleDb and use the ?
for the param marker - I'm not positive on this though. But you really
probably want to at most, use the interfaces and use a factory pattern to
create the specific providers which pretty much leaves you in the same
place if I understand you correctly.
Click to expand...

That just about covers where I've gotten so far :)
If I were in your shoes, I'd use the Enterprise library and just write the
specific stuff out for whichever db I was using at the time. I know this
sounds like a bit of a cop out, however you probably don't switch back
ends too often and to fully take advantage of the features of a new db,
you'll have some porting to do anyway. I'd take a look at the Enterprise
library - I think it'll get you a long way down the path.
Click to expand...

Interesting... I'm not familiar with the Enterprise library at all (first
I've heard of it actually, which surprises me given how much I dig
around...). I'm not sure what's required in terms of integrating it, if
it's a "big" change, it may not be an option, but I will definately check it
out.

Thanks
 
Adam,
Depends on how "enterprise" your needs are, but Enterprise Library is a big
commitment. The decision to go down this route goes way beyond trying to
decide how to manage your parameter naming.

You should definitely be doing an abstration between your application and
your database provider, whether you write or it is indeed Entlib. If you
are rolling your own then you have the right thought in using the
IDBConnection and other interfaces. In an ideal world your application code
shouldn't have awareness of the database provider. I accomplished most of
this but without a suitable interface for DataAdapters I fell a little
short.

I haven't yet had to deal with this issue of parameter naming but I have
been thinking about it. Perhaps, something like the following. Put the
FixCommandText() and getDBParameter into a factory type thing.

I think it could be as simple as using the factory to build the parameters
as needed, then a simple string replace throught the command.text.

The following isn't real code, just some thoughts at this point...

jeff

Public Enum DataProviderType
Sql
OleDb
Odbc
Oracle
End Enum

Global.CurrentProvider = DataProviderType.ODBC

command.text = "select * from somefile where name = '@name'" (your most
common target, or invent your own place holder)
command.text = FixCommandText(command.text)
-------------------------

public function FixCommandText() as string

Select Case Global.CurrentProvider
Case DataProviderType.OleDb
return replace(sCmdText,"@","?")

Case DataProviderType.Sql
return CmdText
Case DataProviderType.Oracle
return new ???/
End Select
end function

Public Function getDBParameter(ByVal sParameterName As String, ByVal
eDBType As DataProviderType) As IDbDataParameter
Select Case eDBType
Case DataProviderType.OleDb
Return New OleDbParameter("?" & sParameterName, eDBType)

Case DataProviderType.Sql
Return New SqlParameter("@" & sParameterName, eDBType)

Case DataProviderType.Oracle
return new ???/
End Select
End Function
 
The OleDb provider is a good start, as you can put ? tokens into your
strings. This does not necessarily help with sprocs, unless you are setting
up with exec, or similar.

I believe you can use the "@" even with Oracle, as long as you are using the
MS OracleClient provider. I do not feel this is the greatest pattern,
however, as it breaks down if you move to ODP.NET.

If you want a more generic pattern, you can create parameter collections,
using the interfaces. You can then add specific "providers" to execute the
code. You then only need providers you will support. This adds a bit of
weight (perf hit), but weight is a consequence of generic programming. It is
normally on a very small level and the added ability to separate out
concerns increases scalability.

I am not fond of the so called "Enterprise library" for true Enterprise
apps. It is a great way to genericize on smaller apps, however.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***********************************************
Think Outside the Box!
***********************************************
 
Adam

I'm kind of in your shoes. I'm bulding something that has to support
multiple databases. My SQL (stored procedures) is
(going to be) ported between those databases.

Have you looked at the DbCommandBuilder in System.Data.Common?

Each DbCommandBuilder leaf has a public static 'DeriveParameters' method.
You give the method an instance of a DbCommand (i.e. SqlCommand,
OracleCommand, etc.) and a live connection. The instance of the DbCommand
you give it need only have the CommandText / CommandType properties set.
Upon return from DeriveParameters, the DbCommand instance you passed will
have its Parameters collection filled out -- with names (database
specific!), types and a default value.

You can then just access your parameter (note that the 'name' of each
Parameter will have the DB-specific prefix -- @ or : or whatever) from the
collection and go from there.

Unless you can afford to make 2 round trips to the database for each call
(stored procedure, dynamic SQL, etc.), people tend to use the output of
DeriveParameters to generate code that you then use in your application in
production.

I believe CodeSmith tries to do this (though I'm not sure if its supports
Oracle directly); the MS Data Set Generator tool used in the VS.NET IDE also
does this.

HTH,

Howard Hoffman
 
Yes, it can. Thanks.

I copy the .xsd, xss, xsc designer.cs to a folder of a WebApplicaiton.
Then I use add exist item to add the .xsd file to the project.
But below the project I can only see the .xsd .xsc and .xss file. The
desginer.cs can't add the the WebApplicaiton.
Dose it mean that I can't modify the desginer.cs file in web application?


Jeff Jarrell said:
Adam,
Depends on how "enterprise" your needs are, but Enterprise Library is a
big commitment. The decision to go down this route goes way beyond trying
to decide how to manage your parameter naming.

You should definitely be doing an abstration between your application and
your database provider, whether you write or it is indeed Entlib. If you
are rolling your own then you have the right thought in using the
IDBConnection and other interfaces. In an ideal world your application
code shouldn't have awareness of the database provider. I accomplished
most of this but without a suitable interface for DataAdapters I fell a
little short.

I haven't yet had to deal with this issue of parameter naming but I have
been thinking about it. Perhaps, something like the following. Put the
FixCommandText() and getDBParameter into a factory type thing.

I think it could be as simple as using the factory to build the parameters
as needed, then a simple string replace throught the command.text.

The following isn't real code, just some thoughts at this point...

jeff

Public Enum DataProviderType
Sql
OleDb
Odbc
Oracle
End Enum

Global.CurrentProvider = DataProviderType.ODBC

command.text = "select * from somefile where name = '@name'" (your most
common target, or invent your own place holder)
command.text = FixCommandText(command.text)
-------------------------

public function FixCommandText() as string

Select Case Global.CurrentProvider
Case DataProviderType.OleDb
return replace(sCmdText,"@","?")

Case DataProviderType.Sql
return CmdText
Case DataProviderType.Oracle
return new ???/
End Select
end function

Public Function getDBParameter(ByVal sParameterName As String, ByVal
eDBType As DataProviderType) As IDbDataParameter
Select Case eDBType
Case DataProviderType.OleDb
Return New OleDbParameter("?" & sParameterName, eDBType)

Case DataProviderType.Sql
Return New SqlParameter("@" & sParameterName, eDBType)

Case DataProviderType.Oracle
return new ???/
End Select
End Function
Click to expand...
 
Back
Top