SQLite Helper类

以前都没怎么关心SQLite,今天在cnblogs上看到一篇blog讲到SQLite,很是感兴趣，于是对SQLite玩弄一番。

简介：

SQLite是一个开源数据库，现在已变得越来越流行，它的体积很小，被广泛应用于各种不同类型的应用中。SQLite已经是世界上布署得最广泛的SQL数据库引擎，被用在无以计数的桌面电脑应用中，还有消费电子设备中，如移动电话、掌上电脑和MP3播放器等。SQLite的源码就放在公有领域（即WikiPedia的public domain)中。

开始使用:

从sourceforge获取SQLite的ADO.NET 2.0数据提供者 :http://sourceforge.net/projects/sqlite-dotnet2

下载和安装一个SQLite GUI工具，SQLiteMan有一个非常出色的windows版本:http://sqliteman.com/index.php/page/4.html

SQLiteHelper类:

 首先向工程添加引用：System.Data.SQLite.dll(http://sourceforge.net/projects/sqlite-dotnet2可以下载)

using System;
using System.Data;
using System.Data.SQLite;

/// 

/// SQLiteHelper 的摘要说明
/// 
public class SQLiteHelper
{
	private SQLiteHelper()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
    }
    #region 静态私有方法
    /// 

    /// 附加参数
    /// 
    /// 
    /// 
    private static void AttachParameters(SQLiteCommand command, SQLiteParameter[] commandParameters) {
        command.Parameters.Clear();
        foreach (SQLiteParameter p in commandParameters) {
            if (p.Direction == ParameterDirection.InputOutput && p.Value == null)
                p.Value = DBNull.Value;
            command.Parameters.Add(p);
        }
    }
    /// 

    /// 分配参数值
    /// 
    /// 
    /// 
    private static void AssignParameterValues(SQLiteParameter[] commandParameters, object[] parameterValues) {
        if (commandParameters == null || parameterValues == null)
            return;
        if(commandParameters.Length!=parameterValues.Length)
            throw new ArgumentException("Parameter count does not match Parameter Value count.");
        for (int i = 0, j = commandParameters.Length; i < j; i++) {
            commandParameters[i].Value = parameterValues[i];
        }
    }
    /// 

    /// 预备执行command命令
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
    private static void PrepareCommand(SQLiteCommand command,
        SQLiteConnection connection, SQLiteTransaction transaction,
        CommandType commandType, string commandText, SQLiteParameter[] commandParameters
        ) {
            if (commandType == CommandType.StoredProcedure)
            {
                throw new ArgumentException("SQLite 暂时不支持存储过程");
            }
            if (connection.State != ConnectionState.Open)
                connection.Open();
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
                command.Transaction = transaction;
            command.CommandType = commandType;
            if (commandParameters != null)
                AttachParameters(command, commandParameters);
            return;
        }
    #endregion

    #region ExecuteNonQuery 执行SQL命令，返回影响行数
        /// 

    /// 执行SQL命名
    /// 
    /// 
    /// 
    /// 
    /// 
  
  
    public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) {
        return ExecuteNonQuery(connectionString, commandType, commandText, (SQLiteParameter[])null);
    }
    /// 

    /// 不支持存储过程，但可以参数化查询
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
  
  
    public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString)) {
            conn.Open();
            return ExecuteNonQuery(conn, commandType, commandText, commandParameters);
        }
    }
    public static int ExecuteNonQuery(SQLiteConnection connection, CommandType commandType, string commandText)
    {
        return ExecuteNonQuery(connection, commandType, commandText, (SQLiteParameter[])null);
    }
    public static int ExecuteNonQuery(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
    {
        SQLiteCommand cmd = new SQLiteCommand();
        PrepareCommand(cmd, connection, (SQLiteTransaction)null, commandType, commandText, commandParameters);
        int retval = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        return retval;
    }
        #endregion

    #region ExecuteDataSet 执行SQL查询，并将返回数据填充到DataSet
    public static DataSet ExecuteDataset(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters) {
        SQLiteCommand cmd = new SQLiteCommand();
        PrepareCommand(cmd, connection, (SQLiteTransaction)null, commandType, commandText, commandParameters);
        SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        cmd.Parameters.Clear();
        return ds;
    }
    public static DataSet ExecuteDataset(SQLiteConnection connection, CommandType commandType, string commandText)
    {
        return ExecuteDataset(connection, commandType, commandText, (SQLiteParameter[])null);
    }
    public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
    {
      
        using (SQLiteConnection cn = new SQLiteConnection(connectionString))
        {
            cn.Open();
           
            return ExecuteDataset(cn, commandType, commandText, commandParameters);
        }
    }
    public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
    {
        return ExecuteDataset(connectionString, commandType, commandText, (SQLiteParameter[])null);
    }
    #endregion

    #region ExecuteReader 执行SQL查询,返回DbDataReader
    public static SQLiteDataReader ExecuteReader(SQLiteConnection connection, SQLiteTransaction transaction, CommandType commandType, string commandText, SQLiteParameter[] commandParameters, DbConnectionOwnership connectionOwnership)
    {
        SQLiteCommand cmd = new SQLiteCommand();
        PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
        SQLiteDataReader dr;
        if (connectionOwnership == DbConnectionOwnership.External)
            dr = cmd.ExecuteReader();
        else
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        cmd.Parameters.Clear();
        return dr;
    }
    /// 

    ///读取数据后将自动关闭连接
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
  
  
    public static SQLiteDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
    {       
        SQLiteConnection cn = new SQLiteConnection(connectionString);
        cn.Open();
        try
        {
            return ExecuteReader(cn, null, commandType, commandText, commandParameters, DbConnectionOwnership.Internal);
        }
        catch
        {
            cn.Close();
            throw;
        }
    }
    /// 

    /// 读取数据后将自动关闭连接
    /// 
    /// 
    /// 
    /// 
    /// 
  
  
    public static SQLiteDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
    {
        return ExecuteReader(connectionString, commandType, commandText, (SQLiteParameter[])null);
    }
    /// 

    /// 读取数据以后需要自行关闭连接
    /// 
    /// 
    /// 
    /// 
    /// 
  
  
    public static SQLiteDataReader ExecuteReader(SQLiteConnection connection, CommandType commandType, string commandText)
    {      
        return ExecuteReader(connection, commandType, commandText, (SQLiteParameter[])null);
    }
    /// 

    /// 读取数据以后需要自行关闭连接
    /// 
    /// 
    /// 
    /// 
    /// 
    /// 
  
  
    public static SQLiteDataReader ExecuteReader(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
    {
        return ExecuteReader(connection, (SQLiteTransaction)null, commandType, commandText, commandParameters, DbConnectionOwnership.External);
    }
    #endregion
}

 

using System;

/// 

/// DbConnectionOwnership DataReader以后是否自动关闭连接
/// 
public enum DbConnectionOwnership
{
    /// 

    /// 自动关闭
    /// 
    Internal,
    /// 

    /// 手动关闭
    /// 
    External,
}

http://sourceforge.net/ 被和谐掉了

这里吧SQLite ADO.Net 源码传上来： https://p-blog.csdn.net/images/p_blog_csdn_net/akunshenjk/SQLite-1.0.51.0-source.jpg 右键另存为，将jpg改为zip，解压

 https://p-blog.csdn.net/images/p_blog_csdn_net/akunshenjk/SQLite-1.0.51.0-binaries.jpg 右键另存为，将jpg改为zip，解压