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

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添加到DataSet
                    DBds_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添加到DataSet
                DBds_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添加到DataSet
                    DBds_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

    }
}

完整代码工程：

项目链接