Winform中操作Sqlite数据增删改查、程序启动时执行创建表初始化操作

场景

Sqlite数据库

SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。

它是一个零配置的数据库，这意味着与其他数据库不一样，您不需要在系统中配置。

就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。

SQLite 直接访问其存储文件。

需求是在Winform程序启动时进行本地建库建表，然后进行增删改查的逻辑操作。

注：

博客：
霸道流氓气质的博客_CSDN博客-C#,架构之路,SpringBoot领域博主

实现

1、本文直接参考博客

SQLite操作指南之.NET篇
https://www.cnblogs.com/wml-it/p/16574254.html

进行调用和适配。

2、Nuget安装System.Data.SQLite.Core

 

3、封装操作Sqlite的帮助类SqLiteHelper

    class SqLiteHelper{/// <summary>/// 数据库连接定义/// </summary>private SQLiteConnection dbConnection;/// <summary>/// SQL命令定义/// </summary>private SQLiteCommand dbCommand;/// <summary>/// 数据读取定义/// </summary>private SQLiteDataReader dataReader;/// <summary>/// 构造函数/// </summary>/// <param name="connectionString">连接SQLite库字符串</param>public SqLiteHelper(string connectionString){try{dbConnection = new SQLiteConnection(connectionString);dbConnection.Open();}catch (Exception e){Log(e.ToString());}}/// <summary>/// 执行SQL命令/// </summary>/// <returns>The query.</returns>/// <param name="queryString">SQL命令字符串</param>public SQLiteDataReader ExecuteQuery(string queryString){try{dbCommand = dbConnection.CreateCommand();dbCommand.CommandText = queryString;dataReader = dbCommand.ExecuteReader();}catch (Exception e){Log(e.Message);}return dataReader;}/// <summary>/// 关闭数据库连接/// </summary>public void CloseConnection(){//销毁Commendif (dbCommand != null){dbCommand.Cancel();}dbCommand = null;//销毁Readerif (dataReader != null){dataReader.Close();}dataReader = null;//销毁Connectionif (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>///<paramname="value">关键字对应的值</param><BR>       ///<paramname="operation">运算符：=,<,>,...，默认“=”</param>public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value, string 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>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>static void Log(string s){Console.WriteLine("class SqLiteHelper:::" + s);}}

注意这里的引入

using System;
using System.Data.SQLite;

4、如果需要在程序启动时执行建库建表等操作，只需要在Program.cs中添加执行代码

上面参考博客中的使用示例

  class Program{private static SqLiteHelper sql;static void Main(string[] args){sql = new SqLiteHelper("data source=StudentSystem.db");//创建名为table1的数据表sql.CreateTable("Students", new string[] { "ID", "Name", "Age", "Email" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" });//插入两条数据sql.InsertValues("Students", new string[] { "1", "张三", "22", "[e-mail]" });sql.InsertValues("Students", new string[] { "2", "李四", "25", "[e-mail]" });//更新数据，将Name="张三"的记录中的Name改为"Zhang3"sql.UpdateValues("Students", new string[] { "Name" }, new string[] { "ZhangSan" }, "Name", "Zhang3");//删除Name="张三"且Age=26的记录,DeleteValuesOR方法类似sql.DeleteValuesAND("Students", new string[] { "Name", "Age" }, new string[] { "张三", "22" }, new string[] { "=", "=" });//读取整张表SQLiteDataReader reader = sql.ReadFullTable("Students");while (reader.Read()){//读取IDLog("ID:" + reader.GetInt32(reader.GetOrdinal("ID")));//读取NameLog("NAME:" + reader.GetString(reader.GetOrdinal("Name")));//读取AgeLog("Age:" + reader.GetInt32(reader.GetOrdinal("Age")));//读取EmailLog("Email:" + reader.GetString(reader.GetOrdinal("Email")));}Console.ReadLine();}static void Log(string s){Console.WriteLine(s);}}

这里只需要在启动后执行建库建表的操作，所以在Program.cs中执行封装的全局工具类方法

namespace DataConvert
{static class Program{/// <summary>/// 应用程序的主入口点。/// </summary>[STAThread]static void Main(){Application.EnableVisualStyles();Application.SetCompatibleTextRenderingDefault(false);//顺序勿动//初始化positions数据库Global.Instance.InitPositionSqLite();Application.Run(new Form1());}}
}

如上添加一行

Global.Instance.InitPositionSqLite();

注意这行添加的位置。

然后构建一个单例的全局工具类Global

    class Global{private SqLiteHelper _sqlLiteHelper;private static string _lockFlag = "GlobalLock";private static Global _instance;//mqtt是否已经连接public  bool isMqttClientConnected = false;private Global(){}public static Global Instance{get{lock (_lockFlag){if (_instance == null){_instance = new Global();}return _instance;}}}public SqLiteHelper sqlLiteHelper{get { return _sqlLiteHelper; }set { _sqlLiteHelper = value; }}public void InitPositionSqLite(){_sqlLiteHelper = new SqLiteHelper("data source=positions.db");//创建名为positions的数据表_sqlLiteHelper.CreateTable("positions", new string[] { "timestamp", "data" }, new string[] { "TEXT", "TEXT" });}}

添加一个方法，用来实例化SqlLiteHelp的实例执行建库建表操作

这里创建名为positions.db的库，并且创建名为positions的表，然后该表有两个字段timestamp和data，全部为字符串文本格式。

5、项目启动后查看exe所在同目录下的db文件已经创建成功

 

6、Sqlite可视化工具DB Browser for SQLite的下载与使用

DB Browser for SQLite:

mirrors / sqlitebrowser / sqlitebrowser · GitCode

下载地址:

Downloads - DB Browser for SQLite

下载并在Windows上解压打开后，双击

DB Browser for SQLite.exe

启动，然后打开数据库，选择上面的db文件

这里可以看到表以及浏览数据和执行sql等操作

 

7、这样的话就可以在Form1.cs等页面中执行增删改查的逻辑

插入数据时就可以

Global.Instance.sqlLiteHelper.InsertValues("positions", new string[] { time2, "霸道的程序猿"});

其中time2是获取的当前时间戳字符串。

读取整张表的数据

            SQLiteDataReader reader = Global.Instance.sqlLiteHelper.ReadFullTable("positions");while (reader.Read()){//读取Console.WriteLine("timestamp:" + reader.GetString(reader.GetOrdinal("timestamp")));Console.WriteLine("data:" + reader.GetString(reader.GetOrdinal("data")));}

如果要读取Sqlite指定表中的前多少行，可以通过执行Sqlite的自定义语句的方式实现

比如要查询前6行

SQLiteDataReader reader = Global.Instance.sqlLiteHelper.ExecuteQuery("SELECT* FROM positions LIMIT 6;");

其他操作都可以通过执行sql语句的方式执行，比如删除指定条件的数据

Global.Instance.sqlLiteHelper.ExecuteQuery("DELETE FROM positions WHERE timestamp = "+ reader.GetString(reader.GetOrdinal("timestamp"))+";");

这里是查询出前6行并挨个操作后删除。

其他更多Sqlite语句的写法和示例可自行网上搜索

SQLite 教程 | 菜鸟教程