Solved

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

Posted on 2006-07-10
8
487 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

627 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