数据库文档生成工具
技术上主要采用的 C#+Dapper+Npod ,开发工具为Vs2013,基于Net4.5框架。
NPoi public void CreateToWord(List<string> list,string conStr,string db) { XWPFDocument doc = new XWPFDocument(); //创建新的word文档 XWPFParagraph p1 = doc.CreateParagraph(); //向新文档中添加段落 p1.Alignment = ParagraphAlignment.CENTER; XWPFRun r1 = p1.CreateRun(); r1.FontFamily = "微软雅黑"; r1.FontSize = 22; r1.IsBold = true; //向该段落中添加文字 r1.SetText(db+"数据库说明文档"); //XWPFParagraph p2 = doc.CreateParagraph(); //XWPFRun r2 = p2.CreateRun(); //r2.SetText("测试段落二"); #region 创建一个表格 if (list.Count > 0) { foreach (var item in list) { XWPFParagraph p3 = doc.CreateParagraph(); //向新文档中添加段落 p3.Alignment = ParagraphAlignment.LEFT; XWPFRun r3 = p3.CreateRun(); //向该段落中添加文字 r3.FontFamily = "微软雅黑"; r3.FontSize = 18; r3.IsBold = true; r3.SetText("表名:"+item); //从第二行开始 因为第一行是表头 int i = 1; var tabledetaillist = service.GetTableDetail(item, conStr); XWPFTable table = doc.CreateTable(tabledetaillist.Count + 1, 9); table.Width = 5000; #region 设置表头 //table.GetRow(0).GetCell(0).SetText("数据库名称"); XWPFParagraph pI = table.GetRow(0).GetCell(0).AddParagraph(); XWPFRun rI = pI.CreateRun(); rI.FontFamily = "微软雅黑"; rI.FontSize = 12; rI.IsBold = true; rI.SetText("序号"); XWPFParagraph pI1 = table.GetRow(0).GetCell(1).AddParagraph(); XWPFRun rI1 = pI1.CreateRun(); rI1.FontFamily = "微软雅黑"; rI1.FontSize = 12; rI1.IsBold = true; rI1.SetText("字段名称"); XWPFParagraph pI2 = table.GetRow(0).GetCell(2).AddParagraph(); XWPFRun rI2 = pI2.CreateRun(); rI2.FontFamily = "微软雅黑"; rI2.FontSize = 12; rI2.IsBold = true; rI2.SetText("标识"); XWPFParagraph pI3 = table.GetRow(0).GetCell(3).AddParagraph(); XWPFRun rI3 = pI3.CreateRun(); rI3.FontFamily = "微软雅黑"; rI3.FontSize = 12; rI3.IsBold = true; rI3.SetText("主键"); XWPFParagraph pI4 = table.GetRow(0).GetCell(4).AddParagraph(); XWPFRun rI4 = pI4.CreateRun(); rI4.FontFamily = "微软雅黑"; rI4.FontSize = 12; rI4.IsBold = true; rI4.SetText("字段类型"); XWPFParagraph pI5 = table.GetRow(0).GetCell(5).AddParagraph(); XWPFRun rI5 = pI5.CreateRun(); rI5.FontFamily = "微软雅黑"; rI5.FontSize = 12; rI5.IsBold = true; rI5.SetText("字段长度"); XWPFParagraph pI6 = table.GetRow(0).GetCell(6).AddParagraph(); XWPFRun rI6 = pI6.CreateRun(); rI6.FontFamily = "微软雅黑"; rI6.FontSize = 12; rI6.IsBold = true; rI6.SetText("允许空"); XWPFParagraph pI7 = table.GetRow(0).GetCell(7).AddParagraph(); XWPFRun rI7 = pI7.CreateRun(); rI7.FontFamily = "微软雅黑"; rI7.FontSize = 12; rI7.IsBold = true; rI7.SetText("字段默认值"); XWPFParagraph pI8 = table.GetRow(0).GetCell(8).AddParagraph(); XWPFRun rI8 = pI8.CreateRun(); rI8.FontFamily = "微软雅黑"; rI8.FontSize = 12; rI8.IsBold = true; rI8.SetText("字段说明"); #endregion if (tabledetaillist != null && tabledetaillist.Count > 0) { foreach (var itm in tabledetaillist) { //第一列 XWPFParagraph pIO = table.GetRow(i).GetCell(0).AddParagraph(); XWPFRun rIO = pIO.CreateRun(); //rIO.FontFamily = "微软雅黑"; rIO.FontSize = 12; rIO.IsBold = true; rIO.SetText(itm.index.ToString()); //第二列 XWPFParagraph pIO2 = table.GetRow(i).GetCell(1).AddParagraph(); XWPFRun rIO2 = pIO2.CreateRun(); //rIO2.FontFamily = "微软雅黑"; rIO2.FontSize = 12; rIO2.IsBold = true; rIO2.SetText(itm.Title); XWPFParagraph pIO3 = table.GetRow(i).GetCell(2).AddParagraph(); XWPFRun rIO3 = pIO3.CreateRun(); //rIO3.FontFamily = "微软雅黑"; rIO3.FontSize = 12; rIO3.IsBold = true; rIO3.SetText(itm.isMark.ToString()); XWPFParagraph pIO4 = table.GetRow(i).GetCell(3).AddParagraph(); XWPFRun rIO4 = pIO4.CreateRun(); //rIO4.FontFamily = "微软雅黑"; rIO4.FontSize = 12; rIO4.IsBold = true; rIO4.SetText(itm.isPK.ToString()); XWPFParagraph pIO5 = table.GetRow(i).GetCell(4).AddParagraph(); XWPFRun rIO5 = pIO5.CreateRun(); //rIO5.FontFamily = "微软雅黑"; rIO5.FontSize = 12; rIO5.IsBold = true; rIO5.SetText(itm.FieldType); XWPFParagraph pIO6 = table.GetRow(i).GetCell(5).AddParagraph(); XWPFRun rIO6 = pIO6.CreateRun(); //rIO6.FontFamily = "微软雅黑"; rIO6.FontSize = 12; rIO6.IsBold = true; rIO6.SetText(itm.fieldLenth.ToString()); XWPFParagraph pIO7 = table.GetRow(i).GetCell(6).AddParagraph(); XWPFRun rIO7 = pIO7.CreateRun(); //rIO7.FontFamily = "微软雅黑"; rIO7.FontSize = 12; rIO7.IsBold = true; rIO7.SetText(itm.isAllowEmpty.ToString()); XWPFParagraph pIO8 = table.GetRow(i).GetCell(7).AddParagraph(); XWPFRun rIO8 = pIO8.CreateRun(); //rIO8.FontFamily = "微软雅黑"; rIO8.FontSize = 12; rIO8.IsBold = true; rIO8.SetText(itm.defaultValue.ToString()); XWPFParagraph pIO9 = table.GetRow(i).GetCell(8).AddParagraph(); XWPFRun rIO9 = pIO9.CreateRun(); //rIO9.FontFamily = "微软雅黑"; rIO9.FontSize = 12; rIO9.IsBold = true; rIO9.SetText(itm.fieldDesc); i++; } } } } #endregion #region 存储过程 XWPFParagraph p2 = doc.CreateParagraph(); XWPFRun r2 = p2.CreateRun(); r2.FontSize = 16; r2.SetText("存储过程"); List<ProcModel> proclist = new List<ProcModel>(); proclist = service.GetProcList(conStr); if(proclist.Count>0) { foreach(var item in proclist) { //存储过程名称 XWPFParagraph pro1 = doc.CreateParagraph(); XWPFRun rpro1 = pro1.CreateRun(); rpro1.FontSize = 14; rpro1.IsBold = true; rpro1.SetText("存储过程名称："+item.procName); //存储过程 详情 XWPFParagraph pro2 = doc.CreateParagraph(); XWPFRun rpro2 = pro2.CreateRun(); rpro2.FontSize = 12; rpro2.SetText(item.proDerails); } } #endregion #region 试图 XWPFParagraph v2 = doc.CreateParagraph(); XWPFRun vr2 = v2.CreateRun(); vr2.FontSize = 16; vr2.SetText("视图"); List<ViewModel> viewlist = new List<ViewModel>(); viewlist = service.GetViewList(conStr); if (proclist.Count > 0) { foreach (var item in viewlist) { //存储过程名称 XWPFParagraph vro1 = doc.CreateParagraph(); XWPFRun vpro1 = vro1.CreateRun(); vpro1.FontSize = 14; vpro1.IsBold = true; vpro1.SetText("视图名称：" + item.viewName); //存储过程 详情 XWPFParagraph vro2 = doc.CreateParagraph(); XWPFRun vpro2 = vro2.CreateRun(); vpro2.FontSize = 12; vpro2.SetText(item.viewDerails); } } #endregion FileStream sw = File.Create("../../Doc/db.docx"); //... doc.Write(sw); //... sw.Close(); //在服务端生成文件 FileInfo file = new FileInfo("../../Doc/db.docx");//文件保存路径及名称 } 操作Word
/// <summary> /// 测试连接数据库是否成功 /// </summary> /// <returns></returns> public bool ConnectionTest(string conStr) { //创建连接对象 mySqlConnection = new SqlConnection(conStr); try { //Open DataBase //打开数据库 mySqlConnection.Open(); IsCanConnectioned = true; } catch { //Can not Open DataBase //打开不成功 则连接不成功 IsCanConnectioned = false; } finally { //Close DataBase //关闭数据库连接 mySqlConnection.Close(); } //mySqlConnection is a SqlConnection object if (mySqlConnection.State == ConnectionState.Closed || mySqlConnection.State == ConnectionState.Broken) { //Connection is not available return IsCanConnectioned; } else { //Connection is available return IsCanConnectioned; } } 测试服务器是否连接成功
/// <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; } } 获取数据库列表
/// <summary> /// 获取字段的信息 /// </summary> /// <param name="tableName"></param> /// <param name="conStr"></param> /// <returns></returns> public List<TableDetail> GetTableDetail(string tableName, string conStr) { 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; } 获取表字段详情
/// <summary> /// 获取特定数据库里面的存储过程 /// </summary> /// <param name="conStr"></param> /// <param name="db"></param> /// <returns></returns> public List<ProcModel> GetProcList(string conStr) { 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 { // http://www.cnblogs.com/minideas/archive/2009/10/29/1591891.html using (SqlConnection connection = new SqlConnection(conStr)) { list = connection.Query<ProcModel>(sql).ToList(); } } catch { } return list; } 获取特定数据库里面的存储过程
实现思路：
1、首先获取数据库的字符串，测试链接是否成功，
2、通过脚本获取该服务器的数据库列表。
3、根据数据库找到该数据库的所有数据表
4、通过脚本找到该数据表所有的字段信息
5、使用Npoi技术把信息导出到Word中去。
彪悍的人生不需要解释，彪悍的代码不需要注释。
