SQLite访问封装类

原文：http://www.cnblogs.com/springtrace/archive/2009/08/31/1557521.html 

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原文。

============================================================================================

我的项目中由于是多线程同时操作数据库，我为每个线程都开一个连接.这是我的访问类，如果有问题，大家可以一起讨论：

using System;
using System.Data;
using System.Data.SQLite;
using System.Xml;
using System.Collections;

namespace IPass.Utility
{
    /// <summary>
    /// 数据库的通用访问代码
    /// 此类是抽象类,不允许实例化,应用时直接调用即可
    /// </summary>
    public abstract class SqliteHelper
    {
        #region 变量声明

        private static readonly string connString;//数据库连接字符串
        private static SQLiteConnection SyncConnection;//同步线程连接
        private static SQLiteConnection BizConnection;//业务线程连接

        #endregion

        #region 构造函数

        static SqliteHelper()
        {
            string configPath = System.IO.Path.
                GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\AppConfig.xml";
            XmlDocument docConfig = new XmlDocument();
            docConfig.Load(configPath);
            XmlNode dalNode = docConfig.SelectSingleNode("//Config//DbName");
            string dbName = dalNode.InnerText;
            string dbDirectoryPath = System.IO.Path.
                GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);

            connString = string.Format("Data Source={0}\\{1};Pooling=true;FailIfMissing=false", dbDirectoryPath, dbName);

            SyncConnection = new SQLiteConnection(connString);
            BizConnection = new SQLiteConnection(connString);

        }

        #endregion

        #region 共用方法
        /// <summary>
        /// 得到同步数据线程的数据库连接
        /// </summary>
        /// <returns></returns>
        public static SQLiteConnection GetConnection(ConnectionType type)
        {
            SQLiteConnection conn = null;
            switch (type)
            {
                case ConnectionType.Sync:
                    conn = SyncConnection;
                    break;
                case ConnectionType.Biz:
                    conn = BizConnection;
                    break;
                default:
                    break;
            }
            return conn;
        }

        /// <summary>
        /// 执行SqlCommand返回受影响的行数
        /// 提供参数
        /// </summary>
        /// <param >一个连接字符串</param>
        /// <param >命令类型(存储过程,或者一个SQL命令.)</param>
        /// <param >存储过程名字,或者是一个SQL语句</param>
        /// <param >执行命令的参数数组</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(CommandType cmdType, ConnectionType type, string cmdText, params SQLiteParameter[] cmdParms)
        {
            SQLiteConnection conn = GetConnection(type);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            using (SQLiteTransaction trans = conn.BeginTransaction())
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        //清除cmd的参数
                        cmd.Parameters.Clear();
                        trans.Commit();
                        return val;
                    }
                    catch (System.Data.SQLite.SQLiteException e)
                    {
                        trans.Rollback();
                        conn.Close();
                        throw new Exception(e.Message);
                    }
                }
            }

        }


        /// <summary>
        /// 执行一条返回结果集的SqlCommand命令
        /// 提供参数数组
        /// </summary>
        /// <param >一个有效字符串</param>
        /// <param >命令类型(存储过程,或者一个SQL命令.)</param>
        /// <param >存储过程名字,或者是一个SQL语句</param>
        /// <param >执行命令的参数数组</param>
        /// <returns>SQLiteDataReader</returns>
        public static SQLiteDataReader ExecuteReader(CommandType cmdType, ConnectionType type, string cmdText,
            params SQLiteParameter[] cmdParms)
        {
            SQLiteConnection conn = GetConnection(type);
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return rdr;
                }
                catch (System.Data.SQLite.SQLiteException e)
                {
                    conn.Close();
                    throw new Exception(e.Message);
                }
            }

        }

        /// <summary>
        /// 执行sql命令,返回首行首列 
        /// 提供参数数组
        /// </summary>
        /// <param >一个有效字符串</param>
        /// <param >命令类型(存储过程,或者一个SQL命令.)</param>
        /// <param >存储过程名字,或者是一个SQL语句</param>
        /// <param >执行命令的参数数组</param>
        /// <returns>一个object类型,可以进行转换</returns>
        public static object ExecuteScalar(CommandType cmdType, ConnectionType type, string cmdText,
            params SQLiteParameter[] cmdParms)
        {
            SQLiteConnection conn = GetConnection(type);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            using (SQLiteTransaction trans = conn.BeginTransaction())
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {

                    try
                    {
                        PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                        object val = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        return val;
                    }
                    catch (System.Data.SQLite.SQLiteException e)
                    {
                        trans.Rollback();
                        conn.Close();
                        throw new Exception(e.Message);
                    }

                }
            }
        }



        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList, ConnectionType type)
        {
            SQLiteConnection conn = GetConnection(type);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            using (SQLiteTransaction trans = conn.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand();
                try
                {
                    //循环
                    foreach (DictionaryEntry myDE in SQLStringList)
                    {
                        string cmdText = myDE.Key.ToString();
                        SQLiteParameter[] cmdParms = (SQLiteParameter[])myDE.Value;
                        PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();

                        trans.Commit();
                    }
                }
                catch (System.Data.SQLite.SQLiteException e)
                {
                    trans.Rollback();
                    conn.Close();
                    throw new Exception(e.Message);
                }
            }

        }


        /// <summary>
        /// 为执行命令做参数准备
        /// </summary>
        /// <param >SqlCommand object</param>
        /// <param >SqlConnection object</param>
        /// <param >SqlTransaction object</param>
        /// <param >一个存储过程,或者sql语句类型</param>
        /// <param >一个命令sql语句</param>
        /// <param >参数集合</param>
        public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, CommandType cmdType,
            string cmdText, SQLiteParameter[] cmdParms)
        {
            try
            {
                //判断连接的状态。如果是关闭状态，则打开
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                //cmd属性赋值
                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                //是否需要用到事务处理
                if (trans != null)
                    cmd.Transaction = trans;

                cmd.CommandType = cmdType;
                //添加cmd需要的存储过程参数
                if (cmdParms != null)
                {
                    foreach (SQLiteParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        #endregion
    }

    #region 枚举
    public enum ConnectionType
    {
        Sync,//同步线程的数据库连接
        Biz//业务线程的数据库连接
    }
    #endregion
}