VB.NET中无法关闭DataReader数据库连接问题求助

你的代码存在多个导致数据库连接泄漏的问题，同时附带SQL注入风险，以下是针对性解决方法：

核心问题点 #

1. 类级数据库对象复用：cn、dr、cmd这类对象被声明为类成员，一旦某个操作抛出异常，后续的Close()语句会被跳过，直接造成连接泄漏。
2. 手动清理资源不可靠：依赖Close()关闭资源的方式，在异常场景下无法保证执行，而Using语句会自动释放资源，不受异常影响。
3. 冗余的dr.Close()调用：方法开头的dr.Close()可能在dr未初始化时抛出异常，直接阻断后续的连接清理逻辑。
4. SQL注入漏洞：TextBox1_TextChanged中的SQL拼接完全未利用参数，存在严重注入风险。

修复后的代码示例 #

将所有数据库操作改用Using包裹局部对象，提取重复逻辑到通用方法，彻底解决连接泄漏问题：

Imports MySql.Data.MySqlClient

Public Class admin

    ' 替换为你的数据库连接字符串
    Private Const DBConnectionString As String = "你的连接字符串"

    Private Sub admin_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadMonitoringData("select * from rfidmaintest.monitoring ORDER BY lname ASC")
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        LoadMonitoringData("select * from rfidmaintest.monitoring where floor_level = 1 ORDER BY lname ASC")
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        LoadMonitoringData("select * from rfidmaintest.monitoring where floor_level = 2 ORDER BY lname ASC")
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        LoadMonitoringData("select * from rfidmaintest.monitoring where floor_level = 3 ORDER BY lname ASC")
    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        LoadMonitoringData("select * from rfidmaintest.monitoring ORDER BY lname ASC")
    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        LoadMonitoringData("select * from rfidmaintest.monitoring ORDER BY lname ASC")
    End Sub

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        ' 参数化查询彻底避免SQL注入
        Dim sql = "SELECT * FROM rfidmaintest.monitoring WHERE lname like @lname"
        Using cn As New MySqlConnection(DBConnectionString)
            cn.Open()
            Using cmd As New MySqlCommand(sql, cn)
                cmd.Parameters.AddWithValue("@lname", "%" & TextBox1.Text & "%")
                Using dr As MySqlDataReader = cmd.ExecuteReader()
                    ListView1.Items.Clear()
                    While dr.Read()
                        Dim newLine = ListView1.Items.Add(dr("id_num").ToString())
                        newLine.SubItems.Add(dr("fname").ToString())
                        newLine.SubItems.Add(dr("lname").ToString())
                        newLine.SubItems.Add(dr("status").ToString())
                        newLine.SubItems.Add(dr("entry_record").ToString())
                        newLine.SubItems.Add(dr("floor_level").ToString())
                        newLine.SubItems.Add(dr("date").ToString())
                    End While
                End Using
            End Using
        End Using
    End Sub

    ' 提取重复逻辑到通用方法，减少冗余代码
    Private Sub LoadMonitoringData(sql As String)
        Using cn As New MySqlConnection(DBConnectionString)
            cn.Open()
            Using cmd As New MySqlCommand(sql, cn)
                Using dr As MySqlDataReader = cmd.ExecuteReader()
                    ListView1.Items.Clear()
                    While dr.Read()
                        Dim newLine = ListView1.Items.Add(dr("id_num").ToString())
                        newLine.SubItems.Add(dr("fname").ToString())
                        newLine.SubItems.Add(dr("lname").ToString())
                        newLine.SubItems.Add(dr("status").ToString())
                        newLine.SubItems.Add(dr("entry_record").ToString())
                        newLine.SubItems.Add(dr("floor_level").ToString())
                        newLine.SubItems.Add(dr("date").ToString())
                    End While
                End Using
            End Using
        End Using
    End Sub

End Class

关键优化说明 #

• Using语句：自动处理IDisposable对象的释放，包括数据库连接、命令、阅读器，无论是否发生异常，都能保证资源被正确回收，彻底避免连接泄漏。
• 局部对象替代类级变量：每次操作创建全新的连接/命令/阅读器，避免复用导致的状态混乱。
• 通用方法提取：减少重复代码，降低维护成本和出错概率。
• SQL注入修复：TextBox1_TextChanged中的查询改用参数化写法，彻底规避注入风险。
• 移除冗余清理代码：无需手动调用Close()，Using会自动完成资源清理。

内容的提问来源于stack exchange，提问作者user19101467