C#给Sqlite数据库进行加密、修改密码_c#怎么给存入sqlite的数据加密_业界新闻

发布时间:2024-07-13 22:28

阅读量:1

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上物联网嵌入式知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、电子书籍、讲解视频，并且后续会持续更新

    /// <summary>      /// 关闭数据库连接      /// </summary>      public void CloseConnection()      {          //销毁Command          if (dbCommand != null)          {              dbCommand.Cancel();          }          dbCommand = null;          //销毁Reader          if (dataReader != null)          {              dataReader.Close();          }          dataReader = null;          //销毁Connection          if (dbConnection != null)          {              dbConnection.Close();          }          dbConnection = null;        }        /// <summary>      /// 读取整张数据表      /// </summary>      /// <returns>The full table.</returns>      /// <param name="tableName">数据表名称</param>      public SQLiteDataReader ReadFullTable(string tableName)      {          string queryString = "SELECT * FROM " + tableName;  //获取所有可用的字段          return ExecuteQuery(queryString);      }        /// <summary>      /// 向指定数据表中插入数据      /// </summary>      /// <returns>The values.</returns>      /// <param name="tableName">数据表名称</param>      /// <param name="values">插入的数值</param>      public SQLiteDataReader InsertValues(string tableName, string[] values)      {          //获取数据表中字段数目          int fieldCount = ReadFullTable(tableName).FieldCount;          //当插入的数据长度不等于字段数目时引发异常          if (values.Length != fieldCount)          {              throw new SQLiteException("values.Length!=fieldCount");          }          string queryString = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";          for (int i = 1; i < values.Length; i++)          {              queryString += ", " + "'" + values[i] + "'";          }          queryString += " )";          return ExecuteQuery(queryString);      }        /// <summary>      /// 更新指定数据表内的数据      /// </summary>      /// <returns>The values.</returns>      /// <param name="tableName">数据表名称</param>      /// <param name="colNames">字段名</param>      /// <param name="colValues">字段名对应的数据</param>      /// <param name="key">关键字</param>      /// <param name="value">关键字对应的值</param>      /// <param name="operation">运算符：=,<,>,...，默认“=”</param>      public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value, string operation)      {          // operation="=";  //默认          //当字段名称和字段数值不对应时引发异常          if (colNames.Length != colValues.Length)          {              throw new SQLiteException("colNames.Length!=colValues.Length");          }          string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";            for (int i = 1; i < colValues.Length; i++)          {              queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";          }          queryString += " WHERE " + key + operation + "'" + value + "'";            return ExecuteQuery(queryString);      }      /// <summary>      /// 更新指定数据表内的数据      /// </summary>      /// <returns>The values.</returns>      /// <param name="tableName">数据表名称</param>      /// <param name="colNames">字段名</param>      /// <param name="colValues">字段名对应的数据</param>      /// <param name="key">关键字</param>      /// <param name="value">关键字对应的值</param>      /// <param name="operation">运算符：=,<,>,...，默认“=”</param>      public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key1, string value1, string operation, string key2, string value2)      {          // operation="=";  //默认          //当字段名称和字段数值不对应时引发异常          if (colNames.Length != colValues.Length)          {              throw new SQLiteException("colNames.Length!=colValues.Length");          }          string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";            for (int i = 1; i < colValues.Length; i++)          {              queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";          }          //表中已经设置成int类型的不需要再次添加‘单引号’，而字符串类型的数据需要进行添加‘单引号’          queryString += " WHERE " + key1 + operation + "'" + value1 + "'" + "OR " + key2 + operation + "'" + value2 + "'";            return ExecuteQuery(queryString);      }          /// <summary>      /// 删除指定数据表内的数据      /// </summary>      /// <returns>The values.</returns>      /// <param name="tableName">数据表名称</param>      /// <param name="colNames">字段名</param>      /// <param name="colValues">字段名对应的数据</param>      public SQLiteDataReader DeleteValuesOR(string tableName, string[] colNames, string[] colValues, string[] operations)      {          //当字段名称和字段数值不对应时引发异常          if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)          {              throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");          }            string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";          for (int i = 1; i < colValues.Length; i++)          {              queryString += "OR " + colNames[i] + operations[0] + "'" + colValues[i] + "'";          }          return ExecuteQuery(queryString);      }        /// <summary>      /// 删除指定数据表内的数据      /// </summary>      /// <returns>The values.</returns>      /// <param name="tableName">数据表名称</param>      /// <param name="colNames">字段名</param>      /// <param name="colValues">字段名对应的数据</param>      public SQLiteDataReader DeleteValuesAND(string tableName, string[] colNames, string[] colValues, string[] operations)      {          //当字段名称和字段数值不对应时引发异常          if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)          {              throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");          }            string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";            for (int i = 1; i < colValues.Length; i++)          {              queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";          }          return ExecuteQuery(queryString);      }          /// <summary>      /// 创建数据表      /// </summary> +      /// <returns>The table.</returns>      /// <param name="tableName">数据表名</param>      /// <param name="colNames">字段名</param>      /// <param name="colTypes">字段名类型</param>      public SQLiteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)      {          string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0];          for (int i = 1; i < colNames.Length; i++)          {              queryString += ", " + colNames[i] + " " + colTypes[i];          }          queryString += "  ) ";          return ExecuteQuery(queryString);      }        /// <summary>      /// Reads the table.      /// </summary>      /// <returns>The table.</returns>      /// <param name="tableName">Table name.</param>      /// <param name="items">Items.</param>      /// <param name="colNames">Col names.</param>      /// <param name="operations">Operations.</param>      /// <param name="colValues">Col values.</param>      public SQLiteDataReader ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)      {          string queryString = "SELECT " + items[0];          for (int i = 1; i < items.Length; i++)          {              queryString += ", " + items[i];          }          queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];          for (int i = 0; i < colNames.Length; i++)          {              queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";          }          return ExecuteQuery(queryString);      }        /// <summary>      /// 本类log      /// </summary>      /// <param name="s"></param>     public static void Log(string s)     {          Console.WriteLine("class SqLiteHelper:::" + s);      }  }

}

 ②给窗体应用程序编写对应的控制功能：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace TestEncryptSqLite
{
public partial class Form1 : Form
{
//文件及其对应路径
private string _fileAndPath = null;

    private SQLiteConnection _con;     bool isCorrect = false;         //原密码是否正确       private SqliteHelper sql;      private string sqliteDatabasePWD = null;       /// <summary>     /// 文件路径     /// </summary>     public string DbFilePath { get =>@"E:\成品\EquipmentMonitoring1.db"; }      /// <summary>     /// 旧密码     /// </summary>     public string OriginalPassword { get; set; }      public string FileAndPath { get => _fileAndPath; set => _fileAndPath = value; }      /// <summary>     /// 修改密码     /// </summary>     /// <param name="newPassword">新密码</param>     public void ChangePassword(string newPassword,string originalPassword)     {                 _con = new SQLiteConnection("Data Source=" + this.FileAndPath);         if (!string.IsNullOrEmpty(originalPassword))         {             try             {                 _con.SetPassword(originalPassword);                 _con.Open();                 isCorrect = true;                 if (isCorrect)                 {                     if (!string.IsNullOrEmpty(newPassword))                     {                         _con.ChangePassword(newPassword);                         _con.Close();                     }                     try                     {                         _con.Open();                     }                     catch (Exception ex)                     {                         throw new Exception("无法连接到数据库!" + ex.Message);                     }                 }             }             catch (Exception e)             {                 isCorrect = false;                 throw new Exception("无法连接到数据库!" + e.Message); ;             }          }         else         {             MessageBox.Show("请点击“选择文件”按钮,选择对应的数据库,选择对应的数据库", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);         }                             }      public Form1()     {         InitializeComponent();     }      private void textBox2_TextChanged(object sender, EventArgs e)     {      }      private void button1_Click(object sender, EventArgs e)     {         string pwd = textBox1.Text.Trim();         string pwd2 = textBox2.Text.Trim();          if (!string.IsNullOrEmpty(pwd))         {             if (!string.IsNullOrEmpty(pwd2))             {                 ChangePassword(pwd2, pwd);                  MessageBox.Show("给 " + this.FileAndPath + " 修改密码成功！！！", "给Sqlite数据库修改密码成功提示", MessageBoxButtons.OK, MessageBoxIcon.Information);                 textBox1.Text = "";                 textBox2.Text = "";             }             else             {                 MessageBox.Show("新密码不能为空，请检查后重新输入！！！", "Sqlite数据库修改提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);                 textBox2.Text = "";             }         }         else         {             MessageBox.Show("请输入正确的数据库原密码！", "Sqlite数据库原密码提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);             textBox1.Text = "";         }             }      private void button2_Click(object sender, EventArgs e)     {         _con = new SQLiteConnection("Data Source=" + this.FileAndPath);         if (!string.IsNullOrEmpty(textBox1.Text.Trim()))         {             if (textBox1.Text.Trim() == sqliteDatabasePWD)             {                 _con.SetPassword(textBox1.Text.Trim());             }             else             {                 MessageBox.Show("请在“打开数据库”按钮左侧输入正确的数据库密码！", "Sqlite数据库密码输入错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);             }                      }         else         {             MessageBox.Show("请点击“选择文件”按钮,选择对应的数据库,选择对应的数据库", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);             MessageBox.Show("请在“打开数据库”按钮左侧输入正确的数据库密码！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);             return;                     }         try         {             _con.Open();         }         catch (Exception ex)         {             throw new Exception("无法连接到数据库!" + ex.Message);         }     }      private void button3_Click(object sender, EventArgs e)     {         if (!string.IsNullOrEmpty(textBox_filePath.Text))         {             if (!string.IsNullOrEmpty(textBox1.Text))             {                 sqliteDatabasePWD = textBox1.Text;                 Query();                 MessageBox.Show("查询数据成功，请看“查询成功”按钮左侧的标签内容", "查询成功提示", MessageBoxButtons.OK, MessageBoxIcon.Information);             }             else             {                 MessageBox.Show("请在“打开数据库”按钮左侧的输入框中输入正确的密码", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);             }                     }         else         {             MessageBox.Show("请点击“选择文件”按钮,选择对应的数据库", "提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);         }                     }       //插入数据     private void insertData()     {         try          {                // sql = new SqLiteHelper("data source=mydb.db");              sql = new SqliteHelper(this.FileAndPath,sqliteDatabasePWD);              //创建名为table1的数据表              sql.CreateTable("table1", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" });              //插入两条数据              sql.InsertValues("table1", new string[] { "1", "张三", "16", "Zhang@163.com" });              //sql.InsertValues("table1", new string[] { "2", "李四", "17", "Li4@163.com" });                //更新数据，将Name="张三"的记录中的Name改为"小三"              sql.UpdateValues("table1", new string[] { "Name" }, new string[] { "sunlei" }, "Name", "小三", "=");              //删除Name="小三"且Age=16的记录,DeleteValuesOR方法类似

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上物联网嵌入式知识点，真正体系化！

如果你需要这些资料，可以戳这里获取

string[] { “1”, “张三”, “16”, “Zhang@163.com” });

            //sql.InsertValues("table1", new string[] { "2", "李四", "17", "Li4@163.com" });                //更新数据，将Name="张三"的记录中的Name改为"小三"              sql.UpdateValues("table1", new string[] { "Name" }, new string[] { "sunlei" }, "Name", "小三", "=");              //删除Name="小三"且Age=16的记录,DeleteValuesOR方法类似

[外链图片转存中…(img-L8wtLW2Q-1715675622294)]
[外链图片转存中…(img-7WprbtTO-1715675622295)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上物联网嵌入式知识点，真正体系化！

如果你需要这些资料，可以戳这里获取