使用VB.net数据库连接维护内存的示例

Question

我在数据库连接处理方面遇到了一个特殊的问题，尽管在连接、数据集和数据表的处理上有很多来回的问题，但作为一个新的VB编码器，我无法完全理解它。我的项目有一个表单，它有一个简单的"导入数据库"按钮，可以打开一个连接。假设我有一个数据库，其中有几个数据集和数据表，它们占用了大量内存，所以我必须小心打开和关闭东西。我有一个按钮来关闭数据库。代码简单地看起来像这样，不会给我带来错误(即我可以连接，获得正确的数据，然后关闭数据库而不会引发错误(

Public ConnectedDB as DataBaseOps
Class Form1
Private Sub ImportDatabaseButton(sender as object, e and eventargs) handles ImportDatabasebutton.click
'Open filedialog and return the path, etc.
strPATH = OpenFileDialogMaster(...my filters here...)
ConnectedDB.OpenDatabase(strPath)
End Sub
Private Sub populateDSDT(....)
'...populate my datasets which then populate my datagrids
ConnectedDB.PopulateMyDataSet()
'...populate my datagrids
End sub
Private Sub CloseDatabaseButton(sender as object, e and eventargs) handles CloseDatabaseButton.click
ConnectedDB.CloseDatabase()
'Can I put something here to clean up the DatabaseOPs class?
End Sub
End Class

现在在一个单独的类中

Private Class DataBaseOps
Public OpenCon As OleDb.OleDbConnection
Public Sub OpenDataBase(strPath as String)
'My.Settings.ConnectionString is a variable in App.Config
OpenCon = New OleDb.OleDbConnection(My.Settings.ConnectionString & strPath)
OpenCon.Open()
End Sub
Public Sub PopulateMyDataSet()
'...Populate my datasets and tables based on data from the database
'...Note that for the adapters I include "Using" to help keep those clean when I no longer need them
End sub 
Public Sub CloseDatabase()
Try
OpenCon.Close()
Catch ex As Exception
MessageBox.Show("No Database file loaded.")
End Try
End Sub
Overrides Sub Finalize()
'...Datasets get .Dispose() which is a topic of its own elsewhere.
OpenCon.Dispose()
End Sub
End Class

请记住，还有其他方法可以做到这一点，但这里的关键因素是打开连接，保持它打开以填充数据网格，在数据库中做一些其他工作，然后关闭它，释放连接和数据集内存。

我的问题是，我尝试了各种方法来限制"ConnectedDB"及其数据集的范围，包括将Finalize((子放入进程中，试图将其从内存中删除。我也曾考虑过范围界定，但这两个想法都不完美，似乎都能保留记忆中的项目。使用我的通用代码，我可以做些什么来清理这里的内存资源？作为一个相对较新的VB.net编码器，我是否遗漏了一些明显的东西(比如我可能会将一个类继承到另一个类，而不是调用它们？(

Answer 1

将您的连接保持在使用它们的本地。然后您可以使用Using。。。结束使用砌块，以确保其封闭和处理。

感谢邓肯-爱德华兹-琼斯的这句话。

"数据库连接就像冰箱门——你只打开它当你需要什么的时候，你只会拿出你需要的，而你取出所需物品后立即关闭。">

数据集可以包含多个DataTable。我不知道为什么您需要多个数据集。

Class Form1
Private ConnectedDB As DataBaseOps
Private ds As DataSet
Private Sub ImportDatabaseButton(sender As Object, e And eventargs) Handles ImportDatabaseButton.click
Dim OpenDial As New OpenFileDialog
'Open filedialog and return the path, etc.
Dim strPATH = OpenDial.FileName
ConnectedDB = New DataBaseOps(strPATH)
End Sub
Private Sub populateDSDT()
'...populate my datasets which then populate my datagrids
ds = ConnectedDB.PopulateMyDataSet()
'...populate my datagrids
End Sub
End Class
Public Class DataBaseOps
Private DBFile As String
Public Sub New(file As String)
DBFile = file
End Sub
Public Function PopulateMyDataSet() As DataSet
Dim ds As New DataSet
Using OpenCon As New OleDb.OleDbConnection(My.Settings.ConnectionString & DBFile)
OpenCon.Open() 'But don't do this until directly before the .Execute...
'...Populate my datasets and tables based on data from the database
'...Note that for the adapters I include "Using" to help keep those clean when I no longer need them
End Using
Return ds
End Function
End Class

Answer 2

在OpenCon.Dispose()之后的Finalize方法中添加这两行

GC.Collect()
GC.WaitForPendingFinalizers()