The sqlparameter is already contained by another sqlparametercollection -Error

Introduction

I have faced issues when I was debugging my project with SqlCommand and Parameters collection. Suddenly application throws error “the sqlparameter is already contained by another sqlparametercollection”. Finally I found solutions and here is that for you.

Why this error?

Let’s say you have created a SqlCommand and added few no of parameter in your command with query and then execute command with ExecuteNonQuery. Finally you have closed the connection as well. Even if you have closed the connection, SqlCommand object was there in memory, with parameters collection.

Problem Code

private void Save(string id, string name)
    {

        SqlConnection connection = new SqlConnection("connstring");
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "sp_emp_save";
            SqlParameter[] paramscol = new SqlParameter[2];
            // set values to params.
            cmd.Parameters.AddRange(paramscol);
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            cmd.ExecuteNonQuery();

        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
        }
    }

In above code, we have closed connection, but if calls save method again .Net throw error the sqlparameter is already contained by another sqlparametercollection.Because command object still does not disposed have give best two solutions for this problem below.

1.    Clear parameters in command object
After call ExecuteNonQuery method, you have to call clear method as well.

cmd.ExecuteNonQuery();
cmd.Parameters.Clear();

2.    Compose code within using block

private void Save(string id, string name)
    {

        try
        {
            using (SqlConnection connection = new SqlConnection("connstring"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_emp_save";
                    SqlParameter[] paramscol = new SqlParameter[2];
                    // set values to params.
                    cmd.Parameters.AddRange(paramscol);
                    if (connection.State != ConnectionState.Open)
                    {
                        connection.Open();
                    }
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (Exception)
        {

            throw;
        }

    }

I hope this is best solution to ignore unnecessary memory leak and connection leaks as well. Out of the using block, no more command or connection objects.
Hopes help and save your lots of times. Thank you.