系列文章

C#底层库–MySQL数据库访问操作辅助类（推荐阅读）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126886379

C#底层库–JSON帮助类_详细（序列化、反序列化、list、datatable）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128805705

WebAPI项目发布和IIS部署
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126539836

---

---

前言

我能抽象出整个世界，但是我不能抽象你。 想让你成为私有常量，这样外部函数就无法访问你。 又想让你成为全局常量，这样在我的整个生命周期都可以调用你。 可惜世上没有这样的常量，我也无法定义你，因为你在我心中是那么的具体。

哈喽大家好，可以发现本文并没有归属我的任何一个专栏，因为我觉得放哪儿都不合适。我主要是想和大家分享我开发过程遇到的问题，以及我的解决方案。
可不要小看这些，或许你没有接触这个项目，但本文的解决方法一定会给你带来启发。

提示：本专栏为项目实战篇，未接触过项目开发的学生理解困难，不建议阅读。

---

一、面临问题

我所在的公司为生产型公司，业务运营会产生各种各样的单据，而这些单据往往需要“编号”区分。比如销售订单–销售订单号、采购订单–采购订单号、原材料出库单–出库单号、财务凭证单–凭证单号、仓库发货单–物流单号等。

这些业务单据号，可不是你想象的那么简单？可不是随机数、自增的IDID能够实现的。我们知道，每个部门有各自的业务，好的设计业务员看他们的单据号，即他们部门单据编号规则，就能够对业务进行分类，归属本单据走哪类处理流程。

显然，如果只是单纯的随机数、自增id无法实现这样的问题。

提示：这是业务部门给出的部分单据号，命令规则。

二、解决方案

2.1 可行性分析

如上图所示，业务部门给出的“编号规则”，基本是XXX+流水号 的形式。这是最初步的一个版本，如果再复杂一点，可以细分为 产品类型代号+日期缩写+流水号，再进行下一步细分，又可以分为 产品类型代号+日期缩写+员工代号+流水号等等。

那么我们必然要采用字符串拼接的形式，XXX是固定的值，保留；流水号是数值类型，需要依次往后1，2，3，4，5…往后编号，在程序中就是 +1操作。

所以，我们是不是要先获取上一次单据号，看流水号排到多少了，然后才能进行+1操作。我们就要查询数据库业务表，获得最大的流水号是多少。

2.2 采用方案

我们作为软件设计师，不能让程序员这么在代码里写大量取数，拼接字符串，这么瞎搞。这对于他们的工作量太大了，而且他们很容易出错，就算他们不出错。退一步来说，如果对于业务部门，哪天业务调整了，编号要变化，你是不是又要改程序？这显然太麻烦了。

所以我为了解决这个问题，我想开发一个专门的服务，统一生成这么个编号。

我将这服务通用化，对平台、开发语言的依赖要尽可能低，就是说，无论你用 电脑、平板、手机、浏览器还是以后的什么设备，都可以获取我的“自动编号”。

解决方案：①创建API应用接口 ②编写自动编号生成器 ③部署服务 ④客户端调用，获取自动编号。

三、软件开发（源码展示）

3.1 创建API项目

C#创建API项目，引入数据库访问帮助类（MySQL版）、字符串操作类等底层库，具体可以看我的专栏《C#底层库》。

3.2 创建数据实体

传入解析的数据类AutoCodeRuleModel.cs，我已定义好，照抄就行了。

using System;namespace DLMESWebAPI
{public class AutoCodeRuleModel{public string 编号规则 { get; set; } //自动编号规则public string 表 { get; set; } //数据库表public string 字段 { get; set; } //表字段}
}

3.3 编写API基础库

这里就是一些封装的库，是我项目开发频繁使用的，包括：数据库连接、xml读写、字符串操作、SQL语句生成等等。
提示：如果对这些类库不了解，或者本文没有附全源码的，你可以关注我的专辑《C#底层库》，里面均有讲解的。

创建类 AutoCodeClient.cs

using DLMESWebAPI.Core;
using System;
using System.Data;namespace DLMESWebAPI.BLL
{public class AutoCodeClient{/// <summary>/// 获取自动编号/// </summary>/// <param name="a_strAutoRule">编号规则</param>/// <param name="a_strCodeTable">表名</param>/// <param name="a_strCodeField">字段名</param>/// <returns></returns>public static string GetAutoCode(string a_strAutoRule, string a_strCodeTable, string a_strCodeField){int a_CodeLength = 4;string[] array = a_strAutoRule.Split(new char[]{',',';'});if (array.Length > 1) //编号规则是否定义了流水号位数。{int.TryParse(array[1], out a_CodeLength);return AutoCodeClient.GetAutoCode(array[0], a_CodeLength, a_strCodeTable, a_strCodeField);}return AutoCodeClient.GetAutoCode(a_strAutoRule, a_CodeLength, a_strCodeTable, a_strCodeField);}public static string GetAutoCode(string a_strAutoRule, int a_CodeLength, string a_strCodeTable, string a_strCodeField){string text = "";string text2 = "";checked{for (int i = 0; i < a_CodeLength; i++){text += "_";text2 += "0";}string text3 = AutoCodeClient.ParseCodeRule(a_strAutoRule, a_CodeLength);string s = AutoCodeClient.ParseQueryRule(a_strAutoRule, a_CodeLength);int num = text3.IndexOf(text);MySQLHelper fetchData = new MySQLHelper();string text4 = "select {0} from {1} where {2} like {3} order by 1 desc LIMIT 0,1";text4 = string.Format(text4, new object[]{string.Format("SUBSTRING({0}, {1}, {2})", a_strCodeField, num + 1, a_CodeLength),a_strCodeTable,a_strCodeField,s.QuotedStr()});DataTable l_dt = fetchData.Query(text4).Tables[0];if (l_dt != null){string newValue;if (l_dt.Rows.Count <= 0){newValue = 1.ToString(text2);}else{text3.Substring(0, num);if (text3.Length > num + 4){text3.Substring(num + 4);}string s2 = l_dt.Rows[0][0].ToString();int num2 = 0;int.TryParse(s2, out num2);num2++;newValue = num2.ToString(text2);}text3 = text3.Replace(text, newValue);}return text3;}}public static string GetAutoCodeAlpha(string a_strAutoRule, string a_strCodeTable, string a_strCodeField){int num = 1;string text = "";string str = "";checked{for (int i = 0; i < num; i++){text += "_";str += "0";}string text2 = AutoCodeClient.ParseCodeRule(a_strAutoRule, num);string s = AutoCodeClient.ParseQueryRule(a_strAutoRule, num);int num2 = text2.IndexOf(text);MySQLHelper fetchData = new MySQLHelper();string text3 = "select top 1 {0} from {1} where {2} like {3} order by 1 desc";text3 = string.Format(text3, new object[]{string.Format("SUBSTRING({0}, {1}, {2})", a_strCodeField, num2 + 1, num),a_strCodeTable,a_strCodeField,s.QuotedStr()});DataTable l_dt = fetchData.Query(text3).Tables[0];if (l_dt != null){string newValue;if (l_dt.Rows.Count <= 0){newValue = "A";}else{text2.Substring(0, num2);if (text2.Length > num2 + 4){text2.Substring(num2 + 4);}string text4 = l_dt.Rows[0][0].ToString();int num3 = 65;if (text4.Length > 0){num3 = (int)text4[0];}num3++;if (num3 > 90){return AutoCodeClient.GetAutoCodeAlpha2(a_strAutoRule, a_strCodeTable, a_strCodeField);}newValue = Convert.ToString((char)num3);}text2 = text2.Replace(text, newValue);}return text2;}}private static string GetAutoCodeAlpha2(string a_strAutoRule, string a_strCodeTable, string a_strCodeField){int num = 2;string text = "";string str = "";checked{for (int i = 0; i < num; i++){text += "_";str += "0";}string text2 = AutoCodeClient.ParseCodeRule(a_strAutoRule, num);string s = AutoCodeClient.ParseQueryRule(a_strAutoRule, num);int num2 = text2.IndexOf(text);MySQLHelper fetchData = new MySQLHelper();string text3 = "select top 1 {0} from {1} where {2} like {3} order by 1 desc";text3 = string.Format(text3, new object[]{string.Format("SUBSTRING({0}, {1}, {2})", a_strCodeField, num2 + 1, num),a_strCodeTable,a_strCodeField,s.QuotedStr()});DataTable l_dt = fetchData.Query(text3).Tables[0];if (l_dt != null){string newValue;if (l_dt.Rows.Count <= 0){newValue = "AA";}else{string input = l_dt.Rows[0][0].ToString();newValue = AutoCodeClient.IncAlphaCode(input);}text2 = text2.Replace(text, newValue);}return text2;}}private static string IncAlphaCode(string input){char c = input[0];char c2 = input[1];int num = (int)c;int num2 = (int)c2;checked{if (num2 < 90){return Convert.ToString((char)num) + Convert.ToString((char)(num2 + 1));}return Convert.ToString((char)(num + 1)) + "A";}}private static string ParseCodeRule(string a_strAutoRule, int a_CodeLength){DateTime date = DateTime.Now.Date;if (a_strAutoRule.Contains("[紧缩天数]")){a_strAutoRule = a_strAutoRule.Replace("[紧缩天数]", date.Day.ToString());}if (a_strAutoRule.Contains("[两位天数]")){a_strAutoRule = a_strAutoRule.Replace("[两位天数]", date.Day.ToString("00"));}if (a_strAutoRule.Contains("[紧缩月份]")){a_strAutoRule = a_strAutoRule.Replace("[两位月份]", date.Month.ToString());}if (a_strAutoRule.Contains("[两位月份]")){a_strAutoRule = a_strAutoRule.Replace("[两位月份]", date.Month.ToString("00"));}checked{if (a_strAutoRule.Contains("[一位年份]")){a_strAutoRule = a_strAutoRule.Replace("[一位年份]", date.Year.ToString().Substring(date.Year.ToString().Length - 1, 1));}if (a_strAutoRule.Contains("[两位年份]")){a_strAutoRule = a_strAutoRule.Replace("[两位年份]", date.Year.ToString().Substring(date.Year.ToString().Length - 2, 2));}if (a_strAutoRule.Contains("[四位年份]")){a_strAutoRule = a_strAutoRule.Replace("[四位年份]", date.Year.ToString("0000"));}string text2 = "";for (int i = 0; i < a_CodeLength; i++){text2 += "_";}a_strAutoRule = a_strAutoRule.Replace("[流水编号]", text2);a_strAutoRule = a_strAutoRule.Replace("[按月编号]", text2);a_strAutoRule = a_strAutoRule.Replace("[按年编号]", text2);return a_strAutoRule;}}private static string ParseQueryRule(string a_strAutoRule, int a_CodeLength){DateTime date = DateTime.Now.Date;if (a_strAutoRule.Contains("[紧缩天数]")){a_strAutoRule = a_strAutoRule.Replace("[紧缩天数]", date.Day.ToString());}if (a_strAutoRule.Contains("[两位天数]")){if (a_strAutoRule.Contains("[按月编号]") || a_strAutoRule.Contains("[按年编号]")){a_strAutoRule = a_strAutoRule.Replace("[两位天数]", "__");}else{a_strAutoRule = a_strAutoRule.Replace("[两位天数]", date.Day.ToString("00"));}}if (a_strAutoRule.Contains("[紧缩月份]")){a_strAutoRule = a_strAutoRule.Replace("[两位月份]", date.Month.ToString());}if (a_strAutoRule.Contains("[两位月份]")){if (a_strAutoRule.Contains("[按年编号]")){a_strAutoRule = a_strAutoRule.Replace("[两位月份]", "__");}else{a_strAutoRule = a_strAutoRule.Replace("[两位月份]", date.Month.ToString("00"));}}checked{if (a_strAutoRule.Contains("[一位年份]")){a_strAutoRule = a_strAutoRule.Replace("[一位年份]", date.Year.ToString().Substring(date.Year.ToString().Length - 1, 1));}if (a_strAutoRule.Contains("[两位年份]")){a_strAutoRule = a_strAutoRule.Replace("[两位年份]", date.Year.ToString().Substring(date.Year.ToString().Length - 2, 2));}if (a_strAutoRule.Contains("[四位年份]")){a_strAutoRule = a_strAutoRule.Replace("[四位年份]", date.Year.ToString("0000"));}string text2 = "";for (int i = 0; i < a_CodeLength; i++){text2 += "_";}a_strAutoRule = a_strAutoRule.Replace("[流水编号]", text2);a_strAutoRule = a_strAutoRule.Replace("[按月编号]", text2);a_strAutoRule = a_strAutoRule.Replace("[按年编号]", text2);return a_strAutoRule;}}}
}

创建数据库辅助类 MySQLHelper.cs

using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using DLMESWebAPI.Core;namespace DLMESWebAPI
{/// <summary>/// 数据访问抽象基础类/// </summary>public class MySQLHelper{//简单工厂模式：将类抽象化处理，编写工厂方法，将客户传入参数转换为对应的实例对象。public string server { get; set; }public string port { get; set; }public string uid { get; set; }public string pwd { get; set; }public string BaseName { get; set; }public string connectionString { get; set; }/// <summary>/// 构造函数/// </summary>public MySQLHelper(){this.server = AppConfig.GetValue("db_server");this.port = AppConfig.GetValue("db_port");this.BaseName = AppConfig.GetValue("db_base");this.uid = AppConfig.GetValue("db_uid");this.pwd = AppConfig.GetValue("db_pwd");this.connectionString = $"server={server};uid={uid};pwd={pwd};database={BaseName};SslMode=None;charset=utf8";}/// <summary>/// 构造函数/// </summary>public MySQLHelper(string server, string port, string uid, string pwd, string BaseName){this.server = server;this.port = port;this.BaseName = BaseName;this.uid = uid;this.pwd = pwd;this.connectionString = $"server={server};uid={uid};pwd={pwd};database={BaseName};SslMode=None;charset=utf8";}#region 公用方法public bool Test(){string l_strSQL = "select 1 ";using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand(l_strSQL, connection)){try{connection.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return false;}else{return true;}}catch (MySqlException e){connection.Close();throw e;}}}}/// <summary>/// 查询表是否存在/// </summary>/// <param name="strTableName">表名</param>/// <returns>bool</returns>public bool Exists(string strTableName){string l_strSQL = "SHOW TABLES LIKE '" + strTableName+"'";using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand(l_strSQL, connection)){try{connection.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return false;}else{return true;}}catch (MySqlException e){connection.Close();throw e;}}}}/// <summary>/// 查询数据是否存在，带参数/// </summary>/// <param name="strSql">查询语句</param>/// <param name="cmdParms">参数列表</param>/// <returns></returns>public bool Exists(string strSql, params MySqlParameter[] cmdParms){using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand(strSql, connection)){cmd.Parameters.AddRange(cmdParms);try{connection.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return false;}else{return true;}}catch (MySqlException e){connection.Close();throw e;}}}}/// <summary>/// 获取表中字段的最大值/// </summary>/// <param name="FieldName">列名</param>/// <param name="TableName">表名</param>/// <returns></returns>public int GetMaxID(string FieldName, string TableName){string strsql = "select max(" + FieldName + ")+1 from " + TableName;using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand(strsql, connection)){try{connection.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return 1;}else{return int.Parse(obj.ToString()); ;}}catch (MySqlException e){connection.Close();throw e;}}}}/// <summary>/// 执行查询语句，无参数，返回DataSet/// </summary>/// <param name="SQLString">查询语句</param>/// <returns>DataSet</returns>public DataSet ShowDataBases(string SQLString){string l_str = $"server={server};uid={uid};pwd={pwd};SslMode=None;";using (MySqlConnection connection = new MySqlConnection(l_str)){DataSet ds = new DataSet();try{connection.Open();MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);command.Fill(ds, "ds");}catch (MySqlException ex){throw new Exception(ex.Message);}return ds;}}#endregion#region  执行简单SQL语句/// <summary>/// 执行SQL语句，返回影响的记录数/// </summary>/// <param name="SQLString">SQL语句</param>/// <returns>影响的记录数</returns>public int ExecuteSql(string SQLString){using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)){try{connection.Open();int rows = cmd.ExecuteNonQuery();return rows;}catch (MySqlException e){connection.Close();throw e;}}}}/// <summary>/// 执行SQL语句(带特殊文本)，返回影响的记录数/// </summary>/// <param name="SQLString">SQL语句</param>/// <param name="strParamText">参数内容</param>/// <returns>影响的记录数</returns>public int ExecuteSql(string SQLString, string strParamText){using (MySqlConnection connection = new MySqlConnection(connectionString)){MySqlCommand cmd = new MySqlCommand(SQLString, connection);MySqlParameter myParameter = new MySqlParameter("@content", SqlDbType.NText);myParameter.Value = strParamText;cmd.Parameters.Add(myParameter);try{connection.Open();int rows = cmd.ExecuteNonQuery();return rows;}catch (MySqlException e){throw e;}finally{cmd.Dispose();connection.Close();}}}/// <summary>/// 执行SQL语句(带参数)，返回影响的记录数/// </summary>/// <param name="SQLString">SQL语句</param>/// <param name="cmdParms">准备好的参数列表</param>/// <returns>影响的记录数</returns>public int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms){using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand()){try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);int rows = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return rows;}catch (MySqlException e){throw e;}}}}/// <summary>/// 指定时间执行，返回影响行数/// </summary>/// <param name="SQLString">执行语句</param>/// <param name="Times">指定时间</param>/// <returns>影响的记录数</returns>public int ExecuteSqlByTime(string SQLString, int Times){using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand(SQLString, connection)){try{connection.Open();cmd.CommandTimeout = Times;int rows = cmd.ExecuteNonQuery();return rows;}catch (MySqlException e){connection.Close();throw e;}}}}/// <summary>/// 批量执行SQL语句，实现数据库事务，返回执行行数。/// </summary>/// <param name="SQLTextList">多条SQL语句</param>		public int ExecuteSqlTran(List<String> SQLTextList){using (MySqlConnection conn = new MySqlConnection(connectionString)){conn.Open();MySqlCommand cmd = new MySqlCommand();MySqlTransaction tx = conn.BeginTransaction();cmd.Transaction = tx;try{int count = 0;for (int n = 0; n < SQLTextList.Count; n++){string strsql = SQLTextList[n];if (strsql.Trim().Length > 1){cmd.CommandText = strsql;count += cmd.ExecuteNonQuery();}}tx.Commit();return count;}catch{tx.Rollback();return 0;}}}/// <summary>/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)/// </summary>/// <param name="strSQL">SQL语句</param>/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>/// <returns>影响的记录数</returns>public int ExecuteSqlInsertImg(string strSQL, byte[] fs){using (MySqlConnection connection = new MySqlConnection(connectionString)){MySqlCommand cmd = new MySqlCommand(strSQL, connection);MySqlParameter myParameter = new MySqlParameter("@fs", SqlDbType.Image);myParameter.Value = fs;cmd.Parameters.Add(myParameter);try{connection.Open();int rows = cmd.ExecuteNonQuery();return rows;}catch (MySqlException e){throw e;}finally{cmd.Dispose();connection.Close();}}}//执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。/// <summary>/// 执行插入语句并返回Id，不需返回Id的用其它执行方法/// </summary>/// <param name="strSql">Sql语句</param>/// <param name="parameters">此参数为Null时进行</param>/// <returns></returns>public int ExecuteInsert(string strSql, MySqlParameter[] parameters){using (MySqlConnection connection = new MySqlConnection(connectionString)){MySqlCommand cmd = new MySqlCommand(strSql, connection);try{connection.Open();if (parameters != null) cmd.Parameters.AddRange(parameters);cmd.ExecuteNonQuery();cmd.CommandText = @"select LAST_INSERT_ID()";//此方法估计也可直接用  select   @@identity 获得，两值基本都是全局服务，使用时不能关闭Connectionint value = Int32.Parse(cmd.ExecuteScalar().ToString());return value;}catch (Exception e){throw e;}}}/// <summary>/// 无参查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )/// </summary>/// <param name="strSqlText">查询语句</param>/// <returns>MySqlDataReader</returns>public MySqlDataReader ExecuteReader(string strSqlText){MySqlConnection conn = new MySqlConnection(connectionString);MySqlCommand cmd = new MySqlCommand(strSqlText, conn);try{conn.Open();MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);return myReader;}catch (MySqlException e){throw e;}}/// <summary>///  带参查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )/// </summary>/// <param name="strSqlText">查询语句</param>/// <param name="cmdParas">参数列表</param>/// <returns>MySqlDataReader</returns>public MySqlDataReader ExecuteReader(string strSqlText, params MySqlParameter[] cmdParas){MySqlConnection connection = new MySqlConnection(connectionString);MySqlCommand cmd = new MySqlCommand();try{PrepareCommand(cmd, connection, null, strSqlText, cmdParas);MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//不加using，执行conn关闭后自动关闭Readercmd.Parameters.Clear();return myReader;}catch (MySqlException e){throw e;}}/// <summary>/// 执行查询语句，无参数，返回DataSet/// </summary>/// <param name="SQLString">查询语句</param>/// <returns>DataSet</returns>public DataSet Query(string SQLString){using (MySqlConnection connection = new MySqlConnection(connectionString)){DataSet ds = new DataSet();try{connection.Open();MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);command.Fill(ds, "ds");}catch (MySqlException ex){throw new Exception(ex.Message);}return ds;}}/// <summary>/// 执行语句，返回DataSet/// </summary>/// <param name="SQLString">Sql语句</param>/// <param name="Times">执行时间</param>/// <returns></returns>public DataSet Query(string SQLString, int Times){using (MySqlConnection connection = new MySqlConnection(connectionString)){DataSet ds = new DataSet();try{connection.Open();MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);command.Fill(ds, "ds");}catch (MySqlException ex){throw new Exception(ex.Message);}return ds;}}/// <summary>/// 带参查询语句，返回DataSet/// </summary>/// <param name="SQLString">查询语句</param>/// <param name="cmdParms">参数列表</param>/// <returns>DataSet</returns>public DataSet Query(string SQLString, params MySqlParameter[] cmdParms){using (MySqlConnection connection = new MySqlConnection(connectionString)){MySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, connection, null, SQLString, cmdParms);using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)){DataSet ds = new DataSet();try{da.Fill(ds, "ds");cmd.Parameters.Clear();}catch (MySqlException ex){throw new Exception(ex.Message);}return ds;}}}/// <summary>/// 准备命令语句/// </summary>/// <param name="cmd">Command命令</param>/// <param name="conn">Connection对象</param>/// <param name="trans">是否是事务</param>/// <param name="cmdText">语句</param>/// <param name="cmdParms">参数列表</param>private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms){if (conn.State != ConnectionState.Open)conn.Open();cmd.Connection = conn;cmd.CommandText = cmdText;if (trans != null)cmd.Transaction = trans;cmd.CommandType = CommandType.Text;//cmdType;if (cmdParms != null){foreach (MySqlParameter parameter in cmdParms){if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&(parameter.Value == null)){parameter.Value = DBNull.Value;}cmd.Parameters.Add(parameter);}}}#endregion#region 参数转换/// <summary>/// 准备MysqlCommad参数/// </summary>/// <param name="cmd">MySqlCommand cmd对象</param>/// <param name="conn">MySqlConnection conn对象</param>/// <param name="trans">数据库事务</param>/// <param name="cmdType">text or StoredProcedure</param>/// <param name="cmdText">存储过程名</param>/// <param name="cmdParms">SqlCommand的参数</param>private void PrepareMySqlCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms){//连接状态是否打开if (conn.State != ConnectionState.Open)conn.Open();cmd.Connection = conn;cmd.CommandText = cmdText;//Transact-SQL 事务if (trans != null)cmd.Transaction = trans;cmd.CommandType = cmdType;if (cmdParms != null){foreach (MySqlParameter parm in cmdParms)cmd.Parameters.Add(parm);}}/// <summary>/// 放回一个MakeMySqlParameter/// </summary>/// <param name="name">参数名字</param>/// <param name="type">参数类型</param>/// <param name="size">参数大小</param>/// <param name="value">参数值</param>/// <returns>SQLiteParameter的值</returns>public MySqlParameter MakeMySqlParameter(string name, MySqlDbType type, int size, object value){MySqlParameter parm = new MySqlParameter(name, type, size);parm.Value = value;return parm;}/// <summary>/// 创建MysqlParameter/// </summary>/// <param name="name">字段名称</param>/// <param name="type">数据类型</param>/// <param name="value">字段值</param>/// <returns>返回类型为MySqlParameter</returns>public MySqlParameter MakeMySqlParameter(string name, MySqlDbType type, object value){MySqlParameter parm = new MySqlParameter(name, type);parm.Value = value;return parm;}#endregion#region 存储过程的调用/// <summary>/// 无参存储过程，用于插入修改删除表数据，无返回值/// </summary>/// <param name="ProcText">存储过程脚本</param>public void ExecuteProc(string ProcText){using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand(ProcText, connection)){try{cmd.CommandType = CommandType.StoredProcedure;connection.Open();cmd.ExecuteScalar();}catch (MySqlException e){connection.Close();throw new Exception(e.Message);}}}}/// <summary>/// 带参存储过程，无返回值/// </summary>/// <param name="ProcText">存储过程脚本</param>/// <param name="cmdParms">参数MySqlParameter[] cmdParms</param>public void ExecuteProc(string ProcText, params MySqlParameter[] cmdParms){using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand()){try{cmd.CommandType = CommandType.StoredProcedure;PrepareMySqlCommand(cmd, connection, null, CommandType.StoredProcedure, ProcText, cmdParms);cmd.ExecuteScalar();}catch (MySqlException e){connection.Close();throw new Exception(e.Message);}}}}/// <summary>/// 无参存储过程，返回DataSet/// </summary>/// <param name="ProcText">存储过程脚本</param>/// <returns>返回DataSet</returns>public DataSet ExecuteProcDataset(string ProcText){using (MySqlConnection connection = new MySqlConnection(connectionString)){MySqlCommand cmd = new MySqlCommand();PrepareMySqlCommand(cmd, connection, null, CommandType.StoredProcedure, ProcText, null);using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)){DataSet ds = new DataSet();try{da.Fill(ds, "ds");cmd.Parameters.Clear();}catch (MySqlException ex){throw new Exception(ex.Message);}return ds;}}}/// <summary>/// 带参存储过程，返回DataSet/// </summary>/// <param name="ProcText">存储过程名</param>/// <param name="cmdParms">参数MySqlParameter[] cmdParms</param>/// <returns>返回DataSet</returns>public DataSet ExecuteProcDataset(string ProcText, params MySqlParameter[] cmdParms){using (MySqlConnection connection = new MySqlConnection(connectionString)){MySqlCommand cmd = new MySqlCommand();PrepareMySqlCommand(cmd, connection, null, CommandType.StoredProcedure, ProcText, cmdParms);using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)){DataSet ds = new DataSet();try{da.Fill(ds, "ds");cmd.Parameters.Clear();}catch (MySqlException ex){throw new Exception(ex.Message);}return ds;}}}#endregion#region 预留代码暂时用不上/*/// <summary>/// 执行多条SQL语句，实现数据库事务。/// </summary>/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>public  void ExecuteSqlTran(Hashtable SQLStringList){using (MySqlConnection conn = new MySqlConnection(connectionString)){conn.Open();using (MySqlTransaction trans = conn.BeginTransaction()){MySqlCommand cmd = new MySqlCommand();try{//循环foreach (DictionaryEntry myDE in SQLStringList){string cmdText = myDE.Key.ToString();MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();}trans.Commit();}catch{trans.Rollback();throw;}}}}/// <summary>/// 执行多条语句，实现数据库事务/// </summary>/// <param name="cmdList">SQL语句的泛式表（key为sql语句，value是该语句的MySqlParameter[]）</param>/// <returns>返回影响行数</returns>public  int ExecuteSqlTran(List<CommandInfo> cmdList){using (MySqlConnection conn = new MySqlConnection(connectionString)){conn.Open();using (MySqlTransaction trans = conn.BeginTransaction()){MySqlCommand cmd = new MySqlCommand();try{int count = 0;//循环foreach (CommandInfo myDE in cmdList){string cmdText = myDE.CommandText;MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;PrepareCommand(cmd, conn, trans, cmdText, cmdParms);if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine){if (myDE.CommandText.ToLower().IndexOf("count(") == -1){trans.Rollback();return 0;}object obj = cmd.ExecuteScalar();bool isHave = false;if (obj == null && obj == DBNull.Value){isHave = false;}isHave = Convert.ToInt32(obj) > 0;if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave){trans.Rollback();return 0;}if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave){trans.Rollback();return 0;}continue;}int val = cmd.ExecuteNonQuery();count += val;if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0){trans.Rollback();return 0;}cmd.Parameters.Clear();}trans.Commit();return count;}catch{trans.Rollback();throw;}}}}/// <summary>/// 执行多条SQL语句，实现数据库事务。/// </summary>/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>public  void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList){using (MySqlConnection conn = new MySqlConnection(connectionString)){conn.Open();using (MySqlTransaction trans = conn.BeginTransaction()){MySqlCommand cmd = new MySqlCommand();try{int indentity = 0;//循环foreach (CommandInfo myDE in SQLStringList){string cmdText = myDE.CommandText;MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;foreach (MySqlParameter q in cmdParms){if (q.Direction == ParameterDirection.InputOutput){q.Value = indentity;}}PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val = cmd.ExecuteNonQuery();foreach (MySqlParameter q in cmdParms){if (q.Direction == ParameterDirection.Output){indentity = Convert.ToInt32(q.Value);}}cmd.Parameters.Clear();}trans.Commit();}catch{trans.Rollback();throw;}}}}/// <summary>/// 执行多条SQL语句，实现数据库事务。/// </summary>/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>public  void ExecuteSqlTranWithIndentity(Hashtable SQLStringList){using (MySqlConnection conn = new MySqlConnection(connectionString)){conn.Open();using (MySqlTransaction trans = conn.BeginTransaction()){MySqlCommand cmd = new MySqlCommand();try{int indentity = 0;//循环foreach (DictionaryEntry myDE in SQLStringList){string cmdText = myDE.Key.ToString();MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;foreach (MySqlParameter q in cmdParms){if (q.Direction == ParameterDirection.InputOutput){q.Value = indentity;}}PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val = cmd.ExecuteNonQuery();foreach (MySqlParameter q in cmdParms){if (q.Direction == ParameterDirection.Output){indentity = Convert.ToInt32(q.Value);}}cmd.Parameters.Clear();}trans.Commit();}catch{trans.Rollback();throw;}}}}/// <summary>/// 执行一条计算查询结果语句，返回查询结果（object）。/// </summary>/// <param name="SQLString">计算查询结果语句</param>/// <param name="cmdParms">准备好的参数列表</param>/// <returns>查询结果（object）</returns>public  object GetSingle(string SQLString, params MySqlParameter[] cmdParms){using (MySqlConnection connection = new MySqlConnection(connectionString)){using (MySqlCommand cmd = new MySqlCommand()){try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);object obj = cmd.ExecuteScalar();cmd.Parameters.Clear();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (MySqlException e){throw e;}}}}*/#endregion}
}

创建字符串扩展类 StringExt.cs

namespace DLMESWebAPI.Core
{public static class StringExt{/// <summary>/// 给字符串两边添加单引号（同时会将内部单引号替换成双单引号）/// </summary>/// <param name="S"></param>/// <returns></returns>public static string QuotedStr(this string S){return "'" + S.Replace("'", "''") + "'";}/// <summary>/// 给字符串两边添加括号/// </summary>/// <param name="str">字符串</param>/// <returns>返回带括号的字符串</returns>public static string BracketStr(this string S){return "(" + S + ")";}/// <summary>/// 使用And连接两个SQL条件/// </summary>/// <param name="strCondition1"></param>/// <param name="strCondition2"></param>/// <returns></returns>public static string ConcatSQL(this string S, string strCondition2){if (S.Trim() == "" || strCondition2.Trim() == ""){return S + strCondition2;}return S + " and " + strCondition2;}}
}

3.4 编写API控制器

Controllers文件夹下，创建类QRController.cs

我这里就只给你们提供，自动编号获取的函数。

     /// <summary>/// 自动编号/// </summary>/// <param name="a_objData">传入数据实体</param>/// <returns></returns>[HttpPost]public ActionResult GetAutoCode([FromBody] object a_objData){string l_strErrorCode = "C04";//错误码ActionResult m_Result = new ActionResult();if (a_objData == null && a_objData.ToString().Length == 0){m_Result.Validate = false;m_Result.RetInfo = new CRetInfo() { ErrorCode = l_strErrorCode + "1", IsSUCD = false, ErrorMsg = Const.ct_strIsNullParam };m_Result.Data = new CData() { Obj = null, Table = null, cByte = null, Tostring = "" };return m_Result;}string l_strData = a_objData.ToString();AutoCodeRuleModel l_DataModel = new AutoCodeRuleModel();try{l_DataModel = JsonConvert.DeserializeObject<AutoCodeRuleModel>(l_strData);}catch (Exception ex){m_Result.Validate = false;m_Result.RetInfo = new CRetInfo() { ErrorCode = l_strErrorCode + "2", IsSUCD = false, ErrorMsg = Const.ct_strAnalysisFail + ex.Message };m_Result.Data = new CData() { Obj = null, Table = null, cByte = null, Tostring = "" };return m_Result;}//查询表是否存在MySQLHelper mySQL = new MySQLHelper();try{bool l_bExists = mySQL.Exists(l_DataModel.表);if (!l_bExists){m_Result.Validate = false;m_Result.RetInfo = new CRetInfo() { ErrorCode = l_strErrorCode + "3", IsSUCD = false, ErrorMsg = Const.ct_strTableNotExists};m_Result.Data = new CData() { Obj = null, Table = null, cByte = null, Tostring = "" };return m_Result;}}catch (Exception ex){m_Result.Validate = false;m_Result.RetInfo = new CRetInfo() { ErrorCode = l_strErrorCode + "4", IsSUCD = false, ErrorMsg = Const.ct_strFetchFail + ex.Message };m_Result.Data = new CData() { Obj = null, Table = null, cByte = null, Tostring = "" };return m_Result;}//取编号try{string l_strAutoCode = AutoCodeClient.GetAutoCode(l_DataModel.编号规则, l_DataModel.表, l_DataModel.字段);m_Result.Validate = true;m_Result.RetInfo = new CRetInfo() { ErrorCode = l_strErrorCode + "5", IsSUCD = true, ErrorMsg = Const.ct_strFetchSuccess };m_Result.Data = new CData() { Obj = null, Table = null, cByte = null, Tostring = l_strAutoCode };}catch (Exception ex){m_Result.Validate = false;m_Result.RetInfo = new CRetInfo() { ErrorCode = l_strErrorCode + "6", IsSUCD = false, ErrorMsg = Const.ct_strFetchFail + ex.Message };m_Result.Data = new CData() { Obj = null, Table = null, cByte = null, Tostring = "" };}return m_Result;}

四、运行效果

4.1 调用方式

【业务编号】获取方式，已部署API服务，支持多端（pad、PC）获取业务编号。
URL： http://172.16.**.*:8888/API/QR/GetAutoCode
http请求方式：POST
数据类型：json
调用样例：
{
“编号规则”: “[一位年份]A0N[流水编号];6”,
“表”: “t_codenumbertest”,
“字段”: “编号”
}

4.2 参数说明

“编号规则”：自动编号的规则定义
“表”：业务编号最终，保存的数据库表。
“字段”：表字段。如“钨条标签打印纪录表”字段“钨条编号”是单据号，那么 表=“钨条标签打印纪录表”、字段=“钨条编号”
其它问题：
①编号规则可根据需求添加 “规则类型”，目前支持：
[一位年份]
[两位年份]
[四位年份]
[两位月份]
[紧缩天数] --10日以内的天数，占用1位
[两位天数]
[设备前缀]
[流水编号]
②规则中，“固定值”如何设置？
不加中括号 []，均为固定值，编码自动保留。
③规则中，流水号位数如何设置？
流水号默认4位，如果有特殊需要+分号+数字。如：“[流水编号];6”，是系统出6位流水号。
“编号规则”: “[一位年份]A0N[流水编号];6”,

4.3 运行效果

我采用Postman给你看一下效果，真实项目涉及公司机密，不便于展示。
① “编号规则”: “[一位年份]A0N[流水编号];6”,

② “编号规则”: “SO[四位年份][两位月份][流水编号];4”,

③ “编号规则”: “[两位年份][两位月份][流水编号]PZ;10”,

你想怎么定义规则，编号就怎么生成。

五、资源链接

略，联系我获取。