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

  • Thread starter Thread starter Herby
  • Start date Start date
H

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.
 
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?
 
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");
}

}
 
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
 
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.
 
Back
Top