C# .NET - Not allowed to change the current state of connection string property.

Asked By Ghulam Siddiq on 31-Mar-14 09:10 AM
Bellow is my code i am facing exception that "Not allowed to change the current state of the connection string property.The connection current state is open."
 
private void LoadListBoxCitiesCountries()
      {
        
        try
       {
          citCountr.OpenConnection(str);   //THE EXCEPTION RAISES HERE..
          SqlDataAdapter sda = citCountr.GetCities();
          DataSet ds = new DataSet();

          sda.Fill(ds);

          lbCities.ItemsSource = null;
          lbCities.ItemsSource = ds.Tables[0].DefaultView;

          SqlDataAdapter sda2 = citCountr.GetCountries();
          DataSet ds2 = new DataSet();

          sda2.Fill(ds2);

          lbCountries.ItemsSource = null;
          lbCountries.ItemsSource = ds2.Tables[0].DefaultView;

          citCountr.CloseConnnection();
      
        }
        catch (Exception ex)
        {
          MyErrorMessage(ex);

        }
        finally
        {
         citCountr.CloseConnnection();
       }
        
      }
......................................Any help most appreciated.............If you need the whole code then tell me i will post the whole  code......
Robbe Morris replied to Ghulam Siddiq on 02-Apr-14 10:20 PM
You've declared a class level variable named citCountr and are reusing it across various methods.  In old VB 6 days, this was common practice.  It is strongly discouraged in .NET.

In .NET, the connection pool will automatically manage the actual closing of connections and whether it maintains an open connection and passes it on to other OpenConnection requests.  There is no need to try and manage this in your code.

Open and close locally declared SqlConnection objects inside your methods.

using(SqlConnection conn = new SqlConnection(str))
{
   using (SqlDataAdapter adapter = new SqlDataAdaper(conn))
   {
    // do stuff
   }
}

The "using" statements are short cuts for try/catch/finally blocks making sure that any class that implements IDisposable gets its .Dispose() method run with manually typing the code to make this happen.
Ghulam Siddiq replied to Robbe Morris on 03-Apr-14 01:05 AM
I have fixed my problem Because the connection was already open.I have checked my connection with if else statement to find that where is the connection open.
for checking the connection wither the connection is open or close. use the following code
 
if (cn.State == ConnectionState.Open)
{
MessageBox.Show("open");
}
else {
MessageBox.Show("closed");
}
...................................................................................................
and hence i put the following line of code

citCountr.CloseConnnection();
 

Before
citCountr.CloseConnnection();
 
and my problem gone away..................
Thanks.........and happy..................
Robbe Morris replied to Ghulam Siddiq on 03-Apr-14 08:32 AM
You've fixed this specific problem but what you haven't addressed is keeping connections open too long before returning them to the connection pool.  While your code works, it goes against best practices for performance and modular code.

In the future, just make sure you don't show this technique you are using in a job interview.  You won't get hired.