如何避免打开多个连接和命令 [英] How to avoid opening multiple connections and commands

问题描述

我有一个代码，在此代码中，我还调用了返回类以获取值(请参见下文).我想知道如何改善这一点，以便仅打开单个连接，同时不会引发错误.

I have a code and in this code I am also calling a return class to get a value (see below). I want to know how can I improve this so only single connection will be opened and at the same time it will not throw errors.

这是我的代码:

try
{
    sql_connection = new SqlConnection(my_connection);
    sql_connection.Open();

    sql_command = new SqlCommand("sp_add_item_to_check", sql_connection);
    sql_command.CommandType = CommandType.StoredProcedure;
    sql_command.Parameters.AddWithValue("@business_date", public_var.get_business_date()).SqlDbType = SqlDbType.Date;
    sql_command.Parameters.AddWithValue("@check_guid", new Guid(Convert.ToString(dataCheck.Tag))).SqlDbType = SqlDbType.UniqueIdentifier;
    sql_command.Parameters.AddWithValue("@item_id", item_id).SqlDbType = SqlDbType.Int;
    sql_command.Parameters.AddWithValue("@item_name", item_name).SqlDbType = SqlDbType.VarChar;

    int result_rows = sql_command.ExecuteNonQuery();
}
catch (Exception exp)
{
    XtraMessageBox.Show(String.Format("Error adding " + "!!{0}{1}", Environment.NewLine, exp.Message), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    return;
}
finally
{
    if (sql_reader != null) { sql_reader.Close(); sql_reader.Dispose(); }
    if (sql_connection != null) { if (sql_connection.State == ConnectionState.Open) { sql_connection.Close(); sql_connection.Dispose(); } }
}

这是get_business_date类:

and this is the get_business_date class:

public DateTime get_business_date()
{
    try
    {
        sql_connection = new SqlConnection(my_connection);
        sql_connection.Open();

        sql_command = new SqlCommand("sp_get_business_date", sql_connection);
        sql_command.CommandType = CommandType.StoredProcedure;
        sql_reader = sql_command.ExecuteReader();

        if (sql_reader.Read())
        {
            if (sql_reader.HasRows)
            {
                business_date = Convert.ToDateTime(sql_reader["business_date"]);
            }
        }
    }
    catch (Exception exp)
    {
        XtraMessageBox.Show(String.Format("Error reading business date!!{0}{1}", Environment.NewLine, exp.Message), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        if (sql_reader != null) { sql_reader.Close(); sql_reader.Dispose(); }
        if (sql_connection != null) { if (sql_connection.State == ConnectionState.Open) { sql_connection.Close(); sql_connection.Dispose(); } }
    }

    return business_date;
}

谢谢

Jassim

推荐答案

Jassim，您好

Hi Jassim,

.Net已为您管理了连接池，因此您无需担心像使用旧的ASP那样重用连接. 您应保持连接打开的时间越短越好.因此，您要打开连接，执行查询或存储过程，然后关闭连接.尽管这听起来很昂贵，但它利用了ADO.NET的内置连接 集中.关闭连接时，它会返回到池中并重新使用，因此不会对性能造成影响.

.Net has connection pooling already managed for you so you don't need to worry about reusing connections like you might have with old asp. You should keep connections open as short a time as possible. Thus, you want to open a connection, execute a query or stored procedure and then close the connection. Although this sounds expensive, it leverages ADO.NET's built-in connection pooling. When you close a connection, it is returned to a pool and reused so you do not suffer a performance hit.

sql_connection.Open();
comm1.ExecuteReader();
comm2.ExecuteReader();

始终将它们打开尽可能短的时间.

Always keep them open for as short a time as possible.

这也是讨论ADO.Net中的异步db调用的好线程.

Here is also a good thread talking about asynchronous db calls in ADO.Net.

ADO.Net最佳实践-单与进行异步数据库调用时的多个连接

最诚挚的问候，

克里斯汀

这篇关于如何避免打开多个连接和命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！