SQLCE2.0经验总结

一：注意每次操作SQLCE前都Close一次，因为SQLCE2.0只支持一个连接；

二：检测数据库里是否存在某个表用：select table_name from Information_Schema.Tables；

三：SQLCE2.0 不支持存储过程、触发器等，都要用SQL来实现；

四：从XML转换过来的时间要转换一下：

        DateTime dt_c2 = DateTime.Parse(xn.ChildNodes[19].InnerXml);

        dt_c2.ToString("G"), //集装箱作业时间

五：SQLCE的字段类型和SQL Server的不一样，具体见SQLCE帮助文档

六：SQLCE不支持Distict 、top 等函数，Distinct可以用GroupBy来实现

七：SQLCE不支持如下语句：

select 'RT' = case when rt1 is not null then rt1 else '' end +
case when rt2 is not null then rt2 else '' end +
case when rt3 is not null then rt3 else '' end +
case when rt4 is not null then rt4 else '' end +
case when rt5 is not null then rt5 else '' end +
case when rt6 is not null then rt6 else '' end +
case when rt7 is not null then rt7 else '' end
from ct_bay

现在是用程序来实现连接的

八：数据库操作部分：

// execute sql, returns first column in first row as an int
  private int ExecuteScalarInt(string sql)
  {
   int result = 0;
   object o = this.ExecuteScalar(sql);
   if (o != System.DBNull.Value)
    result = Convert.ToInt32(o);
   return result;
  }

  // execute sql, returns first column in first row as a string
  private string ExecuteScalarString(string sql)
  {
   string result = String.Empty;
   object o = this.ExecuteScalar(sql);
   if (o != System.DBNull.Value)
    result = Convert.ToString(o);
   return result;
  }

  // execute sql, returns DataSet with result of query
  // uses specified tableName for table in DataSet
  private DataSet Execute(string tableName, string sql)
  {
   DataSet ds = new DataSet();
   try
   {
    SqlCeDataAdapter da = GetAdapter(sql);
    da.Fill(ds, tableName);
   }
   catch (SqlCeException ex)
   {
    HandleError(ex);
   }
   catch (Exception ex)
   {
    HandleError(ex);
   }
   return ds;
  }

  // execute sql, returns number of rows affected
  protected internal int ExecuteNonQuery(string sql)
  {
   int count=0;
   try
   {
    SqlCeCommand cmd = GetCommand();
    cmd.CommandText = sql;
    count = cmd.ExecuteNonQuery();
   }
   catch (SqlCeException ex)
   {
    HandleError(ex);
   }
   catch (Exception ex)
   {
    HandleError(ex);
   }
   return count;
  }

  private SqlCeDataReader ExecuteDataReaderSingleRow(string sql)
  {
   try
   {
    SqlCeCommand cmd = GetCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = sql;
    m_dtr = cmd.ExecuteReader(CommandBehavior.SingleRow);
   }
   catch (SqlCeException ex)
   {
    HandleError(ex);
   }
   catch (Exception ex)
   {
    HandleError(ex);
   }
   return m_dtr;
  }

  // execute sql, returns the first column of the first row
  private object ExecuteScalar(string sql)
  {
   object result=null;
   try
   {
    SqlCeCommand cmd = GetCommand();
    cmd.CommandText = sql;
    result = cmd.ExecuteScalar();
   }
   catch (SqlCeException ex)
   {
    HandleError(ex);
   }
   catch (Exception ex)
   {
    HandleError(ex);
   }
   return result;
  }

  //
  // private helper functions
  //

  private void OpenConnection()
  {
   // make sure we have open connection
   if (m_con == null)
    m_con = new SqlCeConnection(@"Data Source=/Program Files/ChinaTallyPDACSharp/ChinaTallyDB.sdf;");

   if (m_con.State == ConnectionState.Closed)
    m_con.Open();
  }
  

  // return command object
  private SqlCeCommand GetCommand()
  {
   OpenConnection();

   // create command object
   if (m_cmd == null)
   {
    m_cmd = new SqlCeCommand();
    m_cmd.Connection = m_con;
    m_cmd.CommandType = CommandType.Text;
   }
  
   m_cmd.CommandText = String.Empty;
   return m_cmd;
  }
 
  // return data adapter
  private SqlCeDataAdapter GetAdapter(string sql)
  {
   // make sure we have open connection
   if (m_con == null)
    m_con = new SqlCeConnection(@"Data Source=/Program Files/ChinaTallyPDACSharp/ChinaTallyDB.sdf;");

   if (m_con.State == ConnectionState.Closed){
    try{
     m_con.Open();
    }
    catch(SqlCeException ex){
                    throw ex;
    }
   }
   SqlCeDataAdapter scda = new SqlCeDataAdapter(sql, m_con);
   return scda;
  }