How to determine the data-provider type from a DbConnection object

2 Mar 2006 10:00 AM

Herby

Im trying to use prepared SQL and need to support both Oracle and SQL
server. Im only dealing with the abstraction - i.e. DbConnection
object.

When using DbParameters, for Oracle i have to use :myVar but for SQL
Server i have to use @myVar. So the abstraction breaks down here.
Consequently i need to ask "What database server are you?" to then
prefix the correct parameter marker character( bring back the ? ).

Iv checked the DbConnection object and there do not seem to be any
appropriate methods, whereby the provider can override some method
which will return the identity.

How best can i determine the type of the DBMS i am currently connected
too?

I do not want to burden the user with having to pass across some
identity...

Thanks.

2 Mar 2006 11:09 AM

Herby

This is a real pain for me, as im also supporting multiple dsn, so it
would mean the client would need to specify what type the data-source
is for each dsn connection.

Im afraid i may be wasting time coding specifically around this, when
an 'elegant' solution exists?

Except for the obvious solution, all parties agreed to a standard
parameter marker character - mind you agreement on anything is very
hard these days...

Im posting reasonable questions on the board and not getting any
answers?

2 Mar 2006 11:11 AM

Sunil Menon

public void GetDBConnectionProvider( IDbConnection dbc )
{
   OracleConnection oOracleCon = dbc as OracleConnection;
   SqlConnection oSqlCon = dbc as SqlConnection;
   SQLBaseConnection oSqlBaseCon = dbc as SQLBaseConnection;
   if ( oOracleCon != null)
   {
      MessageBox.Show ("It is an OracleConnection Object");
   }

   if ( oSqlCon != null)
   {
      MessageBox.Show ("It is an SqlConnection Object");
   }

   if ( oSqlBaseCon != null)
   {
      MessageBox.Show ("It is an oSqlBaseCon Object");
   }

}

2 Mar 2006 12:00 PM

Herby

Thanks for that, thats given me an idea, i could use reflection too.

2 Mar 2006 3:49 PM

MrSmersh

If you use ODBC is simple using reflection

Something in the line of this

IDbConnection DBConnection4Try= (IDbConnection) DBGenericConnection;
DBConnection4Try.ConnectionString=â€DSN=bla blaâ€
DBConnection4Try.Open();

Type ConnectionType =DBConnection4Try.GetType();
PropertyInfo DriverPropertyInfo = ConnectionType.GetProperty("Driver");
string Driver = (string) DriverPropertyInfo.GetValue(DBConnection4Try, null);

If Driver is "SQLSRV32.DLL" then the connection is to a SQL Server.
If is â€œMSORCL32.DLLâ€ then the connection is to Oracle.

Piece of cake!

The generic connection object works as the same...
Be careful though when closing commands object and reader the close and
explicit dispose order must be respected on ODBC. Otherwise it could happen
that you are able to execute once and next time you could get errors.

Show quote

"Herby" wrote:

> Thanks for that, thats given me an idea, i could use reflection too.
>
>

2 Mar 2006 2:42 PM

Robert Simpson

If you're using ADO.NET 2.0 you can can call
DbConnection.GetSchema("DataSourceInformation"), which returns a datatable
containing (among other things) a regex expression for building parameter
names.

Robert

Show quote

"Herby" <prmarjo***@gmail.com> wrote in message
news:1141293625.845650.223300@e56g2000cwe.googlegroups.com...
> Im trying to use prepared SQL and need to support both Oracle and SQL
> server. Im only dealing with the abstraction - i.e. DbConnection
> object.
>
> When using DbParameters, for Oracle i have to use :myVar but for SQL
> Server i have to use @myVar. So the abstraction breaks down here.
> Consequently i need to ask "What database server are you?" to then
> prefix the correct parameter marker character( bring back the ? ).
>
> Iv checked the DbConnection object and there do not seem to be any
> appropriate methods, whereby the provider can override some method
> which will return the identity.
>
> How best can i determine the type of the DBMS i am currently connected
> too?
>
> I do not want to burden the user with having to pass across some
> identity...
>
> Thanks.
>