SQLite访问封装类

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;  
      
       using System.Data.sqlite;  
      
       using System.IO;  
      
       namespace MyHelper.DataAccess  
      
       {  
      
       public class sqliteHelper  
      
       {  
      
       private static string password = "***"; //请修改***为实际密码  
      
       private static string dbFilePath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly()  
      
       .GetName().CodeBase) + "//***.db"; //请修改***为实际sqlite数据库名  
      
       private static string connectString = string.Format("Data Source =/"{}/"",dbFilePath,password);  
      
       private static sqliteConnection myConnect = new sqliteConnection(connectString);  
      
       /**//// <summary>  
      
       /// 取当前sqlite连接  
      
       /// </summary>  
      
       /// <returns>当前sqlite连接</returns>  
      
       public static sqliteConnection GetConnection()  
      
       {  
      
       return myConnect;  
      
       }  
      
       /**//// <summary>  
      
       /// 执行sql语句,返回受影响的行数  
      
       /// </summary>  
      
       /// <param name="commandString">sql语句</param>  
      
       /// <param name="parameters">sql语句参数</param>  
      
       /// <returns>受影响的行数</returns>  
      
       public static int ExecuteNonQuery(string commandString,params sqliteParameter[] parameters)  
      
       {  
      
       int result = ;  
      
       using (sqliteCommand command = new sqliteCommand())  
      
       {  
      
       PrepareCommand(command,null,commandString,parameters);  
      
       result = command.ExecuteNonQuery();  
      
       command.Parameters.Clear();  
      
       }  
      
       return result;  
      
       }  
      
       /**//// <summary>  
      
       /// 执行带事务的sql语句,返回受影响的行数  
      
       /// </summary>  
      
       /// <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)  
      
       {  
      
       int result = ;  
      
       using (sqliteCommand command = new sqliteCommand())  
      
       {  
      
       PrepareCommand(command,transaction,parameters);  
      
       result = command.ExecuteNonQuery();  
      
       command.Parameters.Clear();  
      
       }  
      
       return result;  
      
       }  
      
       /**//// <summary>  
      
       /// 执行查询,并返回结果集的第一行第一列的值,忽略其它所有的行和列  
      
       /// </summary>  
      
       /// <param name="commandString">sql语句</param>  
      
       /// <param name="parameters">sql语句参数</param>  
      
       /// <returns>第一行第一列的值</returns>  
      
       public static object ExecuteScalar(string commandString,params sqliteParameter[] parameters)  
      
       {  
      
       object result;  
      
       using (sqliteCommand command = new sqliteCommand())  
      
       {  
      
       PrepareCommand(command,parameters);  
      
       result = command.ExecuteScalar();  
      
       }  
      
       return result;  
      
       }  
      
       /**//// <summary>  
      
       /// 执行sql语句,返回结果集的DataReader  
      
       /// </summary>  
      
       /// <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();  
      
       try 
      
       {  
      
       PrepareCommand(command,parameters);  
      
       sqliteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);  
      
       command.Parameters.Clear();  
      
       return reader;  
      
       }  
      
       catch 
      
       {  
      
       throw;  
      
       }  
      
       }  
      
       /**//// <summary>  
      
       /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化  
      
       /// </summary>  
      
       /// <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)  
      
       myConnect.Open();  
      
       command.Connection = myConnect;  
      
       command.CommandText = commandString;  
      
       if (transaction != null)  
      
       command.Transaction = transaction;  
      
       if (parameters != null && parameters.Length > )  
      
       {  
      
       command.Parameters.AddRange(parameters);  
      
       }  
      
       }  
      
       }  
      
       }

为了提高效率，我将数据库连接修改为长连接。这样，只有第一次连接时会比较耗时，以后的操作就比较快了。如果本文有错误的地方，请大家参考egmkang原文。

原文链接：https://www.f2er.com/sqlite/202764.html

猜你在找的Sqlite相关文章