Newbie: "dual" database connection

[steve]

Hi,
This is probably a stupid question but I have spent the whole day without
being able to come up with a soliution.

I want a way to give the user the choice of which database (SQL or Access)
to connect to. It's basically the same database, created with Access and
then ported to SQL server. Therefore most of the "population" code stays the
same.

I was thinking of two radio boxes or something along these lines. However i
seem to have difficulties with filing up the adapters/datasets.

The code of my latest attempt is the following:

'Global Variables
'SQL Connection String
Dim strConnectionSQL As String = "data source=Ecstlaurent23;initial
catalog=Northwind;" _
& "Integrated Security=True"
Dim SQLcn As SqlConnection = New SqlConnection(strConnectionSQL)
'Access Connection String
Dim strConnectionAccess As String = "provider=microsoft.jet.oledb.4.0;" _
& "data source=C:\RAFALE\Beta\BD\rafalebd.mdb"
Dim Accesscn As OleDbConnection = New OleDbConnection(strConnectionAccess)
.......
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button5.Click
Dim strSelect As String = "SELECT * FROM Customers"
Dim sqlAdapter As New SqlDataAdapter(strSelect, SQLcn)
Dim oleAdapter As New OleDbDataAdapter(strSelect, Accesscn)
If rdb1.Checked Then
Try
Dim SQLds As New DataSet
SQLds.Clear()
sqlAdapter.Fill(SQLds, "Customers")
cmb1.DataSource = sqlAdapter
cmb1.DisplayMember = "Customers.CustomerID"
cmb2.DataSource = sqlAdapter
cmb2.DisplayMember = "Customers.ContactName"
cmb3.DataSource = sqlAdapter
cmb3.DisplayMember = "Customers.Country"
Catch ex As Exception
MsgBox(ex.Message)
End Try
Else
Dim OLEds As New DataSet
oleAdapter.Fill(OLEds, "Customers")
cmb1.DataSource = oleAdapter
cmb1.DisplayMember = "Customers.CustomerID"
cmb2.DataSource = oleAdapter
cmb2.DisplayMember = "Customers.ContactName"
cmb3.DataSource = oleAdapter
cmb3.DisplayMember = "Customers.Country"
End If
End Sub

Thanx in advance!

 

[Cor Ligthert]

Steve,

For that I make sepererate (shared) classes which do the handling of the
datasets.

What is different when as you use OleDB and SQLclient as you do as now(what
is the best)
The connection ' however has completly the same methodique
The command 'when you use it but the same as above
The dataadapter 'the same as above
The parameters, which acts different, the OleDB version should always be
added in sequence of existence in the SQL string, while that is for the
SQLClient not important.

The dataset and all the rest can always be the same, so you did in my
opinion to much work what would in my idea end in completly two programs in
one now.

I hope this give some ideas so far?

Cor

 

[W.G. Ryan eMVP]

I'd recommend using a Factory Pattern for this ..
http://www.c-sharpcorner.com/Code/2002/July/GenericDataAccessCompActivator.asp

HTH,

Bill

 

[steve]

Well I still can't see how i can avoid duplication of code ....????
My Object knowledge is not very good but I tried the following:

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button5.Click
Dim SQLObj As New SQLClass
Dim AccessObj As New AccessClass
Try
If rdb1.Checked Then
SQLObj.initialize()
cmb1.DataSource = SQLObj.DBDataSet
cmb1.DisplayMember = "Customers.CustomerID"
cmb2.DataSource = SQLObj.DBDataSet
cmb2.DisplayMember = "Customers.ContactName"
cmb3.DataSource = SQLObj.DBDataSet
cmb3.DisplayMember = "Customers.Country"
Else
AccessObj.initialize()
cmb1.DataSource = AccessObj.DBDataSet
cmb1.DisplayMember = "tblInfoQA.MENVid"
cmb2.DataSource = AccessObj.DBDataSet
cmb2.DisplayMember = "tblInfoQA.Nom"
cmb3.DataSource = AccessObj.DBDataSet
cmb3.DisplayMember = "tblInfoQA.SERTECid"
End If
Catch ex As SqlException
MsgBox(ex.Message)
End Try
End Sub

where there are obviously two classes, SQLClass and AccessClass, one of them
is (the other of course is similar):

Public Class SQLClass
Public strDBConnection As String = "data source=Ecstlaurent23;initial
catalog=Northwind;" _
& "Integrated Security=True"
'SQL Connection !!!!
Public DBConnection As SqlConnection = New SqlConnection(strDBConnection)
Public strSelect As String = "SELECT * FROM Customers"
'SQL Data Adapter !!!
Public DBAdapter As New SqlDataAdapter(strSelect, DBConnection)
Public DBDataSet As New DataSet

Sub initialize()
DBConnection.Open()
DBAdapter.Fill(DBDataSet, "Customers")
DBConnection.Close()
End Sub
End Class

But i still have to access the dataadapter through the objects ,
SQLObj.DBAdapter etc.
Am I doing something wrong?
Thanx again

 

[Cor Ligthert]

Steve,

This is a sample how you can do it

This is very easy one because it is only about the select, when it becomes
about the update it is a lot more work, however the idea can be the same.

(In this sample is assumed that the user made a choise and that that is set
in the register as well as the connection string).

However just as a sample I have the idea that I discover everytime something
new with this and this was one of the first, but that makes it in my opinion
the most clear.

I hope this helps?

Cor

\\\
Imports System.Data.SqlClient
Imports System.Data.OleDb
Public Class Database
Public Shared Function SelectDataset(ByVal sqlStr As String, _
ByVal tablenaam As String) as Dataset
If RegistryC.DB = "Access" Then
Return SelectAccessDataset(sqlStr, tablename)
Else
Return SelectSQLDataset("USE TheTable " & sqlStr, tablename)
End If
End Function
Private Shared Function SelectAccessDataset(ByVal sqlStr _
As String, ByVal tablenaam As String) as Dataset
Dim Conn As New OleDbConnection(RegistryC.Conn)
Try
Dim cmd As New OleDbCommand(sqlStr, Conn)
Dim dsnew As New DataSet
Dim da As New OleDbDataAdapter(cmd)
da.Fill(dsnew, tablenaam)
Return dsnew
Catch ex As OleDbException
MessageBox.Show(ex.ToString)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Conn.Dispose()
End Try
End Function
Private Shared Function SelectSQLDataset(ByVal sqlStr As String, _
ByVal tablenaam As String) As DataSet
Dim Conn As New SqlConnection(RegistryCalc.Conn)
Try
Dim cmd As New SqlCommand(sqlStr, Conn)
Dim dsnew As New DataSet
Dim da As New SqlDataAdapter(cmd)
da.Fill(dsnew)
Return dsnew
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Conn.Dispose()
End Try
End Function
///

 

[steve]

Thanx so much both of you!
I will study the pieces of code attentively and see if i can put together a
solution with my minimum to nonexistent knowledge.

-steve