Re: Oracle vs MSSQL Parameters

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

"Adam Clauss" <cabadam@xxxxxxxx> wrote in message
news:11p0q8ke7lgpu93@xxxxxxxxxxxxxxxxxxxxx
> 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 Clauss
>
>
>


.

Relevant Pages

  • GUID default value
    ... We are developing a complex system, which will run on Oracle, MS SQL ... All the databases are to be replicated. ... GUID column, which is filled once a new record is inserted. ...
    (microsoft.public.access.tablesdbdesign)
  • GUID default value
    ... We are developing a complex system, which will run on Oracle, MS SQL ... All the databases are to be replicated. ... GUID column, which is filled once a new record is inserted. ...
    (microsoft.public.access.replication)
  • Re: Need design advice. Whats my best approach for storing this data?
    ... With something like DB2 or Oracle here, ... relational databases work, but I think it's with the trouble. ... a declarative language, and in principle, your SQL code ...
    (comp.lang.python)
  • Re: MS SQL server Meata data question
    ... >browsing tools poked around and found that the databases in the system were ... >info about the architecture of sql server's inner workings to use it. ... >I need to do this for Oracle too but I think the native support for Oracle ... PHP DB Edit Toolkit -- PHP scripts for building ...
    (comp.lang.php)
  • Re: BizTalk eating up SQL Server (Virii Behaviour)
    ... Yes, one gig is the least to run sql & bts in the same box, ... uninstall procedure, i manually deleted all databases, and run the ... and it was leaving like 20 mbs free memory, ... BizTalk Solution Developer ...
    (microsoft.public.biztalk.general)
Loading