title: Review of Connection handling and Data access layer using C#, sql server compact 3.5
body: <p>I am developing a stand alone application, using sql server compact 3.5 sp2 which runs in process. No Database writes involved. Its purely a reporting application. Read many articles about reusing open db connections in case of sql compact(connection pooling) due to its different behavior from sql server.</p>
  
  <p>Quoting the comments from a quiz opened by Erik Ejlskov Jensen <a href="http://beyondrelational.com/quiz/sqlserver/general/2010/questions/sqlserver-quiz-general-2010-erik-ejlskov-jensen-connection-handling-in-sql-server-compact.aspx?pg=2" rel="nofollow" title="connection handling in sql svr compact">Link</a>, where its discussed an open early close late strategy for sql server compact databases. Based on this, with my limited experience I have implemented a not so complex Connection handling+Data access layer. Basically I am unsure if i am writing it in a recommended way. Please could any one point me in the right direction with rooms for improvement in this connection handling approach i have written?</p>
  
  <h2>The DbConnection class</h2>
  
  <p><div class="snippet" data-lang="js" data-hide="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>public class FkDbConnection
  {
    private static SqlCeConnection conn; 
    private static DataTable table;
    private static SqlCeCommand cmd;
        
    ~FkDbConnection() { conn = null; }
  
    //This will be called when the main winform loads and connection will be open as long as the main form is open
    public static string ConnectToDatabase()
    {
       try {
        conn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Connstr"].ConnectionString);
        if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
        {
            conn.Open();
        }
        return "Connected";
       }
       catch(SqlCeException e) { return e.Message; }
    }
  
    public static void Disconnect()
    {
      if (conn.State == ConnectionState.Open || conn.State == ConnectionState.Connecting || conn.State == ConnectionState.Fetching)
      {
        conn.Close();
        conn.Dispose();
        //conn = null; //does conn have to be set to null?
      }
      //else the connection might be already closed due to failure in opening it
      else if (conn.State == ConnectionState.Closed) {
        conn.Dispose();
        //conn = null; //does conn have to be set to null?
      }
    }
  
  /// &lt;summary&gt;
  ///  Generic Select DataAccess
  /// &lt;/summary&gt;
  /// &lt;param name="sql"&gt; the sql query which needs to be executed by command object &lt;/param&gt;
  public static DataTable ExecuteSelectCommand(SqlCeCommand comm)
  {
  if (conn != null &amp;&amp; conn.State == ConnectionState.Open)
              {
                  #region block using datareader
                  using (table = new DataTable())
                  {
                      //using statement needed for reader? Its closed below
                      using (SqlCeDataReader reader = comm.ExecuteReader())
                      {
                          table.Load(reader);
                          reader.Close(); //is it needed?
                      }
                  }
                  #endregion
                  # region block using dataadpater
                  //I read DataReader is faster?
                  //using (SqlCeDataAdapter sda = new SqlCeDataAdapter(cmd))
                  //{
                  //    using (table = new DataTable())
                  //    {
                  //        sda.Fill(table);
                  //    }
                  //}
                  #endregion
              //}
              }
              return table;
          }
  
          /// &lt;summary&gt;
          ///  Get Data
          /// &lt;/summary&gt;
          /// &lt;param name="selectedMPs"&gt; string csv, generated from a list of selected posts(checkboxes) from the UI, which forms the field names used in SELECT &lt;/param&gt;
          public static DataTable GetDataPostsCars(string selectedMPs)
          {
              DataTable dt;
              //i know this it not secure sql, but will be a separate question to pass column names to select as parameters
              string sql = string.Format(
                  "SELECT " + selectedMPs + " "+
                  "FROM GdRateFixedPosts");
              using (cmd = new SqlCeCommand(sql,conn))
              {
                  cmd.CommandType = CommandType.Text;
                  //cmd.Parameters.Add("@fromDateTime",DbType.DateTime);
                  //cmd.Parameters.Add("@toDateTime",DbType.DateTime);
                  dt = ExecuteSelectCommand(cmd);
              }
              return dt;
          }
  
      }</code></pre>
  </div>
  </div>
  </p>
  
  <p>The Main UI (Form) in which connection opened, for connection to be open through out. 2 other reporting forms are opened from here. Closing main form closes all, at which point connection is closed and disposed.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>private void FrmMain_Load(object sender, EventArgs e)
  {
    string str = FkDbConnection.ConnectToDatabase();
    statStDbConnection.Items[0].Text = str;
  }
  
  private void FrmMain_FormClosing(object sender, FormClosingEventArgs e)
  {
      FkDbConnection.Disconnect();
  }</code></pre>
  </div>
  </div>
  </p>
  
  <p>Comments, improvements on this connection class much appreciated. See my questions also inline code
  Thank you.</p>
  
  <p>Updated classes as per Erik's suggestion. with a correction on ExecuteSelectCommand() and an additional class which will instantiate command objs in "using" and pass data to the UI. I intent to add separate GetDataForFormX() methods since the dynamic sql for each form may differ. Hope this is ok?</p>
  
  <p>Correction to Erik's code:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public static DataTable ExecuteSelectCommand(SqlCeCommand comm)
  {
    var table = new DataTable();
    if (conn != null &amp;&amp; conn.State == ConnectionState.Open)
    {
       comm.Connection = conn;
       using (SqlCeDataReader reader = comm.ExecuteReader())
       {
         table.Load(reader);
       }
    }
    return table;
  }
  </code></pre>
  
  <p>New FkDataAccess class for passing Data to UI</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class FkDataAccess
  {
  
    public static DataTable GetDataPostsCars(string selectedMPs)
    {
      var table = new DataTable();
      string sql = string.Format(
                  "SELECT " + selectedMPs + " " +
                  "FROM GdRateFixedPosts");
      if (FkDbConnection.conn != null &amp;&amp; FkDbConnection.conn.State == ConnectionState.Open)
      {
        using (SqlCeCommand cmd = new SqlCeCommand(sql, FkDbConnection.conn))
        {
          cmd.CommandType = CommandType.Text;
         //cmd.Parameters.Add("@fromDateTime",DbType.DateTime);
          table = FkDbConnection.ExecuteSelectCommand(cmd);
        }
      }
    return table;    
    }
  
    //public static DataTable GetDataXY(string selectedvals)
    // and so on
  
  }
  </code></pre>