C# System.InvalidOperationException on sqlconnection close

Question

System.InvalidOperationException: Timeout expired.
The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

private void uploadDbButton_Click(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor;

    countLabel.Text = autoid("Olvasasok", "olvasas_szama");
 
        string query, itemVlaue = "";
       
        SqlCommand cmd;
        
        if(kiadas.Checked)
        {
            radio = "K";
        }
        else if (bevetelezes.Checked)
        {
            radio = "B";
        }

   


        if (inventoryList.Items.Count > 0)
        {
            for (int i = 0; i < inventoryList.Items.Count; i++)
            {
                connect = new SqlConnection(conStr);
                DateTime time = DateTime.Now;
                String format = "yyyy-MM-dd";
                DateTime ido = DateTime.Now;
                String forma = "HH:mm:ss";
             
            
                itemVlaue = inventoryList.Items[i].Text;
                query = "INSERT INTO Olvasasok (olvasas_szama, rfid_tag, datum, ido, irany)VALUES ('" + countLabel.Text +"', '" + itemVlaue + "','" + DateTime.Now.ToString(format) +"' , '" + DateTime.Now.ToString(forma) + "' , '" + radio +"')";
                cmd = new SqlCommand(query, connect);

                

                if (connect.State == ConnectionState.Closed)
                {
                    connect.Open();
                }


                cmd.ExecuteNonQuery();
            }

            connect.Close();
            functionCallStatusLabel.Text = "Feltöltés sikerült...";

        }
        else
        {
            MessageBox.Show("Csatlakozz rá egy olvasóra...");

        }
        inventoryList.Items.Clear();
        m_TagTable.Clear();
        m_TagTotalCount = 0;
        totalTagValueLabel.Text = "0(0)";
        this.uploadDbButton.Enabled = false;


    }

Answer 1

Potentially, a lot of SqlConnection can be open and never closed :

if (inventoryList.Items.Count > 0)
{
    for (int i = 0; i < inventoryList.Items.Count; i++)
    {
        // For each loop, create new SqlConnection object
        connect = new SqlConnection(conStr);
        // Already true, because the connect is new SqlConnection object
        if (connect.State == ConnectionState.Closed)
            connect.Open();
        ...
    }
    //Close only the last SqlConnection
    connect.Close();
}

Maybe you can try to encapsulate the SqlConnection in using :

if (inventoryList.Items.Count > 0)
{
    // Create one SqlConnection
    using(var connect = new SqlConnection(conStr))
    {
        // Open one SqlConnection
        connect.Open();
        for (int i = 0; i < inventoryList.Items.Count; i++)
        {
            ...
        }
    }
    // End of using, the SqlConnection is automatically closed
}