c#sqlite帮助类

使用方法：

添加引用：
可网上下载或者添加程序包，本人使用：

使用前赋值：

  SQLiteHelper.DatabaseFile = "数据库地址"

使用：

返回数据表：ExecuteDataTale(string sql)
执行操作：
public override IDataReader ExecuteReader(string sql)
public override int ExecuteNonQuery(string sql) //返回影响行数
public override object ExecuteScalar(string sql)//返回数据
批量执行：
public override bool ExcuteNonQuerySqlList(List sqlList)

例如：var dt= SQLiteHelper.Instance.ExecuteDataTale("select * from Devs");

帮助类源码

public class SQLiteHelper : BaseSqlHelper
    {
        public static string DatabaseFile { get; set; }
        public static string sqlAccessStr { get; set; }
        private static SQLiteHelper _instance;
        public static SQLiteHelper Instance
        {
            get { return _instance ?? (_instance = new SQLiteHelper(DatabaseFile)); }

        }

        public SQLiteHelper(string file)
        {
            if (string.IsNullOrEmpty(file))
            {
                throw new Exception("文件为空，请先给DatabaseFile赋值或者传入一个不为空的文件名");
            }

            var accessStr = "Data Source={0};Pooling=true;UseUTF16Encoding=true;FailIfMissing=false";
            sqlAccessStr = String.Format(accessStr, file);
        }

        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans,
            CommandType cmdType, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SQLiteParameter parms in cmdParms)
                {
                    cmd.Parameters.Add(parms);
                }
            }
        }


        public override DataTable ExecuteDataTale(string sql)
        {
            var cmd = new SQLiteCommand();
            using (var conn = new SQLiteConnection(sqlAccessStr))
            {
                PrepareCommand(cmd, conn, null, CommandType.Text, sql, null);
                var adapter = new SQLiteDataAdapter(cmd);
                var table = new DataTable();
                adapter.Fill(table);
                cmd.Parameters.Clear();
                return table;
            }
        }

        public override IDataReader ExecuteReader(string sql)
        {
            SQLiteDataReader rdr;
            var cmd = new SQLiteCommand();
            var conn = new SQLiteConnection(sqlAccessStr);
            try
            {
                PrepareCommand(cmd, conn, null, CommandType.Text, sql, null);
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
            }
            catch
            {
                conn.Close();
                throw;
            }

            return rdr;
        }

        public override int ExecuteNonQuery(string sql)
        {
            var cmd = new SQLiteCommand();
            using (var conn = new SQLiteConnection(sqlAccessStr))
            {
                PrepareCommand(cmd, conn, null, CommandType.Text, sql, null);
                int count = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return count;
            }
        }

        public override object ExecuteScalar(string sql)
        {
            var cmd = new SQLiteCommand();
            using (var conn = new SQLiteConnection(sqlAccessStr))
            {
                PrepareCommand(cmd, conn, null, CommandType.Text, sql, null);
                var value = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return value;
            }
        }
        /// <summary>
        /// 批量导出DataTable的数据到Access表中
        /// 试过自己写的datatable有用，但是公司软件上不可用
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dt"></param>
        public override void ExcuteTableSql(string tableName, DataTable dt)
        {
            List<string> columnList = new List<string>();
            foreach (DataColumn one in dt.Columns)
            {
                columnList.Add(one.ColumnName);
            }
            SQLiteDataAdapter adapter = new SQLiteDataAdapter();
            using (var conn = new SQLiteConnection(sqlAccessStr))
            {
                adapter.SelectCommand = new SQLiteCommand("select * from " + tableName, conn);
                using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter))
                {
                    adapter.InsertCommand = builder.GetInsertCommand();
                    adapter.InsertCommand.Parameters.Clear();
                    foreach (string one in columnList)
                    {
                        adapter.InsertCommand.Parameters.Add(new SQLiteParameter(one, dt.Columns[one].DataType));
                    }
                    adapter.Update(dt);
                }
            }
        }
        /// <summary>
        /// 批量执行sqlList
        /// </summary>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public override bool ExcuteNonQuerySqlList(List<string> sqlList)
        {
            var cmd = new SQLiteCommand();
            using (var conn = new SQLiteConnection(sqlAccessStr))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SQLiteTransaction tra = conn.BeginTransaction();
                cmd.Transaction = tra;
                cmd.Connection = conn;
                try
                {
                    foreach (var sql in sqlList)
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sql;
                        if (cmd.ExecuteNonQuery() < 0)
                            throw new Exception();
                    }
                    tra.Commit();
                    return true;
                }
                catch
                {
                    tra.Rollback();
                    return false;
                }
            }

        }
        #region 事务

        private static SQLiteCommand _cmd;
        private static SQLiteConnection _conn;
        private static SQLiteTransaction _tran;

        public override IDbTransaction BeginTransaction()
        {
            _cmd = new SQLiteCommand();
            _conn = new SQLiteConnection(sqlAccessStr);
            if (_conn.State == ConnectionState.Closed)
            {
                _conn.Open();
            }

            _tran = _conn.BeginTransaction();
            _cmd.Transaction = _tran;
            _cmd.Connection = _conn;
            return _tran;
        }

        public override void Commit()
        {
            _tran.Commit();
            if (_conn.State == ConnectionState.Open)
            {
                _conn.Close();
            }
        }

        public override void Rollback()
        {
            _tran.Rollback();
            if (_conn.State == ConnectionState.Open)
            {
                _conn.Close();
            }
        }

        public override IDataReader ExecuteReaderTrans(string sql)
        {
            try
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = sql;
                return _cmd.ExecuteReader();
            }
            catch
            {
                throw;
            }
        }

        public override int ExecuteNonQueryTrans(string sql)
        {
            try
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = sql;
                return _cmd.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
        }

        public override DataTable ExecuteDataTaleTrans(string sql)
        {
            try
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = sql;
                var adapter = new SQLiteDataAdapter(_cmd);
                var table = new DataTable();
                adapter.Fill(table);
                _cmd.Parameters.Clear();
                return table;
            }
            catch
            {
                throw;
            }
        }

        public override object ExecuteScalarTrans(string sql)
        {
            try
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = sql;
                var value = _cmd.ExecuteScalar();
                _cmd.Parameters.Clear();
                return value;
            }
            catch
            {
                throw;
            }
        }

        #endregion


    }