今天給大家分享我自己編寫的數(shù)據(jù)庫表結(jié)構(gòu)文檔生成工具，方便大家在實際開發(fā)當(dāng)中，可以很方便導(dǎo)出業(yè)務(wù)系統(tǒng)的表結(jié)構(gòu)，也可以作為項目驗收文檔中數(shù)據(jù)庫設(shè)計文檔使用。這樣可以大大減少編寫數(shù)據(jù)庫表結(jié)構(gòu)文檔的時間，有需要的朋友歡迎下載或者溝通交流！

●編程語言：C# （ Net Framework4.5.5）

●數(shù)據(jù)庫技術(shù)框架：Dapper

【資料圖】

●導(dǎo)出Word文檔：NPOI

●訪問方式：WinForm窗體應(yīng)用程序，Windows操作系統(tǒng)可以直接運行

●支持SQLServer、MySQL（5.7、8.0）、SQLite 三種類型的數(shù)據(jù)，持續(xù)更新

●支持Word、Html、MarkDown 三種格式的導(dǎo)出

●導(dǎo)出內(nèi)容包含數(shù)據(jù)表（字段詳情、字段注釋、長度、默認值等）、創(chuàng)建表腳本、視圖及視圖腳本、存儲過程及腳本

●支持生成文檔的同時直接打開文檔

●支持數(shù)據(jù)庫備份（目前只支持SQLServer導(dǎo)出bak備份文件）

/// 
        /// 獲取數(shù)據(jù)庫字符串        /// 
        ///         ///         ///         ///         ///         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);        }        /// 
        /// 獲取數(shù)據(jù)庫列表        /// 
        ///         ///         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;            }        }        /// 
        /// 獲取特定數(shù)據(jù)庫的表名列表        /// 
        ///         ///         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;        }        /// 
        /// 獲取特定數(shù)據(jù)庫里面的存儲過程        /// 
        ///         ///         ///         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;        }        /// 
        /// 獲取特定數(shù)據(jù)庫里面的視圖        /// 
        ///         ///         ///         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;        }

/// 
        /// 生成html文件        /// 
        ///         ///         ///         ///         public void CreateToHtml(List list, string conStr, string db, int type, List checkList)        {            StringBuilder sb = new StringBuilder();            sb.Append("");            sb.Append("");            sb.Append("");            sb.Append("
" + db + "數(shù)據(jù)庫說明文檔
");            GetDBService(type);            #region 創(chuàng)建一個表格            if (checkList.Where(m => m.Equals("表")).Count() > 0)            {                sb.Append("
一、表結(jié)構(gòu)
");                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("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                i++;                            }                        }                        sb.Append("
 
序號
字段名稱
標識
主鍵
字段類型
字段長度
允許空值
字段默認值
字段備注
" + itm.index + "
" + itm.Title + "
" + itm.isMark + "
" + itm.isPK + "
" + itm.FieldType + "
" + itm.fieldLenth + "
" + itm.isAllowEmpty + "
" + itm.defaultValue + "
" + itm.fieldDesc + "
");                        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 + "-數(shù)據(jù)庫說明文檔";//文件名            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);        }

應(yīng)用程序主界面

支持三種生成文檔類型：每次只能選擇一種，推薦使用markdown格式

Word文檔生成效果

Html文檔生成效果

MarkDown文檔效果

針對SQLServer數(shù)據(jù)庫備份

GitHub：??https://github.com/hgmsq/SqlToDocTool??

Gitee：??https://gitee.com/hgm1989/SqlToDocTool??

Gitcode：??https://gitcode.net/xishining/SqlToDocTool???

關(guān)鍵詞：