http://www.cnblogs.com/jackdong/archive/2010/10/08/1845787.html
SQLite作为windows mobile的数据库,是一种不错的选择。SQLite的下载地址为:SQLite,SQLite的ADO.net Provider下载地址为:System.Data.SQLite ,在发布程序时,请别忘记拷贝SQLite.Interop.06.DLL、system.data.sqlite.dll两个文件到安装目录下。
本文非原创,而是参考egmkang撰写的 WM下访问SQLite(一种替代SQL CE的解决方案) ,并根据我自己的编程需要和习惯做了少许修改,错误在所难免。下面实现的是一个SQLiteHelper封装类:
| using System.Data.SQLite; |
| namespace MyHelper.DataAccess |
| public class SQLiteHelper |
| private static string password = "***" ; |
| private static string dbFilePath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly() |
| .GetName().CodeBase) + "//***.db" ; |
| private static string connectString = string .Format( "Data Source =/"{}/"" , dbFilePath, password); |
| private static SQLiteConnection myConnect = new SQLiteConnection(connectString); |
| /// <returns>当前SQLite连接</returns> |
| public static SQLiteConnection GetConnection() |
| /// <param name="commandString">SQL语句</param> |
| /// <param name="parameters">SQL语句参数</param> |
| /// <returns>受影响的行数</returns> |
| public static int ExecuteNonQuery( string commandString, params SQLiteParameter[] parameters) |
| using (SQLiteCommand command = new SQLiteCommand()) |
| PrepareCommand(command, null , commandString, parameters); |
| result = command.ExecuteNonQuery(); |
| command.Parameters.Clear(); |
| /// <param name="transaction">SQL事务</param> |
| /// <param name="commandString">SQL语句</param> |
| /// <param name="parameters">SQL语句参数</param> |
| /// <returns>受影响的行数</returns> |
| public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandString, |
| params SQLiteParameter[] parameters) |
| using (SQLiteCommand command = new SQLiteCommand()) |
| PrepareCommand(command, transaction, commandString, parameters); |
| result = command.ExecuteNonQuery(); |
| command.Parameters.Clear(); |
| /// 执行查询,并返回结果集的第一行第一列的值,忽略其它所有的行和列 |
| /// <param name="commandString">SQL语句</param> |
| /// <param name="parameters">SQL语句参数</param> |
| /// <returns>第一行第一列的值</returns> |
| public static object ExecuteScalar( string commandString, params SQLiteParameter[] parameters) |
| using (SQLiteCommand command = new SQLiteCommand()) |
| PrepareCommand(command, null , commandString, parameters); |
| result = command.ExecuteScalar(); |
| /// 执行SQL语句,返回结果集的DataReader |
| /// <param name="commandString">SQL语句</param> |
| /// <param name="parameters">SQL语句参数</param> |
| /// <returns>结果集的DataReader</returns> |
| public static SQLiteDataReader ExecuteReader( string commandString, params SQLiteParameter[] parameters) |
| SQLiteCommand command = new SQLiteCommand(); |
| PrepareCommand(command, null , commandString, parameters); |
| SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); |
| command.Parameters.Clear(); |
| /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化 |
| /// <param name="command">Command对象</param> |
| /// <param name="transaction">transaction对象</param> |
| /// <param name="commandString">SQL语句</param> |
| /// <param name="parameters">SQL语句参数</param> |
| private static void PrepareCommand(SQLiteCommand command, SQLiteTransaction transaction, |
| string commandString, params SQLiteParameter[] parameters) |
| if (myConnect.State != ConnectionState.Open) |
| command.Connection = myConnect; |
| command.CommandText = commandString; |
| command.Transaction = transaction; |
| if (parameters != null && parameters.Length > ) |
| command.Parameters.AddRange(parameters); |
为了提高效率,我将数据库连接修改为长连接。这样,只有第一次连接时会比较耗时,以后的操作就比较快了。如果本文有错误的地方,请大家参考egmkang原文。