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,

[TnTinMN]

Both OleDb and Sqlclient classes inherit from base classes in System.Data.Common, so you can assign the specific type to the parent type.

This quick test worked fine for me.

   Dim applicType As String = "AccessDatabase"

   'I just intitialized the objects to get rid of the annoying IDE messages
   Dim conn As System.Data.Common.DbConnection = New SqlClient.SqlConnection
   Dim cmd As System.Data.Common.DbCommand = New SqlClient.SqlCommand
   Dim rdr As System.Data.Common.DbDataReader


   Select Case applicType
      Case "AccessDatabase"
         conn = New OleDb.OleDbConnection()
         cmd = New OleDb.OleDbCommand
      Case "SQLClientDatabase"
         conn = New SqlClient.SqlConnection
         cmd = New SqlClient.SqlCommand
   End Select

   Debug.WriteLine(conn.GetType.FullName)

   conn.ConnectionString = My.Settings.PlayConnectionString
   conn.Open()
   cmd.Connection = conn
   cmd.CommandText = "Select * From Labor"
   rdr = cmd.ExecuteReader
   While rdr.Read
      'do your stuff
      'Stop
   End While
   conn.Close()

[waleed.makarem]

Dear TnTinMN ...
Thanks so much for your great code .. I'm tryig your code now. Feedback soon

Thanks so much again.

[waleed.makarem]

I understand the logic.. and looks nice.. but I have a big issue (although may be simple)

when I replace the code from defining command as sqlcommnd to common.dbcommand .... i got many error , eg

SQLCmd.Parameters.Add("@Name", SqlDbType.NVarChar) >>> the datatype sqldbtype.nvarchar is not defined

SQLCmd.Parameters.AddWithValue("...... >>>> 'AddWithValue' is not a member of 'System.Data.Common.DbParameterCollection.

Any solution ?

Thanks for your support.

[TnTinMN]

You have discovered the problem in casting (Narrowing conversion) an inheritor class back to it's ancestor class; you loose the added functionality defined in the decendent class. i.e you lost the Add overrides, and the AddWithValue method.

This in itself is not a big problem. You just need to construct a completed parameter of the type you want and add it to the System.Data.Common.DbParametersCollection.

      If applicType = "AccessDatabase" Then
         cmd.Parameters.Add(New OleDb.OleDbParameter("Name", "Fred"))
      Else
         cmd.Parameters.Add(New SqlClient.SqlParameter("Name", "Fred"))
      End If

This should work, but I would like to suggest a different solution to your issue; Instead of using SqlClient, use the "Microsoft OLE DB Provider for SQL Server" (Provider=SQLOLEDB). This way all you would need to do is set the appropriate connection string.

Just remember to use question marks (?) for the substituted parameters in the command text string. I.E.: `cmd.CommandText = "Select * From Labor Where Emp_Name=?"

[waleed.makarem]

Dear TnTinMn,

Kindly find attached example of the issue.