原创SQlServer数据库⽣成简单的说明⽂档包含（存储过程、视图、数据库批量备
份）⼩⼯具。。。
这是⼀款简单的数据库⽂档⽣成⼯具，主要实现了SQlServer⽣成说明⽂档的⼩⼯具，⽬前不够完善，主要可以把数据库的表以及表的详细字段信息，导出到
Word中，可以⽅便开发⼈员了解数据库的信息或写技术说明⽂档。
技术上主要采⽤的 C#+Dapper+Npod ，开发⼯具为Vs2015，基于Net4.5框架。
操作Word代码
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;
}
}
测试服务器是否连接成功
1///<summary>
2///获取数据库列表
3///</summary>
4///<param name="conStr"></param>
5///<returns></returns>
6public List<string> GetDBNameList(string conStr)
7 {
8//List<DBName> list =new List<DBName>();
9string sql = "select [name] from master.dbo.sysdatabases where DBId>6 Order By [Name] ";
10try
11 {
12using (SqlConnection connection = new SqlConnection(conStr))
13 {
14var list = connection.Query<string>(sql).ToList();
15return list;
16 }
17 }
18catch
19 {
20return null;
mysql帮助文档
21 }
22
23 }
获取数据库列表
///<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 sb.Append(" FieldType = b.name,fieldLenth = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),isAllowEmpty = CASE WHEN a.isnullable = 1 THEN '1' ELSE '0' END, defaultValue = , ''), 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 pe = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id
sb.Append("LEFT ded_properties g ON a.id = G.major_id lid = g.minor_id LEFT ded_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;
}
获取表字段详情
1///<summary>
2///获取特定数据库⾥⾯的存储过程
3///</summary>
4///<param name="conStr"></param>
5///<param name="db"></param>
6///<returns></returns>
7public List<ProcModel> GetProcList(string conStr)
8 {
9var list = new List<ProcModel>();
10string sql = @" select name as procName, (select text from syscomments where id=OBJECT_ID(name)) as proDerails 11 from dbo.sysobjects o where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name ";
12try
13 {
14//wwwblogs/minideas/archive/2009/10/29/1591891.html
15using (SqlConnection connection = new SqlConnection(conStr))
16 {
17 list = connection.Query<ProcModel>(sql).ToList();
18 }
19 }
20catch
21 {
22
23 }
24return list;
25 }
获取特定数据库⾥⾯的存储过程
实现思路：
1、⾸先获取数据库的字符串，测试链接是否成功，
2、通过脚本获取该服务器的数据库列表。
3、根据数据库到该数据库的所有数据表
4、通过脚本到该数据表所有的字段信息
5、使⽤Npoi技术把信息导出到Word中去。
效果如下：
⽂档效果
⽬前功能⽐较简单，后续会慢慢完善，当然⾃⼰技术有限，欢迎⾼⼿多多指点！
Github地址：github/hgmsq/SqlToDocTool
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论