系列文章
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#底层库–数据库类型与程序类型转换器
本文链接:https://blog.csdn.net/youcheng_ge/article/details/128817610
C#底层库–SQLite的使用(小型、本地数据库)
本文链接:https://blog.csdn.net/youcheng_ge/article/details/123666958
【C#】MySQL数据库导入工具(批量Excel插入)
本文链接:https://blog.csdn.net/youcheng_ge/article/details/126427323
【Oracle】数据库服务器限制ip访问
本文链接:https://blog.csdn.net/youcheng_ge/article/details/122220930
【Oracle】Excel导入数据教程
本文链接:https://blog.csdn.net/youcheng_ge/article/details/118722756
【Oracle】数据库还原教程_数据泵
本文链接:https://blog.csdn.net/youcheng_ge/article/details/118054855
【SQL】如何查询表字段并识别主键
本文链接:https://blog.csdn.net/youcheng_ge/article/details/110820405
【SQL】outer apply的用法
本文链接:https://blog.csdn.net/youcheng_ge/article/details/79903489
MySQL安装教程(详细)
本文链接:https://blog.csdn.net/youcheng_ge/article/details/126037520
MySQL卸载教程(详细)
本文链接:https://blog.csdn.net/youcheng_ge/article/details/129279265
【MySQL】group by分类汇总,如何增加“总计”字段?
本文链接:https://blog.csdn.net/youcheng_ge/article/details/128217837
【MySQL】WITH CHECK OPTION的用法
本文链接:https://blog.csdn.net/youcheng_ge/article/details/128147196
【MySQL】使用存储过程插入千万级数据如何提升效率?
本文链接:https://blog.csdn.net/youcheng_ge/article/details/77728189
【MySQL】数据库表行列转置的实现
本文链接:https://blog.csdn.net/youcheng_ge/article/details/77625052
【MySQL】查询中,NULL值转换为空字符串
本文链接:https://blog.csdn.net/youcheng_ge/article/details/130381878
【MySQL】插入文件路径,反斜杠消失
本文链接:https://blog.csdn.net/youcheng_ge/article/details/130361131
【MySQL】单表获取库存数
本文链接:https://blog.csdn.net/youcheng_ge/article/details/130964513
【sqlite】联查Join更新
本文链接:https://blog.csdn.net/youcheng_ge/article/details/130660370
【MySQL】MES中,发货计划取数逻辑
本文链接:https://blog.csdn.net/youcheng_ge/article/details/130383090
文章目录
前言
本专栏为【底层库】,主要介绍编程过程中 通用函数。我们将这些通用固化的源码,进行重写、封装、拓展,再进行单元测试、集成测试、beta测试,最终形成通用化模板,这里我们称为“底层库”。
作为研发人员的你,并不需要花大量时间,研究“底层库”的含义,及“底层库”的实现方法。你只需要几行调用代码,就可以解决项目上碰到的难题。而底层库使用方法,本专栏均有详细介绍,也有项目应用场景。
底层库已实现功能:MySQL脚本构建器、MySQL数据库访问操作、参数配置文件读写、加解密算法、日志记录、HTTP通信、Socket通信、API前后端交互、邮件发送、文件操作、配置参数存储、Excel导入导出、CSV和DataTable转换、压缩解压、自动编号、Session操作等。
本专栏会持续更新,不断优化【底层库】,大家有任何问题,可以私信我。本专栏之间关联性较强(我会使用到某些底层库,某些文章可能忽略介绍),如果您对本专栏感兴趣,欢迎关注,我将带你用最简洁的代码,实现最复杂的功能。
一、底层库作用
本文主要介绍如何快速生成 数据库SQL脚本,无论我们做什么开发语言,始终离不开与数据库的交互,所以必然要使用数据操纵语言–SQL。
使用我们的底层库,你将不在需要人工写SQL语句了,你只需要关注数据逻辑赋值、关注数据模型,顺便提一句:数据逻辑赋值这里我也完成了工具生成,可以阅读专栏《提升编程效率》。畅想一下你只需要把DataTable、List传给它,它就自动帮你构建SQL脚本,结合文章《C#底层库–MySQL数据库访问操作辅助类(推荐阅读)》,你也不用管数据库连接了,是不是给你编程带来很大便利。
功能包含:select语句、insert语句、update语句、内部构建select子句、内部构建set子句、内部构建where子句。
注意:项目开发中,需要配合《C#底层库--MySQL数据库访问操作辅助类(推荐阅读)》使用
C#底层库–MySQL数据库访问操作辅助类(推荐阅读)
本文链接:https://blog.csdn.net/youcheng_ge/article/details/126886379
二、底层库源码
2.1 创建SQLBuilder类
创建类MySQLBuilder.cs,复制以下代码。
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;
using Geyc_Utils.Core;
namespace Geyc_Utils.DataBase
{
//类内容: 按键输入,回车键触发,扫成品编号
//创建人:gyc
//创建时间:2023-02-01
//作用:用于构造SQL脚本:select、insert、update语句。使用过程中发现错误,请联系作者修改 https://blog.csdn.net/youcheng_ge。
//============================================================================================
//注意事项
// 1. 支持MySQL、SQL Server
// 2.
//============================================================================================
public class SQLBuilder
{
#region 构造Select语句
/// <summary>
/// 构造Select语句
/// </summary>
/// <param name="a_strDestTable">数据库表名</param>
/// <param name="a_strFields">字段列</param>
/// <param name="a_strKeys">Where字段key</param>
/// <param name="a_list">IList</param>
/// <returns></returns>
public static string CreateSelectSQLBuilder(string a_strDestTable, string a_strFields, string a_strKeys, IList a_list)
{
DataTable l_tableSchema = ListToDataTable(a_list);
StringBuilder sqlBuilder = new StringBuilder();//返回的结果
//查询字段
List<string> listField = new List<string>();
listField = StringExtension.StringSplitToList(a_strFields);
//条件字段
List<string> listKey = new List<string>();
listKey = StringExtension.StringSplitToList(a_strKeys);
bool l_bflag = false;
foreach (DataRow dr in l_tableSchema.Rows)
{
if (l_bflag)
{
sqlBuilder.AppendLine("UNION ALL");
}
string sql1 = InternalBuildColumnSelect(listField);
string sql2 = " FROM " + a_strDestTable;
string sql3 = InternalBuildWhereByValue(dr, l_tableSchema.Columns, listKey);//WHERE语句
sqlBuilder.AppendLine(sql1 + sql2 + sql3);
l_bflag = true;
}
return sqlBuilder.ToString();
}
/// <summary>
/// 构造Select语句
/// </summary>
/// <param name="a_strDestTable">数据库表名</param>
/// <param name="a_strFields">字段列</param>
/// <param name="a_strKeys">Where字段key</param>
/// <param name="a_tableSchema">DataTable</param>
/// <returns></returns>
public static string CreateSelectSQLBuilder(string a_strDestTable, string a_strFields, string a_strKeys, DataTable a_tableSchema)
{
string sqlResult = string.Empty;//返回的结果
//查询字段
List<string> listField = new List<string>();
listField = StringExtension.StringSplitToList(a_strFields);
//条件字段
List<string> listKey = new List<string>();
listKey = StringExtension.StringSplitToList(a_strKeys);
foreach (DataRow dr in a_tableSchema.Rows)
{
string sql1 = InternalBuildColumnSelect(listField);
string sql2 = " FROM " + a_strDestTable;
string sql3 = InternalBuildWhereByValue(dr, a_tableSchema.Columns, listKey);//WHERE语句
sqlResult = sqlResult + sql1 + sql2 + sql3 + "\r\n";
}
return sqlResult;
}
/// <summary>
/// 内部构造Select语句
/// </summary>
/// <param name="a_listField">list字段</param>
/// <returns></returns>
private static string InternalBuildColumnSelect(List<string> a_listField)
{
string text = "SELECT ";
foreach (string item in a_listField)
{
text += item + ",";
}
return text = StringExtension.DelLastComma(text);
}
#endregion
#region 构造update语句
/// <summary>
/// 构造update语句
/// </summary>
/// <param name="a_strDestTable">数据库表名字</param>
/// <param name="a_strFields">更新字段名,多字段以分号隔开</param>
/// <param name="a_strKeys">条件key名,多key以分号隔开</param>
/// <param name="a_tableSchema">DataTable数据</param>
/// <returns>SQL</returns>
public static string CreateUpdateSQLBuilder(string a_strDestTable, string a_strFields, string a_strKeys, DataTable a_tableSchema)
{
string sqlResult = string.Empty;//返回的结果
//更新字段
List<string> listField = new List<string>();
listField = StringExtension.StringSplitToList(a_strFields);
//条件字段
List<string> listKey = new List<string>();
listKey = StringExtension.StringSplitToList(a_strKeys);
foreach (DataRow dr in a_tableSchema.Rows)
{
string sql1 = "UPDATE " + a_strDestTable;
string sql2 = string.Empty;
string sql3 = string.Empty;
if (listField.Count > 0) //指定更新字段
{
sql2 = InternalBuildSetByValue(dr, a_tableSchema.Columns, listField, listKey);//SET子句
}
else //未指定更新字段
{
sql2 = InternalBuildSetByValue(dr, a_tableSchema.Columns, listKey);//SET子句
}
sql3 = InternalBuildWhereByValue(dr, a_tableSchema.Columns, listKey);//WHERE子句
sqlResult = sqlResult + sql1 + sql2 + sql3 + ";\r\n";
}
return sqlResult;
}
/// <summary>
/// 构造update语句
/// </summary>
/// <param name="a_strDestTable">数据库表名字</param>
/// <param name="a_strFields">更新字段名,多字段以分号隔开</param>
/// <param name="a_strKeys">条件key名,多key以分号隔开</param>
/// <param name="a_list">list数据</param>
/// <returns>SQL</returns>
public static string CreateUpdateSQLBuilder(string a_strDestTable, string a_strFields, string a_strKeys, IList a_list)
{
DataTable l_tableSchema = ListToDataTable(a_list);
string sqlResult = string.Empty;//返回的结果
//更新字段
List<string> listField = new List<string>();
listField = StringExtension.StringSplitToList(a_strFields);
//条件字段
List<string> listKey = new List<string>();
listKey = StringExtension.StringSplitToList(a_strKeys);
foreach (DataRow dr in l_tableSchema.Rows)
{
string sql1 = "UPDATE " + a_strDestTable;
string sql2 = string.Empty;
string sql3 = string.Empty;
if (listField.Count > 0) //指定更新字段
{
sql2 = InternalBuildSetByValue(dr, l_tableSchema.Columns, listField, listKey);//SET子句
}
else //未指定更新字段
{
sql2 = InternalBuildSetByValue(dr, l_tableSchema.Columns, listKey);//SET子句
}
sql3 = InternalBuildWhereByValue(dr, l_tableSchema.Columns, listKey);//WHERE子句
sqlResult = sqlResult + sql1 + sql2 + sql3 + ";\r\n";
}
return sqlResult;
}
/// <summary>
/// 内部构造set子句(未指定更新字段)
/// </summary>
/// <param name="a_dataRow">DataRow</param>
/// <param name="a_DataColumns">DataColumnCollection</param>
/// <param name="a_listKey">List条件key</param>
/// <returns></returns>
private static string InternalBuildSetByValue(DataRow a_dataRow, DataColumnCollection a_DataColumns, List<string> a_listKey)
{
a_listKey.Add("id");//加一个id列
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SET ");
foreach (DataColumn col in a_DataColumns)
{
string text = col.ColumnName;
if (!a_listKey.Contains(text.ToLower()))
{
sqlBuilder.Append(text + "='" + a_dataRow[text] + "',");
}
}
return StringExtension.DelLastComma(sqlBuilder.ToString());
}
/// <summary>
/// 内部构造set子句(指定更新字段)
/// </summary>
/// <param name="a_dataRow">DataRow</param>
/// <param name="a_DataColumns">DataColumnCollection</param>
/// <param name="a_listField">List更新字段</param>
/// <param name="a_listKey">List条件key</param>
/// <returns></returns>
private static string InternalBuildSetByValue(DataRow a_dataRow, DataColumnCollection a_DataColumns, List<string> a_listField, List<string> a_listKey)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SET ");
foreach (DataColumn col in a_DataColumns)
{
string text = col.ColumnName;
if (a_listField.Contains(text.ToLower()))
{
sqlBuilder.Append(text + "='" + a_dataRow[text] + "',");
}
}
return StringExtension.DelLastComma(sqlBuilder.ToString());
}
/// <summary>
/// 内部构造WHERE子句
/// </summary>
/// <param name="a_dataRow">a_dataRow</param>
/// <param name="a_DataColumns">a_DataColumns</param>
/// <param name="a_listKey">a_listKey</param>
/// <returns>SQL</returns>
private static string InternalBuildWhereByValue(DataRow a_dataRow, DataColumnCollection a_DataColumns, List<string> a_listKey)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" WHERE (1=1)");
foreach (DataColumn col in a_DataColumns)
{
string text = col.ColumnName;
if (a_listKey.Contains(text.ToLower()))//主键
{
sqlBuilder.Append(" AND ");
sqlBuilder.Append(text + "='" + a_dataRow[text] + "'");
}
}
return sqlBuilder.ToString();
}
#endregion
#region 构造insert语句
/// <summary>
/// 构造insert语句
/// </summary>
/// <param name="a_strDestTable">数据库表名</param>
/// <param name="a_list">List数据</param>
/// <returns>SQL</returns>
public static string CreateInsertSQLBuilder(string a_strDestTable, IList a_list)
{
//转换成DataTable
DataTable l_tableSchema = ListToDataTable(a_list);
string sql1 = string.Format("INSERT INTO {0} ({1})\r\n", a_strDestTable, InternalGetColumnList(l_tableSchema));
string sql2 = InternalBuildColumnSelectByValue(l_tableSchema);
string sqlResult = sql1 + "\r\n" + sql2;
return sqlResult;
}
/// <summary>
/// 构造insert语句
/// </summary>
/// <param name="a_strDestTable">数据库表名</param>
/// <param name="a_tableSchema">DataTable数据</param>
/// <returns>SQL</returns>
public static string CreateInsertSQLBuilder(string a_strDestTable, DataTable a_tableSchema)
{
string sql1 = string.Format("INSERT INTO {0} ({1})\r\n", a_strDestTable, InternalGetColumnList(a_tableSchema));
string sql2 = InternalBuildColumnSelectByValue(a_tableSchema);
string sqlResult = sql1 + "\r\n" + sql2;
return sqlResult;
}
/// <summary>
/// 构造含条件的insert语句(根据主键判断,数据存在则更新,数据不存在则插入)
/// </summary>
/// <param name="a_strDestTable">数据库表名</param>
/// <param name="a_list">List数据</param>
/// <param name="a_strUpdateValue">更新值(数量可能需要叠加,无法根据键构造)</param>
/// <returns>SQL</returns>
public static string CreateInsertSQLWithDuplicateKeyBuilder(string a_strDestTable, string a_strUpdateValue, IList a_list)
{
//转换成DataTable
DataTable l_tableSchema = ListToDataTable(a_list);
string sql1 = string.Format("INSERT INTO {0} ({1})\r\n", a_strDestTable, InternalGetColumnList(l_tableSchema));
string sql2 = InternalBuildColumnSelectByValue(l_tableSchema);
string sql3 = "ON DUPLICATE KEY UPDATE " + a_strUpdateValue;
string sqlResult = sql1 + "\r\n" + sql2 + "\r\n" + sql3;
return sqlResult;
}
/// <summary>
/// 构造含条件的insert语句(根据主键判断,数据存在则更新,数据不存在则插入)
/// </summary>
/// <param name="a_strDestTable">数据库表名</param>
/// <param name="a_tableSchema">DataTable</param>
/// <param name="a_strUpdateValue">更新值(数量可能需要叠加,无法根据键构造)</param>
/// <returns>SQL</returns>
public static string CreateInsertSQLWithDuplicateKeyBuilder(string a_strDestTable, string a_strUpdateValue, DataTable a_tableSchema)
{
string sql1 = string.Format("INSERT INTO {0} ({1})\r\n", a_strDestTable, InternalGetColumnList(a_tableSchema));
string sql2 = InternalBuildColumnSelectByValue(a_tableSchema);
string sql3 = "ON DUPLICATE KEY UPDATE " + a_strUpdateValue;
string sqlResult = sql1 + "\r\n" + sql2 + "\r\n" + sql3;
return sqlResult;
}
/// <summary>
/// 内部获取字段列表
/// </summary>
/// <param name="a_tbSchema">DataTable</param>
/// <returns>SQL</returns>
private static string InternalGetColumnList(DataTable a_tbSchema)
{
string text = string.Empty;
for (int i = 0; i < a_tbSchema.Columns.Count; i++)
{
string text2 = a_tbSchema.Columns[i].ColumnName;
if (text2.ToLower() != "id")
{
text = text + text2 + ",";
}
}
return StringExtension.DelLastComma(text);
}
/// <summary>
/// 内部的,构建列Select语句
/// </summary>
/// <param name="a_tbSchema">DataTable</param>
/// <returns>string</returns>
private static string InternalBuildColumnSelectByValue(DataTable a_tbSchema)
{
bool l_bflag = false;
StringBuilder sqlBuilder = new StringBuilder();
foreach (DataRow dataRow in a_tbSchema.Rows)
{
if (l_bflag)
{
sqlBuilder.AppendLine("UNION ALL");
}
sqlBuilder.Append("SELECT ");
string text = string.Empty;
for (int i = 0; i < a_tbSchema.Columns.Count; i++)
{
text = text + "'" + dataRow[i].ToString() + "'" + ",";
}
text = StringExtension.DelLastComma(text);
sqlBuilder.AppendLine(text);
l_bflag = true;
}
return sqlBuilder.ToString();
}
#endregion
#region 通用方法
/// <summary>
/// List转换成DataTable
/// </summary>
/// <param name="list">list</param>
/// <returns>DataTable</returns>
private static System.Data.DataTable ListToDataTable(IList list)
{
System.Data.DataTable result = new System.Data.DataTable();
if (list.Count > 0)
{
PropertyInfo[] propertys = list[0].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
//获取类型
Type colType = pi.PropertyType;
//当类型为Nullable<>时
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
result.Columns.Add(pi.Name, colType);
}
for (int i = 0; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
#endregion
}
}
三、调用方法
我写了测试例子,我们看一下构造的SQL语句是不是正确的。
3.1 数据模型类
创建类, t_wutiaoModel.cs。为了测试方便,我删除了部分字段,本代码由
【代码生成工具】
自动生成,不需要人工编码。
【C#】代码模板生成工具
本文链接:https://blog.csdn.net/youcheng_ge/article/details/126890673
代码如下:
namespace ConsoleApp2
{
public class T_wutiao
{
public T_wutiao() { }
public string 编号 { get; set; }
public string 规格 { get; set; }
public string 重量 { get; set; }
public string 采购订单号 { get; set; }
}
}
3.2 调用主方法
创建一个控制台程序,主方法里面复制以下代码:
static void Main(string[] args)
{
List<T_wutiao> model = new List<T_wutiao>();
T_wutiao t_Wutiao = new T_wutiao()
{
编号 = "20230217",
规格 = "15.6",
重量 = "5",
采购订单号 = "SO20230223"
};
model.Add(t_Wutiao);
t_Wutiao = new T_wutiao()
{
编号 = "20230222",
规格 = "0.78",
重量 = "10",
采购订单号 = "SO20230224"
};
model.Add(t_Wutiao);
//01-insert语句
string l_sqlInsert = MySQLBuilder.CreateInsertSQLBuilder("T_wutiao", model);
//02-update语句
string l_sqlUpdate1 = MySQLBuilder.CreateUpdateSQLBuilder("T_wutiao", "编号;规格", model);
string l_sqlUpdate2 = MySQLBuilder.CreateUpdateSQLBuilder("T_wutiao", "编号;", model);
}
3.3 测试Insert语句
INSERT INTO T_wutiao (编号,规格,重量,采购订单号)
SELECT '20230217','15.6','5','SO20230223'
UNION ALL
SELECT '20230222','0.78','10','SO20230224'
3.4 测试update语句
① update语句:
UPDATE T_wutiao SET 重量='5',采购订单号='SO20230223' WHERE (1=1) AND 编号='20230217' AND 规格='15.6'
UPDATE T_wutiao SET 重量='10',采购订单号='SO20230224' WHERE (1=1) AND 编号='20230222' AND 规格='0.78'
② update语句:
UPDATE T_wutiao SET 规格='15.6',重量='5',采购订单号='SO20230223' WHERE (1=1) AND 编号='20230217'
UPDATE T_wutiao SET 规格='0.78',重量='10',采购订单号='SO20230224' WHERE (1=1) AND 编号='20230222'
怎么样?畅想一下你只需要把DataTable、List传给它,它就自动帮你构建SQL脚本,你再不用管会不会SQL了?是不是给你编程带来很大便利。
四、项目样例
我用于MES系统,服务器端数据保存的,原理:客户端传过来json数据,服务端反序列化,然后将数据模型更新到MySQL数据库。
代码非常的简洁,调用代码如下(部分):
List<钨条出入库表Model> l_listTBarStockIn = ConvertJson.DeserializeObject<List<钨条出入库表Model>>(l_strDataTBarStockIn);
strSQL = MySQLBuilder.CreateInsertSQLBuilder(l_strTableTBarStockIn, l_listTBarStockIn);
strSQL = MySQLBuilder.CreateSelectSQLBuilder(l_strTableTBarStockIn, "存货编码;钨条编号;数量", "存货编码;钨条编号;" ,l_listTBarStockIn);
strSQL = MySQLBuilder.CreateInsertSQLWithDuplicateKeyBuilder(l_strTableTBarStockIn, "数量=数量+1", l_listTBarStockIn);
strSQL = MySQLBuilder.CreateUpdateSQLBuilder(l_strTableTBarStockIn, "存货编码", "存货编码;钨条编号", l_listTBarStockIn);
//MySQL.Excute(strSQL);
五、资源链接
【C#】代码模板生成工具
本文链接:https://blog.csdn.net/youcheng_ge/article/details/126890673