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
}

 

 

转载于:https://www.cnblogs.com/shineqiujuan/archive/2010/01/06/1640214.html