生鲜配送ERP系统_升鲜宝生鲜配送供应链管理系统Mysql表结构数据字典的生成小工具V0.01
生鲜配送ERP系统_升鲜宝生鲜配送供应链管理系统Mysql表结构数据字典的生成小工具V0.01_SaaS全链路生鲜供应链管理系统_升鲜宝_15382353715
最近要交付升鲜宝生鲜配送供应链管理系统源代码给上海的客户，需要将蓝湖UI设计图及数据字典交接给别人。在网上找了半天没有找到合适的根据Mysql生成Word数据字典，自己就写了几行代码，记录一下.后面可能会继续改造。主要的代码如下：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Net.Mime.MediaTypeNames;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Tab;
using System.Xml.Linq;
using MySql.Data.MySqlClient;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using Body = DocumentFormat.OpenXml.Wordprocessing.Body;
using Text = DocumentFormat.OpenXml.Wordprocessing.Text;
using DocumentFormat.OpenXml;
namespace DotNet.WinForm
{
public partial class FrmCreateDBHtml : Form
{
public FrmCreateDBHtml()
{
InitializeComponent();
}
private void btnSave_Click(object sender, EventArgs e)
{
string connectionString = $"server={txtServer.Text};user={txtUser.Text};password={txtPassword.Text};database={txtDatabase.Text};";
string wordFilePath = "升鲜宝数据字典.docx";
try
{
GenerateDataDictionary(connectionString, wordFilePath);
MessageBox.Show("Data dictionary has been generated successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show($"An error occurred: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void BtnSave_Click(object sender, EventArgs e)
{
string connectionString = $"server={txtServer.Text};user={txtUser.Text};password={txtPassword.Text};database={txtDatabase.Text};";
string wordFilePath = "DataDictionary.docx";
try
{
GenerateDataDictionary(connectionString, wordFilePath);
MessageBox.Show("Data dictionary has been generated successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show($"An error occurred: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void GenerateDataDictionary(string connectionString, string wordFilePath)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
DataTable tables = connection.GetSchema("Tables");
using (WordprocessingDocument wordDocument = WordprocessingDocument.Create(wordFilePath, DocumentFormat.OpenXml.WordprocessingDocumentType.Document))
{
MainDocumentPart mainPart = wordDocument.AddMainDocumentPart();
mainPart.Document = new Document();
Body body = new Body();
foreach (DataRow row in tables.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
string tableComment = "";
// Get table comment
using (MySqlConnection commentConnection = new MySqlConnection(connectionString))
{
commentConnection.Open();
string commentQuery = $"SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{connection.Database}' AND TABLE_NAME = '{tableName}';";
MySqlCommand commentCommand = new MySqlCommand(commentQuery, commentConnection);
tableComment = commentCommand.ExecuteScalar()?.ToString() ?? "";
}
Paragraph tableTitle = new Paragraph(new Run(new Text($"Table: {tableName} - {tableComment}")));
body.Append(tableTitle);
// Add a gap between tables
body.Append(new Paragraph(new Run(new Text(" "))));
string query = $"DESCRIBE {tableName}";
MySqlCommand command = new MySqlCommand(query, connection);
using (MySqlDataReader reader = command.ExecuteReader())
{
Table wordTable = new Table();
// Set table properties for A4 page size and fixed width
TableProperties tblProperties = new TableProperties(
new TableWidth { Type = TableWidthUnitValues.Pct, Width = "5000" },
new TableBorders(
new TopBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new BottomBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new LeftBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new RightBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new InsideHorizontalBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new InsideVerticalBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 }
));
wordTable.AppendChild(tblProperties);
// Add table headers with bold text
TableRow headerRow = new TableRow();
headerRow.Append(CreateTableCell("列名", "1500", true)); // 150 px
headerRow.Append(CreateTableCell("字段类型", "1200", true)); // 120 px
headerRow.Append(CreateTableCell("是否为空", "800", true)); // 80 px
headerRow.Append(CreateTableCell("主键", "500", true)); // 50 px
headerRow.Append(CreateTableCell("说明", "3000", true)); // Remaining width
wordTable.Append(headerRow);
// Add table rows
while (reader.Read())
{
TableRow dataRow = new TableRow();
dataRow.Append(CreateTableCell(reader["Field"].ToString(), "1500"));
dataRow.Append(CreateTableCell(reader["Type"].ToString(), "1200"));
dataRow.Append(CreateTableCell(reader["Null"].ToString(), "800"));
dataRow.Append(CreateTableCell(reader["Key"].ToString(), "500"));
// Get column comment
string comment = "";
using (MySqlConnection commentConnection = new MySqlConnection(connectionString))
{
commentConnection.Open();
string commentQuery = $"SELECT COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{connection.Database}' AND TABLE_NAME = '{tableName}' AND COLUMN_NAME = '{reader["Field"].ToString()}';";
MySqlCommand commentCommand = new MySqlCommand(commentQuery, commentConnection);
comment = commentCommand.ExecuteScalar()?.ToString() ?? "";
}
dataRow.Append(CreateTableCell(comment, "3000"));
wordTable.Append(dataRow);
}
body.Append(wordTable);
}
}
mainPart.Document.Append(body);
}
}
}
static TableCell CreateTableCell(string text, string width, bool isBold = false)
{
TableCell tableCell = new TableCell();
TableCellProperties tableCellProperties = new TableCellProperties(
new TableCellWidth { Type = TableWidthUnitValues.Pct, Width = width }
);
tableCell.Append(tableCellProperties);
Run run = new Run(new Text(text));
if (isBold)
{
run.RunProperties = new RunProperties(new Bold());
}
Paragraph paragraph = new Paragraph(run);
tableCell.Append(paragraph);
return tableCell;
}
}
}
生成的数据字典效果如下：
遇到的问题如下：
1.生成的时候，需要注意列宽，使之适应竖版A4的显示
2.表名后面，需要加上中文的显示。
3.每个表格之间，需要间隙，解决显示拥挤。
简易Mysql数据库生存docx数据字典工具下载：
运行界面
