C#使用Sqlite总结

1、下载Sqlite的dll

页面地址：http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

基于本项目的版本，下载Setups for 32-bit Windows (.NET Framework 3.5 SP1)

这个下面有两个exe，一个带bundle，一个不带bundle。

如果要发布anycpu的应用，应该用不带bundle的，如果要x86的，应该用带bundle的。

如果是不带bundle的，除了System.Data.SQLite.dll还需要SQLite.Interop.dll；如果是带bundle的，则只需要System.Data.SQLite.dll。

一开始下载的是不带bundle的，发现SQLite.Interop.dll无法添加引用（注意直接拷贝到当前运行目录下）；另网上有说这个dll需要VC++运行库支持。因此放弃anycpu改用x86的，在bin下面创建x86文件夹，然后创建Debug文件夹，把System.Data.SQLite.dll放进去。

2、创建项目，对刚才那个dll添加引用

3、连接字符串：

"Data Source=xxx.xx;Version=3;Pooling=true;FailIfMissing=false;"

Data Source即数据库地址，如果只有数据库名称，则是在安装目录下创建。名称和后缀都可以任意。

Verion只能是3，即sqlite的版本只能是3。

Pooling即连接池，如果是true即支持连接池，如果有则从连接池获取，如果没有则创建，默认这个是true，即默认有连接池存在。

FailIfMissing如果（数据库文件）丢失则失败，就是说如果连不到指定的数据库则失败，这个值如果是false则如果连不上指定数据库就创建，默认是false，即默认会自动创建数据库。

4、连接池自动生效

连接池是自动生效的，当数据库不操作的时候，应该把连接池关闭，才能从外部操作数据库文件，比如说使用可视化软件打开数据库文件，如果程序有连接池一直连着的话，是操作不了的，释放连接池：

System.Data.SQLite.SQLiteConnection.ClearAllPools();

5、连接池的概念：

初浅的理解，连接池就是SQLiteConnection本身，事实上并非如此，连接池是比SQLiteConnection更底层的存在。

普通的连接动作是这样的：

SQLiteConnection.open()->【创建一个数据库连接】（在这里面完成逻辑连接+物理连接的创建，非常消耗资源）->连接到实体数据库

有连接池的连接动作是这样：

SQLiteConnection.open()->连接池里有空闲的连接->【使用这个已存在的数据库连接（可能只需要进行逻辑连接，物理连接本来已完成）】->连接到实体数据库

SQLiteConnection.open()->连接池里没有空闲的连接->【创建一个数据库连接，并在连接池注册】->连接到实体数据库

可见，这个连接池是在背后运作的，并非指SQLiteConnection的实例。而是通过框架背后在自动进行的。

同理，当执行SQLiteConnection.close()的时候，逻辑连接将会取消，并将该数据库连接释放到连接池里。

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace TestSqlite
{
    class MySQLiteHelper
    {

        private static string cString = "";

        private MySQLiteHelper() { }

        // 获取连接字符串
        public static string ConnectionString
        {
            get 
            {
                if (string.IsNullOrEmpty(cString)) 
                {
                    // 在App.config-appSettings里面配置：
                    // <add key="SQLiteConnectionString" value="Data Source=MyDatabase.db;Version=3;"/>
                    cString = ConfigurationManager.AppSettings["SQLiteConnectionString"];
                }
                return cString;
            }
        }

        // 创建表，创建表连接只需要用一次，所以新建并释放就可以了
        // 直接传入要执行的sql语句就可以，因为将来可能涉及添加索引等复杂需求，如果用动态创建的方案，局限性比较大
        public static int CreateTable(string commandText) 
        {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            conn.Open();

            SQLiteCommand command = new SQLiteCommand();
            command.Connection = conn;
            command.CommandText = commandText;
            int val = command.ExecuteNonQuery();

            command.Dispose();// 释放command
            conn.Close();
            conn.Dispose();

            return val;
        }

        // 参数里面标记为params即可以传0个，也可以传null

        // SQLiteParameter name = MySQLiteHelper.CreateParameter("name", DbType.String, "SUSAN LI");
        // SQLiteParameter sex = MySQLiteHelper.CreateParameter("sex", DbType.Int16, 1);
        // SQLiteParameter age = MySQLiteHelper.CreateParameter("age", DbType.Int16, 30);
        // SQLiteParameter[] pa = new SQLiteParameter[3] { name, sex, age };
        // MySQLiteHelper.ExecuteNonQuery("INSERT INTO USER6 (NAME,SEX,AGE) values (@name,@sex,@age)",pa);
        public static int ExecuteNonQuery(string commandText, params SQLiteParameter[] commandParameters)
        {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            conn.Open();

            SQLiteCommand command = new SQLiteCommand();
            command.Connection = conn;
            command.CommandText = commandText;
            if (commandParameters != null)
                command.Parameters.AddRange(commandParameters);
            int val = command.ExecuteNonQuery();

            command.Dispose();
            conn.Close();
            conn.Dispose();
            return val;
        }

        // 使用传入已初始化完成并且配置了conn的command
        // 此函数的作用是重用，即如果一个频繁的数据库操作，不要总是关闭及开启，而是要执行完毕再关闭即可
        // 所以在这里不要执行关闭了
        // 在调用之前先要配置conn和command：

        // SQLiteConnection conn = new SQLiteConnection(ConnectionString);
        // conn.Open();
        // SQLiteCommand command = new SQLiteCommand();
        // command.Connection = conn;
        public static int ExecuteNonQuery(SQLiteCommand command, string commandText,params SQLiteParameter[] commandParameters) 
        {
            if (command.Connection.State == ConnectionState.Closed)
                command.Connection.Open();
            command.CommandText = commandText;
            command.Parameters.Clear();
            if (commandParameters != null)
                command.Parameters.AddRange(commandParameters);
            return command.ExecuteNonQuery();
        }

        // 查询并返回datatable
        public static DataTable ExecuteDataTable(string commandText, params SQLiteParameter[] commandParameters) 
        {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            conn.Open();

            SQLiteCommand command = new SQLiteCommand();
            command.Connection = conn;
            command.CommandText = commandText;
            if (commandParameters != null)
                command.Parameters.AddRange(commandParameters);
            // 开始读取
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
            DataTable data = new DataTable();
            adapter.Fill(data);
            // dispose
            adapter.Dispose();
            command.Dispose();
            conn.Close();
            conn.Dispose();

            return data;
        }
        
        // 创建参数
        public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
        {
            SQLiteParameter parameter = new SQLiteParameter();
            parameter.DbType = parameterType;
            parameter.ParameterName = parameterName;
            parameter.Value = parameterValue;
            return parameter;
        }
    }
}

增加：查询表是否存在：

        public static int IsTableExists(string tableName) 
        {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            conn.Open();

            SQLiteCommand command = new SQLiteCommand();
            command.Connection = conn;
            command.CommandText = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='" + tableName + "'";
            int val = Convert.ToInt32(command.ExecuteScalar());

            command.Dispose();
            conn.Close();
            conn.Dispose();
            return val;
        }