因为这个我的项目没有应用框架，应用了sqlite数据库。前面的业务可想而知会应用多少拼接sql，这就会相当麻烦 一旦数据库扭转 就要改很多货色。
所以根底的就是封装生成sql。须要应用反射和自定义的个性。 自定义个性在于“绑定数据库列名”和“绑定业务格局”
一、业务一---主动生成sql
1.首先是实体对应的表:[个性--》TableNameAttr]
using System;
using System.Collections.Generic;
using System.Text;
namespace PublicLibrary.attr
{
//数据库表名[AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]public class TableNameAttr: Attribute{ string value; public TableNameAttr(string value) { this.Value = value; } public string Value { get => value; set => this.value = value; }}}
2.其次是实体字段对应的列名:[个性--》TableFieldAttr]
using System;
namespace PublicLibrary.attr
{
//查问 新增 批改的注解[AttributeUsage(AttributeTargets.Field, AllowMultiple = true)]public class TableFieldAttr:Attribute{ //bool是boolean 的类型 //是否存在的字段==> 针对查问 private bool isExist =true; //数据库列名 private string columName; //字符串类型 private string jdbcType; //默认值 private string value;/* //新增 批改 选着性操作的字段
private bool selective;*/ //须要自定义的数据库函数 private bool definedFunc; //是否主键 private bool primaryKey =false; //函数的表达式 private string patternStr; //是否更新 默认为true private bool isUpdate =true; public TableFieldAttr(string columName) { this.ColumName = columName; } //字段数据库不存在 public TableFieldAttr(bool isExist) { this.IsExist = false; } //行数据列名 默认值 public TableFieldAttr(string columName, string value) { this.ColumName = columName; this.Value = value; } public TableFieldAttr(string columName, string value, string jdbcType,bool definedFunc, bool primaryKey, string patternStr) { this.ColumName = columName; this.DefinedFunc = definedFunc; this.JdbcType = jdbcType; this.DefinedFunc = definedFunc; this.PrimaryKey = primaryKey; this.PatternStr = patternStr; this.Value = value; } public TableFieldAttr(string columName, string value, string jdbcType,bool definedFunc, bool primaryKey, string patternStr,bool isUpdate) { this.ColumName = columName; this.DefinedFunc = definedFunc; this.JdbcType = jdbcType; this.DefinedFunc = definedFunc; this.PrimaryKey = primaryKey; this.PatternStr = patternStr; this.Value = value; this.IsUpdate = isUpdate; } public bool IsExist { get => isExist; set => isExist = value; } public string ColumName { get => columName; set => columName = value; } public string JdbcType { get => jdbcType; set => jdbcType = value; } public string Value { get => value; set => this.value = value; } public bool DefinedFunc { get => definedFunc; set => definedFunc = value; } public bool PrimaryKey { get => primaryKey; set => primaryKey = value; } public string PatternStr { get => patternStr; set => patternStr = value; } public bool IsUpdate { get => isUpdate; set => isUpdate = value; }}}
3.再就是创立sql的公共办法:
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using PublicLibrary.attr;
using PublicLibrary.serivce;
using PublicLibrary.serivce.impl;
namespace PublicLibrary
{
public static class SQLiteSqlUtils{ /// <summary> /// 创立 查问语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t">对象</param> /// <param name="whereColums">查问出的字段,如[id,name]</param> /// <returns></returns> public static string CreateSelectSql<T>(T t,string[] whereColums) { Type type = typeof(T); BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic; var startTime = DateTime.Now; TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>(); string tableName = tableAttr != null ? tableAttr.Value : ""; string sqlTemp = "SELECT #{colums} FROM " + tableName + " WHERE 1=1 #{whereColums}"; Dictionary<string, TableFieldAttr> fieldAttrDic = new Dictionary<string, TableFieldAttr>(); StringBuilder colums = new StringBuilder(); StringBuilder whereColumsTem = new StringBuilder(""); FieldInfo[] fieldInfos = type.GetFields(bindingFlags); PropertyInfo propertyInfo = null; int countFlag = 0; IEnumerable<TableFieldAttr> enumerable = null; for (int i = 0; i < fieldInfos.Length; i++) { enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>(); foreach (TableFieldAttr attr in enumerable){ //不是主键 和是数据库字段 if (attr.IsExist) { fieldAttrDic.Add(fieldInfos[i].Name, attr); colums.Append((countFlag == 0 ? "" : ",") + attr.ColumName); countFlag++; } } } countFlag = 0; TableFieldAttr fieldAttr = null; if (whereColums != null) { for (int k = 0; k < whereColums.Length; k++) { propertyInfo = type.GetProperty(UpperCaseFirst(whereColums[k]), bindingFlags); if (propertyInfo != null) { fieldAttr = fieldAttrDic[whereColums[k]]; whereColumsTem.Append(" and " + (fieldAttr == null ? "NoField" : fieldAttr.ColumName) + "='" + propertyInfo.GetValue(t) + "'"); /*whereColumsTem.Append((countFlag == 0 ? " " : " and ") + tableName + "." + (fieldAttr == null ? "NoField" : fieldAttr.ColumName) + "='" + propertyInfo.GetValue(t) + "'"); countFlag++;*/ } } } string selectSql = sqlTemp.Replace("#{colums}", colums.ToString()).Replace("#{whereColums}", whereColumsTem.ToString()); return selectSql; } /// <summary> /// 创立 删除语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t">对象</param> /// <param name="whereColums">查问出的字段,如[id,name]</param> /// <returns></returns> public static string CreateDeleteSql<T>(T t, string[] whereColums) { Dictionary<string, TableFieldAttr> fieldAttrDic = new Dictionary<string, TableFieldAttr>(); Type type = typeof(T); BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic; var startTime = DateTime.Now; TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>(); string tableName = tableAttr != null ? tableAttr.Value : ""; string sqlTemp = "DELETE FROM " + tableName + " WHERE 1=1 #{whereColums}"; StringBuilder whereColumsTem = new StringBuilder(""); FieldInfo[] fieldInfos = type.GetFields(bindingFlags); PropertyInfo propertyInfo = null; IEnumerable<TableFieldAttr> enumerable = null; int countFlag = 0; for (int i = 0; i < fieldInfos.Length; i++){ enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>(); foreach (TableFieldAttr attr in enumerable){ //不是主键 和是数据库字段 if (attr.IsExist){ fieldAttrDic.Add(fieldInfos[i].Name, attr); } } } TableFieldAttr fieldAttr = null; if (whereColums != null) { for (int k = 0; k < whereColums.Length; k++) { propertyInfo = type.GetProperty(UpperCaseFirst(whereColums[k]), bindingFlags); if (propertyInfo != null) { fieldAttr = fieldAttrDic[whereColums[k]]; whereColumsTem.Append(" and " + (fieldAttr == null ? "NoField" : fieldAttr.ColumName) + "='" + propertyInfo.GetValue(t) + "'"); } } } string deleteSql = sqlTemp.Replace("#{whereColums}", whereColumsTem.ToString()); return deleteSql; } public static string CreateInsertSql<T>(T t){ List<T> list = new List<T>(); list.Add(t); return CreateInsertSql<T>( list, new ColumServiceImpl()); } public static string CreateInsertSql<T>(T t, ColumService columService){ List<T> list = new List<T>(); list.Add(t); return CreateInsertSql<T>(list, columService); } public static string CreateInsertSql<T>( List<T> list) { return CreateInsertSql<T>(list, new ColumServiceImpl()); } //新增 批量新增的sql public static string CreateInsertSql<T>(List<T> list,ColumService columService) { Type type = typeof(T); BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic; var startTime = DateTime.Now; TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>(); string tableName = tableAttr != null ? tableAttr.Value : ""; string insertHead = "INSERT INTO " + tableName + "(#{colums}) VALUES\n"; string valuesTemp = "(#{values})"; FieldInfo[] fieldInfos = type.GetFields(bindingFlags); Dictionary<string, TableFieldAttr> table = new Dictionary<string, TableFieldAttr>(); Dictionary<string, PropertyInfo> tablePropertys = new Dictionary<string, PropertyInfo>(); //string iteminfo = ""; PropertyInfo propertyinfo = null; TableFieldAttr tableFieldAttr = null; IEnumerable<TableFieldAttr> enumerable = null; string columsTemplet = ""; string valuesTemplet = ""; for (int i = 0; i < fieldInfos.Length; i++) { tablePropertys.Add(fieldInfos[i].Name, type.GetProperty(UpperCaseFirst(fieldInfos[i].Name), bindingFlags)); enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>(); foreach (TableFieldAttr attr in enumerable) { table.Add(fieldInfos[i].Name, attr); attr.JdbcType = fieldInfos[i].FieldType.ToString(); //不是主键 和是数据库字段 if (attr.IsExist) { columsTemplet += " " + attr.ColumName + " ,"; valuesTemplet += " #{" + fieldInfos[i].Name + "} ,"; } } } insertHead = insertHead.Replace("#{colums}", columsTemplet.Substring(0, columsTemplet.Length - 1)); valuesTemp = valuesTemp.Replace("#{values}", valuesTemplet.Substring(0, valuesTemplet.Length - 1)); string valuesInfo = ""; string setValue = ""; for (int k = 0; k < list.Count; k++) { object value = ""; string currentValue = ""; string rowInfo = valuesTemp; //rowInfo = ; foreach (string key in table.Keys) { propertyinfo = tablePropertys[key]; tableFieldAttr = table[key]; //默认值 不进行函数转换 if (tableFieldAttr.Value != null) { value = tableFieldAttr.Value; currentValue = value.ToString(); setValue = currentValue; } else if (propertyinfo != null) { value = tablePropertys[key].GetValue(list[k], null); currentValue = (value == null) ? "''" : "'" + value.ToString() + "'"; setValue = tableFieldAttr.DefinedFunc ? columService.DoFormat(key, currentValue, tableFieldAttr.PatternStr) : currentValue; } rowInfo = rowInfo.Replace("#{" + key + "}", setValue); } valuesInfo += rowInfo + ","; } var endTime = DateTime.Now; TimeSpan ts = endTime.Subtract(startTime); Console.WriteLine("insert语句生成耗时{0}ms.", ts.TotalMilliseconds); return insertHead + valuesInfo.Substring(0, valuesInfo.Length - 1); } /// <summary> /// 单条数据更新 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="whereColums"></param> /// <returns></returns> public static string CreateUpdateSql<T>(T t, string[] whereColums) { List<T> list = new List<T>(); list.Add(t); return CreateUpdateSql<T>(list, whereColums, new ColumServiceImpl()); } /// <summary> /// 更新的语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="whereColums">whereColums 必填 参考 新增和批改【id,name】</param> /// <param name="columService"></param> /// <returns></returns> public static string CreateUpdateSql<T>(T t, string[] whereColums, ColumService columService){ List<T> list = new List<T>(); list.Add(t); return CreateUpdateSql<T>( list, whereColums, columService); } /// <summary> /// 多条数据更新 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <param name="whereColums"></param> /// <returns></returns> public static string CreateUpdateSql<T>(List<T> list, string[] whereColums) { return CreateUpdateSql<T>(list, whereColums, new ColumServiceImpl()); } /// <summary> /// 批改 批量批改的sql 主语法 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <param name="whereColums"></param> /// <param name="columService"></param> /// <returns></returns> public static string CreateUpdateSql<T>(List<T> list, string[] whereColums, ColumService columService) { StringBuilder whereColumsTem = new StringBuilder(""); List<string> whereColumTs = new List<string>(); if (whereColums != null) { whereColumTs = new List<string>(whereColums); } var startTime = DateTime.Now; BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic; Type type = typeof(T); TableNameAttr tableAttr = type.GetCustomAttribute<TableNameAttr>(); string tableName = tableAttr != null ? tableAttr.Value : ""; string updateSql = ""; string updateHead = "UPDATE " + tableName + " #{colums} "; FieldInfo[] fieldInfos = type.GetFields(bindingFlags); Dictionary<string, TableFieldAttr> table = new Dictionary<string, TableFieldAttr>(); Dictionary<string, PropertyInfo> tablePropertys = new Dictionary<string, PropertyInfo>(); PropertyInfo propertyinfo = null; TableFieldAttr tableFieldAttr = null; IEnumerable<TableFieldAttr> enumerable = null; string setTemplet = ""; for (int i = 0; i < fieldInfos.Length; i++) { tablePropertys.Add(fieldInfos[i].Name, type.GetProperty(UpperCaseFirst(fieldInfos[i].Name), bindingFlags)); enumerable = fieldInfos[i].GetCustomAttributes<TableFieldAttr>(); foreach (TableFieldAttr attr in enumerable) { table.Add(fieldInfos[i].Name, attr); attr.JdbcType = fieldInfos[i].FieldType.ToString(); //不是主键 和是数据库字段 if (attr.IsExist && !attr.PrimaryKey && attr.IsUpdate) { setTemplet += " " + attr.ColumName + "= #{" + fieldInfos[i].Name + "} ,"; } //匹配 if (whereColumTs.Contains(fieldInfos[i].Name)) { whereColumsTem.Append(" AND " + attr.ColumName + " = #{"+ fieldInfos[i].Name + "}"); } } } setTemplet = "set " + setTemplet.Substring(0, setTemplet.Length - 1); if (whereColumTs.Count>0){ setTemplet += " WHERE 1=1 " + whereColumsTem.ToString(); } //updateHead += setTemplet; string colum = ""; object value = ""; string currentValue = ""; for (int k = 0; k < list.Count; k++) { colum = setTemplet; foreach (string key in table.Keys){ propertyinfo = tablePropertys[key]; tableFieldAttr = table[key]; if (tableFieldAttr.Value != null) { value = tableFieldAttr.Value; //默认值不做函数解决 currentValue = value.ToString(); } else if (propertyinfo != null){ value = tablePropertys[key].GetValue(list[k], null); currentValue = (value == null) ? "''" : "'" + value.ToString() + "'"; //理论值才会做函数解决 currentValue = tableFieldAttr.DefinedFunc ? columService.DoFormat(key, currentValue, tableFieldAttr.PatternStr) : currentValue; } else{ currentValue = "''"; } colum = colum.Replace("#{" + key + "}", currentValue); } updateSql += updateHead.Replace("#{colums}", colum) + "\n;"; } updateSql = updateSql.Substring(0, updateSql.Length - 1); var endTime = DateTime.Now; TimeSpan ts = endTime.Subtract(startTime); Console.WriteLine("update语句生成耗时{0}ms.", ts.TotalMilliseconds); return updateSql; } /// <summary> /// 首字母大写 /// </summary> /// <param name="str"></param> /// <returns></returns> public static string UpperCaseFirst(this string str) { if (string.IsNullOrWhiteSpace(str)) return string.Empty; char[] s = str.ToCharArray(); char c = s[0]; if ('a' <= c && c <= 'z') c = (char)(c & ~0x20); s[0] = c; return new string(s); }}}
理论应用
针对实体：
办法调用示例:
List<OsZbSupplierProductInfo> record =new List<OsZbSupplierProductInfo>();
SQLiteSqlUtils.CreateInsertSql(record)
业务2.针对导入excel
如法炮制 须要什么？须要列的下标 列内容非空验证 重复性验证 格局验证 长度验证
1.自定义特效
using System;
namespace PublicLibrary.attr
{
//导入的注解【特征值/注解】[AttributeUsage(AttributeTargets.Field, AllowMultiple = true)]public class ColumnInfoAttr:Attribute{ //地位 int index = -1; // 长度 int length = 32; // 是否为空 bool nullable = false; // 工夫格局 string dateFormat = ""; //正则表单式 验证数据格式 string pattern = ""; public ColumnInfoAttr(int index, int length){ this.index = index; this.length = length; } public ColumnInfoAttr(int index, int length, bool nullable) { this.index = index; this.length = length; this.nullable = nullable; } public ColumnInfoAttr(int index, int length, bool nullable,string dateFormat) { this.index = index; this.length = length; this.nullable = nullable; this.dateFormat = dateFormat; } public ColumnInfoAttr(int index, int length, bool nullable, string dateFormat, string pattern) { this.index = index; this.length = length; this.nullable = nullable; this.dateFormat = dateFormat; this.Pattern = pattern; } public int Index { get => index; set => index = value; } public int Length { get => length; set => length = value; } public bool Nullable { get => nullable; set => nullable = value; } public string DateFormat { get => dateFormat; set => dateFormat = value; } public string Pattern { get => pattern; set => pattern = value; }}}
2.公共办法【注此局部没有校验重复性】
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using ess_zbfz_main.dto;
using ExcelDataReader;
using PublicLibrary;
using PublicLibrary.attr;
namespace ess_zbfz_main.util
{
//导入工具类public class ExcelUtil{ //, Dictionary<string, string> dbListDict, string[] keys public static MessageInfo<T> ReadExcel<T>(int firstIndex){ MessageInfo<T> messageInfo = new MessageInfo<T>(); FileStream fileStream =null; IExcelDataReader reader = null; DataSet result = null; try { //小于0 默认是第三行开始读取 firstIndex = firstIndex < 0 ? 2 : firstIndex; OpenFileDialog fileImportDialog = new OpenFileDialog(); fileImportDialog.Filter = "导入文件包(*.xlsx)|*.xlsx";//扩展名 fileImportDialog.FileName = ""; if (fileImportDialog.ShowDialog() == DialogResult.OK) { string saveTempPath = @System.AppDomain.CurrentDomain.BaseDirectory + "data\\receiveData\\";//长期寄存的门路 /* if (!File.Exists(saveTempPath)) { File.Create(saveTempPath); }*/ string saveName = fileImportDialog.FileName.Substring(fileImportDialog.FileName.LastIndexOf("\\") + 1); string dataPath = saveTempPath + saveName;//文件地址 File.Copy(fileImportDialog.FileNames[0], dataPath, true); //解析解决 start //stream = fileStream = File.Open(fileImportDialog.FileName, FileMode.Open, FileAccess.Read); reader = ExcelReaderFactory.CreateReader(fileStream); object[] curObject = new object[10]; result = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true, ReadHeaderRow = (rowReader) => { // 从第几行之后开始读取 int index = firstIndex; if (index != rowReader.Depth) { rowReader.Read(); } } } }); DataTableCollection tableCollection = result.Tables; //初步解决的数据 messageInfo = GetmessageInfo<T>(tableCollection[0], firstIndex); //reader.Close(); return messageInfo; } } catch(Exception ex) { Console.WriteLine(ex.Message); MessageBox.Show("导入文件错误信息：" + ex.Message); } finally{ //须要开释资源 if (reader != null) { reader.Close(); } if (fileStream != null) { fileStream.Close(); } if (result != null) { result.Clear(); } } return messageInfo; } public static MessageInfo<T> GetmessageInfo<T>(DataTable dt, int firstIndex) { MessageInfo<T> messageInfo = new MessageInfo<T>(); bool existError = false; int totalCount = dt.Rows.Count; int successCount = 0; //错误信息 StringBuilder errorSb = new StringBuilder(); BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic; var list = new List<T>(); Type type = typeof(T); ColumnInfoAttr columnInfoAttr = null; FieldInfo[] fieldInfos = type.GetFields(bindingFlags); Dictionary<int, PropertyInfo> dictionAry = new Dictionary<int, PropertyInfo>(); Dictionary<int, ColumnInfoAttr> columnDic = new Dictionary<int, ColumnInfoAttr>(); //行属性 //PropertyInfo propertyInfo = type.GetProperty("CurExcelIndex"); for (int p = 0; p < fieldInfos.Length; p++) { columnInfoAttr = fieldInfos[p].GetCustomAttribute<ColumnInfoAttr>(); if (columnInfoAttr != null) { dictionAry.Add(columnInfoAttr.Index, type.GetProperty(SQLiteSqlUtils.UpperCaseFirst(fieldInfos[p].Name))); columnDic.Add(columnInfoAttr.Index, columnInfoAttr); } } PropertyInfo currentProp = null; //实体 T s; bool flag = false; int currentRow = firstIndex; foreach (DataRow item in dt.Rows){ currentRow++; s = Activator.CreateInstance<T>(); for (int i = 0; i < dt.Columns.Count; i++) { if (dictionAry.ContainsKey(i)){ currentProp = dictionAry[i]; columnInfoAttr = columnDic[i]; object v = null; if (currentProp.PropertyType.ToString().Contains("System.Nullable")) { v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(currentProp.PropertyType)); } else{ v = Convert.ChangeType(item[i], currentProp.PropertyType); } //不能够为空==> 非空验证 if (!columnInfoAttr.Nullable && (v == null || v.ToString().Trim().Length<=0)) { //successCount++; existError = true; errorSb.Append("第" + currentRow + "行，" + dt.Columns[i].ColumnName + " 数据不得为空\n"); flag = true; //break; } //不为空 超过了最大长度==> 长度验证 if (v != null && columnInfoAttr.Length < v.ToString().Length) { //successCount++; existError = true; errorSb.Append("第" + currentRow + "行，" + dt.Columns[i].ColumnName + " 数据长度不得超过" + columnInfoAttr.Length + "个字符\n"); flag = true; //break; } //正则验证局部==> 数据格式验证 if (v != null && columnInfoAttr.Pattern!=null && columnInfoAttr.Pattern != "" && v.ToString().Trim().Length > 0){ //不匹配正则 if(!Regex.IsMatch(v.ToString().Trim(), columnInfoAttr.Pattern)) { existError = true; errorSb.Append("第" + currentRow + "行，" + dt.Columns[i].ColumnName + " 数据格式不正确"); flag = true; } } //是否校验不合格 if (flag) { flag = false; successCount++; break; } currentProp.SetValue(s, v, null); } } currentProp = dictionAry[-10]; currentProp.SetValue(s, currentRow, null);//设置excel的行列 currentProp = null; list.Add(s); } //返回的信息 messageInfo.Record = list; messageInfo.ErrorInfo = errorSb.ToString(); messageInfo.TotalCount = totalCount; messageInfo.ErrorCount = totalCount - successCount; messageInfo.SuccessCount = successCount; messageInfo.ExistError = existError; return messageInfo; }}}
3.具体的应用
实体：
办法调用:
int firstIndex = 2;MessageInfo<OsZbSupplierProductInfo> messageInfo = ExcelUtil.ReadExcel<OsZbSupplierProductInfo>(firstIndex);本文来源于：宋文超super，专属平台有csdn、思否(SegmentFault)、 简书、 开源中国(oschina)，转载请注明出处。