上一篇我们可以自动为每一个表生成列表和编辑界面，如果有多个表，将生成许多个编辑和列表界面，也是不方便的。
我们可以尝试一种只使用一个列表界面和一个编辑界面，兼容所有表（泛型T）的设计界面。
整体思路：为统一的、共同的列表界面（FrmGenerateList）和编辑界面（FrmGenerateEdit）增加一个泛型T，这个泛型T其实就是表名对应的类。使用时通过反射来映射这个泛型窗体即可。
注意：
列表窗体的类名是泛型的FrmGenerateList<T>
编辑窗体的类名也是泛型的FrmGenerateEdit<T>
新建Winform应用程序CommonFormDesignerDemo。（.net framework 4.5）。
添加引用 System.Configuration，NPOI，SqlSugar类库框架。
添加Mysql数据库操作类库MySql.Data.dll
可以直接利用我们的实体类工具生成表对应的实体类。
在解决方案项目添加配置文件：\Conf\DataGridViewConf_Form.xls，并将DataGridViewConf_Form.xls 文件的属性 【复制到输出目录】 设置为 【始终复制】
DataGridViewConf_Form.xls文件的设计如图：
将默认的Form1重命名为FrmNavigate，用于所有相关表的导航。
导航窗体 FrmNavigate的设计如下：
【一个TreeView和一个panel】
统一公共泛型列表窗体 FrmGenerateList 设计【DataGridView用于显示数据】
统一公共泛型编辑窗体 FrmGenerateEdit 设计
一、添加对MySql数据库基本的类SugarDao.cs,源程序如下
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using SqlSugar;
namespace CommonFormDesignerDemo
{
/// <summary>
/// SqlSugar
/// </summary>
public class SugarDao
{
//禁止实例化
private SugarDao()
{
}
/// <summary>
/// 需添加对System.Configuration的引用
/// </summary>
/// <param name="index">1为Mysql连接 2为SqlServer连接</param>
/// <returns></returns>
public static string ConnectionString(int index = 1)
{
string reval = "server=localhost;Database=eve_db;Uid=root;Pwd=12345"; //这里可以动态根据cookies或session实现多库切换
if (index == 1)
reval = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlDataConnect"].ConnectionString;//连接字符串
else if (index == 2)
reval = System.Configuration.ConfigurationManager.ConnectionStrings["SqlServerDataConnect"].ConnectionString;//连接字符串
return reval;
}
public static SqlSugarClient GetInstance(SqlSugar.DbType dbType = SqlSugar.DbType.MySql, int index = 1)
{
return new SqlSugarClient(new ConnectionConfig()
{
DbType = dbType,
ConnectionString = ConnectionString(index),
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
});
}
/// <summary>
/// 检查数据库连接
/// </summary>
public static void CheckConnect()
{
var db = GetInstance();
db.Open();
}
}
}二、添加原生的数据库操作RawSql.cs,源程序如下
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CommonFormDesignerDemo
{
/// <summary>
/// 原生的sql增删改查
/// </summary>
public class RawSql
{
/// <summary>
/// 执行原生的Sql与sql参数的insert、update、delete等操作，返回受影响的行数
/// </summary>
/// <param name="sql"></param>
/// <param name="dict"></param>
/// <param name="dbType">数据库类型，一般是mysql</param>
/// <param name="index">额外的连接数据库，2为新的数据库连接字符串</param>
/// <returns></returns>
public static int ExecuteCommand(string sql, Dictionary<string, object> dict, SqlSugar.DbType dbType = SqlSugar.DbType.MySql, int index = 1)
{
List<SugarParameter> parameters = DictToList(dict);
using (var db = SugarDao.GetInstance(dbType, index))
{
return db.Ado.ExecuteCommand(sql, parameters);
}
}
/// <summary>
/// 执行原生的Sql与sql参数的select查询等操作，返回首行首列的数据
/// </summary>
/// <param name="sql"></param>
/// <param name="dict"></param>
/// <param name="dbType">数据库类型，一般是mysql</param>
/// <param name="index">额外的连接数据库，2为新的数据库连接字符串</param>
/// <returns></returns>
public static object GetScalar(string sql, Dictionary<string, object> dict, SqlSugar.DbType dbType = SqlSugar.DbType.MySql, int index = 1)
{
List<SugarParameter> parameters = DictToList(dict);
using (var db = SugarDao.GetInstance(dbType, index))
{
return db.Ado.GetScalar(sql, parameters);
}
}
/// <summary>
/// 执行原生的Sql与sql参数的select查询等操作，返回一个数据表
/// </summary>
/// <param name="sql"></param>
/// <param name="dict"></param>
/// <param name="dbType">数据库类型，一般是mysql</param>
/// <param name="index">额外的连接数据库，2为新的数据库连接字符串</param>
/// <returns></returns>
public static DataTable GetDataTable(string sql, Dictionary<string, object> dict, SqlSugar.DbType dbType = SqlSugar.DbType.MySql, int index = 1)
{
List<SugarParameter> parameters = DictToList(dict);
using (var db = SugarDao.GetInstance(dbType, index))
{
return db.Ado.GetDataTable(sql, parameters);
}
}
/// <summary>
/// 字典转参数列表
/// </summary>
/// <param name="dict"></param>
/// <returns></returns>
private static List<SugarParameter> DictToList(Dictionary<string, object> dict)
{
List<SugarParameter> parameters = new List<SugarParameter>();
for (int i = 0; dict != null && i < dict.Count; i++)
{
KeyValuePair<string, object> keyValuePair = dict.ElementAt(i);
parameters.Add(new SugarParameter(keyValuePair.Key, keyValuePair.Value));
}
return parameters;
}
/// <summary>
/// 查询表的相关项，生成一个内存表
/// </summary>
/// <param name="selectItems"></param>
/// <param name="where_expression"></param>
/// <param name="dictAddPara"></param>
/// <param name="tableName"></param>
/// <param name="tableNameAs"></param>
/// <param name="orderfild"></param>
/// <returns></returns>
public static DataTable Select_By_Expression(string selectItems, string where_expression, Dictionary<string, object> dictAddPara, string tableName, string tableNameAs, string orderfild = "")
{
DataTable dataTable = new DataTable(tableName);
using (var db = SugarDao.GetInstance())
{
if (!selectItems.Contains(orderfild))
{
orderfild = "";
}
var datax = db.Queryable(tableName, tableNameAs).Where(where_expression).AddParameters(dictAddPara).Select(selectItems).OrderByIF(orderfild != "", orderfild);
try
{
dataTable = datax.ToDataTable();
}
catch (Exception ex)
{
Console.WriteLine($"【查询出现异常】表【{tableName}】-{ex.Message}");
}
return dataTable;
}
}
}
}
三、添加读写Excel操作NpoiExcelOperateUtil.cs,源程序如下
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CommonFormDesignerDemo
{
public static class NpoiExcelOperateUtil
{
#region Excel操作
/// <summary>
/// Excel的第一个工作簿（Sheet）转化成DataTable
/// 使用EXCEL的第一个工作簿，默认为Sheet1
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTable(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
return null;
}
//第一个工作簿
ISheet sheet = workbook.GetSheetAt(0);
if (sheet == null)
{
return null;
}
return ExcelToTable(file, sheet.SheetName);
}
}
/// <summary>
/// Excel的指定Sheet转化成内存表
/// </summary>
/// <param name="file">路径</param>
/// <param name="sheetName">sheet名称</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file, string sheetName)
{
DataTable[] dataTables = ExcelToTable(file, new List<string>() { sheetName });
if (dataTables != null && dataTables.Length > 0)
{
return dataTables[0];
}
return null;
}
/// <summary>
/// 一个excel文件的多个Sheet转化成内存表数组，
/// 每个Sheet都对应一个数据表
/// </summary>
/// <param name="file">路径</param>
/// <param name="list_SheetName">sheet名称集合</param>
/// <returns></returns>
public static DataTable[] ExcelToTable(string file, List<string> list_SheetName)
{
int count = list_SheetName.Count;
DataTable[] dtS = new DataTable[count];
//===============================//
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
return null;
}
ISheet[] sheetS = new ISheet[count];
for (int k = 0; k < count; k++)
{
dtS[k] = new DataTable(list_SheetName[k]);
sheetS[k] = workbook.GetSheet(list_SheetName[k]);
ISheet sheet = sheetS[k];
if (sheet == null)
{
continue;
}
DataTable dt = new DataTable(list_SheetName[k]);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
dtS[k] = dt;
}
}
return dtS;
}
/// <summary>
/// Datable导出成Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcel(DataTable dt, string file)
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx")
{
//workbook = new XSSFWorkbook();
workbook = new HSSFWorkbook();
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
if (workbook == null)
{
return;
}
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
}
}
四、添加对数据列表的操作辅助类 DataGridViewUtil.cs,源程序如下
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace CommonFormDesignerDemo
{
/// <summary>
/// 将Excel的配置映射到一个内存数据表中，然后为DataGridView设置数据绑定，以及自动生成列
/// </summary>
public class DataGridViewUtil
{
/// <summary>
/// 初始化设置DataGridView的列和相关数据绑定
/// </summary>
/// <param name="dt"></param>
/// <param name="dgvw"></param>
public static void DataGridViewIni(DataTable dt, ref DataGridView dgvw)
{
dgvw.Columns.Clear();
string tableName = dt.TableName;
try
{
if (dt != null && dt.Rows.Count > 0)
{
if (!dt.Columns.Contains("En_Name"))
{
MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在En_Name列");
return;
}
if (!dt.Columns.Contains("Another_Name"))
{
MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在Another_Name列");
return;
}
if (!dt.Columns.Contains("Use"))
{
MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在Use列");
return;
}
if (!dt.Columns.Contains("Table_Name"))
{
MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在Table_Name列");
return;
}
int columnCount = dt.Rows.Count;
if (dt.Rows[0]["Table_Name"] == null || tableName != dt.Rows[0]["Table_Name"].ToString().Trim())
{
MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--表名与表中Table_Name不匹配");
return;
}
for (int i = 0; i < columnCount; i++)
{
string isUse = dt.Rows[i]["Use"].ToString().Trim();
if (isUse != "1")
{
continue;//不添加
}
string columnName = dt.Rows[i]["En_Name"].ToString();
string columnDescription = dt.Rows[i]["Another_Name"].ToString();
DataGridViewColumn dgvc = new DataGridViewColumn();
dgvc.CellTemplate = new DataGridViewTextBoxCell();
dgvc.Name = "dgvc_" + columnName;
int minWidth = 50;
bool rtn = int.TryParse(dt.Rows[i]["Min_Width"].ToString(), out minWidth);
if (!rtn)
{
minWidth = 50;
}
dgvc.MinimumWidth = minWidth;
dgvc.DataPropertyName = columnName;
dgvc.HeaderText = (columnDescription == null || columnDescription.Trim().Length == 0) ? columnName : columnDescription;
dgvw.Columns.Add(dgvc);
}
}
}
catch (Exception ex)
{
MessageBox.Show($"{tableName}表的UI配置文件错误，请检查:" + ex.Message);
}
return;
}
}
}
五、自动生成控件表单的辅助类 AutoGenerateUtil.cs,源程序如下
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace CommonFormDesignerDemo
{
/// <summary>
/// 自动生成窗体相关控件代码 以及绑定内容，以及读取控件的值绑定到实体类的属性上等相关操作类
/// 斯内科 2021-02-01
/// </summary>
public class AutoGenerateUtil
{
/// <summary>
/// 获取主键列 和 条码列的名称
/// </summary>
/// <param name="_primaryKeyField"></param>
/// <param name="_barcodeField"></param>
public static void GetPrimaryKeyAndBarcodeField<T>(out string _primaryKeyField, out string _barcodeField)
{
_primaryKeyField = "CoreId";
_barcodeField = "Barcode";
Type type = typeof(T);
//调用实例化方法(非静态方法)需要创建类型的一个实例
object instanceObject = Activator.CreateInstance(type);
//遍历所有可交互控件，获取控件的Tag以及对应的值，并为属性赋值
IEnumerable<PropertyInfo> propertyCollection = type.GetProperties().Where(property => property.CustomAttributes.Count() > 0);
//查找主键列名
PropertyInfo propertyPrimaryKey = propertyCollection.Where(property => property.CustomAttributes.
Where(attrData => attrData.NamedArguments.Where(attrArg => attrArg.MemberName == "IsPrimaryKey").Count() > 0).Count() > 0).FirstOrDefault();
if (propertyPrimaryKey == null)
{
MessageBox.Show("没有配置实体类的主键列特性：IsPrimaryKey");
return;
}
_primaryKeyField = propertyPrimaryKey.Name;
//找到指定的条码列 [SqlSugar.SugarColumn(ColumnDescription = "Barcode")]
PropertyInfo propertyBarcode = propertyCollection.Where(property => property.CustomAttributes.
Where(attrData => attrData.NamedArguments.Where(attrArg => attrArg.MemberName == "ColumnDescription" && Convert.ToString(attrArg.TypedValue.Value) == "Barcode").Count() > 0).Count() > 0).FirstOrDefault();
if (propertyBarcode == null)
{
MessageBox.Show($"没有配置实体类的条码列【查询条件列】特性：ColumnDescription = \"Barcode\"");
return;
}
_barcodeField = propertyBarcode.Name;
}
/// <summary>
/// 使用表设计器初始化DataGridView的列绑定，以及增加 编辑和删除列
/// </summary>
/// <param name="dt"></param>
/// <param name="dgw_Data"></param>
public static void InitialDataGridViewColumns(DataTable dt, DataGridView dgw_Data)
{
dgw_Data.AutoGenerateColumns = false;
DataGridViewUtil.DataGridViewIni(dt, ref dgw_Data);
//增加编辑、删除列
DataGridViewButtonColumn dgvcEdit = new DataGridViewButtonColumn();
dgvcEdit.Name = "dgvcEdit";
dgvcEdit.HeaderText = "编辑";
dgvcEdit.Text = "编辑";
dgvcEdit.ReadOnly = true;
dgvcEdit.UseColumnTextForButtonValue = true;//显示按钮列的单元格的文本
dgw_Data.Columns.Add(dgvcEdit);
DataGridViewButtonColumn dgvcDelete = new DataGridViewButtonColumn();
dgvcDelete.Name = "dgvcDelete";
dgvcDelete.HeaderText = "删除";
dgvcDelete.Text = "删除";
dgvcDelete.ReadOnly = true;
dgvcDelete.UseColumnTextForButtonValue = true;
dgw_Data.Columns.Add(dgvcDelete);
}
/// <summary>
/// 绑定查询的数据到DataGridView上
/// </summary>
/// <param name="keyword">查询的条件</param>
/// <param name="barcodeField">查询的字段</param>
/// <param name="selectItemCollection">选择的列集合，以逗号拼接</param>
/// <param name="currentTableName">查询的表名</param>
/// <param name="dgw_Data">要绑定的DataGridView</param>
public static void BindDataList(string keyword, string barcodeField, string selectItemCollection, string currentTableName, DataGridView dgw_Data)
{
string tableNameAs = "tb";//表的别名
Dictionary<string, object> dictAddPara = new Dictionary<string, object>();//SQL中的参数以及对应的值
List<string> conditionCollection = new List<string>();
//如果关键字不为空
if (keyword.Length > 0)
{
conditionCollection.Add($"{tableNameAs}.{barcodeField}=@PackBarcode");
dictAddPara.Add("PackBarcode", keyword);
}
//默认使用and连接
string where_expression = string.Join(" and ", conditionCollection);
string tableName = currentTableName;//查找的表名
DataTable dtTemp = new DataTable(currentTableName);
dtTemp = RawSql.Select_By_Expression(selectItemCollection, where_expression, dictAddPara, tableName, tableNameAs);
dgw_Data.DataSource = dtTemp;
}
/// <summary>
/// 初始化创建相关设计类的所有控件信息
/// </summary>
/// <param name="dt"></param>
/// <param name="form"></param>
public static void InitialCreateControls(DataTable dt, Form form)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string formVisible = dt.Rows[i]["Form_Visible"].ToString();
if (formVisible != "1")
{
//如果不显示就 忽略
continue;
}
string chineseName = dt.Rows[i]["Ch_Name"].ToString();//标签中文名
string englishName = dt.Rows[i]["En_Name"].ToString();//英文名
string dataType = dt.Rows[i]["Data_Type"].ToString();//数据类型
string defaultValue = dt.Rows[i]["Default_Value"].ToString();//默认值
string formCollection = dt.Rows[i]["Form_Collection"].ToString();//组合下拉框 或者 单选按钮的项集合，使用|（竖线）分割
string formMin = dt.Rows[i]["Form_Min"].ToString();//数字显示框的下限值
string formMax = dt.Rows[i]["Form_Max"].ToString();//数字显示框的上限值
string formLocationX = dt.Rows[i]["Form_Location_X"].ToString();//起始位置坐标X
string formLocationY = dt.Rows[i]["Form_Location_Y"].ToString();//起始位置坐标Y
int locationX;//X坐标
int locationY;//Y坐标
int.TryParse(formLocationX, out locationX);
int.TryParse(formLocationY, out locationY);
FormCategory formCategory;
if (!Enum.TryParse(dt.Rows[i]["Form_Category"].ToString(), true, out formCategory))
{
//默认使用文本框
formCategory = FormCategory.TextBox;
}
Label lbl = AutoGenerateUtil.SetLabel(chineseName, englishName, locationX + 5 - 120, locationY);
switch (formCategory)
{
case FormCategory.None:
break;
case FormCategory.TextBox:
form.Controls.Add(lbl);//增加标签显示
TextBox txb = SetTextBox(englishName, defaultValue, locationX, locationY);
form.Controls.Add(txb);
break;
case FormCategory.ComboBox:
form.Controls.Add(lbl);//增加标签显示
ComboBox cbo = SetComboBox(englishName, defaultValue, formCollection, locationX, locationY);
form.Controls.Add(cbo);
break;
case FormCategory.RadioButton:
form.Controls.Add(lbl);//增加标签显示
//这里使用GroupBox包含所有的单选按钮
GroupBox grp = SetGroupAndRadioButton(englishName, formCollection, locationX, locationY);
form.Controls.Add(grp);
break;
case FormCategory.DateTimePicker:
form.Controls.Add(lbl);//增加标签显示
DateTimePicker dtp = SetDateTimePicker(englishName, locationX, locationY);
form.Controls.Add(dtp);
break;
case FormCategory.NumericUpDown:
form.Controls.Add(lbl);//增加标签显示
NumericUpDown numericUpDown = SetNumericUpDown(englishName, dataType, formMin, formMax, locationX, locationY);
form.Controls.Add(numericUpDown);
break;
}
}
}
/// <summary>
/// 绑定控件的初始值【从数据库中读取】
/// </summary>
/// <param name="dataTable"></param>
public static void BindControlValue(DataTable dataTable, Form form)
{
if (dataTable == null || dataTable.Rows.Count < 1)
{
return;
}
DataRow dataRow = dataTable.Rows[0];
for (int i = 0; i < form.Controls.Count; i++)
{
Control control = form.Controls[i];
if (control is Label || control is Button || control.Tag == null)
{
//不考虑标签 或者 Tag为null的控件
continue;
}
string fieldName = Convert.ToString(control.Tag);
//NumericUpDown控件的Tag特殊处理，使用二元组，第一个项代表绑定的列名(字段名)，第二个项代表数据类型
if (control is NumericUpDown)
{
fieldName = (control.Tag as Tuple<string, string>).Item1;
}
//如果数据表的列集合中不含有该字段，则忽略
if (!dataTable.Columns.Contains(fieldName))
{
continue;
}
//为指定的控件赋值
if (control is TextBox)
{
((TextBox)control).Text = dataRow[fieldName].ToString();
}
else if (control is ComboBox)
{
ComboBox cbo = control as ComboBox;
for (int index = 0; index < cbo.Items.Count; index++)
{
if (cbo.Items[index].ToString() == dataRow[fieldName].ToString())
{
cbo.SelectedIndex = index;
break;
}
}
}
else if (control is GroupBox)
{
//设置RadioButton的选中
GroupBox grp = control as GroupBox;
for (int index = 0; index < grp.Controls.Count; index++)
{
RadioButton rdo = grp.Controls[index] as RadioButton;
if (rdo != null && rdo.Text == dataRow[fieldName].ToString())
{
rdo.Checked = true;
break;
}
else if (rdo != null)
{
rdo.Checked = false;
}
}
}
else if (control is DateTimePicker)
{
((DateTimePicker)control).Value = Convert.ToDateTime(dataRow[fieldName]);
}
else if (control is NumericUpDown)
{
((NumericUpDown)control).Value = Convert.ToDecimal(dataRow[fieldName]);
}
}
}
/// <summary>
/// 遍历所有可交互控件，获取控件的Tag以及对应的值，并为实体类的对应属性赋值
/// </summary>
/// <param name="type"></param>
/// <param name="instanceObject"></param>
public static void SetInstanceObjectPropertyValues(Type type, object instanceObject, Form form)
{
for (int i = 0; i < form.Controls.Count; i++)
{
Control control = form.Controls[i];
if (control is Label || control is Button || control.Tag == null)
{
//不考虑标签 或者 Tag为null的控件
continue;
}
//找出控件的数据对象 也就是属性名（字段名、列名）
string fieldName = Convert.ToString(control.Tag);
//NumericUpDown控件的Tag特殊处理，使用二元组，第一个项代表绑定的列名(字段名)，第二个项代表数据类型
if (control is NumericUpDown)
{
fieldName = (control.Tag as Tuple<string, string>).Item1;
}
PropertyInfo propertyInfoName = type.GetProperty(fieldName);
//如果类不存在该属性，则忽略
if (propertyInfoName == null)
{
continue;
}
//为指定的控件赋值
if (control is TextBox)
{
propertyInfoName.SetValue(instanceObject, ((TextBox)control).Text);
}
else if (control is ComboBox)
{
ComboBox cbo = control as ComboBox;
propertyInfoName.SetValue(instanceObject, ((ComboBox)control).Text);
}
else if (control is GroupBox)
{
//设置RadioButton的选中
GroupBox grp = control as GroupBox;
for (int index = 0; index < grp.Controls.Count; index++)
{
RadioButton rdo = grp.Controls[index] as RadioButton;
if (rdo != null && rdo.Checked)
{
propertyInfoName.SetValue(instanceObject, rdo.Text);
break;
}
}
}
else if (control is DateTimePicker)
{
propertyInfoName.SetValue(instanceObject, ((DateTimePicker)control).Value);
}
else if (control is NumericUpDown)
{
NumericUpDown numericUpDown = control as NumericUpDown;
string dataType = (numericUpDown.Tag as Tuple<string, string>).Item2;
if (dataType != null && dataType.IndexOf("int", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
propertyInfoName.SetValue(instanceObject, Convert.ToInt32(numericUpDown.Value));
}
else if (dataType != null && dataType.IndexOf("decimal", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
propertyInfoName.SetValue(instanceObject, numericUpDown.Value);
}
else
{
propertyInfoName.SetValue(instanceObject, Convert.ToSingle(numericUpDown.Value));
}
}
}
}
/// <summary>
/// 设置Label标签控件
/// </summary>
/// <param name="chineseName"></param>
/// <param name="englishName"></param>
/// <param name="locationX"></param>
/// <param name="locationY"></param>
/// <returns></returns>
private static Label SetLabel(string chineseName, string englishName, int locationX, int locationY)
{
Label lbl = new Label();
//如果字体较多 就设置为两行。如果字体的宽度少于16个空格【八个中文宽度大小】，就补充空格数：(16 - bufferSize.Length)
byte[] bufferSize = Encoding.GetEncoding("gbk").GetBytes(chineseName);
if (bufferSize.Length > 16)
{
lbl.Size = new Size(110, 26);
}
else
{
chineseName = chineseName.PadLeft(chineseName.Length + 16 - bufferSize.Length);
lbl.AutoSize = true;
}
lbl.Name = $"lbl{englishName}";
lbl.Text = chineseName;
lbl.Location = new Point(locationX, locationY);
return lbl;
}
/// <summary>
/// 设置文本框控件
/// </summary>
/// <param name="englishName"></param>
/// <param name="defaultValue"></param>
/// <param name="locationX"></param>
/// <param name="locationY"></param>
/// <returns></returns>
private static TextBox SetTextBox(string englishName, string defaultValue, int locationX, int locationY)
{
TextBox txb = new TextBox();
txb.Name = $"txb{englishName}";
txb.Text = defaultValue;
txb.Location = new Point(locationX, locationY);
txb.Size = new Size(200, 21);
txb.Tag = englishName;//增加控件 绑定的列名(字段名)
return txb;
}
/// <summary>
/// 设置组合框控件
/// </summary>
/// <param name="englishName"></param>
/// <param name="defaultValue"></param>
/// <param name="formCollection"></param>
/// <param name="locationX"></param>
/// <param name="locationY"></param>
/// <returns></returns>
private static ComboBox SetComboBox(string englishName, string defaultValue, string formCollection, int locationX, int locationY)
{
ComboBox cbo = new ComboBox();
cbo.Name = $"cbo{englishName}";
cbo.Text = defaultValue;
cbo.Location = new Point(locationX, locationY);
if (!string.IsNullOrEmpty(formCollection))
{
string[] itemArray = formCollection.Split('|');
for (int index = 0; index < itemArray.Length; index++)
{
cbo.Items.Add(itemArray[index]);
}
}
cbo.Size = new Size(200, 21);
cbo.Tag = englishName;//增加控件 绑定的列名(字段名)
return cbo;
}
/// <summary>
/// 设置分组框和单选按钮集合【多个单选按钮放入在同一个分组框中】
/// </summary>
/// <param name="englishName"></param>
/// <param name="formCollection"></param>
/// <param name="locationX"></param>
/// <param name="locationY"></param>
/// <returns></returns>
private static GroupBox SetGroupAndRadioButton(string englishName, string formCollection, int locationX, int locationY)
{
//这里使用GroupBox包含所有的单选按钮
GroupBox grp = new GroupBox();
grp.Name = $"grp{englishName}";
//grp.Text = chineseName;
grp.Location = new Point(locationX, locationY - 20);
grp.Size = new Size(200, 40);
grp.Tag = englishName;//增加控件 绑定的列名(字段名)
if (string.IsNullOrEmpty(formCollection))
{
formCollection = "选项一";
}
string[] itemArr = formCollection.Split('|');
for (int index = 0; index < itemArr.Length; index++)
{
RadioButton rdo = new RadioButton();
rdo.Name = $"rdo{englishName}{index + 1}";
rdo.Text = itemArr[index];
rdo.Location = new Point(10 + 70 * index, 20);
rdo.AutoSize = true;
if (index == 0)
{
rdo.Checked = true;//设置第一项为选中
}
grp.Controls.Add(rdo);
}
return grp;
}
/// <summary>
/// 设置日期时间选择控件
/// </summary>
/// <param name="englishName"></param>
/// <param name="locationX"></param>
/// <param name="locationY"></param>
/// <returns></returns>
private static DateTimePicker SetDateTimePicker(string englishName, int locationX, int locationY)
{
DateTimePicker dtp = new DateTimePicker();
dtp.Name = $"dtp{englishName}";
dtp.Value = DateTime.Now;
dtp.Location = new Point(locationX, locationY);
dtp.Format = DateTimePickerFormat.Custom; //设置日期格式为2020-08-05
dtp.CustomFormat = "yyyy-MM-dd HH:mm:ss";
dtp.Tag = englishName;//增加控件 绑定的列名(字段名)
return dtp;
}
/// <summary>
/// 设置数字显示框控件
/// NumericUpDown控件的Tag特殊处理，使用二元组，第一个项代表绑定的列名(字段名)，第二个项代表数据类型
/// </summary>
/// <param name="englishName"></param>
/// <param name="dataType"></param>
/// <param name="formMin"></param>
/// <param name="formMax"></param>
/// <param name="locationX"></param>
/// <param name="locationY"></param>
/// <returns></returns>
private static NumericUpDown SetNumericUpDown(string englishName, string dataType, string formMin, string formMax, int locationX, int locationY)
{
NumericUpDown numericUpDown = new NumericUpDown();
numericUpDown.Name = $"numericUpDown{englishName}";
//numericUpDown.Value = DateTime.Now;
numericUpDown.Location = new Point(locationX, locationY);
numericUpDown.Size = new Size(200, 121);
decimal decMin;
decimal decMax;
decimal.TryParse(formMin, out decMin);
decimal.TryParse(formMax, out decMax);
numericUpDown.Minimum = decMin;
if (decMax > decMin)
{
numericUpDown.Maximum = decMax;
}
//如果是int类型
if (dataType != null && dataType.IndexOf("int", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
numericUpDown.DecimalPlaces = 0;
}
else if (dataType != null && dataType.IndexOf("decimal", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
//decimal设置为4位小数
numericUpDown.DecimalPlaces = 4;
}
else
{
//float,double设置为2位小数
numericUpDown.DecimalPlaces = 2;
}
//NumericUpDown控件的Tag特殊处理，使用二元组，第一个项代表绑定的列名(字段名)，第二个项代表数据类型
numericUpDown.Tag = Tuple.Create(englishName, dataType);//增加控件 绑定的列名(字段名)
return numericUpDown;
}
}
/// <summary>
/// 表单（字段）对应的控件类型
/// </summary>
public enum FormCategory
{
/// <summary>
/// 自增或者默认当前时间的字段显示
/// </summary>
None = -1,
/// <summary>
/// 文本框，比如条码
/// </summary>
TextBox = 0,
/// <summary>
/// 组合框，下拉等选项
/// </summary>
ComboBox = 1,
/// <summary>
/// 单选按钮控件
/// </summary>
RadioButton = 2,
/// <summary>
/// 日期时间字段
/// </summary>
DateTimePicker = 3,
/// <summary>
/// 数字显示框
/// </summary>
NumericUpDown = 4
}
}
六、统一列表窗体FrmGenerateList.cs的主要程序如下（忽略设计器自动生成的代码）：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace CommonFormDesignerDemo
{
public partial class FrmGenerateList<T> : Form where T : class, new()
{
/// <summary>
/// 表Excel设计器
/// </summary>
DataTable dt = new DataTable("FormDesign");
/// <summary>
/// 表名 或者 Excel工作簿名
/// </summary>
string CurrentTableName = "test_msg";
/// <summary>
/// 所有英文名称集合【列集合】，以英文逗号(,)拼接
/// </summary>
string selectItems = string.Empty;
/// <summary>
/// 主键字段名
/// </summary>
string primaryKeyField = string.Empty;
/// <summary>
/// 条码字段名
/// </summary>
string barcodeField = string.Empty;
public FrmGenerateList(string sheetName)
{
InitializeComponent();
this.Text = this.Text + $"_表单【{sheetName}】";
CurrentTableName = sheetName;
//获取主键列 和 条码(查询条件对应的字段)列的名称
AutoGenerateUtil.GetPrimaryKeyAndBarcodeField<T>(out primaryKeyField, out barcodeField);
dt = NpoiExcelOperateUtil.ExcelToTable(AppDomain.CurrentDomain.BaseDirectory + "Conf\\DataGridViewConf_Form.xls", sheetName);
//所有英文名称集合【列集合】
string[] DataPropertyNameArr = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
DataPropertyNameArr[i] = dt.Rows[i]["En_Name"].ToString();
}
selectItems = string.Join(",", DataPropertyNameArr);
AutoGenerateUtil.InitialDataGridViewColumns(dt, dgw_Data);
}
private void FrmGenerateList_Load(object sender, EventArgs e)
{
btnQuery_Click(null, e);
}
private void btnQuery_Click(object sender, EventArgs e)
{
AutoGenerateUtil.BindDataList(txbBarcode_query.Text.Trim(), barcodeField, selectItems, CurrentTableName, dgw_Data);
}
private void btnAdd_Click(object sender, EventArgs e)
{
FrmGenerateEdit<T> frmGenerateEdit = new FrmGenerateEdit<T>(CurrentTableName, primaryKeyField);
DialogResult dialog = frmGenerateEdit.ShowDialog();
if (dialog == DialogResult.OK)
{
btnQuery_Click(null, e);//刷新
}
}
private void dgw_Data_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex < 0 || e.ColumnIndex < 0)
{
return;
}
int coreId = Convert.ToInt32(dgw_Data[$"dgvc_{primaryKeyField}", e.RowIndex].Value);
if (dgw_Data.Columns[e.ColumnIndex].Name == "dgvcEdit") //编辑操作
{
FrmGenerateEdit<T> frmGenerateEdit = new FrmGenerateEdit<T>(coreId, CurrentTableName, primaryKeyField);
DialogResult dialog = frmGenerateEdit.ShowDialog();
if (dialog == DialogResult.OK)
{
btnQuery_Click(null, e);//刷新
}
}
else if (dgw_Data.Columns[e.ColumnIndex].Name == "dgvcDelete") //删除操作
{
DialogResult dialog = MessageBox.Show($"您确定要删除该条记录吗？一经删除，不可恢复！\n当前编号【{coreId}】", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (dialog != DialogResult.Yes)
{
return;
}
try
{
//根据主键值删除
using (var db = SugarDao.GetInstance())
{
db.Deleteable<T>(coreId).ExecuteCommand();
}
//CRUD_test_msg.DeleteByCoreId(coreId);
btnQuery_Click(null, e);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "删除时出现异常");
}
}
}
}
}
七、统一编辑窗体FrmGenerateEdit.cs的主要程序如下（忽略设计器自动生成的代码）：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace CommonFormDesignerDemo
{
public partial class FrmGenerateEdit<T> : Form where T : class, new()
{
/// <summary>
/// 添加【insert=true】 还是 修改【update=false】 表单
/// </summary>
bool isAdd = true;
int coreId = -1;
DataTable dt = new DataTable("FormDesign");
/// <summary>
/// 表名 或者 Excel工作簿名
/// </summary>
string CurrentTableName = "test_msg";
/// <summary>
/// 当前主键列名
/// </summary>
string PrimaryKeyField = "CoreId";
public FrmGenerateEdit(string sheetName, string primaryKeyField)
{
InitializeComponent();
isAdd = true;
CurrentTableName = sheetName;
dt = NpoiExcelOperateUtil.ExcelToTable(AppDomain.CurrentDomain.BaseDirectory + "Conf\\DataGridViewConf_Form.xls", sheetName);
}
public FrmGenerateEdit(int _coreId, string sheetName, string primaryKeyField) : this(sheetName, primaryKeyField)
{
coreId = _coreId;
isAdd = false;
}
private void FrmGenerateEdit_Load(object sender, EventArgs e)
{
AutoGenerateUtil.InitialCreateControls(dt, this);
//如果是修改表单，则从数据库中读取数据行，并为文本框赋值
if (!isAdd)
{
DataTable dataTable = new DataTable("xxx");
using (var db = SugarDao.GetInstance())
{
dataTable = db.Queryable<T>().Where($"{PrimaryKeyField}=@CoreId", new Dictionary<string, object>() { { "CoreId", coreId } }).ToDataTable();
}
if (dataTable == null || dataTable.Rows.Count < 1)
{
MessageBox.Show($"没有找到符合条件的记录，该行数据不存在或者已经被删除,当前编号【{coreId}】");
return;
}
AutoGenerateUtil.BindControlValue(dataTable, this);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
Type type = typeof(T);// typeof(HansMySqlSugar.Models.test_aa);
//调用实例化方法(非静态方法)需要创建类型的一个实例
object instanceObject = Activator.CreateInstance(type);
//遍历所有可交互控件，获取控件的Tag以及对应的值，并为属性赋值
AutoGenerateUtil.SetInstanceObjectPropertyValues(type, instanceObject, this);
T test_aaObj = (T)instanceObject;
int affectCount = -1;
if (isAdd)
{
using (var db = SugarDao.GetInstance())
{
affectCount = db.Insertable(test_aaObj).ExecuteCommand();
}
}
else
{
//更新一条数据 按照 主键
PropertyInfo propertyInfoName = type.GetProperty($"{PrimaryKeyField}");
//如果类不存在主键属性，则报警
if (propertyInfoName == null)
{
MessageBox.Show($"没有找到关键主键字段【{PrimaryKeyField}】，无法更新");
}
//必须为主键CoreId赋值，否则CoreId将为默认值0，就完了
propertyInfoName.SetValue(instanceObject, coreId);
using (var db = SugarDao.GetInstance())
{
affectCount = db.Updateable(test_aaObj).Where($"{PrimaryKeyField}=@CoreIdUpdate", new Dictionary<string, object>() { { "CoreIdUpdate", coreId } }).ExecuteCommand();
}
}
MessageBox.Show($"保存【{(isAdd ? "添加" : "更新")}】表【{CurrentTableName}】信息成功,受影响的行数【{affectCount}】");
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show($"保存【{(isAdd ? "添加" : "更新")}】表信息【{CurrentTableName}】失败，异常信息：\n{ex.Message}");
}
}
private void btnCancel_Click(object sender, EventArgs e)
{
this.DialogResult = DialogResult.Cancel;
this.Close();
}
}
}
八、导航窗体FrmNavigate.cs的主要程序如下（忽略设计器自动生成的代码）：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace CommonFormDesignerDemo
{
public partial class FrmNavigate : Form
{
public FrmNavigate()
{
InitializeComponent();
}
private void FrmNavigate_Load(object sender, EventArgs e)
{
//这里也可以通过Excel配置表单的工作簿名集合，或者 从数据库的表名集合加载
tvTables.Nodes[0].Nodes.AddRange(new TreeNode[]
{
new TreeNode("input_output_log"),
new TreeNode("input_output_msg"),
new TreeNode("pallet_bind_pack")
});
tvTables.ExpandAll();
}
private void tvTables_AfterSelect(object sender, TreeViewEventArgs e)
{
panel1.Controls.Clear();
if (e.Node.Parent == null)
{
//如果是根节点，就不考虑
return;
}
/* 直接实例化对应的窗体
Form formEx = new Form();
switch (e.Node.Text)
{
case "input_output_log":
formEx = new FrmGenerateList<CommonFormDesignerDemo.Models.input_output_log>(e.Node.Text);
break;
case "input_output_msg":
formEx = new FrmGenerateList<CommonFormDesignerDemo.Models.input_output_msg>(e.Node.Text);
break;
case "pallet_bind_pack":
formEx = new FrmGenerateList<CommonFormDesignerDemo.Models.pallet_bind_pack>(e.Node.Text);
break;
}
formEx.TopLevel = false;
formEx.Parent = panel1;
panel1.Controls.Add(formEx);
formEx.Show();
*/
System.Reflection.Assembly assembly = System.Reflection.Assembly.Load(Application.ProductName);
Type typeGeneric = assembly.GetType($"{Application.ProductName}.Models.{e.Node.Text}");
Type typeForm = typeof(FrmGenerateList<>);
Type type = typeForm.MakeGenericType(typeGeneric);
//反射泛型窗体类
object instanceObject = Activator.CreateInstance(type, e.Node.Text);
Form form = instanceObject as Form;
form.TopLevel = false;//一定要将TopLevel设置为false,否则不能添加
form.Parent = panel1;
panel1.Controls.Add(form);
form.Show();
}
}
}
九、运行如图：
点击添加 或者 编辑 按钮
3896
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
