Solved

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

Posted on 2006-07-10
8
483 Views
Last Modified: 2008-01-09
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

0
Comment
Question by:PSCTECH
  • 5
  • 3
8 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17075708
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
 

Author Comment

by:PSCTECH
ID: 17083923
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
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17086863
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
 

Author Comment

by:PSCTECH
ID: 17138093
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17138457
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
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 500 total points
ID: 17138525
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
 

Author Comment

by:PSCTECH
ID: 17138873
thanks for the help!!!!
I appreciate you getting backto me so quickly
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17139200
No problem. Glad to be of assistance.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now