Casting oledb to Sqlclient objects

[waleed.makarem]

Dear Experts,

I have a situation , I am sure about the best approach for it. I have a database application that is designed to handle both SQLServer database and Oleddb "Access mdb" database. the issue is that during application start up , the application detects the configuration file and decides which database to use , eg , oledb to Sqlclient objects. the problem is that the application itself is, eg code to use teh sqlclient objects at different locations of the program. If i need to use oledb , i need to replace all of the sqlclient objects with oledb. who can i do it at run time. the bad solution will be to make at make both objects and decide which one to use , but the application is large. so it will be un-logic to use this approach. I tried to define an object as below

Public aa As SqlClient.SqlConnection
Public bb As OleDb.OleDbConnection
Public gg as object


 if applicType="AccessDatabase" then directcast(gg,oledib.oleDbConnection") 

but this gave me an error .

I tried also :

if applicType="AccessDatabase" then directcast(aa,oledib.oleDbConnection")

but gave an error that I can not cast sqlclient to oledb .

Any solution ?

Thanks,

[nmaillet]

This looks like VB.NET, not C#. In either case, neither directly inherits from the other; therefore, you can't cast between the two types. The do however, share a base class: System.Data.Common.DbConnection. You could, possibly, work with a DbConnection class directly. If there are specific functions implemented by the SqlConnection or OleDbConnection classes, you'd have to handle them specially.

[waleed.makarem]

dear nmaillet,
Thanks for your clarification . and sorry for posting the code here. I think it is a .net concept so both C#,and vb.net are the same.

Regarding your feedback , I am checking it . it seems working. But many functions as you said does not work. So I think it will be better to do the job manually , ie add a seprate function for each job.

Thanks

[nmaillet]

I'm curious, since I've never compared the two, what functions are giving you issues? Most classes that you need to work with should derive from the common namespace, so initialization may be different, but they could be passed fairly easily.

[nmaillet]

I can't believe I forgot about this... there's a DbProviderFactory class you can use. You get the DbProviderFactory by using either of following (C# sorry; I don't use VB.NET very much):

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
//or
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

You then use the factory to create object like so:

DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand command = factory.CreateCommand();
//etc.

You can list all the available DbProviderFactorys using DbProviderFactories.GetFactoryClasses(), which gives you a DataTable of possibilities. Hope that helps.

[waleed.makarem]

Ohhhhh. if it works .. it will be like a magic.. I will be testing it for the coming hours...
Still I have a simple issue , I need to define the adaptor and the command on Global level , ie the entire application can see all of these adaptors, builder,connection. so I put them into a module. while my application decides which database type in the mainform startup . How can I define the connection on a global level using your code ?. is there a kind of redefinition as REDIM in arrays ?.
if my question is not clear , do you need an example ?

Again , Thanks so much for your magic code...

[nmaillet]

Yeah, I'm not entirely sure what your getting at... it may be a disconnect between C# and VB.NET, as they do have some differences. So yeah, an example might help.

The whole idea though, just in case this is what you're refering to, is using DbDataAdapter and DbCommand as the type, not SqlDataAdapter, OleDbDataAdapter, etc. The only time where you would really need to use anything specific to the data source type, is the creation of the factory and the connection string.

[waleed.makarem]

dear nmaillet,

Kindly find an example of the issue.

Thanks,

[nmaillet]

Ok, so you should start by modifying your GlobalVariable module to the base types, like this:

Module GlobalVariable
    Public DbCmd As DbCommand
    Public DbConn As DbConnection
    Public DbConnStr As String
    ...
End Module

Then, when you want to initialize (Form1_Load in your case), you would do it like this:

'You may want to make this global in case you want to make more Db classes later.
Dim factory As DbProviderFactory

Select Case sdatatype
    Case "SQLClient"
        DbConnStr = "..."
        factory = DbProviderFactories.GetFactory("System.Data.SqlClient")
    Case "OleDb"
        DbConnStr = "..."
        factory = DbProviderFactories.GetFactory("System.Data.OleDb")
    ...
End Select

DbConn = factory.CreateConnection()
DbConn.ConnectionString = DbConnStr

DbCmd = factory.CreateCommand()
...

Hope that's clear (syntax might be off slightly, but you should be able to get the idea). Let me know if you have more questions.

[waleed.makarem]

Dear NMaillet ,

Big Thanks.... for the excellent code ..
Thanks again.....

Waleed