系列文章
C#底层库--RegexHelper正则表达式辅助类
本文链接:https://blog.csdn.net/youcheng_ge/article/details/109745286
C#底层库–程序日志记录类
本文链接:https://blog.csdn.net/youcheng_ge/article/details/124187709
C#底层库--SQLBuilder脚本构建类(select、insert、update、in SQL语句自动生成)
本文链接:https://blog.csdn.net/youcheng_ge/article/details/129179216
C#底层库--MySQL数据库访问操作辅助类(推荐阅读)
本文链接:https://blog.csdn.net/youcheng_ge/article/details/126886379
C#底层库--XML配置参数读写辅助类(推荐阅读)
本文链接:C#底层库--XML配置参数读写辅助类(推荐阅读)_花北城的博客-CSDN博客
C#底层库–获取文件版本和MD5值
本文链接:https://blog.csdn.net/youcheng_ge/article/details/112513871
C#底层库–FilesHelper文件辅助类(删除目录文件、复制文件到指定目录)
本文链接:C#底层库--FilesHelper文件辅助类(删除目录文件、复制文件到指定目录)_c# filehelper_花北城的博客-CSDN博客
C#底层库--Excel操作帮助库(可读加密Excel表格)
本文链接:C#底层库--操作Excel帮助类(读取、导出表格)_c#操作excel类库_花北城的博客-CSDN博客
C#底层库–随机数生成器
本文链接:C#底层库--随机数生成器_花北城的博客-CSDN博客
C#底层库–RegexHelper正则表达式辅助类
本文链接:C#底层库--RegexHelper正则表达式辅助类_c# 正则表达式 helper_花北城的博客-CSDN博客
C#底层库–CSV和DataTable相互转换
本文链接:C#底层库--CSV和DataTable相互转换_c# csv转datatable_花北城的博客-CSDN博客
C#底层库–Image图片操作类
本文链接:C#底层库--Image图片操作类_c# 图片处理类库_花北城的博客-CSDN博客
C#底层库–JSON使用教程_详细(序列化、反序列化、list&datatable互换)
本文链接:C#底层库--JSON使用教程_详细(序列化、反序列化、list&datatable互换)_c#json帮助类_花北城的博客-CSDN博客
C#底层库–cookie使用教程
本文链接:C#底层库--cookie的使用教程_花北城的博客-CSDN博客
C#底层库–Session操作辅助类
本文链接:C#底层库--Session的使用类_sessionhelper文件_花北城的博客-CSDN博客
C#底层库–Image图片操作类
本文链接:C#底层库--Image图片操作类_c# 图片处理类库_花北城的博客-CSDN博客
C#底层库–数据库类型与程序类型转换器
本文链接:C#底层库--数据库类型与程序类型转换_花北城的博客-CSDN博客
C#底层库–StringExtension字符串扩展类
本文链接:C#底层库--StringExtension字符串扩展类_花北城的博客-CSDN博客
C#底层库--万能进制转换器(自定义有序字符,支持任意进制)
本文链接:https://blog.csdn.net/youcheng_ge/article/details/130444724
C#底层库--文件上传方法
本文链接:https://blog.csdn.net/youcheng_ge/article/details/128817487
目录
前言
本专栏为【底层库】,主要介绍编程过程中 通用函数。我们将这些通用固化的源码,进行重写、封装、拓展,再进行单元测试、集成测试、beta测试,最终形成通用化模板,这里我们称为“底层库”。
作为研发人员的你,并不需要花大量时间,研究“底层库”的含义,及“底层库”的实现方法。你只需要几行调用代码,就可以解决项目上碰到的难题。而底层库使用方法,本专栏均有详细介绍,也有项目应用场景。
底层库已实现功能:MySQL脚本构建器、MySQL数据库访问操作、参数配置文件读写、加解密算法、日志记录、HTTP通信、Socket通信、API前后端交互、邮件发送、文件操作、配置参数存储、Excel导入导出、CSV和DataTable转换、压缩解压、自动编号、Session操作等。
本专栏会持续更新,不断优化【底层库】,大家有任何问题,可以私信我。本专栏之间关联性较强(我会使用到某些底层库,某些文章可能忽略介绍),如果您对本专栏感兴趣,欢迎关注,我将带你用最简洁的代码,实现最复杂的功能。
一、底层库作用
SQLite,是一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中。
本文将介绍,SQLite的用法。实现SQLite小型数据的操作,包含:创建、读取、修改、写入。
运用场景:
1、开发程序时,需要记录数据,而xml配置文件方式,无法纪录较多数据。
2、缺少网络环境,不想连接正式数据库服务器,采用本地化方式。
二、底层库源码
2.1 引入动态链接库
System.Data.SQLite.dll
SQLite.Interop.dll(该组件如果没有注册可能引入不进去,可以直接放在bin目录即可)
2.2 创建SQLite辅助类
新建类,重命名 SQLiteHelper.cs,复制以下代码:
using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Collections.Generic;
using System.Data.Common;
namespace DLQRLabel
{
/// <summary>
/// SQLite帮助类
/// 创建人:gyc
/// 创建事件:2022-03-31
/// 说明:负责Sqlite操纵,支持数据库创建、删除,表增删改,事务回滚等,已完善。
/// Data Source=DB\DBPlayer.sqlite3;Version=3;
/// 使用过程中发现错误,请联系作者修改 https://blog.csdn.net/youcheng_ge
/// </summary>
public class SQLiteHelper
{
public string DB_FilePath { get; set; }
public string DB_Password { get; set; }
public string DB_version { get; set; }
public string m_DBConnection { get; set; }
/// <summary>
/// 构造函数
/// </summary>
public SQLiteHelper()
{
this.m_DBConnection = AppConfig.GetValue("m_DBConnection");
}
/// <summary>
/// 构造函数
/// </summary>
public SQLiteHelper(string str_DBConnect)
{
this.m_DBConnection = str_DBConnect;
}
/// <summary>
/// 构造函数(指定数据库)
/// </summary>
/// <param name="datasource"></param>
/// <param name="password"></param>
/// <param name="version"></param>
public void CreateConnection(string datasource, string password = "", int version = 3)
{
this.DB_FilePath = datasource;
this.DB_Password = password;
this.DB_version = version.ToString();
this.m_DBConnection = $"Data Source={DB_FilePath};password={DB_Password},Version={DB_version};";
}
/// <summary>
/// 创建数据库文件
/// </summary>
/// <param name="filePath">文件名</param>
public void CreateDBFile(string filePath)
{
string extension = Path.GetExtension(filePath);
if (extension != "sqlite3")
{
filePath = Path.ChangeExtension(filePath, "sqlite3");
}
if (!File.Exists(filePath))
{
try
{
SQLiteConnection.CreateFile(filePath);
}
catch (Exception)
{
throw;
}
}
}
/// <summary>
/// 删除数据库
/// </summary>
/// <param name="filePath">文件名</param>
public void DeleteDBFile(string filePath)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
/// <summary>
/// 测试数据库连接
/// </summary>
/// <returns>bool</returns>
public bool TestConnect()
{
using (SQLiteConnection connection = new SQLiteConnection(m_DBConnection))
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
try
{
connection.Open();
command.CommandText = "select 1";
command.ExecuteNonQuery();
return true;
}
catch (Exception)
{
return false;
}
}
}
}
/// <summary>
/// 删除表
/// </summary>
/// <param name="tablename">表名称</param>
/// <returns></returns>
public bool DropTable(string tablename)
{
using (SQLiteConnection connection = new SQLiteConnection(m_DBConnection))
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
try
{
connection.Open();
command.CommandText = "DROP TABLE IF EXISTS " + tablename;
command.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
return false;
throw ex;
}
}
}
}
/// <summary>
/// 删除表数据
/// </summary>
/// <param name="tablename">表名</param>
/// <returns></returns>
public bool DeleteTable(string tablename)
{
using (SQLiteConnection connection = new SQLiteConnection(m_DBConnection))
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
try
{
connection.Open();
command.CommandText = "DELETE FROM " + tablename;
command.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
return false;
throw ex;
}
}
}
}
/// <summary>
/// 在指定表中添加列
/// </summary>
/// <param name="a_strTableName">表名称</param>
/// <param name="a_strCol">要添加的列名称</param>
/// <param name="a_strDataType">列数据类型</param>
/// <returns></returns>
public bool AddColumn(string a_strTableName, string a_strCol, string a_strDataType)
{
using (SQLiteConnection connection = new SQLiteConnection(m_DBConnection))
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
try
{
connection.Open();
command.CommandText = "ALTER TABLE " + a_strTableName + " ADD COLUMN " + a_strCol + " " + a_strDataType;
command.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
return false;
throw ex;
}
}
}
}
/// <summary>
/// 根据行号删除数据
/// </summary>
/// <param name="a_strTableName">表名</param>
/// <param name="a_intRowNum">行号</param>
/// <returns></returns>
public bool DeleteRowByNum(string a_strTableName, int a_intRowNum)
{
using (SQLiteConnection connection = new SQLiteConnection(m_DBConnection))
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
try
{
connection.Open();
command.CommandText = "delete from " + a_strTableName + " where rowid = (select rowid from " + a_strTableName + " Limit " + a_intRowNum + ", 1)";
command.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
return false;
throw ex;
}
}
}
}
/// <summary>
/// 不带参数,SQL执行语句
/// update、delete、insert
/// </summary>
/// <param name="a_Sql">SQL</param>
/// <returns></returns>
public int Execute(string a_Sql)
{
SQLiteConnection connection = new SQLiteConnection(m_DBConnection);
SQLiteCommand cmd = new SQLiteCommand(connection);
try
{
connection.Open();
cmd.CommandText = a_Sql;
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex);
return 0;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
/// <summary>
/// 带参数,执行脚本
/// insert,update,delete
/// </summary>
/// <param name="sql">sql</param>
/// <param name="parameters">可变参数,目的是省略了手动构造数组的过程,直接指定对象,编译器会帮助我们构造数组,并将对象加入数组中,传递过来</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
{
using (SQLiteConnection connection = new SQLiteConnection(m_DBConnection))
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
try
{
connection.Open();
command.CommandText = sql;
if (parameters.Length > 0)
{
command.Parameters.AddRange(parameters);
}
return command.ExecuteNonQuery();
}
catch (Exception) { throw; }
}
}
}
/// <summary>
/// 执行查询语句,并返回第一个结果。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
{
using (SQLiteConnection conn = new SQLiteConnection(m_DBConnection))
{
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
try
{
conn.Open();
cmd.CommandText = sql;
if (parameters.Length != 0)
{
cmd.Parameters.AddRange(parameters);
}
return cmd.ExecuteScalar();
}
catch (Exception) { throw; }
}
}
}
/// <summary>
/// 执行一个查询语句,返回一个包含查询结果的DataTable。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)
{
using (SQLiteConnection connection = new SQLiteConnection(m_DBConnection))
{
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
if (parameters.Length != 0)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
try
{
adapter.Fill(data);
}
catch (Exception)
{
throw;
}
return data;
}
}
}
/// <summary>
/// 执行一个查询语句,返回一个关联的SQLiteDataReader实例。
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] parameters)
{
SQLiteConnection connection = new SQLiteConnection(m_DBConnection);
SQLiteCommand command = new SQLiteCommand(sql, connection);
try
{
if (parameters.Length != 0)
{
command.Parameters.AddRange(parameters);
}
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception) { throw; }
}
/// <summary>
/// 带事务回滚批量执行脚本
/// </summary>
/// <param name="a_listSqls">SQL脚本</param>
/// <returns></returns>
public int ExecuteNonQuery(List<string> a_listSqls)
{
SQLiteConnection connection = new SQLiteConnection(m_DBConnection);
SQLiteCommand cmd = new SQLiteCommand(connection);
DbTransaction trans = connection.BeginTransaction();
try
{
try
{
foreach (string sql in a_listSqls)
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
trans.Rollback();//回滚
}
return 1;
}
catch (Exception)
{
return 0;
}
finally
{
trans.Dispose();
cmd.Dispose();
connection.Close();
}
}
}
}
2.3 创建SQL语句构造类(可选)
本章节与SQlite无关,如果你程序开发过程中,有构造SQL语句的需求,可以阅读下文。
C#底层库--SQLBuilder脚本构建类(select、insert、update、in SQL语句自动生成)
https://blog.csdn.net/youcheng_ge/article/details/129179216
您只需要,传入参数 临时表(DataTable)、范式类型(IList),本底层库,自动帮你生成SQL脚本。
三、调用方法
3.1 创建数据库操作
创建类文件 DBOperation.cs,复制以下代码:
using System.Data;
namespace DLQRLabel
{
public class DBService
{
#region 仓库打标
/// <summary>
/// 获取最大编号
/// </summary>
/// <returns></returns>
public static string GetMaxNoByCK()
{
string l_strSql = "select code_no from T_stock order by code_no desc LIMIT 0,1;";
string str_dbCon = AppConfig.GetValue("DBConnect_CK");
SQLiteHelper sQLite = new SQLiteHelper(str_dbCon);
DataTable dt_Temp = sQLite.ExecuteQuery(l_strSql);
if (dt_Temp != null && dt_Temp.Rows.Count > 0)
{
return dt_Temp.Rows[0]["code_no"].ToString();
}
else
{
return "1111";
}
}
/// <summary>
/// 插入主数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertData(PrintModel model)
{
string strSQL = " INSERT INTO " +
"T_stock" +
" (supply_no ,code_no,date)" +
" SELECT " +
"'" + model.supply_no + "'" + "," +
"'" + model.code_no + "'" + "," +
"'" + model.date + "'"
;
string str_dbCon = AppConfig.GetValue("DBConnect_CK");
SQLiteHelper sQLite = new SQLiteHelper(str_dbCon);
return sQLite.ExecuteNonQuery(strSQL) > 0;
}
/// <summary>
/// 获取供应商批次号
/// </summary>
/// <param name="strCodeNo"></param>
/// <returns></returns>
public static string GetSupplyNo(string strCodeNo)
{
string strSQL = $" SELECT supply_no FROM T_stock WHERE code_no='{strCodeNo}'";
string str_dbCon = AppConfig.GetValue("DBConnect_CK");
SQLiteHelper sQLite = new SQLiteHelper(str_dbCon);
DataTable dt = sQLite.ExecuteQuery(strSQL);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0][0].ToString();
}
else
{
return "";
}
}
#endregion
#region 车间焊接
/// <summary>
/// 获取最大编号
/// </summary>
/// <returns></returns>
public static string GetMaxNoByCJ()
{
string l_strSql = "select code_no from T_stop order by code_no desc LIMIT 0,1;";
string str_dbCon = AppConfig.GetValue("DBConnect_CJ");
SQLiteHelper sQLite = new SQLiteHelper(str_dbCon);
DataTable dt_Temp = sQLite.ExecuteQuery(l_strSql);
if (dt_Temp != null && dt_Temp.Rows.Count > 0)
{
return dt_Temp.Rows[0]["code_no"].ToString();
}
else
{
return "M1111";
}
}
/// <summary>
/// 插入合并标签数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertMergeData(PrintMergeModel model)
{
string strSQL = " INSERT INTO " +
"T_stop" +
" (first_code ,second_code,code_no,supply_no,date)" +
" SELECT " +
"'" + model.first_code + "'" + "," +
"'" + model.second_code + "'" + "," +
"'" + model.code_no + "'" + "," +
"'" + model.supply_no + "'" + "," +
"'" + model.date + "'"
;
string str_dbCon = AppConfig.GetValue("DBConnect_CJ");
SQLiteHelper sQLite = new SQLiteHelper(str_dbCon);
return sQLite.ExecuteNonQuery(strSQL) > 0;
}
#endregion
}
}
3.2 保存数据到SQLite
//保存
private void BTN_OK_Click(object sender, EventArgs e)
{
PrintMergeModel model = new PrintMergeModel();
model.first_code = text_1.Text.Trim();
model.second_code = text_2.Text.Trim();
model.code_no = this.text_NewLabel.Text.Trim();
model.date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
if (DBService.InsertMergeData(model))
{
Add_Print(model);
DialogResult = DialogResult.OK;
}
DialogResult = DialogResult.No;
}
四、项目样例
五、资源链接
钨条仓库打标程序链接,本程序临时、测试使用。
链接:https://pan.baidu.com/s/1TzM5aX8FZmA2YZm6IngQcA?pwd=odp8
提取码:odp8