List of DB drivers.

  • Thread starter Thread starter Mr. X.
  • Start date Start date
M

Mr. X.

Hello,
How can I expose to C# the list of all DB-Drivers, and are all the drivers
must be on ODBC ?

Thanks :)
 
How can I expose to C# the list of all DB-Drivers, and are all the drivers
must be on ODBC ?
Click to expand...

ODBC drivers will be the only drivers that are enumerated in one place (the
registry). As far as I know, to discover other drivers would require
searching the entire machine and looking for various files. I doubt there's
any generic way to find everything. In other words, if you want to know if
the system has the MySQL driver, I think you'll have to know what you're
looking for; I doubt the assembly is wearing a name tag that says "Hi, I'm
the MySQL driver!"
 
How can I expose to C# the list of all DB-Drivers, and are all the
drivers must be on ODBC ?
Click to expand...

..NET can use:
- ODBC drivers via System.Data.Odbc
- OLE DB providers via System.Data.OleDb
- ADO.NET provides

Especially the last ones are difficult to detect due to .NET's
(otherwise nice) XCOPY deployment capabilities.

I thin the most practical approach is to get the list
of classes for the most used database and simply see
if you can load them.

Even if you can't then they can still be somewhere on
your system, but they are not made available for the
app, so you should probably not use them anyway..

Arne
 
For MySql : Isn't any MySql for ODBC (Ole DB, ADO.NET) ?
If not - is there any common parent object I can use for each driver?

How can I search drivers in ODBC, Ole DB, ADO.NET ?

Thanks :)
 
Also.
If I use MySql :
How can I use the SqlConnection, SqlDataAdapter, etc. instead of
MySqlConnection, MySqlDataAdapter.
(When creating and object - I think I should use MySqlConnection, etc ...
but when refer to the parent object type : SqlConnection etc ... Is that
right in OOP ?)

Thanks :)
 
Mr. X. said:
Also.
If I use MySql :
How can I use the SqlConnection, SqlDataAdapter, etc. instead of
MySqlConnection, MySqlDataAdapter.
(When creating and object - I think I should use MySqlConnection, etc
... but when refer to the parent object type : SqlConnection etc ... Is
that right in OOP ?)
Click to expand...

If you want your code to be able to use a variety of providers then you
should program to their interfaces: IDbConnection, IDbDataParameter,
IDbCommand, IDbDataAdapter, IDbDataParameter, etc.

Perhaps you should look at using the Enterprise Library, a code
generator (eg MyGeneration), or an Object-Relational Mapper (eg
NHibernate, Entity Framework) if you don't want to write the code yourself.
 
Seems too complicated.
I have also solutions for VB.NET to override by myself interfaces,
but I always found other simple ways solving the problems,
so I never implemented interfaces (with all the methods, that I have to
figure out what to write ...)

I don't understand why should I implement the IDbConnection, etc.
for the specific problem.

If there is no other choice than implementing interfaces, I would be glade
to have some samples for that.
Just need a way to make one class for the known db-drivers, I would like to
access.

Thanks :)
 
Mr. X. said:
Well I have found something :
http://www.dotnetjohn.com/articles.aspx?articleid=244

It is not compiled !
Take a look at this code, and let me know if that solve situation when I
need to use several kind of databases.
If there any some better example, I would be glade to know about.
Click to expand...

Yes, that's the sort of thing I was suggesting.

Now you can have fun adding code for the MySQL provider - assuming it
implements the same interfaces as all the others.
 
O.K.
I am trying using that example (the only I found - I can start from it, even
it is not compiled. It's minor problem ...)
The sample doesn't have the command builder.
I didn't find interface for the command-builder so I use :DbCommandBuilder

For the following code (added to sample) :
....
public static DbCommandBuilder GetCommandBuilder(DataProvider
providerType)
{
DbCommandBuilder commandBuilder = null;
switch (providerType)
{
case DataProvider.SqlServer:
commandBuilder = new SqlCommandBuilder();
break;
case DataProvider.OleDb:
commandBuilder = new OleDbCommandBuilder();
break;
case DataProvider.Odbc:
commandBuilder = new OdbcCommandBuilder();
break;
case DataProvider.Oracle:
commandBuilder = new OracleCommandBuilder();
break;
case DataProvider.MySql:
commandBuilder = new MySqlCommandBuilder();
break;

}
return commandBuilder;
}
....
and use of the above :

DbCommandBuilder commandBldr =
DBManagerFactory.GetCommandBuilder(DataProvider.MySql);
IDbDataAdapter adapter =
DBManagerFactory.GetDataAdapter(DataProvider.MySql);
....
commandBldr.DataAdapter = adapter; // ***** THIS LINE IS NOT COMPILED *** //
Cannot implicitly convert type 'System.Data.IDbDataAdapter' to
'System.Data.Common.DbDataAdapter'. An explicit conversion exists (are you
missing a cast?)

What should I write instead ?

Thanks :)
 
I need to understand this code :
(I have added the following to DBManagerFactory)
public static IDbCommand GetCommand(DataProvider providerType, string
qry, IDBManager aConnMgr)
{
switch (providerType)
{
case DataProvider.SqlServer:
return new SqlCommand(qry, aConnMgr.Connection);
case DataProvider.OleDb:
return new OleDbCommand(qry, aConnMgr.Connection);
case DataProvider.Odbc:
return new OdbcCommand(qry, aConnMgr.Connection);
case DataProvider.Oracle:
return new OracleCommand(qry, aConnMgr.Connection);
case DataProvider.MySql:
return new MySqlCommand(qry, aConnMgr.Connection);
default:
return null;
}
}

Every new is not compiled (I.e oledb) :
The best overloaded method match for
'System.Data.OleDb.OleDbCommand.OleDbCommand(string,
System.Data.OleDb.OleDbConnection)' has some invalid arguments
Argument '2': cannot convert from 'System.Data.IDbConnection' to
'System.Data.OleDb.OleDbConnection'

Need to understand what's wrong, and what should I write instead.

Thanks :)
 
I need to understand this code :
(I have added the following to DBManagerFactory)
public static IDbCommand GetCommand(DataProvider providerType, string
qry, IDBManager aConnMgr)
{
switch (providerType)
{
case DataProvider.SqlServer:
return new SqlCommand(qry, aConnMgr.Connection);
case DataProvider.OleDb:
return new OleDbCommand(qry, aConnMgr.Connection);
case DataProvider.Odbc:
return new OdbcCommand(qry, aConnMgr.Connection);
case DataProvider.Oracle:
return new OracleCommand(qry, aConnMgr.Connection);
case DataProvider.MySql:
return new MySqlCommand(qry, aConnMgr.Connection);
default:
return null;
}
}

Every new is not compiled (I.e oledb) :
The best overloaded method match for
'System.Data.OleDb.OleDbCommand.OleDbCommand(string,
System.Data.OleDb.OleDbConnection)' has some invalid arguments
Argument '2': cannot convert from 'System.Data.IDbConnection' to
'System.Data.OleDb.OleDbConnection'

Need to understand what's wrong, and what should I write instead.
Click to expand...

That code looks very fishy.

If you need a IDbCommand and you have an IDbConnection,
then you do not make a switch on the type but simply call the
CreateCommand method.

Example:

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;

class MultiDb2
{
private static void test(string provider, string constr)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory(provider);
IDbConnection con = dbf.CreateConnection();
con.ConnectionString = constr;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM T1";
IDataReader rdr = cmd.ExecuteReader();
while(rdr.Read()) {
int f1 = (int)rdr[0];
string f2 = (string)rdr[1];
Console.WriteLine(f1 + " " + f2);
}
con.Close();
}
public static void Main(string[] args)
{
test("System.Data.OleDb",
@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Databases\MSAccess\Test.mdb");
test("System.Data.SqlClient", "server=ARNEPC3;Integrated
Security=SSPI;database=Test");
}
}

Applied to your method then I think it can be done as:

public static IDbCommand GetCommand(DataProvider providerType, string
qry, IDBManager aConnMgr)
{
IDbCommand res = aConnMgr.Connection.CreateConnection;
res.CommandText = qry;
return res;
}

Much simpler !!

Arne
 
Well.
I see that the example on the link
http://www.dotnetjohn.com/articles.aspx?articleid=244 is too complicated.
The DbProviderFactory does all the thing.

Also - about CreateCommand, it's much simplier.

Thanks :)

Arne Vajhøj said:
I need to understand this code :
(I have added the following to DBManagerFactory)
public static IDbCommand GetCommand(DataProvider providerType, string
qry, IDBManager aConnMgr)
{
switch (providerType)
{
case DataProvider.SqlServer:
return new SqlCommand(qry, aConnMgr.Connection);
case DataProvider.OleDb:
return new OleDbCommand(qry, aConnMgr.Connection);
case DataProvider.Odbc:
return new OdbcCommand(qry, aConnMgr.Connection);
case DataProvider.Oracle:
return new OracleCommand(qry, aConnMgr.Connection);
case DataProvider.MySql:
return new MySqlCommand(qry, aConnMgr.Connection);
default:
return null;
}
}

Every new is not compiled (I.e oledb) :
The best overloaded method match for
'System.Data.OleDb.OleDbCommand.OleDbCommand(string,
System.Data.OleDb.OleDbConnection)' has some invalid arguments
Argument '2': cannot convert from 'System.Data.IDbConnection' to
'System.Data.OleDb.OleDbConnection'

Need to understand what's wrong, and what should I write instead.
Click to expand...

That code looks very fishy.

If you need a IDbCommand and you have an IDbConnection,
then you do not make a switch on the type but simply call the
CreateCommand method.

Example:

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;

class MultiDb2
{
private static void test(string provider, string constr)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory(provider);
IDbConnection con = dbf.CreateConnection();
con.ConnectionString = constr;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM T1";
IDataReader rdr = cmd.ExecuteReader();
while(rdr.Read()) {
int f1 = (int)rdr[0];
string f2 = (string)rdr[1];
Console.WriteLine(f1 + " " + f2);
}
con.Close();
}
public static void Main(string[] args)
{
test("System.Data.OleDb", @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Databases\MSAccess\Test.mdb");
test("System.Data.SqlClient", "server=ARNEPC3;Integrated
Security=SSPI;database=Test");
}
}

Applied to your method then I think it can be done as:

public static IDbCommand GetCommand(DataProvider providerType, string qry,
IDBManager aConnMgr)
{
IDbCommand res = aConnMgr.Connection.CreateConnection;
res.CommandText = qry;
return res;
}

Much simpler !!

Arne
Click to expand...
 
Also :
How can I create DataAdapter (something simmiliar to CreateCommand) ?

Thanks :)

Arne Vajhøj said:
I need to understand this code :
(I have added the following to DBManagerFactory)
public static IDbCommand GetCommand(DataProvider providerType, string
qry, IDBManager aConnMgr)
{
switch (providerType)
{
case DataProvider.SqlServer:
return new SqlCommand(qry, aConnMgr.Connection);
case DataProvider.OleDb:
return new OleDbCommand(qry, aConnMgr.Connection);
case DataProvider.Odbc:
return new OdbcCommand(qry, aConnMgr.Connection);
case DataProvider.Oracle:
return new OracleCommand(qry, aConnMgr.Connection);
case DataProvider.MySql:
return new MySqlCommand(qry, aConnMgr.Connection);
default:
return null;
}
}

Every new is not compiled (I.e oledb) :
The best overloaded method match for
'System.Data.OleDb.OleDbCommand.OleDbCommand(string,
System.Data.OleDb.OleDbConnection)' has some invalid arguments
Argument '2': cannot convert from 'System.Data.IDbConnection' to
'System.Data.OleDb.OleDbConnection'

Need to understand what's wrong, and what should I write instead.
Click to expand...

That code looks very fishy.

If you need a IDbCommand and you have an IDbConnection,
then you do not make a switch on the type but simply call the
CreateCommand method.

Example:

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;

class MultiDb2
{
private static void test(string provider, string constr)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory(provider);
IDbConnection con = dbf.CreateConnection();
con.ConnectionString = constr;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM T1";
IDataReader rdr = cmd.ExecuteReader();
while(rdr.Read()) {
int f1 = (int)rdr[0];
string f2 = (string)rdr[1];
Console.WriteLine(f1 + " " + f2);
}
con.Close();
}
public static void Main(string[] args)
{
test("System.Data.OleDb", @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Databases\MSAccess\Test.mdb");
test("System.Data.SqlClient", "server=ARNEPC3;Integrated
Security=SSPI;database=Test");
}
}

Applied to your method then I think it can be done as:

public static IDbCommand GetCommand(DataProvider providerType, string qry,
IDBManager aConnMgr)
{
IDbCommand res = aConnMgr.Connection.CreateConnection;
res.CommandText = qry;
return res;
}

Much simpler !!

Arne
Click to expand...
 
For MySql : Isn't any MySql for ODBC (Ole DB, ADO.NET) ?
Click to expand...

MySQL has:
MySQL Connector for ODBC
MySQL Connector for .NET

I will strongly recommend the second option.
If not - is there any common parent object I can use for each driver?
Click to expand...

Yes. See other emails.

Arne
 
Yes, that's the sort of thing I was suggesting.
Click to expand...

Hopefully not.

That code sucks big time.
Now you can have fun adding code for the MySQL provider - assuming it
implements the same interfaces as all the others.
Click to expand...

The MySQL ADO.NET provider is similar to other ADO.NET providers.

Arne
 
Back
Top