C#连接操作sqlite

1. 介绍

SQLite 是一个开源的嵌入式关系数据库，实现自包容、零配置、支持事务的SQL数据库引擎。 其特点是高度便携、使用方便、结构紧凑、高效、可靠。 与其他数据库管理系统不同，SQLite 的安装和运行非常简单，在大多数情况下 - 只要确保SQLite的二进制文件存在即可开始创建、连接和使用数据库。如果您正在寻找一个嵌入式数据库项目或解决方案，SQLite是绝对值得考虑。

【SQLite相关有用的链接地址】

SQLite官方网站：http://www.sqlite.org/

SQLite内置核心函数参考文档：http://www.sqlite.org/lang_corefunc.html

SQLite日期时间函数参考文档：http://www.sqlite.org/lang_datefunc.html

SQLite数学函数参考文档：http://www.sqlite.org/lang_aggfunc.html

SQLite相关SQL语法参考文档：http://www.sqlite.org/lang.html

System.Data.SQLite.dll数据访问驱动下载地址：http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

2. SQLiteHelper.cs

　　

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

namespace LocalDatabase
{
    public class SQLiteHelper
    {
        public String connectionString;
        private SQLiteConnection connection;
        private SQLiteTransaction transaction;
        private SQLiteCommand command;

        public SQLiteHelper(String db)
        {
            this.connectionString = "DateTimeKind = Utc;Data Source = " + db;
            this.connection = new SQLiteConnection(connectionString);
            connection.Open();
        }
        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable ??
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {
            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> 
        /// 对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 (SQLiteTransaction 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;
        }

        public SQLiteConnection getConnection()
        {
            return this.connection;
        }
      
        /// <summary>
        /// 打开一个连接
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public void OpenConnection()
        {
            connection = new SQLiteConnection(connectionString);
            connection.Open();
            command = null;
        }

        /// <summary>
        /// Close a connection
        /// </summary>
        /// <param name="conn"></param>
        public void CloseConnection()
        {
            if (connection == null) return;
            connection.Close();
        }

        public void StartTransaction()
        {
            if (connection == null) return;
            transaction = connection.BeginTransaction();
        }

        public void Commit()
        {
            if (null == transaction) return;
            transaction.Commit();
        }

        public void Rollback()
        {
            if (null == transaction) return;
            transaction.Rollback();
        }

        public void PrepareCommand(string sql)
        {
            command = new SQLiteCommand(connection);
            command.CommandText = sql;
        }

        public int ExecuteCommand(SQLiteParameter[] parameters)
        {
            int affectedRows = 0;
            command.Parameters.Clear();
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            affectedRows = command.ExecuteNonQuery();                        
            return affectedRows;
        }

        public object ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            object obj = null;
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = sql;
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                obj = command.ExecuteScalar();
            }
            return obj;
        }

        public int ExecuteScalarInt(string sql, SQLiteParameter[] parameters)
        {
            object value = ExecuteScalar(sql, parameters);
            if (Convert.IsDBNull(value)) return -1;

            return Convert.ToInt32(value);
        }

        public double ExecuteScalarDouble(string sql, SQLiteParameter[] parameters)
        {
            object value = ExecuteScalar(sql, parameters);
            if (Convert.IsDBNull(value)) return 0.0;
            return Convert.ToDouble(value);
        }

        public string ExecuteScalarString(string sql, SQLiteParameter[] parameters)
        {
            object value = ExecuteScalar(sql, parameters);
            if (Convert.IsDBNull(value)) return "";
            return Convert.ToString(value);
        }
    }
}