late binding or vb.net CF multiple datasource types for data connection

This question is for vb.net CF not full vb.net.

Does vb.net 2005 support late binding?

I am currently using vb.net 2003 and it doesn't.

My problem is that I have an app that can utilize several different database types (Access, Sql, Oracle etc).
I wanted to know how I can set up my variables one time instead of twice as below because doing it as I have
below, forces me to have multiple lines of repetitive code (1 for each type of connection)

    Public Shared cnS As New SqlServerCe.SqlCeConnection("Data Source=SDMMC Disk\isis.SDF")
    Public Shared cnCmdS As New SqlServerCe.SqlCeCommand
    Public Shared drDBS As SqlServerCe.SqlCeDataReader
    Public Shared custDAS As SqlServerCe.SqlCeDataAdapter = New SqlServerCe.SqlCeDataAdapter

    Public Shared cnA As AdoceConnection
    Public Shared cnCmdA As New InTheHand.Data.Adoce.AdoceCommand
    Public Shared drDBA As InTheHand.Data.Adoce.AdoceDataReader
    Public Shared cnA2 As AdoceConnection

Here is a sample of code.  I would like to make this more efficient

        If SPLASH.MDATABASE = "ACCESS" Then
            SPLASH.cnA.Open()
            SPLASH.cnCmdA.CommandText = "Insert into tbl...................."
            SPLASH.cnCmdA.Connection = SPLASH.cnA
            SPLASH.cnCmdA.ExecuteNonQuery()
            SPLASH.cnA.Close()
        Else
            ''''''''''''''SQL DB''''''''''''''''''
            SPLASH.cnS.Open()
            SPLASH.cnCmdS.CommandText = "Insert into tbl..................."
            SPLASH.cnCmdS.Connection = SPLASH.cnS
            SPLASH.cnCmdS.ExecuteNonQuery()
            SPLASH.cnS.Close()
        End If

PSCTECHAsked:
Who is Participating?
 
Jeff CertainConnect With a Mentor Commented:
Check out this article... it walks you through waht you'll need to do:
http://www.devx.com/dotnet/Article/27297/0/page/1
0
 
Jeff CertainCommented:
I don't know specifically about CF 2005.

However, ADO .NET 2005 has abstracted all the specific database types into a single database provider. So, DBConnection is the interface for SqlConnection, OLEDBConnection, etc. This means that you can fairly easily set up database-agnostic connections, trusting the individual providers to handle the differences between the databases behind the scenes.

All that to say, I would expect a similar approach to work in CF. At least, it exposes System.Data.Common, which is where DbConnection lives.

0
 
PSCTECHAuthor Commented:
Thanks, that sounds like what I need.
Do you have an example for creating the proper connections, commands, datareaders etc. (Access or Sql) and then how to refer to them.  
The code above and below shows the particulars I am looking for.   Also the data reader.....ex:  

 cnA.Open()
 cnCmdA.CommandText = "Select * from MyTbl"
 drDBA = cnCmdA.ExecuteReader

 While drDBA.Read
       do something here
 End While
 
 drDBA.Close()
 SPLASH.cnA.Close()


Thanks in advance for any help

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeff CertainCommented:
The approach you need actually requires implementation of a number of factory classes. Any good book on ADO .NET 2.0 should have a good walkthrough (you might try Sahil Malik's book from APress).

Unfortunately, this is a topic that runs to dozens of pages, and is probably outside the scope of what I can provide here.

In general, let me say that you can create objects that are analgous to what you're used to, but assigned to the interface class types...

Imports System.Data.Common
Imports System.Data.SqlClient

Dim myConnection as DbConnection = New SqlConnection("insert connection string here")

0
 
PSCTECHAuthor Commented:
That sounds good, but maybe I'm missing something.  The app runs on some clients with Access and some with SQL.
In order to have only 1 set of source code, the app checks an .xml file on startup to determine if the client is using
Access or SQL so....

I would have to dim myConnection  as "New SqlConnection("   ") in some instances and as Access connection in others
depending on what's in the .xml

I tried putting it in a conditional:
                                               Read .xml file
                                               Determine db type

                                                if db=Access then
                                                       myConnection = Access Connection
                                                else
                                                      myConnection = SQL Connection
                                                end if

Obviously, that didn't work, but that's what I need to accomplish
0
 
Jeff CertainCommented:
The issue is that you need OLEDB objects for Access and SqlClient objects for SQL. Since all of these objects inherit from System.Data.Common, you need to use a factory approach with ADO .NET 2.0 and System.Data.Common to provide database agnosticism.

Did you check out the book I recommended?

0
 
PSCTECHAuthor Commented:
thanks for the help!!!!
I appreciate you getting backto me so quickly
0
 
Jeff CertainCommented:
No problem. Glad to be of assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.