helper类

  

    class SqlCeHelper : IDisposable
    {
        private SqlCeConnection connection;
        private SqlCeCommand command;
        private const string connectionString = "Data Source=/DB/db.sdf";

        #region Open/Close
        public void Open()
        {
            try
            {
                connection = new SqlCeConnection(connectionString);
                command = connection.CreateCommand();
                command.Connection = connection;
                command.CommandType = CommandType.Text;

                connection.Open();
            }
            catch (DataException e)
            {
                Console.WriteLine(e.Message);
            }
        }

        public void Close()
        {
            connection.Close();
            connection.Dispose();
        }

        public void Dispose()
        {
            connection.Close();
            connection.Dispose();
            command.Dispose();
        }
        #endregion

        #region Operatons
        public SqlCeDataReader ExecuteReader(string sql)
        {
            command.CommandText = sql;
            SqlCeDataReader reader = null;
            try
            {
                reader = command.ExecuteReader();
            }
            catch (DataException e)
            {
                Console.WriteLine(e.Message);
            }
            return reader;
        }

        public DataSet ExecuteDataSet(string sql)
        {
            command.CommandText = sql;
            SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);
            DataSet ds = new DataSet(); ;

            try
            {
                adapter.Fill(ds);
            }
            catch (DataException e)
            {
                Console.WriteLine(e.Message);
            }
            return ds;
        }

        public int ExecuteNonQuery(string sql)
        {
            command.CommandText = sql;
            int result = -1;

            try
            {
                result = command.ExecuteNonQuery();
            }
            catch (DataException e)
            {
                Console.WriteLine(e.Message);
               
            }
            return result;
        }

        public object ExecuteScalar(string sql)
        {
            command.CommandText = sql;
            object o = null;
            try
            {
                o = command.ExecuteScalar();
            }
            catch (DataException e)
            {
                Console.WriteLine(e.Message);
            }
            return o;
        }
        #endregion

        #region Transaction
        public void BeginTransaction()
        {
            command.Transaction = connection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            command.Transaction.Commit();
        }

        public void RollbackTransaction()
        {
            command.Transaction.Rollback();
        }
        #endregion
    }

 

1.建立SQL CE的连接（SqlCeConnection）只需要指定数据库文件路径，在这个类，我hardcode了文件路径，在实际应用中，可以在构造函数进行初始化，或者在Open函数中传递路径参数。

2.由于SQL CE当前版本不支持存储过程，所以SqlCeCommand的CommandType指定为CommandType.Text，只支持执行SQL语句。

3.实现了通用操作方法ExecuteReader，ExecuteDataSet，ExecuteNonQuery和ExecuteScalar。

4.SQL CE支持事务（Transaction）。

 

            SqlCeHelper sqlCe = new SqlCeHelper();

            sqlCe.Open();

            sqlCe.BeginTransaction();
            if (sqlCe.ExecuteNonQuery("delete from t") < 0)
            {
                sqlCe.RollbackTransaction();
                return;
            }

            if (sqlCe.ExecuteNonQuery("insert into t (f1, f2) values (1, 'abc')") < 0)
            {
                sqlCe.RollbackTransaction();
                return;
            }

            SqlCeDataReader reader = sqlCe.ExecuteReader("select * from t where f1 = 1");
            while (reader.Read())
            {
                Console.WriteLine("reader: f2:{0}\n", reader["f2"]);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }

            if (sqlCe.ExecuteNonQuery("update t set f2 = 'xyz'") < 0)
            {
                sqlCe.RollbackTransaction();
                return;
            }

            DataSet ds = sqlCe.ExecuteDataSet("select * from t");
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Console.WriteLine("dataset: f2:{0}\n", dr["f2"]);
            }

            sqlCe.CommitTransaction();
            sqlCe.Close();

 

 

使用这个Helper类很简单，先生成这个类的实例，打开数据库连接，使用ExecuteReader和ExecuteDataSet进行查询操作，使用ExecuteNonQuery进行增删改的操作。在操作过程中，同时可以加入事务处理操作，使用完毕关闭数据库连接。