【c#】c#使用SqLite数据库并实现简单的增删改查操作

运行环境：Visual Studio 2022
数据库：SQLite
数据库管理工具：Navicat

0. 引言

SQLite简介.

1. SQLite工具安装

在vs 2022 中创建一个控制台应用（.NET Framework）项目，之后点开工具——》NuGet包管理器——》管理解决方案的… 。

在浏览中 搜索sqlite，找到System.Data.SQLite ,点击安装即可。

之后在引用中就可以看到这三个引用了，安装成功。

2. 实现增删改查

首先进行指定一个数据库文件名来存数据库的信息，之后创建数据库的连接，进行建表，增删改查。相应的注释都在代码中，就不废话了，直接上代码。
Program.cs

using System;
using System.Data.SQLite;
using tools;
class Program
{
    static void Main(string[] args)
    {
        string cs = "Data Source=mydb.db"; // 指定数据库文件名  
        // 创建一个新的数据库连接
        using (var con = new SQLiteConnection(cs))
        {
            con.Open(); // 打开连接
            // 创建一个新表
            var cmd = new SQLiteCommand(con);
            cmd.CommandText = "DROP TABLE IF EXISTS people";
            cmd.ExecuteNonQuery();
            cmd.CommandText = @"CREATE TABLE people(id INTEGER PRIMARY KEY,
                    name TEXT, age INTEGER)"; 
            cmd.ExecuteNonQuery();
            // 创建一个新表2
            var cmd1 = new SQLiteCommand(con);
            cmd1.CommandText = "DROP TABLE IF EXISTS class";
            cmd1.ExecuteNonQuery();
            cmd1.CommandText = @"CREATE TABLE class(id INTEGER PRIMARY KEY,
                    name TEXT,course TEXT, score INTEGER)";//integer 整数；PRIMARY KEY 主键；TEXT 文本
            cmd1.ExecuteNonQuery();
            Tools.Read(con);
            Tools.Read2(con);
            // 插入数据
            Tools.Insert(con, "Alice", 30);
            Tools.Insert(con, "Bob", 25);
            Tools.Insert(con, "Anna", 35);
            Tools.Insert(con, "Join", 28);
            Tools.Insert(con, "Join", 28);
            Tools.Insert(con, "Join", 28);
            Tools.Insert2(con, "Join", "英语", 100);
            Tools.Insert2(con, "anna", "数学", 100);
            Tools.Insert2(con, "anna", "数学", 100);
            Tools.Insert2(con, "anna", "数学", 100);
            Tools.Insert2(con, "anna", "数学", 100);
            
            // 读取并显示数据
            Console.WriteLine("Read and display the inserted data:");
            Tools.Read(con);
            Tools.Read2(con);

            // 更新数据
            string s = "Anna"; int a = 50;
            Tools.Update(con, s, a);
            Console.WriteLine("\n更新之后的结果：");
            Tools.Read(con);
            //键盘输入
            /*Console.WriteLine("\nUpdating who:");
            string s = Console.ReadLine();
            Console.WriteLine("\nUpdating age to what:");
            int a = Int32.Parse(Console.ReadLine());*/
            
            // 删除数据
            Console.WriteLine("\nDeleting Bob's data:");
            Tools.Delete(con, "Bob");
            Tools.Read(con);
            Console.ReadLine();
            // 关闭数据库连接
            con.Close();
        }
    } 
}

Tools.cs

using System;
using System.Data.SQLite;

namespace tools
{
    public class Tools
    {
        /// <summary>
        /// 给people表格添加数据
        /// </summary>
        /// <param name="con"></param>
        /// <param name="name"></param>
        /// <param name="age"></param>
        internal static void Insert(SQLiteConnection con, string name, int age)
        {
            var cmd = new SQLiteCommand(con);
            cmd.CommandText = "INSERT INTO people(name, age) VALUES(@name, @age)";
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.Prepare();

            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 给class表格添加数据
        /// </summary>
        /// <param name="con"></param>
        /// <param name="name"></param>
        /// <param name="course"></param>
        /// <param name="score"></param>
        internal static void Insert2(SQLiteConnection con, string name, string course, int score)
        {
            var cmd = new SQLiteCommand(con);
            cmd.CommandText = "INSERT INTO class(name,course,score) VALUES(@name,@course,@score)";
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@course", course);
            cmd.Parameters.AddWithValue("@score", score);
            cmd.Prepare();

            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 读取people数据
        /// </summary>
        /// <param name="con"></param>
        public static void Read(SQLiteConnection con)
        {
            string stm = "SELECT * FROM people";
            var cmd = new SQLiteCommand(stm, con);
            SQLiteDataReader rdr = cmd.ExecuteReader();
            Console.WriteLine("*-----------------------------------------------*");
            Console.WriteLine("|******              people               ******|");
            while (rdr.Read()){
                Console.WriteLine("*-----------------------------------------------*");
                Console.WriteLine($"|\t{rdr.GetInt32(0)}\t|\t{rdr.GetString(1)}\t|\t{rdr.GetInt32(2)}\t|");
            }
            Console.WriteLine("*-----------------------------------------------*");
        }

        /// <summary>
        /// 读取class数据
        /// </summary>
        /// <param name="con"></param>
        public static void Read2(SQLiteConnection con)
        {
            string stm = "SELECT * FROM class";
            var cmd = new SQLiteCommand(stm, con);
            SQLiteDataReader rdr = cmd.ExecuteReader();
            Console.WriteLine("*---------------------------------------------------------------*");
            Console.WriteLine("|******                     class                         ******|");
            while (rdr.Read())
            {
                Console.WriteLine("*---------------------------------------------------------------*");
                Console.WriteLine($"|\t{rdr.GetInt32(0)}\t|\t{rdr.GetString(1)}\t|\t{rdr.GetString(2)}\t|\t{rdr.GetInt32(3)}\t|");
            }
            Console.WriteLine("*---------------------------------------------------------------*");
        }

        /// <summary>
        /// 修改people表格数据
        /// </summary>
        /// <param name="con"></param>
        /// <param name="name"></param>
        /// <param name="age"></param>
        public static void Update(SQLiteConnection con, string name, int age)
        {
            var cmd = new SQLiteCommand(con);
            cmd.CommandText = "UPDATE people SET age=@age WHERE name=@name";
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.Prepare();

            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 删除people数据
        /// </summary>
        /// <param name="con"></param>
        /// <param name="name"></param>
        public static void Delete(SQLiteConnection con, string name)
        {
            var cmd = new SQLiteCommand(con);
            cmd.CommandText = "DELETE FROM people WHERE name=@name";
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Prepare();

            cmd.ExecuteNonQuery();
        }
    }
}

运行结果：

3. 查看数据库信息内容