PDA项目 SQL CE 数据库注意事项

一：注意每次操作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; }