Review Of Connection Handling And Data Access Layer Using C#, Sql Server Compact 3.5

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 articl

Solution 1:

Too much code in your data access class, makes it unreadable and hard to maintain

The SqlCeonnection object will be disposed when you close it (and when the app closes)

You cannot dispose the DataTable if you want to use it elsewhere, and it is an completely managed object anyway.

It is a good pattern to limit your classes to a single responsibility

publicclassFkDbConnection
{
  privatestatic SqlCeConnection conn; 

  ~FkDbConnection() { conn = null; }

  //This will be called when the main winform loads and connection will be open as long as the main form is openpublicstaticvoidConnectToDatabase()
  {
      // Handle failure to open in the caller
      conn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Connstr"].ConnectionString);
      conn.Open();
  }

  publicstaticvoidDisconnect()
  {
    if (conn != null)
    {
      conn.Close();
    }
  }

publicstatic DataTable ExecuteSelectCommand(SqlCeCommand comm)
{
    var table = new DataTable();
    if (conn != null && conn.State == ConnectionState.Open)                
    {

       comm.Connection = conn;
       using (SqlCeDataReader reader = comm.ExecuteReader())
       {
          table.Load(reader);
       }           
    }
    return table;
}

privatevoidFrmMain_Load(object sender, EventArgs e)
{
  try
  {
     FkDbConnection.ConnectToDatabase();
     statStDbConnection.Items[0].Text = "Connected";
  }
  catch (Exception ex)
  {
     //Inform use that we canot proceed, what she can do to remedy, and exit
  }
}

privatevoidFrmMain_FormClosing(object sender, FormClosingEventArgs e)
{
    FkDbConnection.Disconnect();
}