使用Dapper插入MySQL数据时遇‘Connection must be valid and open’错误求助

我正在学习Blazor Server数据库连接，使用Dapper进行模型转换。Razor组件中查询数据正常，但插入数据时抛出“Connection must be valid and open”错误。已确认连接字符串可用，SQL语句在MySQL Workbench中可正常执行，怀疑问题出在DataAccess类但无法定位，简化数据表后问题仍存在。

相关代码 #

数据访问类 #

public class DataAccess : IDataAccess
{
    //Code from Timothy Corey
    public async Task<List<T>> LoadData<T, U>(string sql, U parameters, string connectionString)
    {
        using (IDbConnection connection = new MySqlConnection(connectionString))
        {
            var rows = await connection.QueryAsync<T>(sql, parameters);

            return rows.ToList();
        }
    }
    public Task SaveData<T>(string sql, T parameters, string connectionString)
    {
        using (IDbConnection connection = new MySqlConnection(connectionString))
        {
            return connection.ExecuteAsync(sql, parameters);
        }
    }
}

Razor组件 #

@inject IDataAccess _data
@inject IConfiguration _config

<h3>MaintenanceList</h3>

@if (mLog == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class="table">
        <thead>
            <tr>
                <th>Title</th>
                <th>Type</th>
                <th>Description</th>
                <th>Date</th>
                <th>Cost</th>
                <th>Shop</th>
            </tr>
        </thead>
        <tbody>
            @foreach(var item in mLog)
            {
                <tr>
                    <td>@item.Title</td>
                    <td>@item.Type</td>
                    <td>@item.Description</td>
                    <td>@item.Date.ToShortDateString()</td>
                    <td>$@String.Format("{0:0.00}", item.Cost)</td>
                    <td>@item.Shop</td>
                </tr>
            }
        </tbody>
    </table>
}
<button @onclick=InsertData>insert data</button>

@code {
    List<MaintenanceLog> mLog;
    protected override async Task OnInitializedAsync()
    {
        string sql = "select * from maintenancelog";

        mLog = await _data.LoadData<MaintenanceLog, dynamic>(sql, new {  }, _config.GetConnectionString("Default"));
    }

    private async Task InsertData()
    {
        string sql = "INSERT INTO maintenancelog (Title, Type, Description, Date, Cost, Shop) VALUES (@Title, @Type, @Description, @Date, @Cost, @Shop); ";
        await _data.SaveData(sql, new { Title = "Tire Replacement", Type = "General Maintenance", Description = "tires repaired", Date = new DateTime(), Cost = 345.67, Shop = "Discount Tire" }, _config.GetConnectionString("Default"));
    }
 }

解决方案 #

问题根源在SaveData方法的实现：直接返回connection.ExecuteAsync的Task时，using块会立即释放数据库连接（方法执行到return就结束，using作用域终止），但此时ExecuteAsync的异步操作尚未完成，导致操作时连接已关闭。

修复方法是给SaveData添加async/await，确保等待异步操作完成后再释放连接：

public async Task SaveData<T>(string sql, T parameters, string connectionString)
{
    using (IDbConnection connection = new MySqlConnection(connectionString))
    {
        await connection.ExecuteAsync(sql, parameters);
    }
}

补充说明 #

Dapper的QueryAsync和ExecuteAsync在连接未打开时会自动尝试打开连接，但核心问题是要保证连接在异步操作完成前不被using块释放。添加await后，方法会等待ExecuteAsync执行完毕，using块才会释放连接，此时操作已完成，不会再触发连接无效的错误。

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