我正在尝试制作一个非常简单的 aspx 页面，它可以从 mysql 数据库中提取一些数据。 页面构建没有问题。 (aspx 仅包含默认表单和一个仅用于打印一些数据的 div)

默认.aspx.vb:

Imports System.Configuration
Imports System.Data
Imports MySql
Imports MySql.Data
Imports MySql.Data.MySqlClient

Partial Class _Default
     Inherits System.Web.UI.Page

    Private cnstr As String =     ConfigurationManager.ConnectionStrings.Item("thisdb").ConnectionString

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        Dim cn As New MySqlConnection(cnstr)
        Dim cmd As New MySqlCommand("SELECT user_firstname,user_lastname FROM tb_users;", cn)
        cmd.CommandType = CommandType.Text
        Dim dt As DataTable
        dt = GetDataTableMySQL(cmd)
        If dt.Rows.Count > 0 Then
            testdiv.InnerHtml = dt.Rows(0).Item("user_firstname")
            testdiv.InnerHtml += "<br/>" & dt.Rows(0).Item("user_lastname")
        End If
        dt.Dispose()
        cmd.Dispose()
        cn.Dispose()

    End Sub

    Private Function GetDataTableMySQL(ByVal cmd As MySqlCommand) As DataTable
        Dim da As New MySqlDataAdapter()
        Dim dt As New DataTable()
        Try
           cmd.Connection.Open()
           da.SelectCommand = cmd
           da.Fill(dt)
           Return dt
        Catch ex As MySqlException
            Throw ex
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            cmd.Connection.Close()
            da.Dispose()
       End Try
    End Function

End Class

网络配置:

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <system.web>
    <compilation debug="true" strict="false" explicit="true" targetFramework="4.0">
      <assemblies>
        <add assembly="MySql.Data, Version=6.4.4.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"/>
      </assemblies>
    </compilation>
    <customErrors mode="Off"/>
  </system.web>
  <connectionStrings>
    <add name="thisdb" connectionString="Server=localhost;Database=mydatabase;Uid=mydbuser;Pwd=dbpasswd;CharSet=UTF8; "/>
  </connectionStrings>
</configuration>

当我浏览到页面 URL 时，它运行完美。 如果我继续刷新页面，一切正常。

现在烦人的问题来了......

如果我让页面闲置大约 3-4 分钟，然后点击刷新，我总是会遇到以下异常:

The given key was not present in the dictionary.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: The given key was not present in the dictionary.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Exception: The given key was not present in the dictionary.]
_Default.GetDataTableMySQL(MySqlCommand cmd) +236 _Default.Page_Load(Object sender, EventArgs e) +112 System.Web.UI.Control.OnLoad(EventArgs e) +91
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.272

如果我再次点击“刷新”，页面将再次正常运行...等等。

在网络上搜索了几个小时之后，唯一听起来 与我的问题有关，与 mysql 的连接器/网络有关:

Connector/Net 文档说:

Starting with MySQL Connector/Net 6.2, there is a background job that runs every three minutes and removes connections from pool that have been idle (unused) for more than three minutes. The pool cleanup frees resources on both client and server side. This is because on the client side every connection uses a socket, and on the server side every connection uses a socket and a thread.

Prior to this change, connections were never removed from the pool, and the pool always contained the peak number of open connections. For example, a web application that peaked at 1000 concurrent database connections would consume 1000 threads and 1000 open sockets at the server, without ever freeing up those resources from the connection pool.

Note, connections, no matter how old, will not be closed if the number of connections in the pool is less than or equal to the value set by the Min Pool Size connection string parameter.

好的。即使这是我的问题， 连接-->获取数据-->断开连接的正确方法是什么？

有什么想法吗？这真让我发疯!

更新 根据@Andrews 的建议，我将“GetDataTableMySQL”函数更改为如下:

Private Function GetDataTableMySQL(ByVal cmd As MySqlCommand) As DataTable
    Dim dt As New DataTable
    Using da = New MySqlDataAdapter(cmd)
        da.Fill(dt)
    End Using
    Return dt
End Function

(它没有解决问题，但我认为展示代码现在的样子很有用)

异常的堆栈跟踪，更改为以下内容:

[KeyNotFoundException: The given key was not present in the dictionary.] System.Collections.Generic.Dictionary`2.get_Item(TKey key) +9624829
MySql.Data.MySqlClient.CharSetMap.GetCharacterSet(DBVersion version, String CharSetName) +23
MySql.Data.MySqlClient.CharSetMap.GetEncoding(DBVersion version, String CharSetName) +47
MySql.Data.MySqlClient.Driver.Configure(MySqlConnection connection) +510 MySql.Data.MySqlClient.MySqlConnection.Open() +418 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +123
System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +166 System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +115 _Default.GetDataTableMySQL(MySqlCommand cmd) +86
_Default.Page_Load(Object sender, EventArgs e) +112 System.Web.UI.Control.OnLoad(EventArgs e) +91
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

更新 2 阅读 Connector/Net Connection String Options Reference 后 我使用以下选项测试了我的连接字符串:

 Pooling=false;

然后我测试了将合并选项更改为:

 Pooling=no;

通过测试 Pooling 选项，页面无法正常工作! 我每次都收到异常“字典中不存在给定的键”。

最佳答案

可能不能解决问题，但是DataAdapter.Fill不需要自己打开和关闭连接。

此外，我怀疑在 Try block 中使用 Return - 你可以做一些更像

Private Function GetDataTableMySQL(ByVal cmd As MySqlCommand) As DataTable
    Dim dt As DataTable = Nothing
    Using da = New MySqlDataAdapter()
        da.fill(dt)
    End Using
    Return dt
End Function

并检查返回值是否为 IsNot Nothing 以确保其有效。

关于asp.net 大约每三分钟出现一次奇怪的异常，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/9710251/