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

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

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

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

  1 using System;
  2 using System.Collections;
  3 using System.Data;
  4 using System.Data.SqlServerCe;
  5 
  6 namespace MyTool.DataBase
  7 {
  8     /// <summary>
  9     /// SQLCE本地数据库操作
 10     /// </summary>
 11   public  class SqlCeHelper
 12     {
 13         #region[字段]
 14         private string connectstring = @"Data Source=|DataDirectory|\Database1.sdf";
 15         SqlCeConnection connect = null;
 16         SqlCeCommand command = null;
 17         #endregion
 18         #region[属性]
 19         /// <summary>
 20         /// 数据库连接字符串
 21         /// </summary>
 22         public string ConnectString
 23         {
 24             get { return connectstring; }
 25             set { connectstring = value; }
 26         }
 27         #endregion
 28         #region[构造函数]
 29         /// <summary>
 30         /// 构造函数
 31         /// </summary>
 32         /// <param name="DBPath">数据库路径</param>
 33         public SqlCeHelper(string DBPath)
 34         {
 35             this.ConnectString = "Data Source=" + DBPath;
 36             connect = new SqlCeConnection(ConnectString);
 37         }       
 38         #endregion
 39         #region[私有函数]
 40         private void Open()
 41         {
 42             try
 43             {
 44                 if (connect.State != System.Data.ConnectionState.Open)
 45                 {
 46                     connect.Open();
 47                 }
 48 
 49             }
 50             catch (Exception ex)
 51             {
 52                 throw (new Exception(ex.Message));
 53             }
 54         }
 55 
 56         private void Close()
 57         {
 58             try
 59             {
 60                 if (connect.State != System.Data.ConnectionState.Closed)
 61                 {
 62                     connect.Close();
 63                 }
 64 
 65             }
 66             catch (Exception ex)
 67             {
 68                 throw (new Exception(ex.Message));
 69             }
 70         }
 71         #endregion
 72      
 73         /// <summary>
 74         /// 测试连通性
 75         /// </summary>
 76         /// <returns></returns>
 77         public bool ConnectTest()
 78         {
 79             try
 80             {
 81                 connect.Open();
 82             }
 83             catch
 84             {
 85                 connect.Close();
 86                 return false;
 87             }
 88             return true;
 89         }
 90 
 91         /// <summary>
 92         /// 执行无返回的Sql语句，如插入，删除，更新,注意异常的抛出
 93         /// </summary>
 94         /// <param name="sqlstr">SQL语句</param>
 95         /// <returns>受影响的条数</returns>
 96         public int ExecuteNonQuery(string sqlstr)
 97         {
 98             try
 99             {
100                 Open();
101                 command = new SqlCeCommand(sqlstr, connect);
102                 int num = command.ExecuteNonQuery();
103                 command.Parameters.Clear();
104                 Close();
105                 return num;
106             }
107             catch
108             {
109                 throw;
110             }
111 
112         }
113 
114         /// <summary>
115         /// 执行查询语句，返回DataSet
116         /// </summary>
117         /// <param name="sqlstr">Sql</param>
118         /// <returns>DataSet数据集</returns>
119         public DataSet ReturnDataSet(string sqlstr)
120         {
121             DataSet ds = new DataSet();
122             try
123             {
124                 Open();
125                 SqlCeDataAdapter adapter = new SqlCeDataAdapter(sqlstr, connect);
126                 adapter.Fill(ds, "Obj");
127             }
128             catch
129             {
130                 throw;
131             }
132             return ds;
133         }
134 
135         /// <summary>
136         /// 执行查询语句，返回DataTable。注意异常的抛出
137         /// </summary>
138         /// <param name="sqlstr">Sqk</param>
139         /// <returns>DataTable数据表</returns>
140         public DataTable ReturnDataTable(string sqlstr)
141         {
142             try
143             {
144                 return ReturnDataSet(sqlstr).Tables[0];
145             }
146             catch
147             { throw; }
148         }
149 
150         /// <summary>
151         /// 执行查询语句，返回DataReader
152         /// </summary>
153         /// <param name="sqlstr">Sql</param>
154         /// <returns>DataReader</returns>
155         public SqlCeDataReader ReturnDataReader(string sqlstr)
156         {
157 
158             try
159             {
160                 Open();
161                 command = new SqlCeCommand(sqlstr, connect);
162                 SqlCeDataReader myReader = command.ExecuteReader();
163                 command.Parameters.Clear();
164                 Close();
165                 return myReader;
166             }
167             catch
168             {
169                 throw;
170             }
171 
172         }
173 
174         /// <summary>
175         /// 执行事务
176         /// </summary>
177         /// <param name="SQLStringList"></param>
178         public void ExecuteSqlTran(ArrayList SQLStringList)
179         {
180 
181             Open();
182             command = new SqlCeCommand();
183             command.Connection = connect;
184             SqlCeTransaction tx = connect.BeginTransaction();
185             command.Transaction = tx;
186             try
187             {
188                 for (int n = 0; n < SQLStringList.Count; n++)
189                 {
190                     string strsql = SQLStringList[n].ToString();
191                     if (strsql.Trim().Length > 1)
192                     {
193                         command.CommandText = strsql;
194                         command.ExecuteNonQuery();
195                     }
196                 }
197                 tx.Commit();
198             }
199             catch (Exception)
200             {
201                 tx.Rollback();
202                 throw;
203             }
204         }
205     }
206 }