title: Too many sessions are open error, while creating connection to Sql Server Ce
body: <p>My C# application uses <em>Sql Server Compact Edition</em> in which I got connections to the database here and there. I use this method to run <code>ExecuteScalar</code> and I use it repetitive in some point.</p>
  
  <p>This is the class:</p>
  
  <pre><code>//_cnt &amp; _cmd are private variables
  public static object Run_ExecuteScaler(string query)
  {
      object objResult;
     _cnt = new SqlCeConnection {ConnectionString = ConnectionString};
  
      _cmd = new SqlCeCommand
          {
              Connection = _cnt,
              CommandType = System.Data.CommandType.Text,
              CommandText = query
          };
  
      if (_cnt.State != System.Data.ConnectionState.Open)
          _cnt.Open();
  
      objResult = _cmd.ExecuteScalar();
      _cmd.Dispose();
  
      if (_cnt.State != System.Data.ConnectionState.Closed)
          _cnt.Close();
      _cnt.Dispose();
      return objResult;
  }
  </code></pre>
  
  <p>I use this line in a <code>foreach</code> loop which uses the above method to ctreate a connection to the database and run the command. It then <strong>closes</strong> the connection and <strong>disposes</strong> it.</p>
  
  <pre><code>var middleCat = RSSql.Run_ExecuteScaler(string.Format(
                  "SELECT Implication FROM tblMiddleCategory where IDMiddleCategory='{0}'",someInt));
  </code></pre>
  
  <p>But I'm getting this error after second loop:</p>
  
  <blockquote>
    <p>Too many sessions are open</p>
  </blockquote>
  
  <p>I realize that this usually happens when a connection hasn't been disposed. but I do it in my method. So Am I doing it in a wrong way?</p>
  
  <p><strong>Update</strong></p>
  
  <p>I changed the method to use <code>using</code> statement as @Damien_The_Unbeliever offered:</p>
  
  <pre><code>public static object Run_ExecuteScaler(string query)
  {
      object objResult = null;
      using (_cnt = new SqlCeConnection( ConnectionString))
      {
          _cmd = new SqlCeCommand
          {
              Connection = _cnt,
              CommandType = System.Data.CommandType.Text,
              CommandText = query
          };
          if (_cnt.State != System.Data.ConnectionState.Open)
              _cnt.Open();
          objResult = _cmd.ExecuteScalar();
      }
  
      return objResult;
  }
  </code></pre>
  
  <p>But I'm still getting the same error.</p>