博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

SQLite访问封装类

Posted on 2009-08-31 19:16  春之痕  阅读(3575)  评论(10编辑  收藏  举报
    SQLite作为windows mobile的数据库,是一种不错的选择。SQLite的下载地址为:SQLite,SQLite的ADO.net Provider下载地址为:System.Data.SQLite ,在发布程序时,请别忘记拷贝SQLite.Interop.065.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 =\"{0}\"", 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 = 0;
            
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 = 0;
            
using (SQLiteCommand command = new SQLiteCommand())
            
{
                PrepareCommand(command, transaction, commandString, 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, 
null, commandString, 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, 
null, commandString, 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 > 0)
            
{
                command.Parameters.AddRange(parameters);
            }

        }

    }

}

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