c# - 在合并连接时正确重用 SqlCommand 和 SqlParameter

Question

我正在处理一个可扩展的 WCF 服务组件连接到单个 MS SQL Server 数据库的设置。 RESTful 服务允许用户将数据保存到数据库中以及从中获取数据。

在实现处理数据库连接/方法的类时，我开始努力正确地重用准备好的 SqlCommands和连接。我在 MSDN 上阅读了有关连接池以及如何使用 SqlCommand 的信息。和 SqlParameter .

我的类(class)初始版本如下所示:

public class SqlRepository : IDisposable
{
    private object syncRoot = new object();

    private SqlConnection connection;

    private SqlCommand saveDataCommand;
    private SqlCommand getDataCommand;

    public SqlRepository(string connectionString)
    {
        // establish sql connection
        connection = new SqlConnection(connectionString);
        connection.Open();

        // save data
        saveDataCommand = new SqlCommand("INSERT INTO Table (Operation, CustomerId, Data, DataId, CreationDate, ExpirationDate) VALUES (@Operation, @CustomerId, @Data, @DataId, @CreationDate, @ExpirationDate)", connection);
        saveDataCommand.Parameters.Add(new SqlParameter("Operation", SqlDbType.NVarChar, 20));
        saveDataCommand.Parameters.Add(new SqlParameter("CustomerId", SqlDbType.NVarChar, 50));
        saveDataCommand.Parameters.Add(new SqlParameter("Data", SqlDbType.NVarChar, 50));
        saveDataCommand.Parameters.Add(new SqlParameter("DataId", SqlDbType.NVarChar, 50));
        saveDataCommand.Parameters.Add(new SqlParameter("CreationDate", SqlDbType.DateTime));
        saveDataCommand.Parameters.Add(new SqlParameter("ExpirationDate", SqlDbType.DateTime));
        saveDataCommand.Prepare();

        // get data
        getTripCommand = new SqlCommand("SELECT TOP 1 Data FROM Table WHERE CustomerId = @CustomerId AND DataId = @DataId AND ExpirationDate > @ExpirationDate ORDER BY CreationDate DESC", connection);
        getTripCommand.Parameters.Add(new SqlParameter("CustomerId", SqlDbType.NVarChar, 50));
        getTripCommand.Parameters.Add(new SqlParameter("DataId", SqlDbType.NVarChar, 50));
        getTripCommand.Parameters.Add(new SqlParameter("ExpirationDate", SqlDbType.DateTime));
        getTripCommand.Prepare();
    }

    public void SaveData(string customerId, string dataId, string operation, string data, DateTime expirationDate)
    {
        lock (syncRoot)
        {
            saveDataCommand.Parameters["Operation"].Value = operation;
            saveDataCommand.Parameters["CustomerId"].Value = customerId;
            saveDataCommand.Parameters["CreationDate"].Value = DateTime.UtcNow;
            saveDataCommand.Parameters["ExpirationDate"].Value = expirationDate;
            saveDataCommand.Parameters["Data"].Value = data;
            saveDataCommand.Parameters["DataId"].Value = dataId;

            saveDataCommand.ExecuteNonQuery();
        }
    }

    public string GetData(string customerId, string dataId)
    {
        lock (syncRoot)
        {
            getDataCommand.Parameters["CustomerId"].Value = customerId;
            getDataCommand.Parameters["DataId"].Value = dataId;
            getDataCommand.Parameters["ExpirationDate"].Value = DateTime.UtcNow;

            using (var reader = getDataCommand.ExecuteReader())
            {
                if (reader.Read())
                {
                    string data = reader.GetFieldValue<string>(0);
                    return data;
                }
                else
                {
                    return null;
                }
            }
        }
    }

    public void Dispose()
    {
        try
        {
            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
            }

            DisposeCommand(saveDataCommand);
            DisposeCommand(getDataCommand);
        }
        catch { }
    }

    private void DisposeCommand(SqlCommand command)
    {
        try
        {
            command.Dispose();
        }
        catch (Exception)
        {
        }
    }
}

有几个方面很重要:

• 我正在使用 SqlCommand.Prepare()加快执行命令的过程
• 重复使用命令可避免在每次调用 GetData 和 SaveData 方法时创建新对象，从而不会导致垃圾收集器出现问题
• SqlRepository 只有一个实例类，由 WCF 服务使用。
• 每分钟有很多次调用此服务，因此保持与数据库的连接打开是我想要的。

现在我阅读了更多关于连接池的内容以及强烈建议使用 SqlConnection 的事实。 using 中的对象声明，以确保处置。据我了解，连接池技术负责保持连接打开，即使 Dispose() SqlConnection的方法|已被 using 调用声明。

使用它的方法是有一个 using(SqlConnection connection = new SqlConnection(connectionString))在GetData里面和 SaveData方法。但是，然后 - 至少根据我的直觉 - 我需要在 GetData 中创建 SqlCommands/SaveData方法也是如此。或不？我找不到任何关于如何以这种方式重用命令的文档。也不会调用 SqlCommand.Prepare()如果我每次进入 GetData 都需要准备一个新命令，那将毫无意义/SaveData方法？

如何正确实现 SqlRepository类(class)？现在的方式我相信如果连接中断(可能是因为数据库服务器关闭一段时间并重新启动)，那么 SqlRepository类将不会自动恢复并正常运行。据我所知，这种故障保存场景是在池化技术中处理的。

感谢您的想法和反馈!基督徒

Answer 1

不要重复使用 SqlCommand 实例。

您正在同步数据库访问。

通过您的实现，您将重新使用一个小对象(即使有数千个，这对 GC 来说也没有问题)来交换并发数据库操作。

1. 解除同步锁。
2. 为每个数据库操作创建新的 SqlCommands 实例。
3. 不要调用 Prepare。准备加速数据库操作，但是在 SqlCommand 上执行 ExecuteReader() 后，CommandType = Text 并且参数数量不为零，命令在内部没有准备好。