目录
一、软件介绍
二、技术框架
三、功能介绍
四、代码展示
1、获取数据库信息部分代码
2、导出Html文档代码
五、运行效果
六、项目开源地址
一、软件介绍
今天给大家分享我自己编写的数据库表结构文档生成工具，方便大家在实际开发当中，可以很方便导出业务系统的表结构，也可以作为项目验收文档中数据库设计文档使用。这样可以大大减少编写数据库表结构文档的时间，有需要的朋友欢迎下载或者沟通交流！
二、技术框架
- 编程语言：C# （ Net Framework4.5.5）
- 数据库技术框架：Dapper
- 导出Word文档：NPOI
- 访问方式：WinForm窗体应用程序，Windows操作系统可以直接运行
三、功能介绍
- 支持SQLServer、MySQL（5.7、8.0）、SQLite 三种类型的数据，持续更新
- 支持Word、Html、MarkDown 三种格式的导出
- 导出内容包含数据表（字段详情、字段注释、长度、默认值等）、创建表脚本、视图及视图脚本、存储过程及脚本
- 支持生成文档的同时直接打开文档
- 支持数据库备份（目前只支持SQLServer导出bak备份文件）
四、代码展示
1、获取数据库信息部分代码
/// /// 获取数据库字符串 /// /// /// /// /// /// public string GetConnectioning(string servername, string uid, string pwd, string db, string port) { return string.Format("server={0};uid={1};pwd={2};database={3}", servername, uid, pwd, db); } /// /// 获取数据库列表 /// /// /// public List GetDBNameList(string conStr) { //List list =new List(); string sql = "select [name] from master.dbo.sysdatabases where DBId>6 Order By [Name] "; try { using (SqlConnection connection = new SqlConnection(conStr)) { var list = connection.Query(sql).ToList(); return list; } } catch { return null; } } public List GetDBList(string conStr) { //List list =new List(); string sql = "select [name] from master.dbo.sysdatabases where DBId>6 Order By [Name] "; try { using (SqlConnection connection = new SqlConnection(conStr)) { var list = connection.Query(sql).ToList(); return list; } } catch { return null; } } /// /// 获取特定数据库的表名列表 /// /// /// public List GetDBTableList(string conStr, string dbName = "") { var list = new List(); //string sql = "SELECT TABLE_NAME as name FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' "; string sql = "select a.name AS tableName,CONVERT(NVARCHAR(100),isnull(g.[value],'')) AS tableDesc from sys.tables a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)"; try { using (SqlConnection connection = new SqlConnection(conStr)) { list = connection.Query(sql).ToList(); } } catch { } return list; } /// /// 获取特定数据库里面的存储过程 /// /// /// /// public List GetProcList(string conStr, string dbName = "") { var list = new List(); string sql = @" select name as procName, (select text from syscomments where id=OBJECT_ID(name)) as proDerails from dbo.sysobjects o where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name "; try { using (SqlConnection connection = new SqlConnection(conStr)) { list = connection.Query(sql).ToList(); } } catch { } return list; } /// /// 获取特定数据库里面的视图 /// /// /// /// public List GetViewList(string conStr, string dbName = "") { var list = new List(); string sql = @" select name as viewName, (select text from syscomments where id=OBJECT_ID(name)) as viewDerails from dbo.sysobjects o where OBJECTPROPERTY(id, N'IsView') = 1 order by name "; try { using (SqlConnection connection = new SqlConnection(conStr)) { list = connection.Query(sql).ToList(); } } catch { } return list; } /// /// 获取字段的信息 /// /// /// /// public List GetTableDetail(string tableName, string conStr, string dbName = "") { var list = new List(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT [index] = a.colorder, Title = a.name, isMark = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '1' ELSE '0' END, "); sb.Append("isPK = CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN(SELECT name FROM sysindexes WHERE indid IN(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid)) ) THEN '1' ELSE '0' END, "); sb.Append("FieldType = b.name,fieldLenth = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),isAllowEmpty = CASE WHEN a.isnullable = 1 THEN '1' ELSE '0' END, defaultValue = ISNULL(e.text, ''), fieldDesc = ISNULL(g.[value], '') "); sb.Append("FROM syscolumns a LEFT JOIN systypes b ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id "); sb.Append("LEFT JOIN sys.extended_properties g ON a.id = G.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0"); //--如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息 sb.Append("WHERE d.name = '" + tableName + "' ORDER BY a.id, a.colorder, d.name"); try { using (SqlConnection connection = new SqlConnection(conStr)) { list = connection.Query(sb.ToString()).ToList(); } } catch { } return list; }2、导出Html文档代码
/// /// 生成html文件 /// /// /// /// /// public void CreateToHtml(List list, string conStr, string db, int type, List checkList) { StringBuilder sb = new StringBuilder(); sb.Append(""); sb.Append("数据库说明文档 "); sb.Append("\n"); sb.Append("body { font-size: 9pt;}\n"); sb.Append(".styledb { font-size: 14px; }\n"); sb.Append(".styletab {font-size: 14px;padding-top: 15px; }\n"); sb.Append("" + db + "数据库说明文档
"); GetDBService(type); #region 创建一个表格 if (checkList.Where(m => m.Equals("表")).Count() > 0) { sb.Append("一、表结构
"); sb.Append(""); sb.Append(""); if (list.Count > 0) { foreach (var item in list) { if (item.tableDesc != null && item.tableDesc != "") { sb.Append("表名:" + item.tableName + "(" + item.tableDesc + ")
"); } else { sb.Append("表名:" + item.tableName + "
"); } sb.Append(" "); sb.Append(" 序号 字段名称 标识 主键 字段类型 字段长度 允许空值 字段默认值 字段备注 "); sb.Append(""); //从第二行开始 因为第一行是表头 int i = 1; var tabledetaillist = service.GetTableDetail(item.tableName, conStr, db); if (tabledetaillist != null && tabledetaillist.Count > 0) { foreach (var itm in tabledetaillist) { sb.Append(""); sb.Append("" + itm.index + " "); sb.Append("" + itm.Title + " "); sb.Append("" + itm.isMark + " "); sb.Append("" + itm.isPK + " "); sb.Append("" + itm.FieldType + " "); sb.Append("" + itm.fieldLenth + " "); sb.Append("" + itm.isAllowEmpty + " "); sb.Append("" + itm.defaultValue + " "); sb.Append("" + itm.fieldDesc + " "); sb.Append(" "); i++; } } sb.Append("
"); sb.Append("" + item.tableName + "建表脚本
"); sb.Append("" + service.GetTableSQL(item.tableName, conStr) + ""); } } } #endregion #region 存储过程 if (checkList.Where(m => m.Equals("存储过程")).Count() > 0) { List proclist = new List(); proclist = service.GetProcList(conStr, db); sb.Append("二、存储过程
"); if (proclist != null && proclist.Count > 0) { foreach (var item in proclist) { sb.Append("存储过程名称：" + item.procName + "
"); sb.Append("" + item.proDerails + ""); } } } #endregion #region 视图 if (checkList.Where(m => m.Equals("视图")).Count() > 0) { List viewlist = new List(); viewlist = service.GetViewList(conStr, db); sb.Append("三、视图
"); if (viewlist.Count > 0) { foreach (var item in viewlist) { sb.Append("视图名称：" + item.viewName + "
"); sb.Append("" + item.viewDerails + ""); } } } #endregion sb.Append(""); sb.ToString(); string filename = db + "-数据库说明文档";//文件名 SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "html"; saveDialog.Filter = "html文件|*.html"; saveDialog.FileName = filename; saveDialog.ShowDialog(); filename = saveDialog.FileName; if (filename.IndexOf(":") < 0) return; //被点了取消 StreamWriter sw1 = new StreamWriter(saveDialog.FileName, false); sw1.WriteLine(sb); sw1.Close(); System.Diagnostics.Process.Start(filename); }五、运行效果
应用程序主界面
支持三种生成文档类型：每次只能选择一种，推荐使用markdown格式
Word文档生成效果
Html文档生成效果
MarkDown文档效果
针对SQLServer数据库备份
六、项目开源地址
GitHub：https://github.com/hgmsq/SqlToDocTool
Gitee：https://gitee.com/hgm1989/SqlToDocTool
Gitcode：https://gitcode.net/xishining/SqlToDocTool