评论连接处理和数据访问层使用C＃，SQL Server精简3.5 [英] Review of Connection handling and Data access layer using C#, sql server compact 3.5

问题描述

我正在开发独立的应用程序，使用的SQL Server Compact 3.5 SP2中运行的进程。没有数据库写入参与。其纯粹的报表应用程序。看了很多篇关于重用的情况下，SQL紧凑型（连接池），由于其从SQL Server的不同行为开放数据库连接。

引述打开由Erik Ejlskov詹森<一个测验的评论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"相对=nofollow标题=连接处理的SQL SVR紧凑>链接，在那里它讨论了一个开放的早关门较晚战略的SQL Server Compact数据库。在此基础上，用我有限的经验，我已经实现了一个不那么复杂的连接处理+数据访问层。基本上，我不能确定，如果我写在一个推荐的方法。请可以任何一个有房指明了正确的方向改进在这方面的处理方法，我已经写了？

的的DbConnection类

公共类FkDbConnection {   私有静态的SqlCeConnection康恩;   私有静态数据表表;   私有静态SqlCeCommand CMD;          〜FkDbConnection（）{康恩= NULL; }   //这被称为当主WinForm的负载和连​​接将开放，只要主要形式是开放的   公共静态字符串ConnectToDatabase（）   {      尝试 {       康恩=新的SqlCeConnection（ConfigurationManager.ConnectionStrings [Connstr]的ConnectionString。）;       如果（conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken）       {           conn.Open（）;       }       返回已连接;      }      赶上（SqlCeException E）{返回e.Message; }   }   公共静态无效断开（）   {     如果（conn.State == ConnectionState.Open || conn.State == ConnectionState.Connecting || conn.State == ConnectionState.Fetching）     {       conn.Close（）;       conn.Dispose（）;       //康恩= NULL; //确实conn将已被设置为空？     }     //否则连接可能已经打开它关闭，由于故障     否则，如果（conn.State == ConnectionState.Closed）{       conn.Dispose（）;       //康恩= NULL; //确实conn将已被设置为空？     }   } ///＆LT;总结＆gt; ///通用选择数据访问 ///＆LT; /总结＆gt; ///＆LT; PARAM NAME =SQL＆GT;这需要通过命令对象＆LT要执行的SQL查询; /参数＆GT; 公共静态数据表ExecuteSelectCommand（SqlCeCommand通讯） { 如果（conn将= NULL和放大器;！＆安培; conn.State == ConnectionState.Open）             {                 使用DataReader的＃区域块                 使用（表=新的DataTable（））                 {                     //使用需要的读者发言？低于其关闭                     使用（SqlCeDataReader读卡器= comm.ExecuteReader（））                     {                         table.Load（读卡器）;                         reader.Close（）; //需要它？                     }                 }                 #endregion                 ＃区域使用dataadpater块                 //我读DataReader的比较快？                 //使用（SqlCeDataAdapter SDA =新SqlCeDataAdapter（CMD））                 // {                 //使用（表=新的DataTable（））                 // {                 // sda.Fill（表）;                 //}                 //}                 #endregion             //}             }             返回表;         }         ///＆LT;总结＆gt;         ///获取数据         ///＆LT; /总结＆gt;         ///＆LT; PARAM NAME =selectedMPs＆GT;字符串的csv，从用户界面，从而形成在SELECT中所使用的字段名称选中的帖子（复选框）的列表产生; /参数＆GT;         公共静态数据表GetDataPostsCars（字符串selectedMPs）         {             数据表DT;             //我知道这是不是安全的SQL，但将是一个单独的问题通过列名来选择作为参数             字符串SQL =的String.Format（                 选择+ selectedMPs ++                 FROM GdRateFixedPosts）;             使用（CMD =新SqlCeCommand（SQL，康涅狄格州））             {                 cmd.CommandType = CommandType.Text;                 //cmd.Parameters.Add("@fromDateTime",DbType.DateTime）;                 //cmd.Parameters.Add("@toDateTime",DbType.DateTime）;                 DT = ExecuteSelectCommand（CMD）;             }             返回DT;         }     }

主界面（表格），其中连接打开，用于连接通过了公开。 2其他报告形式都是从这里打开。关闭主窗体关闭所有，此时连接关闭和处置。

私人无效FrmMain_Load（对象发件人，EventArgs的） {   字符串str = FkDbConnection.ConnectToDatabase（）;   statStDbConnection.Items [0]。文=海峡; } 私人无效FrmMain_FormClosing（对象发件人，FormClosingEventArgs E） {     FkDbConnection.Disconnect（）; }

意见，改进这个连接类多AP preciated。见我的问题还内嵌code 谢谢你。

更新类按埃里克的建议。与ExecuteSelectCommand修正（）和将实例命令OBJ文件中的使用和数据传递给UI一个附加的类。我打算增加单独GetDataForFormX（）方法，因为动态SQL每种形式可能有所不同。希望这是确定的？

更正Erik的code：

公共静态数据表ExecuteSelectCommand（SqlCeCommand通讯） {   无功表=新的DataTable（）;   如果（conn将= NULL和放大器;！＆安培; conn.State == ConnectionState.Open）   {      comm.Connection =康涅狄格州;      使用（SqlCeDataReader读卡器= comm.ExecuteReader（））      {        table.Load（读卡器）;      }   }   返回表; }

新FkDataAccess类将数据传递到用户界面

公共类FkDataAccess {   公共静态数据表GetDataPostsCars（字符串selectedMPs）   {     无功表=新的DataTable（）;     字符串SQL =的String.Format（                 选择+ selectedMPs ++                 FROM GdRateFixedPosts）;     如果（FkDbConnection.conn = NULL和放大器;！＆安培; FkDbConnection.conn.State == ConnectionState.Open）     {       使用（SqlCeCommand CMD =新的SqlCeCommand（SQL，FkDbConnection.conn））       {         cmd.CommandType = CommandType.Text;        //cmd.Parameters.Add("@fromDateTime",DbType.DateTime）;         表= FkDbConnection.ExecuteSelectCommand（CMD）;       }     }   返回表;   }   //公共静态数据表GetDataXY（字符串selectedvals）   // 等等 }

解决方案

太多code在数据访问类，使得它无法读取，难以维持

当您关闭它（和应用程序关闭时）的SqlCeonnection对象将被全部销毁。

您可以将废弃的数据表，如果你想在其他地方使用它，它是一个完全管理的对象呢。

这是一个很好的方式来限制你的类单一责任

公共类FkDbConnection {   私有静态的SqlCeConnection康恩;   〜FkDbConnection（）{康恩= NULL; }   //这被称为当主WinForm的负载和连​​接将开放，只要主要形式是开放的   公共静态无效ConnectToDatabase（）   {       //处理失败的调用者打开       康恩=新的SqlCeConnection（ConfigurationManager.ConnectionStrings [Connstr]的ConnectionString。）;       conn.Open（）;   }   公共静态无效断开（）   {     如果（conn将！= NULL）     {       conn.Close（）;     }   } 公共静态数据表ExecuteSelectCommand（SqlCeCommand通讯） {     无功表=新的DataTable（）;     如果（conn将= NULL和放大器;！＆安培; conn.State == ConnectionState.Open）     {        comm.Connection =康涅狄格州;        使用（SqlCeDataReader读卡器= comm.ExecuteReader（））        {           table.Load（读卡器）;        }     }     返回表; }

私人无效FrmMain_Load（对象发件人，EventArgs的） {   尝试   {      FkDbConnection.ConnectToDatabase（）;      statStDbConnection.Items [0]。文=已连接;   }   赶上（例外前）   {      //通知使用我们canot继续，她能做的补救，并退出   } } 私人无效FrmMain_FormClosing（对象发件人，FormClosingEventArgs E） {     FkDbConnection.Disconnect（）; }

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.

Quoting the comments from a quiz opened by Erik Ejlskov Jensen Link, 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?

The DbConnection class

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?
    }
  }

/// <summary>
///  Generic Select DataAccess
/// </summary>
/// <param name="sql"> the sql query which needs to be executed by command object </param>
public static DataTable ExecuteSelectCommand(SqlCeCommand comm)
{
if (conn != null && 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;
        }

        /// <summary>
        ///  Get Data
        /// </summary>
        /// <param name="selectedMPs"> string csv, generated from a list of selected posts(checkboxes) from the UI, which forms the field names used in SELECT </param>
        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;
        }

    }

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.

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();
}

Comments, improvements on this connection class much appreciated. See my questions also inline code Thank you.

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?

Correction to Erik's code:

public static 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;
}

New FkDataAccess class for passing Data to UI

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 && 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

}

解决方案

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

public class FkDbConnection
{
  private static 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 open
  public static void ConnectToDatabase()
  {
      // Handle failure to open in the caller
      conn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Connstr"].ConnectionString);
      conn.Open();
  }

  public static void Disconnect()
  {
    if (conn != null)
    {
      conn.Close();
    }
  }

public static 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;
}

private void FrmMain_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
  }
}

private void FrmMain_FormClosing(object sender, FormClosingEventArgs e)
{
    FkDbConnection.Disconnect();
}

这篇关于评论连接处理和数据访问层使用C＃，SQL Server精简3.5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！