当前位置： 首页 > web >正文

C# 实现轻量化数据库SQLite在工业中上的应用

web 2025/8/16 22:38:33

1、安装：

安装SQLite可视化工具，SQLiteStudio.下载地址：SQLite

新建.NET FromWork项目，在NUGet包中安装System.Data.SQLite包。或者直接在引用中添加System.Data.SQLite.dll配置文件。

2、连接数据库

public const string ConfigPath = "D:\\FuyaoGroup\\SQLite_Test\\Config.db";//数据库成员变量public SQLiteConnection DBcn_Config;      //DB连接对象public SQLiteDataAdapter DBadp_Config;    //作为DataSet与数据库之间的桥梁，用于填充数据(Fill方法)和将修改更新回数据库(Update方法)。public DataSet DBds_Config;               //BD数据临时储存public SQLiteCommand DBSqlCmd_Config;     //表格操作public void ConnectDataBase(){try{DBcn_Config = new SQLiteConnection($"Data Source={ConfigPath};Persist Security Info=False");if (DBcn_Config.State == ConnectionState.Closed) DBcn_Config.Open();         // 打开数据库                             string SqlstrConfig = "select * from Sys_Config Order by id DESC";           // 定义查询规则，从下往上         DBadp_Config = new SQLiteDataAdapter(SqlstrConfig, DBcn_Config);             // 创建数据适配器               DBds_Config = new DataSet();                                                 // 创建数据集               DBadp_Config.Fill(DBds_Config, "Sys_Config");                                // 将数据源中选中的记录填充到数据集 Fig_DBOpen = true;TB_DBState.Text = "数据库连接成功！";}catch(Exception ex) {MessageBox.Show(ex.Message);              TB_DBState.Text = "数据库连接失败！";Fig_DBOpen = false;}}

2、加载数据库中所有表，并在listBox中显示

  public void loadRecipe(){string sql = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";SQLiteCommand cmd = new SQLiteCommand(sql, DBcn_Recipe);using (SQLiteDataReader dr = cmd.ExecuteReader()){listBox1.Items.Clear();while (dr.Read()){if (dr["name"].ToString() != "sqlite_sequence"){listBox1.Items.Add(dr["name"].ToString());}                 }}}

3、读取数据库中某表

 public void ReadRecipe(string Recipe_Table){if (Fig_DBOpen){try{ //初始化查询变量string SqlstrRecipe = $"select * from {Recipe_Table} Order by id DESC";DBadp_Recipe = new SQLiteDataAdapter(SqlstrRecipe, DBcn_Recipe);DBds_Recipe = new DataSet();DBadp_Recipe.Fill(DBds_Recipe, Recipe_Table);//读取数据TB_Name.Text = Convert.ToString(DBds_Recipe.Tables[Recipe_Table].Rows[0]["name"] as object);TB_Age.Text = Convert.ToString(DBds_Recipe.Tables[Recipe_Table].Rows[0]["age"] as object);TB_Height.Text = Convert.ToString(DBds_Recipe.Tables[Recipe_Table].Rows[0]["height"] as object);TB_Weight.Text = Convert.ToString(DBds_Recipe.Tables[Recipe_Table].Rows[0]["weight"] as object);}catch(Exception ex){MessageBox.Show(ex.Message);}             }else{MessageBox.Show("读取失败，请检查数据库连接");}}

4、新建表，并向表中填充数据

public void WriteRecipe(string Recipe_Table){          if (Fig_DBOpen){try{string createTableSql = $@"CREATE TABLE IF NOT EXISTS {Recipe_Table} (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age TEXT,height TEXT ,weight TEXT )";using (SQLiteCommand cmd = new SQLiteCommand(createTableSql, DBcn_Recipe)){cmd.ExecuteNonQuery();}string SqlstrRecipe = $"select * from {Recipe_Table} Order by id DESC";DBadp_Recipe = new SQLiteDataAdapter(SqlstrRecipe, DBcn_Recipe);DBds_Recipe = new DataSet();DBadp_Recipe.Fill(DBds_Recipe, Recipe_Table);SQLiteCommandBuilder DBCmdBd_SysConfig = new SQLiteCommandBuilder(DBadp_Recipe);      //使用SQLiteCommandBuilder简化操作            DataRow DataRow_Sys = DBds_Recipe.Tables[Recipe_Table].NewRow();                      //在DataSet新增一行//写入数据DataRow_Sys["name"] = TB_Name.Text;DataRow_Sys["age"] = TB_Age.Text;DataRow_Sys["height"] = TB_Height.Text;DataRow_Sys["weight"] = TB_Weight.Text;//新建的Row添加到DataSetDBds_Recipe.Tables[Recipe_Table].Rows.Add(DataRow_Sys);//更新DataSet数据缓存到真实数据表中DBadp_Recipe.InsertCommand = DBCmdBd_SysConfig.GetInsertCommand();DBadp_Recipe.Update(DBds_Recipe, Recipe_Table);loadRecipe();}catch(Exception ex){MessageBox.Show(ex.Message);}            }else{MessageBox.Show("写入失败，请检查数据库连接");}}

5、删除某表

 public void dropTabRecipe(string Recipe_Table){try{string dropTableSql = $"DROP TABLE IF EXISTS {Recipe_Table};";using (SQLiteCommand cmd = new SQLiteCommand(dropTableSql, DBcn_Recipe)){cmd.ExecuteNonQuery();MessageBox.Show($"表{Recipe_Table}已成功删除。");loadRecipe();}}catch (Exception ex){ MessageBox.Show($"删除表时发生错误: {ex.Message}");}}

完整代码
 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;namespace SQLite_Test
{public partial class Form1 : Form{public Form1(){InitializeComponent();}private void Form1_Load(object sender, EventArgs e){ConnectDataBase(); //打开数据库ReadConfig();      //加载系统信息loadRecipe();      //加载产品至listBox中}#region 变量//数据库路径public const string ConfigPath = "D:\\FuyaoGroup\\SQLite_Test\\Config.db";public const string RecipePath = "D:\\FuyaoGroup\\SQLite_Test\\Recipe.db";public const string ResultPath = "D:\\FuyaoGroup\\SQLite_Test\\Sys_Result.db";//数据库成员变量public SQLiteConnection DBcn_Config;      //DB连接对象public SQLiteDataAdapter DBadp_Config;    //作为DataSet与数据库之间的桥梁，用于填充数据(Fill方法)和将修改更新回数据库(Update方法)。public DataSet DBds_Config;               //BD数据临时储存public SQLiteCommand DBSqlCmd_Config;     //表格操作public SQLiteConnection DBcn_Recipe;   public SQLiteDataAdapter DBadp_Recipe; public DataSet DBds_Recipe;            public SQLiteCommand DBSqlCmd_Recipe;  public SQLiteConnection DBcn_Result;   public SQLiteDataAdapter DBadp_Result; public DataSet DBds_Result;            public SQLiteCommand DBSqlCmd_Result;  //系统相关变量public bool   Fig_DBOpen=false;#endregion//连接数据库public void ConnectDataBase(){try{DBcn_Config = new SQLiteConnection($"Data Source={ConfigPath};Persist Security Info=False");if (DBcn_Config.State == ConnectionState.Closed) DBcn_Config.Open();         // 打开数据库                             string SqlstrConfig = "select * from Sys_Config Order by id DESC";           // 定义查询规则，从下往上         DBadp_Config = new SQLiteDataAdapter(SqlstrConfig, DBcn_Config);             // 创建数据适配器               DBds_Config = new DataSet();                                                 // 创建数据集               DBadp_Config.Fill(DBds_Config, "Sys_Config");                                // 将数据源中选中的记录填充到数据集 DBcn_Recipe= new SQLiteConnection($"Data Source={RecipePath};Persist Security Info=False");if (DBcn_Recipe.State == ConnectionState.Closed) DBcn_Recipe.Open();DBcn_Result = new SQLiteConnection($"Data Source={ResultPath};Persist Security Info=False");if (DBcn_Result.State == ConnectionState.Closed) DBcn_Result.Open();string SqlstrResult = "select * from Prods_Result Order by id DESC";DBadp_Result = new SQLiteDataAdapter(SqlstrResult, DBcn_Result);DBds_Result = new DataSet();DBadp_Result.Fill(DBds_Result, "Prods_Result");Fig_DBOpen = true;TB_DBState.Text = "数据库连接成功！";}catch(Exception ex) {MessageBox.Show(ex.Message);              TB_DBState.Text = "数据库连接失败！";Fig_DBOpen = false;}}//*********COnfig***********////读公共配置public void ReadConfig(){if (Fig_DBOpen){//重新执行查询（防止数据库已经被改动）DBadp_Config.SelectCommand.CommandText = "select * from Sys_Config Order by id DESC";DBds_Config.Clear();DBadp_Config.Fill(DBds_Config, "Sys_Config");//读取数据TB_Shool.Text = Convert.ToString(DBds_Config.Tables["Sys_Config"].Rows[0]["shool"] as object);TB_Grade.Text = Convert.ToString(DBds_Config.Tables["Sys_Config"].Rows[0]["grade"] as object);TB_Class.Text = Convert.ToString(DBds_Config.Tables["Sys_Config"].Rows[0]["class"] as object);TB_Teacher.Text = Convert.ToString(DBds_Config.Tables["Sys_Config"].Rows[0]["teacher"] as object);}else {MessageBox.Show("读取失败，请检查数据库连接");}}//写公共配置public void WriteConfig(){if (Fig_DBOpen){//使用SQLiteCommandBuilder对操作进行简化，实现增删改操作SQLiteCommandBuilder DBCmdBd_SysConfig = new SQLiteCommandBuilder(DBadp_Config);//在DataSet新增一行DataRow DataRow_Sys = DBds_Config.Tables["Sys_Config"].NewRow();//写入数据DataRow_Sys["shool"] = TB_Shool.Text;DataRow_Sys["grade"]  = TB_Grade.Text;DataRow_Sys["class"] = TB_Class.Text;DataRow_Sys["teacher"] = TB_Teacher.Text;//新建的Row添加到DataSetDBds_Config.Tables["Sys_Config"].Rows.Add(DataRow_Sys);//更新DataSet数据缓存到真实数据表中DBadp_Config.InsertCommand = DBCmdBd_SysConfig.GetInsertCommand();DBadp_Config.Update(DBds_Config, "Sys_Config");ReadConfig();}else{MessageBox.Show("写入失败，请检查数据库连接");}}//********Recipe************//public void loadRecipe(){string sql = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";SQLiteCommand cmd = new SQLiteCommand(sql, DBcn_Recipe);using (SQLiteDataReader dr = cmd.ExecuteReader()){listBox1.Items.Clear();while (dr.Read()){if (dr["name"].ToString() != "sqlite_sequence"){listBox1.Items.Add(dr["name"].ToString());}                 }}}public void ReadRecipe(string Recipe_Table){if (Fig_DBOpen){try{ //初始化查询变量string SqlstrRecipe = $"select * from {Recipe_Table} Order by id DESC";DBadp_Recipe = new SQLiteDataAdapter(SqlstrRecipe, DBcn_Recipe);DBds_Recipe = new DataSet();DBadp_Recipe.Fill(DBds_Recipe, Recipe_Table);//读取数据TB_Name.Text = Convert.ToString(DBds_Recipe.Tables[Recipe_Table].Rows[0]["name"] as object);TB_Age.Text = Convert.ToString(DBds_Recipe.Tables[Recipe_Table].Rows[0]["age"] as object);TB_Height.Text = Convert.ToString(DBds_Recipe.Tables[Recipe_Table].Rows[0]["height"] as object);TB_Weight.Text = Convert.ToString(DBds_Recipe.Tables[Recipe_Table].Rows[0]["weight"] as object);}catch(Exception ex){MessageBox.Show(ex.Message);}             }else{MessageBox.Show("读取失败，请检查数据库连接");}}public void WriteRecipe(string Recipe_Table){          if (Fig_DBOpen){try{string createTableSql = $@"CREATE TABLE IF NOT EXISTS {Recipe_Table} (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age TEXT,height TEXT ,weight TEXT )";using (SQLiteCommand cmd = new SQLiteCommand(createTableSql, DBcn_Recipe)){cmd.ExecuteNonQuery();}string SqlstrRecipe = $"select * from {Recipe_Table} Order by id DESC";DBadp_Recipe = new SQLiteDataAdapter(SqlstrRecipe, DBcn_Recipe);DBds_Recipe = new DataSet();DBadp_Recipe.Fill(DBds_Recipe, Recipe_Table);SQLiteCommandBuilder DBCmdBd_SysConfig = new SQLiteCommandBuilder(DBadp_Recipe);      //使用SQLiteCommandBuilder简化操作            DataRow DataRow_Sys = DBds_Recipe.Tables[Recipe_Table].NewRow();                      //在DataSet新增一行//写入数据DataRow_Sys["name"] = TB_Name.Text;DataRow_Sys["age"] = TB_Age.Text;DataRow_Sys["height"] = TB_Height.Text;DataRow_Sys["weight"] = TB_Weight.Text;//新建的Row添加到DataSetDBds_Recipe.Tables[Recipe_Table].Rows.Add(DataRow_Sys);//更新DataSet数据缓存到真实数据表中DBadp_Recipe.InsertCommand = DBCmdBd_SysConfig.GetInsertCommand();DBadp_Recipe.Update(DBds_Recipe, Recipe_Table);loadRecipe();}catch(Exception ex){MessageBox.Show(ex.Message);}            }else{MessageBox.Show("写入失败，请检查数据库连接");}}public void dropTabRecipe(string Recipe_Table){try{string dropTableSql = $"DROP TABLE IF EXISTS {Recipe_Table};";using (SQLiteCommand cmd = new SQLiteCommand(dropTableSql, DBcn_Recipe)){cmd.ExecuteNonQuery();MessageBox.Show($"表{Recipe_Table}已成功删除。");loadRecipe();}}catch (Exception ex){ MessageBox.Show($"删除表时发生错误: {ex.Message}");}}#region 界面操作//Config更新private void BT_UpdataConfig_Click(object sender, EventArgs e){WriteConfig();}//Recipe加载private void BT_RecipeLode_Click(object sender, EventArgs e){if (textBox1.Text != ""){ReadRecipe(textBox1.Text);}else {MessageBox.Show("请选择产品");}}//Recipe保存private void button4_Click(object sender, EventArgs e){if (textBox1.Text != ""){WriteRecipe(textBox1.Text);}else{MessageBox.Show("请选择产品");}}//Recipe删除private void button3_Click(object sender, EventArgs e){if (textBox1.Text != ""){dropTabRecipe(textBox1.Text);}else{MessageBox.Show("请选择产品");}}//listBox选择事件private void listBox1_SelectedIndexChanged(object sender, EventArgs e){if (listBox1.SelectedItem != null){textBox1.Text = listBox1.SelectedItem.ToString();}}#endregion}
}

完整代码工程：

项目链接

http://www.xdnf.cn/news/8070.html

相关文章：

  • TensorFlow深度学习实战（17）——主成分分析详解
  • 鞋服行业数据防泄露——企业解决方案
  • NFS服务器实验
  • 深入了解linux系统—— 文件系统
  • 物联网、云计算技术加持，助推楼宇自控系统实现智能高效管理
  • 机器学习与深度学习算法：从决策树到 GAN 的原理与 PyTorch 实现
  • 数智读书笔记系列033《软件设计的哲学（第2版）》：复杂性管理的艺术
  • Qt C++图书管理系统
  • 在Linux debian12系统上使用go语言以及excelize库处理excel数据
  • CQF预备知识：一、微积分 —— 1.2.2 函数f(x)的类型详解
  • 【力扣题目分享】二叉树专题(C++)
  • Spring开发系统时如何实现上传和下载文件
  • Java转Go日记（五十四）：gin路由
  • P1833 樱花
  • 端口号详解（技术向）
  • Java之函数式接口、lambda表达式、stream流操作、Optional容器、方法引用
  • 企业级调度器LVS
  • Java读写分离实战
  • Java SpringBoot 项目中 Redis 存储 Session 具体实现步骤
  • Java阻塞队列（BlockingQueue）的使用：ArrayBlockingQueue类、LinkedBlockingQueue类
  • JVM参数详解与实战案例指南(AI)
  • BurpSuite学习安装
  • MobaXterm 解除只能保存14个session会话限制
  • 篇章十 消息持久化（二）
  • 加密软件对企业安全的帮助
  • 水浒后传-暹罗国建立新国家的故事
  • 影刀Fun叉鸟-2048
  • 【强化学习】PPO如何根据奖励模型调整模型参数
  • 使用 OpenCV 实现哈哈镜效果
  • Python-阶跃函数与激活函数