[Solved] Parallel For loop error : The connection was not closed. The connections current state

pandeyism
 
on Feb 20, 2021 12:23 AM
413 Views

Insert using parallel for loop error : The connection was not closed. The connections current state

Please check code revert ASAP.

protected void Insert(object sender, EventArgs e)
{

    string constr = "Data Source=;Initial Catalog=Test;User ID=uid;Password=psw;";
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = null;
    try
    {

        Parallel.For(0, 100, j =>
        {
            cmd = new SqlCommand("INSERT INTO tbl_CustomerData VALUES(@Name,@Country)", con);

            cmd.Parameters.AddWithValue("@Name", "Ajeet");
            cmd.Parameters.AddWithValue("@Country", "India");
            if (cmd.Connection.State != ConnectionState.Open)
            {
                cmd.Connection.Close();
                cmd.Connection.Open();
            }
            cmd.ExecuteNonQuery();
        });
    }
    catch (SqlException exce)
    {

        throw;
    }
    finally
    {

        con.Close();
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Feb 20, 2021 12:31 AM
on Feb 20, 2021 12:32 AM

Hi Ajeet,

Use below code.

string constr = "Data Source=;Initial Catalog=Test;User ID=uid;Password=psw;";
try
{
    Parallel.For(0, 100, j =>
    {
        SqlConnection con = new SqlConnection(constr);
        SqlCommand cmd = null;
        con.Open();
        cmd = new SqlCommand("INSERT INTO Customers VALUES(@Name,@Country)", con);
        cmd.CommandTimeout = 0;
        cmd.Parameters.AddWithValue("@Name", "Ajeet" + j);
        cmd.Parameters.AddWithValue("@Country", "India" + j);
        cmd.ExecuteNonQuery();
        con.Close();
    });
}
catch (SqlException exce)
{
    throw;
}