封裝了 System.Data.SQLite 的數據庫助手類

System.Data.SQLite數據庫通用類 
針對對數據庫的操作情況，分為以下幾種情況：
創建數據庫文件；
返回DataTable；
返回DataReader；
執行增刪改，返回受影響的行數；
執行查詢，返回第一行第一列（通常用于帶有行函數的查詢，如SUM/AVG/COUNT等）；
返回庫中所有的表；
因為在System.Data.SQLite中不存在存儲過程，所以所有的操作都是基于文本的SQL語句，為了避免SQL注入，所以使用了參數化的SQL語句，這個數據庫通用類如下：

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

namespace SQLiteQueryBrowser
{
    /// <summary>
    /// 說明：這是一個針對System.Data.SQLite的數據庫常規操作封裝的通用類。
    /// Version:0.1
    /// </summary>
    public class SQLiteDBHelper
    {
        private string connectionString = string.Empty;
        /// <summary>
        /// 構造函數
        /// </summary>
        /// <param name="dbPath">SQLite數據庫文件路徑</param>
        public SQLiteDBHelper(string dbPath)
        {
            this.connectionString = "Data Source=" + dbPath;
        }
        /// <summary>
        /// 創建SQLite數據庫文件
        /// </summary>
        /// <param name="dbPath">要創建的SQLite數據庫文件路徑</param>
        public static void CreateDB(string dbPath)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
                    command.ExecuteNonQuery();

                command.CommandText = "DROP TABLE Demo";
                command.ExecuteNonQuery();
            }
        }
    }
    /// <summary>
    /// 對SQLite數據庫執行增刪改操作，返回受影響的行數。
    /// </summary>
    /// <param name="sql">要執行的增刪改的SQL語句</param>
    /// <param name="parameters">執行增刪改語句所需要的參數，參數必須以它們在SQL語句中的順序為準</param>
    /// <returns></returns>
    public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
    {
        int affectedRows = 0;
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            using (DbTransaction transaction = connection.BeginTransaction())
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = sql;
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    affectedRows = command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }
        return affectedRows;
    }
    /// <summary>
    /// 執行一個查詢語句，返回一個關聯的SQLiteDataReader實例
    /// </summary>
    /// <param name="sql">要執行的查詢語句</param>
    /// <param name="parameters">執行SQL查詢語句所需要的參數，參數必須以它們在SQL語句中的順序為準</param>
    /// <returns></returns>
    public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
    {
        SQLiteConnection connection = new SQLiteConnection(connectionString);
        SQLiteCommand command = new SQLiteCommand(sql, connection);
        if (parameters != null)
        {
            command.Parameters.AddRange(parameters);
        }
        connection.Open();
        return command.ExecuteReader(CommandBehavior.CloseConnection);
    }
    /// <summary>
    /// 執行一個查詢語句，返回一個包含查詢結果的DataTable
    /// </summary>
    /// <param name="sql">要執行的查詢語句</param>
    /// <param name="parameters">執行SQL查詢語句所需要的參數，參數必須以它們在SQL語句中的順序為準</param>
    /// <returns></returns>
    public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            using (SQLiteCommand command = new SQLiteCommand(sql, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                DataTable data = new DataTable();
                adapter.Fill(data);
                return data;
            }
        }

    }
    /// <summary>
    /// 執行一個查詢語句，返回查詢結果的第一行第一列
    /// </summary>
    /// <param name="sql">要執行的查詢語句</param>
    /// <param name="parameters">執行SQL查詢語句所需要的參數，參數必須以它們在SQL語句中的順序為準</param>
    /// <returns></returns>
    public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            using (SQLiteCommand command = new SQLiteCommand(sql, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                DataTable data = new DataTable();
                adapter.Fill(data);
                return data;
            }
        }
    }
    /// <summary>
    /// 查詢數據庫中的所有數據類型信息
    /// </summary>
    /// <returns></returns>
    public DataTable GetSchema()
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            DataTable data=connection.GetSchema("TABLES");
            connection.Close();
            //foreach (DataColumn column in data.Columns)
            //{
            //    Console.WriteLine(column.ColumnName);
            //}
            return data;
        }
    }

}

}
</pre>