開源項目：數據庫表結構生成文檔工具
作者：郝光明
今天給大家分享我自己編寫的數據庫表結構文檔生成工具，方便大家在實際開發當中，可以很方便導出業務系統的表結構，也可以作為項目驗收文檔中數據庫設計文檔使用。這樣可以大大減少編寫數據庫表結構文檔的時間，有需要的朋友歡迎下載或者溝通交流！
一、軟件介紹
今天給大家分享我自己編寫的數據庫表結構文檔生成工具，方便大家在實際開發當中，可以很方便導出業務系統的表結構，也可以作為項目驗收文檔中數據庫設計文檔使用。這樣可以大大減少編寫數據庫表結構文檔的時間，有需要的朋友歡迎下載或者溝通交流！
二、技術框架
● 編程語言：C# （ Net Framework4.5.5）
● 數據庫技術框架：Dapper
● 導出Word文檔：NPOI
● 訪問方式：WinForm窗體應用程序，Windows操作系統可以直接運行
三、功能介紹
● 支持SQLServer、MySQL（5.7、8.0）、SQLite 三種類型的數據，持續更新
● 支持Word、Html、MarkDown 三種格式的導出
● 導出內容包含數據表（字段詳情、字段注釋、長度、默認值等）、創建表腳本、視圖及視圖腳本、存儲過程及腳本
● 支持生成文檔的同時直接打開文檔
● 支持數據庫備份（目前只支持SQLServer導出bak備份文件）
四、代碼展示
1、獲取數據庫信息部分代碼
/// <summary>
/// 獲取數據庫字符串
/// </summary>
/// <param name="servername"></param>
/// <param name="uid"></param>
/// <param name="pwd"></param>
/// <param name="db"></param>
/// <returns></returns>
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);
}
/// <summary>
/// 獲取數據庫列表
/// </summary>
/// <param name="conStr"></param>
/// <returns></returns>
public List<string> GetDBNameList(string conStr)
{
//List<DBName> list =new List<DBName>();
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<string>(sql).ToList();
return list;
}
}
catch
{
return null;
}
}
public List<DBModel> GetDBList(string conStr)
{
//List<DBName> list =new List<DBName>();
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<DBModel>(sql).ToList();
return list;
}
}
catch
{
return null;
}
}
/// <summary>
/// 獲取特定數據庫的表名列表
/// </summary>
/// <param name="conStr"></param>
/// <returns></returns>
public List<TableModel> GetDBTableList(string conStr, string dbName = "")
{
var list = new List<TableModel>();
//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<TableModel>(sql).ToList();
}
}
catch
{
}
return list;
}
/// <summary>
/// 獲取特定數據庫里面的存儲過程
/// </summary>
/// <param name="conStr"></param>
/// <param name="db"></param>
/// <returns></returns>
public List<ProcModel> GetProcList(string conStr, string dbName = "")
{
var list = new List<ProcModel>();
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<ProcModel>(sql).ToList();
}
}
catch
{
}
return list;
}
/// <summary>
/// 獲取特定數據庫里面的視圖
/// </summary>
/// <param name="conStr"></param>
/// <param name="db"></param>
/// <returns></returns>
public List<ViewModel> GetViewList(string conStr, string dbName = "")
{
var list = new List<ViewModel>();
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<ViewModel>(sql).ToList();
}
}
catch
{
}
return list;
}
/// <summary>
/// 獲取字段的信息
/// </summary>
/// <param name="tableName"></param>
/// <param name="conStr"></param>
/// <returns></returns>
public List<TableDetail> GetTableDetail(string tableName, string conStr, string dbName = "")
{
var list = new List<TableDetail>();
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<TableDetail>(sb.ToString()).ToList();
}
}
catch
{ }
return list;
}
2、導出Html文檔代碼
/// <summary>
/// 生成html文件
/// </summary>
/// <param name="list"></param>
/// <param name="conStr"></param>
/// <param name="db"></param>
/// <param name="type"></param>
public void CreateToHtml(List<TableModel> list, string conStr, string db, int type, List<string> checkList)
{
StringBuilder sb = new StringBuilder();
sb.Append("<html><meta charset=\"utf-8\" /><meta http-equiv = \"Content-Language\" content = \"zh-CN\" >");
sb.Append("<head><title>數據庫說明文檔</title><body>");
sb.Append("<style type=\"text/css\">\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</style></head><body>");
sb.Append("<h1 style=\"text-align:center;\">" + db + "數據庫說明文檔</h1>");
GetDBService(type);
#region 創建一個表格
if (checkList.Where(m => m.Equals("表")).Count() > 0)
{
sb.Append("<h2>一、表結構</h2>");
sb.Append("");
sb.Append("");
if (list.Count > 0)
{
foreach (var item in list)
{
if (item.tableDesc != null && item.tableDesc != "")
{
sb.Append("<h3>表名:" + item.tableName + "(" + item.tableDesc + ")</h3>");
}
else
{
sb.Append("<h3>表名:" + item.tableName + "</h3>");
}
sb.Append(" <table cellspacing=\"0\" cellpadding=\"5\" border=\"1\" width=\"100%\" bordercolorlight=\"#4F7FC9\" bordercolordark=\"#D3D8E0\">");
sb.Append("<thead bgcolor=\"#E3EFFF\"> <th>序號</th><th>字段名稱</th><th>標識</th><th>主鍵</th><th>字段類型</th><th>字段長度</th><th>允許空值</th><th>字段默認值</th><th>字段備注</th></thead>");
sb.Append("<tbody>");
//從第二行開始 因為第一行是表頭
int i = 1;
var tabledetaillist = service.GetTableDetail(item.tableName, conStr, db);
if (tabledetaillist != null && tabledetaillist.Count > 0)
{
foreach (var itm in tabledetaillist)
{
sb.Append("<tr>");
sb.Append("<td>" + itm.index + "</td>");
sb.Append("<td>" + itm.Title + "</td>");
sb.Append("<td>" + itm.isMark + "</td>");
sb.Append("<td>" + itm.isPK + "</td>");
sb.Append("<td>" + itm.FieldType + "</td>");
sb.Append("<td>" + itm.fieldLenth + "</td>");
sb.Append("<td>" + itm.isAllowEmpty + "</td>");
sb.Append("<td>" + itm.defaultValue + "</td>");
sb.Append("<td>" + itm.fieldDesc + "</td>");
sb.Append("</tr>");
i++;
}
}
sb.Append("</tbody></table>");
sb.Append("<h4>" + item.tableName + "建表腳本</h4><br/>");
sb.Append("<span>" + service.GetTableSQL(item.tableName, conStr) + "</span>");
}
}
}
#endregion
#region 存儲過程
if (checkList.Where(m => m.Equals("存儲過程")).Count() > 0)
{
List<ProcModel> proclist = new List<ProcModel>();
proclist = service.GetProcList(conStr, db);
sb.Append("<h2>二、存儲過程</h2>");
if (proclist != null && proclist.Count > 0)
{
foreach (var item in proclist)
{
sb.Append("<h3>存儲過程名稱：" + item.procName + "</h3>");
sb.Append("<span>" + item.proDerails + "</span>");
}
}
}
#endregion
#region 視圖
if (checkList.Where(m => m.Equals("視圖")).Count() > 0)
{
List<ViewModel> viewlist = new List<ViewModel>();
viewlist = service.GetViewList(conStr, db);
sb.Append("<h2>三、視圖</h2>");
if (viewlist.Count > 0)
{
foreach (var item in viewlist)
{
sb.Append("<h3>視圖名稱：" + item.viewName + "</h3>");
sb.Append("<span>" + item.viewDerails + "</span>");
}
}
}
#endregion
sb.Append("</body></html>");
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???
責任編輯：武曉燕
來源：
IT技術分享社區
