none
How to avoid opening multiple connections and commands RRS feed

  • Question

  • Hi,

    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.

    Here is my code:

    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(); } }
    }
    

    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;
    }
    

    Thanks,

    Jassim

    Saturday, December 10, 2016 12:35 AM

All replies

  • Hi Jassim,

    .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.

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

    ADO.Net best practice - Single vs multiple Connections when making asynchronous db calls

    Best regards,

    Kristin


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Kristin Xie Wednesday, December 14, 2016 1:31 AM
    Monday, December 12, 2016 2:43 AM
  • Instead of trying to answer your question, I think reading about Isolation Levels in T-SQL will help you fix that error.
    • Proposed as answer by Kristin Xie Wednesday, December 14, 2016 1:32 AM
    Monday, December 12, 2016 9:14 AM
    Answerer