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.Collections.Generic;  
using System.Data.SQLite;  
using System.Reflection;  
using System.Collections;     
 
namespace GetTime  
{  
    public class SQLiteDBHelper  
    {  
        private string connectionString = string.Empty;  
 
        static SQLiteDBHelper()  
        {  
              
        }  
        /// <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, IList<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 || parameters.Count == 0))  
                        {  
                            foreach (SQLiteParameter parameter in parameters)  
                            {  
                                command.Parameters.Add(parameter);  
                            }  
                        }  
                        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, IList<SQLiteParameter> parameters)  
        {  
            SQLiteConnection connection = new SQLiteConnection(connectionString);  
            SQLiteCommand command = new SQLiteCommand(sql, connection);  
            if (!(parameters == null || parameters.Count == 0))  
            {  
                foreach (SQLiteParameter parameter in parameters)  
                {  
                    command.Parameters.Add(parameter);  
                }  
            }  
            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, IList<SQLiteParameter> parameters)  
        {  
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
            {  
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))  
                {  
                    if (!(parameters == null || parameters.Count == 0))  
                    {  
                        foreach (SQLiteParameter parameter in parameters)  
                        {  
                            command.Parameters.Add(parameter);  
                        }  
                    }  
                    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, IList<SQLiteParameter> parameters)  
        {  
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))  
            {  
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))  
                {  
                    if (!(parameters == null || parameters.Count == 0))  
                    {  
                        foreach (SQLiteParameter parameter in parameters)  
                        {  
                            command.Parameters.Add(parameter);  
                        }  
                    }  
                    return command.ExecuteScalar();  
                }  
            }  
        }  
 
        /// <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;  
            }  
        }  
          
    }  
} 

System.Data.SQLite数据库通用类的用法
下面演示一下刚刚编写的数据库通用类的用法，代码如下：

using System; 
using System.Collections.Generic; 
using System.Text; 
using System.Data; 
using System.Data.Common; 
using System.Data.SQLite; 
using SQLiteQueryBrowser; 
namespace SQLiteDemo 
{ 
        class Program 
        { 
                static void Main(string[] args) 
                { 
                        //CreateTable(); 
                        //InsertData(); 
                        ShowData(); 
                        Console.ReadLine(); 
                } 
                public static void CreateTable() 
                { 
                        string dbPath = "D:\\Demo.db3"; 
                        //如果不存在改数据库文件，则创建该数据库文件 
                        if (!System.IO.File.Exists(dbPath)) 
                        { 
                                SQLiteDBHelper.CreateDB("D:\\Demo.db3"); 
                        } 
                        SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); 
                        string sql = "CREATE TABLE Test3(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,Name char(3),TypeName varchar(50),addDate datetime,UpdateTime Date,Time time,Comments blob)"; 
                        db.ExecuteNonQuery(sql, null); 
                } 
                public static void InsertData() 
                { 
                        string sql = "INSERT INTO Test3(Name,TypeName,addDate,UpdateTime,Time,Comments)values(@Name,@TypeName,@addDate,@UpdateTime,@Time,@Comments)"; 
                        SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); 
                        for (char c = 'A'; c <= 'Z'; c++) 
                        { 
                                for (int i = 0; i < 100; i++) 
                                { 
                                        SQLiteParameter[] parameters = new SQLiteParameter[]{ 
                                                new SQLiteParameter("@Name",c+i.ToString()), 
                                        new SQLiteParameter("@TypeName",c.ToString()), 
                                        new SQLiteParameter("@addDate",DateTime.Now), 
                                        new SQLiteParameter("@UpdateTime",DateTime.Now.Date), 
                                        new SQLiteParameter("@Time",DateTime.Now.ToShortTimeString()), 
                                        new SQLiteParameter("@Comments","Just a Test"+i) 
                                        }; 
                                        db.ExecuteNonQuery(sql, parameters); 
                                } 
                        } 
                } 
                public static void ShowData() 
                { 
                        //查询从50条起的20条记录 
                        string sql = "select * from test3 order by id desc limit 50 offset 20"; 
                        SQLiteDBHelper db = new SQLiteDBHelper("D:\\Demo.db3"); 
                        using (SQLiteDataReader reader = db.ExecuteReader(sql, null)) 
                        { 
                                while (reader.Read()) 
                                { 
                                        Console.WriteLine("ID:{0},TypeName{1}", reader.GetInt64(0), reader.GetString(1)); 
                                } 
                        } 
                } 
 
        } 
}