升鲜宝生鲜配送供应链管理系统Mysql表结构数据字典的生成小工具V0.01

最近最近要交付升鲜宝生鲜配送供应链管理系统源代码给上海的客户，需要将蓝湖UI设计图及数据字典交接给别人。在网上找了半天没有找到合适的根据Mysql生成Word数据字典，自己就写了几行代码，记录一下.后面可能会继续改造。主要的代码如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

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;
        }
    }
}