C#中使用SQLite

最近要做一个单机小程序，但涉及到一些数据的存取和增删改查，研究了一番，觉得还是轻量级数据库sqlite最合适，于是先做了个样例，方便进一步开发。

sqlite可以在如下网址下载

https://www.sqlite.org/download.html

但可直接下载System.Data.SQLite用在c#中，在如下网址下载

http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

我选择了其中的Precompiled Binaries for 64-bit Windows (.NET Framework 4.0)

sqlite-netFx40-binary-bundle-x64-2010-1.0.113.0.zip

不过貌似需要tunnel cross great wall。也可下载我文末的样例程序包，里面已经包含了必要的库及帮助文件。

新建工程，因为下载的是64位SQLite库，在配制管理器中修改工程为x64架构。

然后引用中添加System.Data.SQLite.dll即可使用。

程序本身比较简单，核心是下面的SQLite帮助类，用于封装一些函数，便于调用。直接上代码，每个函数都有注释，也比较简单。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLiteDemo
{
    public enum SQLiteType
    {
        INTEGER,
        REAL,
        TEXT,
        BLOB
    }

    class SQLiteHelper
    {
        string m_connString;

        public SQLiteHelper(string databasepath)
        {
            m_connString = "data source = " + databasepath;
            if (!File.Exists(databasepath))
            {
                SQLiteConnection.CreateFile(databasepath);
            }
        }

        /// <summary>
        /// 执行不带参数的SQL语句
        /// </summary>
        /// <param name="cmdString">SQL语句</param>
        /// <returns>受影响的行数</returns>
        public int Execute(string cmdString)
        {
            using (SQLiteConnection conn = new SQLiteConnection(m_connString))
            {
                using (SQLiteCommand command = new SQLiteCommand(cmdString, conn))
                {
                    try
                    {
                        conn.Open();
                        int row = command.ExecuteNonQuery();
                        return row;
                    }
                    catch (SQLiteException e)
                    {
                        conn.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 批量执行SQL语句
        /// </summary>
        /// <param name="cmdStrings">要批量执行的所有SQL语句</param>
        public void BatchExecute(string[] cmdStrings)
        {
            using (SQLiteConnection conn = new SQLiteConnection(m_connString))
            {
                conn.Open();
                SQLiteTransaction trans = conn.BeginTransaction();
                SQLiteCommand command = new SQLiteCommand(conn);
                command.Transaction = trans;
                try
                {
                    for (int i = 0; i < cmdStrings.Length; i++)
                    {
                        command.CommandText = cmdStrings[i];
                        command.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
                catch (SQLiteException e)
                {
                    trans.Rollback();
                    throw new Exception(e.Message);
                }
            }
        }

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="tableName">数据表名</param>
        /// <param name="colNames">所有列名称</param>
        /// <param name="colTypes">每一列的类型</param>
        /// <param name="infos">每一列的其他描述，比如是否可为NULL，是否为主键</param>
        /// <returns>返回数据表Reader</returns>
        public SQLiteDataReader CreateTable(string tableName, string[] colNames, SQLiteType[] colTypes, string[] infos = null)
        {
            string cmd = "CREATE TABLE IF NOT EXISTS " + tableName + "( ";
            for (int i = 0; i < colNames.Length; i++)
            {
                cmd += colNames[i] + " " + colTypes[i].ToString();
                if (infos != null)
                {
                    cmd += " " + infos[i];
                }
                if (i != colNames.Length - 1)
                {
                    cmd += ", ";
                }
            }
            cmd += " )";

            return Select(cmd);
        }

        /// <summary>
        /// 删除数据表
        /// </summary>
        /// <param name="tableName">要删除的数据表</param>
        /// <returns>该表的行数</returns>
        public int DropTable(string tableName)
        {
            string cmd = "DROP TABLE " + tableName;

            return Execute(cmd);
        }

        /// <summary>
        /// 在数据表中插入数据，但不适用于插入二进制数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="types">每一列的类型</param>
        /// <param name="values">每一列的值</param>
        /// <param name="columns">可选参数，每一列的列名</param>
        /// <returns>插入的行数</returns>
        public int InsertData(string tableName, SQLiteType[] types, string[] values, string[] columns = null)
        {
            string cmd = "INSERT INTO " + tableName;
            if (columns != null)
            {
                cmd += " ( ";
                for (int i = 0; i < columns.Length; i++)
                {
                    cmd += columns[i];
                    if (i != columns.Length - 1)
                    {
                        cmd += ", ";
                    }
                }
                cmd += " ) ";
            }
            cmd += " VALUES ( ";
            for (int i = 0; i < values.Length; i++)
            {
                switch (types[i])
                {
                    case SQLiteType.INTEGER:
                    case SQLiteType.REAL:
                        cmd += values[i];
                        break;
                    case SQLiteType.BLOB:
                        throw new Exception("Please use InsertBinary to do the job!");
                    default:
                        cmd += "‘" + values[i] + "‘";
                        break;
                }
                if (i != values.Length - 1)
                {
                    cmd += ", ";
                }
            }
            cmd += " )";

            return Execute(cmd);
        }

        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="cmdString">带参数的SQL语句</param>
        /// <param name="parameters">对应的参数</param>
        /// <returns>受影响的行数</returns>