Solved

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

Posted on 2006-07-10
8
479 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
Comment Utility
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
Comment Utility
thanks for the help!!!!
I appreciate you getting backto me so quickly
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
No problem. Glad to be of assistance.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
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 …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 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

12 Experts available now in Live!

Get 1:1 Help Now