Error in server 'executenonquery requires an open and available connection'

Question

I got this error on server not in local and when facing this error, then i re-upload that related class file. after doing this problem solved but not permanently.

Error:

executenonquery requires an open and available connection. The connection's current state is open.

Code:

int n;

try
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = DataConnection.Con;
        cmd.CommandText = "sp_InsertUpdateDeleteValidationDate";
        cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0;
        cmd.Parameters.AddWithValue("@Task", "CheckExist");
        cmd.Parameters.AddWithValue("@id", 0);
        cmd.Parameters.AddWithValue("@AdId", "");
        cmd.Parameters.AddWithValue("@Username", "");
        cmd.Parameters.AddWithValue("@DOE", DOE);
        cmd.Parameters.AddWithValue("@ExpieryDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@DOR", DateTime.Now);
        cmd.Parameters.Add("@flag", SqlDbType.Int).Direction = ParameterDirection.Output;

        if (cmd.Connection.State  == ConnectionState.Closed)
        {
            cmd.Connection.Open();
        }
        cmd.ExecuteNonQuery();
        n = Convert.ToInt32(cmd.Parameters["@flag"].Value);
        return n;
    }
}
catch (SqlException Ex)
{

    return 0;
}

Answer 1

You only create one connection in your DataConnection class. You should create a new connection for each database call and let the driver's connection pooling take care of efficiently reusing them.

change your DataConnection class to this:

public class DataConnection
{
    public static SqlConnection Con
    {
        get 
        { 
            return new SqlConnection(ConfigurationManager
                .ConnectionStrings["conn"].ConnectionString); 
        }
    }
}

and use a using statement when you use the connection like in ekad's answer:

using (SqlConnection conn = DataConnection.Con)
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        //use the command here
    }
}

Answer 2

Looks like your SqlConnection is never closed. Try to use using statement to make sure that the SqlConnection is closed after executing cmd.ExecuteNonQuery()

int n;

try
{
    using (SqlConnection conn = DataConnection.Con)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "sp_InsertUpdateDeleteValidationDate";
            cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0;
            cmd.Parameters.AddWithValue("@Task", "CheckExist");
            cmd.Parameters.AddWithValue("@id", 0);
            cmd.Parameters.AddWithValue("@AdId", "");
            cmd.Parameters.AddWithValue("@Username", "");
            cmd.Parameters.AddWithValue("@DOE", DOE);
            cmd.Parameters.AddWithValue("@ExpieryDate", DateTime.Now);
            cmd.Parameters.AddWithValue("@DOR", DateTime.Now);
            cmd.Parameters.Add("@flag", SqlDbType.Int).Direction = ParameterDirection.Output;

            conn.Open();        
            cmd.ExecuteNonQuery();
            n = Convert.ToInt32(cmd.Parameters["@flag"].Value);
            return n;
        }
    }
}
catch (SqlException Ex)
{

    return 0;
}