SqliteHelper

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

public class SqliteHelper
{
    //从配置文本中读取连接字符串
    // public static string connStr = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;

    private readonly static string connStr = "data source=ZqDataBase.db;version=3;";
    private readonly SQLiteTransaction _sqLiteTransaction;
    private readonly SQLiteConnection _sqLiteConnection;
    private readonly bool _transaction;

    /// <summary>
    /// 构造函数（参数为true,将开启事务模式，否则开启常规模式）
    /// </summary>
    /// <param name="transaction"></param>
    public SqliteHelper(bool transaction = false)
    {
        _transaction = transaction;
        if (transaction)
        {
            _sqLiteConnection = new SQLiteConnection(connStr);
            _sqLiteConnection.Open();
            _sqLiteTransaction = _sqLiteConnection.BeginTransaction();
        }
    }

    /// <summary>
    /// 返回受影响行数(适用：Insert/Update/Delete)
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="ps"></param>
    /// <returns></returns>
    public int ExecuteNonQuery(string sql, params SQLiteParameter[] ps)
    {
        if (_transaction)
        {
            var cmd = new SQLiteCommand(_sqLiteConnection) { CommandText = sql };
            cmd.Parameters.AddRange(ps);
            cmd.Transaction = _sqLiteTransaction;
            return cmd.ExecuteNonQuery();
        }
        else
        {
            var cnn = new SQLiteConnection(connStr);
            cnn.Open();
            var mycommand = new SQLiteCommand(cnn) { CommandText = sql };
            mycommand.Parameters.AddRange(ps);
            var rowsUpdated = mycommand.ExecuteNonQuery();
            cnn.Close();
            return rowsUpdated;
        }
    }

    /// <summary>
    /// 获取首行首列值的方法
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="ps"></param>
    /// <returns></returns>
    public object ExecuteScalar(string sql, params SQLiteParameter[] ps)
    {
        if (_transaction)
        {
            var cmd = new SQLiteCommand(_sqLiteConnection) { CommandText = sql };
            cmd.Parameters.AddRange(ps);
            cmd.Transaction = _sqLiteTransaction;
            var value = cmd.ExecuteScalar();
            return value != null ? value.ToString() : "";
        }
        else
        {
            var cnn = new SQLiteConnection(connStr);
            cnn.Open();
            var mycommand = new SQLiteCommand(cnn) { CommandText = sql };
            mycommand.Parameters.AddRange(ps);
            var value = mycommand.ExecuteScalar();
            cnn.Close();
            return value != null ? value.ToString() : "";
        }
    }

    /// <summary>
    /// 获取结果集
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="ps"></param>
    /// <returns></returns>
    public DataTable GetDataTable(string sql, params SQLiteParameter[] ps)
    {
        if (_transaction)
        {
            //构造适配器对象
            var adapter = new SQLiteDataAdapter(sql, _sqLiteConnection);
            var dt = new DataTable();
            adapter.SelectCommand.Parameters.AddRange(ps);
            adapter.Fill(dt);
            return dt;
        }
        else
        {
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                var adapter = new SQLiteDataAdapter(sql, conn);
                var dt = new DataTable();
                adapter.SelectCommand.Parameters.AddRange(ps);
                adapter.Fill(dt);
                return dt;
            }
        }
    }


    /// <summary>
    /// 提交当前事务，并关闭链接。SQLiteTransaction，
    /// </summary>
    public void CommitTransaction()
    {
        _sqLiteTransaction.Commit();
        _sqLiteTransaction.Dispose();
        _sqLiteConnection.Close();
        _sqLiteConnection.Dispose();
    }
}