Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-10
8
Medium Priority
?
489 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

661 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