>Whenever I bring programs written at home into the office, I have to change the backend database used by the Datasets. The problem I have is that it takes such a long time, about 3-5 minutes using the Configure command which I assume is because Visual Studio is looking for the home database, and it has to be done for each table in the Dataset. Is there a quicker way of doing it, like changing a connection string somewhere?
>
>I also have to delete and re-created any parameterized queries defined for the tables, but that doesn't take very long though I'd rather not have to do that either.
>
>Thanks for any tips you may have,
>David.


Repeat after me:
The GUI asks the Business layer for data.
The business layer connects to the backend of choice.
The business layer crunches whatever is expected for the GUI's request.
The business layer spits back to the GUI the output.
The GUI then slices and dices for display.

My DAL Class
Public Shared Function FillConnString(ByVal Appname As String) As String
Dim ConnString As String = ""
If AppName = "ChargeIt" Then
ConnString = ConfigurationManager.ConnectionStrings("AutoZone.AllData.ChargeIt.My.MySettings.ConnectionString").ConnectionString

ElseIf Appname = "CCBatch" Then
ConnString = ConfigurationManager.ConnectionStrings("AutoZone.AllData.CreditCards.My.MySettings.ConnectionString").ConnectionString

ElseIf Appname = "LOL" Then

ConnString = ConfigurationManager.ConnectionStrings("ListsOfLists.My.MySettings.ConnectionString").ConnectionString

ElseIf Appname = "SalesConfig" Then
ConnString = ConfigurationManager.ConnectionStrings("AutoZone.AllData.SalesConfigTool.My.MySettings.ConnectionString").ConnectionString

End If


Public Shared Function FillDataSet(ByVal sqlStr As String, ByVal ds As DataSet, ByVal tn As String, ByVal AppName As String) As DataSet
' Get a connection string to the darn INFOMIX.
Dim ConnString As String = FillConnString(AppName)

Using Conn As odbcConnection = New odbcConnection(ConnString)

Using selectCMD As odbcCommand = New odbcCommand(sqlStr, Conn)
selectCMD.CommandTimeout = 30
Using DA As odbcDataAdapter = New odbcDataAdapter
DA.SelectCommand = selectCMD

Conn.Open()
Try
DA.Fill(ds, tn)
FillDataSet = ds
Catch ex As Exception
If ds Is Nothing Then
Dim dsNull As DataSet = New DataSet
FillDataSet = dsNull
End If
End Try
End Using
End Using
End Using

FillDataSet = FillDataSet

I have an ODBC driven class as well as an Informix Driver driven class, so all I have to do is change the class NAME to switch the functionality over. I also have one for SQL Server but have not needed that one as of yet here at recent contract.

A Call from BizClass:
Public Shared Function RollTransaction(ByVal trankey As String, ByVal newdate As Date) As Boolean
Dim sql As StringBuilder = New StringBuilder
' set the update statement and fire it off.
sql.Append("Update informix.cc_recur_billing set cbl_charge_sts_cd = 'R' where cbl_cc_trn_id = " & trankey)

If DAL.DataPortal.ExecNonQuery(sql.ToString(), "CCBatch") = 1 Then
RollTransaction = True
Else
RollTransaction = False
Exit Function
End If

Now GUI Call for Data where I am determining if I need to run an update :
For Each oRow As DataGridViewRow In dgRollover.Rows
If oRow.Cells(0).Value Is Nothing Or DBNull.Value.Equals(oRow.Cells(0).Value) Then
Else
If oRow.Cells(0).Value = 1 Then
ii = ii + 1

If CCProcess.RollTransaction(oRow.Cells(3).Value, Me.DateTimePicker1.Value) Then
oRow.Dispose() 'Visible = False
End If
End If
End If
Next


I can switch the backend easily this way.