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;


    }

Hello, please could you tell us what exactly is your question? — Manu
– Manu, Commented Mar 31, 2021 at 11:25
Does this answer your question? C# Data Connections Best Practice? Always dispose you SQL connection and reader objects with using, and always parameterize queries properly — Charlieface
– Charlieface, Commented Mar 31, 2021 at 12:04
I have a reader program,and when I want to upload 200-300 to the database records it will print out the error — Ujlaki Soma
– Ujlaki Soma, Commented Apr 1, 2021 at 8:34

vernou · Accepted Answer · 2021-03-31 10:54:18Z

0

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
}

answered Mar 31, 2021 at 10:54

vernou

7,6825 gold badges40 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ujlaki Soma Over a year ago

Thanks for the reply.........Thread wouldn't be better?

vernou Over a year ago

To parallelize? You can use Thread, but prefer to use TPL.

Ujlaki Soma Over a year ago

Can you help me how can i use TPL for my code?

Collectives™ on Stack Overflow

C# System.InvalidOperationException on sqlconnection close

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related