Too many sessions are open error, while creating connection to Sql Server Ce
<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>