Perhaps naively, I created a class (AdminDatabase) to handle connection to different MS-Access database files (see code at bottom). The purpose of the class was to allow retrieval of data from a MS-Access database and to manipulate the data. This works well. I can feed an instance of the AdminDatabase class an SQL statement and fill a dataTable with the result (getDataTable method in the code section. Now I have added data sets to the project using Visual Studio's data designer.
Now I am a bit confused. The AdminDatabase class set's the connection string once the user has chosen the relevant data file. Since the new data sets also use the same setting, My.Settings.AdminConnectionString for the connection, which brings me to my questions:
After instantiating a AdminDatabase object, can I assume that the data sets I created using the data designer will connect to the MS-Access database file chosen by the user at run time?
Should I write methods in my connection class to access data in the data sets created with the data designer, or would accessing the data sets directly be ok? I guess I could just have a method somewhere to set the connection string setting in the project.
How else can I approach this?
Code
public class AdminDatabase
' stores the connection string which is set in the New() method
dim strAdminConnection as string
public sub New()
...
adminName = dlgopen.FileName
conAdminDB = New OleDbConnection
conAdminDB.ConnectionString = "Data Source='" + adminName + "';" + _
"Provider=Microsoft.ACE.OLEDB.12.0"
' store the connection string in strAdminConnection
strAdminConnection = conAdminDB.ConnectionString.ToString()
My.Settings.SetUserOverride("AdminConnectionString", strAdminConnection)
...
End Sub
' retrieves data from the database
Public Function getDataTable(ByVal sqlStatement As String) As DataTable
Dim ds As New DataSet
Dim dt As New DataTable
Dim da As New OleDbDataAdapter
Dim localCon As New OleDbConnection
localCon.ConnectionString = strAdminConnection
Using localCon
Dim command As OleDbCommand = localCon.CreateCommand()
command.CommandText = sqlStatement
localCon.Open()
da.SelectCommand = command
da.Fill(dt)
getDataTable = dt
End Using
End Function
End Class
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…