C#中的使用本地数据库(SQLCE)

 一般写软件，若用到单机数据库，用的几乎都是Access。其实在VS中，可以使用以sdf为扩展名的本地数据库。（**项目-->添加-->新建项-->本地数据库）

这样的数据库兼具两大优点：第一，可以使用LinqToSql，第二，单机化，不需要其他控件等等。

分享一个用LinqToSql来操作这个数据库的简单类。注意引用System.Data.SqlServerCe。 

在安装VS的时候要安装SQL Server Compact 的组件，这样引用程序集中才会有System.Data.SqlServerCe。

 

using System;
using System.Collections;
using System.Data;
using System.Data.SqlServerCe;

namespace MyTool.DataBase
{
    /// <summary>
    /// SQLCE本地数据库操作
    /// </summary>
  public  class SqlCeHelper
    {
        #region[字段]
        private string connectstring = @"Data Source=|DataDirectory|\Database1.sdf";
        SqlCeConnection connect = null;
        SqlCeCommand command = null;
        #endregion
        #region[属性]
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectString
        {
            get { return connectstring; }
            set { connectstring = value; }
        }
        #endregion
        #region[构造函数]
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="DBPath">数据库路径</param>
        public SqlCeHelper(string DBPath)
        {
            this.ConnectString = "Data Source=" + DBPath;
            connect = new SqlCeConnection(ConnectString);
        }       
        #endregion
        #region[私有函数]
        private void Open()
        {
            try
            {
                if (connect.State != System.Data.ConnectionState.Open)
                {
                    connect.Open();
                }

            }
            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }
        }

        private void Close()
        {
            try
            {
                if (connect.State != System.Data.ConnectionState.Closed)
                {
                    connect.Close();
                }

            }
            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }
        }
        #endregion
     
        /// <summary>
        /// 测试连通性
        /// </summary>
        /// <returns></returns>
        public bool ConnectTest()
        {
            try
            {
                connect.Open();
            }
            catch
            {
                connect.Close();
                return false;
            }
            return true;
        }

        /// <summary>
        /// 执行无返回的Sql语句，如插入，删除，更新,注意异常的抛出
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <returns>受影响的条数</returns>
        public int ExecuteNonQuery(string sqlstr)
        {
            try
            {
                Open();
                command = new SqlCeCommand(sqlstr, connect);
                int num = command.ExecuteNonQuery();
                command.Parameters.Clear();
                Close();
                return num;
            }
            catch
            {
                throw;
            }

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlstr">Sql</param>
        /// <returns>DataSet数据集</returns>
        public DataSet ReturnDataSet(string sqlstr)
        {
            DataSet ds = new DataSet();
            try
            {
                Open();
                SqlCeDataAdapter adapter = new SqlCeDataAdapter(sqlstr, connect);
                adapter.Fill(ds, "Obj");
            }
            catch
            {
                throw;
            }
            return ds;
        }

        /// <summary>
        /// 执行查询语句，返回DataTable。注意异常的抛出
        /// </summary>
        /// <param name="sqlstr">Sqk</param>
        /// <returns>DataTable数据表</returns>
        public DataTable ReturnDataTable(string sqlstr)
        {
            try
            {
                return ReturnDataSet(sqlstr).Tables[0];
            }
            catch
            { throw; }
        }

        /// <summary>
        /// 执行查询语句，返回DataReader
        /// </summary>
        /// <param name="sqlstr">Sql</param>
        /// <returns>DataReader</returns>
        public SqlCeDataReader ReturnDataReader(string sqlstr)
        {

            try
            {
                Open();
                command = new SqlCeCommand(sqlstr, connect);
                SqlCeDataReader myReader = command.ExecuteReader();
                command.Parameters.Clear();
                Close();
                return myReader;
            }
            catch
            {
                throw;
            }

        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="SQLStringList"></param>
        public void ExecuteSqlTran(ArrayList SQLStringList)
        {

            Open();
            command = new SqlCeCommand();
            command.Connection = connect;
            SqlCeTransaction tx = connect.BeginTransaction();
            command.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        command.CommandText = strsql;
                        command.ExecuteNonQuery();
                    }
                }
                tx.Commit();
            }
            catch (Exception)
            {
                tx.Rollback();
                throw;
            }
        }
    }
}