SQLite加密程序

1.前言

最近做一个项目使用到SQLite数据库,但免费版的没有加密功能,手写一个加密程序,解密将新密码设置为空即可

2.效果

 

3.相关代码

using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
//using System.Configuration;
using System.Data;
using System.IO;
using System.Data.SQLite;

namespace SQLiteEncryptionPro
{
    public static class SQLiteHelper
    {
        //SQLiteHelper.ConStr = "Data Source="+Application.StartupPath+"\\SQLiteDemo;Pooling=true;FaillfMissing=false";
        //private string connString="Server=.;Database=TemperatureDB;Uid=sa;Pwd=luozhiwei"
        //private static string ConStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ToString();
        public static string ConStr = "";
        //<connectionStrings>
        //<add name="connString" connectionString="Data Source=.;Initial Catalog=SMDB;Persist Security Info=True;User ID=sa;Password=luozhiwei"/>
        //</connectionStrings>

        #region 执行格式化的SQL语句
        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="sql"><
        /// ram>
        /// <returns></returns>
        public static int Update(string sql)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand(sql, DBConnection);
            try
            {
                DBConnection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DBConnection.Close();
            }
        }
        /// <summary>
        /// SQLite数据库设置密码
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static void SetPassword(string pwd)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);            
            DBConnection.Open();
            DBConnection.ChangePassword(pwd);
            //DBConnection.Close();
        }

        public static void SetPasswordFirst(string pwd)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            
            DBConnection.SetPassword(pwd);
            DBConnection.Open();
            //DBConnection.Close();
        }
        /// <summary>
        /// 获取单一结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand(sql, DBConnection);
            try
            {
                DBConnection.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                DBConnection.Close();
            }
        }
        /// <summary>
        /// 返回结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SQLiteDataReader GetReader(string sql)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand(sql, DBConnection);
            try
            {
                DBConnection.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                DBConnection.Close();
                throw ex;
            }


        }
        /// <summary>
        /// 返回数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql, SQLiteParameter[] param=null)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand(sql, DBConnection);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                DBConnection.Open();
                if (param!=null)
                {
                    cmd.Parameters.AddRange(param);//添加参数
                }
                da.Fill(ds);
                return ds;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                DBConnection.Close();
            }
        }
        #endregion

        #region 启用事务执行多条SQL语句
        /// <summary>
        /// 启用事务执行多条SQL语句
        /// </summary>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public static bool UpdateByTran(List<string> sqlList)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = DBConnection;
            try
            {
                DBConnection.Open();
                cmd.Transaction = DBConnection.BeginTransaction();//开启事务
                foreach (string itemsql in sqlList)
                {
                    cmd.CommandText = itemsql;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务
                return true;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();//回滚事务（前面提交了，但是没有正常更新）
                throw new Exception("调用事务方法时出现错误：" + ex.Message);
            }
            finally
            {
                if (cmd.Transaction != null)
                    cmd.Transaction = null;//清空事务
                DBConnection.Close();

            }
        }
        #endregion

        #region 错误信息写入日志
        /// <summary>
        /// 将错误信息写入日志文件
        /// </summary>
        /// <param name="msg"></param>
        private static void WriteLog(string msg)
        {
            FileStream fs = new FileStream("Log.text", FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);
            sw.WriteLine("[{0}]  错误信息：{1}", DateTime.Now.ToString(), msg);
            sw.Close();
            fs.Close();
        }
        #endregion

        #region 执行带参数的SQL语句
        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Update(string sql, SQLiteParameter[] param)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand(sql, DBConnection);

            try
            {

                DBConnection.Open();
                cmd.Parameters.AddRange(param);//添加参数
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteLog("执行Update(string sql)方法发生错误，错误日志：" + ex.Message);
                throw;
            }
            finally
            {
                DBConnection.Close();
            }
        }
        /// <summary>
        /// 返回单一结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql, SQLiteParameter[] param)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand(sql, DBConnection);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);//添加参数
            }
            try
            {
                DBConnection.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                WriteLog("执行GetSingleResult(string sql)方法发生错误，错误日志：" + ex.Message);
                throw;
            }
            finally
            {
                DBConnection.Close();
            }
        }
        /// <summary>
        /// 返回数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SQLiteDataReader GetReader(string sql, SQLiteParameter[] param)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand(sql, DBConnection);
            try
            {
                DBConnection.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                WriteLog("执行GetReader(string sql)方法发生错误，错误日志：" + ex.Message);
                DBConnection.Close();
                throw ex;
            }

        }
        /// <summary>
        /// 获取数据库服务器时间
        /// </summary>
        /// <returns></returns>
        public static DateTime GetDBServerTime()
        {
            string sql = "select getdate()";
            return Convert.ToDateTime(GetSingleResult(sql, null));
        }
        #endregion

        #region 调用存储过程
        /// <summary>
        /// 基于存储过程的Update
        /// </summary>
        /// <param name="storeProcedureName"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int UpdateByProcedureName(string storeProcedureName, SQLiteParameter[] param)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = DBConnection;
            cmd.CommandType = CommandType.StoredProcedure;//当前执行的是存储过程
            cmd.CommandText = storeProcedureName;
            try
            {
                DBConnection.Open();
                cmd.Parameters.AddRange(param);//添加参数
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteLog("执行UpdateByProcedureName(string storeProcedureName, SQLiteParameter[] param)方法发生错误，错误日志：" + ex.Message);
                throw ex;
            }
            finally
            {
                DBConnection.Close();
            }
        }
        /// <summary>
        /// 基于存储过程的查询,返回结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static SQLiteDataReader GetReaderByProcedure(string storeProcedureName, SQLiteParameter[] param)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = DBConnection;
            cmd.CommandType = CommandType.StoredProcedure;//当前执行的是存储过程
            cmd.CommandText = storeProcedureName;
            try
            {
                DBConnection.Open();
                if (param!=null)
                {
                    cmd.Parameters.AddRange(param);
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                WriteLog("执行GetReaderByProcedure(string storeProcedureName, SQLiteParameter[] param)方法发生错误，错误日志：" + ex.Message);
                DBConnection.Close();
                throw ex;
            }

        }
        /// <summary>
        /// 基于存储过程,返回数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetDataSetByProcedure(string storeProcedureName, SQLiteParameter[] param)
        {
            SQLiteConnection DBConnection = new SQLiteConnection(ConStr);
            SQLiteCommand cmd = new SQLiteCommand(storeProcedureName, DBConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                DBConnection.Open();
                if (param!=null)
                {
                    cmd.Parameters.AddRange(param);
                }
                da.Fill(ds);
                return ds;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                DBConnection.Close();
            }
        }
        #endregion
    }
}