第100篇博文纪念 | C# 根据数据库表结构生成DOC数据库文档

　　这是本博的第100篇文章，日期是2009-9-9，开博至今正好2年多点，曾一度一月才写一篇，可还是坚持下来，坚持到现在，偶尔翻翻几天前、几周前、几个月前、1年前的文章，欣慰的发现自己真的在进步，这里都是我的脚印。其实在这之前有在CSDN和Bolgger上写过，要么是不好用，要么就是懒得几个月都不写，结果是只写了不到30篇就没有继续下去，不过现在看来真的是在这里扎根了下来 :) 在园子里看的文章何止百篇，累积应该不少于五千篇，大家都在辛勤的记录和分享着，感激之余也努力着试图回报给园子里。我的博客并没有写最新的技术，也没有写大型的架构设计，有的只是在小公司里面摸爬滚打的一点点经验，觉得帮助较大的放首页，小技巧什么的就放新手区，在关键字上也会花点功夫以便更加容易被寻求帮助的朋友搜到，总之，谢谢大家，我也会一如既往的继续写下去 :)

---------------------------------------------------------------------------------------------------

前言
　　在小公司待过的朋友可能常常会碰到收烂摊子的事情，比如程序写完了，没有文档，尤其是数据库文档，那么你接受的第一件事情可能就是数据库文档了，如果按一个个表一个个字段来复制来写文档的话表一多估计会累死，而且容易出错，还得反复校验，且枯燥无味，这里我写了一个小程序来帮助大家快速准确的来完成这一任务。

参考文章
　　1.　　^全^ 获取SQL SERVER2000/2005、MySql、Oracle元数据的SQL语句 [SQL语句来自CodeSmith]
　　2.　　C# 操作Excel之旁门左道 [ C# | Excel ]

正文
　　一、目标
　　　　下图是我们要实现的目标：

　　二、实现
　　　　原理非常简单，首先制作样式模版，可以参照文章2，将排版好的Word另存为html，然后复制粘贴到aspx页面中，然后从数据库读取表以及字段信息，动态的插入表名和字段信息。
　　　　2.1　　SqlSchemaProvider.cs

//==============================================================================
//
// 作者：农民伯伯
// 邮箱：over140@gmail.com
// 博客：http://over140.cnblogs.com/
// 时间：2009-9-9
// 描述：获取SQL SERVER 元数据
//
//==============================================================================

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

public sealed class SqlSchemaProvider
{

 #region Constructor

 public SqlSchemaProvider(string connectstring)
 {
 ConnectString = connectstring;
 }

 #endregion

 #region GetTableColumns

 public IList<ColumnInfo> GetTableColumns(string tableName)
 {
 IList<ColumnInfo> result = new List<ColumnInfo>();

 SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(ConnectString);

 using (SqlConnection conn = new SqlConnection(scsb.ConnectionString))
 {
 conn.Open();
 SqlCommand cmd = new SqlCommand(SQL2000_GetTableColumns, conn);
 cmd.Parameters.Add(new SqlParameter("@DatabaseName", scsb.InitialCatalog));
 cmd.Parameters.Add(new SqlParameter("@SchemaName", "dbo"));
 cmd.Parameters.Add(new SqlParameter("@TableName", tableName));
 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 while (reader.Read())
 {
 result.Add(new ColumnInfo()
 {
 Name = reader.GetString(0),
 DataType = reader.GetString(1),
 Length = reader.GetInt32(3),
 Nullable = reader.GetString(6).Trim().Equals("YES") ? true : false,
 DefaultValue = reader.IsDBNull(7) ? "" : reader[7].ToString(),
 Identity = reader.GetInt32(8),
 IdentitySeed = Convert.ToInt32(reader.GetString(12)),
 IdentityIncrement = Convert.ToInt32(reader.GetString(13)),
 ColumnDesc = reader.GetString(17)
 });
 }
 reader.Close();
 }

 return result;
 }

 public IList<string> GetTables()
 {
 IList<string> result = new List<string>();

 SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(ConnectString);

 using (SqlConnection conn = new SqlConnection(scsb.ConnectionString))
 {
 conn.Open();
 SqlCommand cmd = new SqlCommand(SQL2000_GetTables, conn);
 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 while (reader.Read())
 {
 result.Add(reader.GetString(0));
 }
 reader.Close();
 }

 return result;
 }

 #region Type Maps

 private string GetCSharpType(string type)
 {
 if (string.IsNullOrEmpty(type))
 return "string";

 string reval = string.Empty;
 switch (type.ToLower())
 {
 case "varchar":
 case "nchar":
 case "ntext":
 case "text":
 case "char":
 case "nvarchar":
 reval = "string";
 break;
 case "int":
 reval = "int";
 break;
 case "smallint":
 reval = "Int16";
 break;
 case "bigint":
 reval = "Int64";
 break;
 case "float":
 reval = "double";
 break;
 case "bit":
 reval = "bool";
 break;
 case "decimal":
 case "smallmoney":
 case "money":
 case "numeric":
 reval = "decimal";
 break;
 case "binary":
 reval = "System.Byte[]";
 break;
 case "real":
 reval = "System.Single";
 break;
 case "datetime":
 case "smalldatetime":
 case "timestamp":
 reval = "System.DateTime";
 break;
 case "tinyint":
 reval = "System.Byte";
 break;
 case "uniqueidentifier":
 reval = "System.Guid";
 break;
 case "image":
 case "varbinary":
 reval = "System.Byte[]";
 break;
 case "Variant":
 reval = "Object";
 break;

 default:
 reval = "string";
 break;
 }
 return reval;
 }

 #endregion

 #endregion

 #region SQL Templates

 #region GetTableColumns

 private const string SQL2000_GetTables = @"
 SELECT
 object_name(so.id) AS OBJECT_NAME,
 user_name(so.uid) AS USER_NAME,
 so.type AS TYPE,
 so.crdate AS DATE_CREATED,
 fg.file_group AS FILE_GROUP,
 so.id AS OBJECT_ID
 FROM
 dbo.sysobjects so
 LEFT JOIN (
 SELECT
 s.groupname AS file_group,
 i.id AS id
 FROM dbo.sysfilegroups s
 INNER JOIN dbo.sysindexes i
 ON i.groupid = s.groupid
 WHERE i.indid < 2
 ) AS fg
 ON so.id = fg.id
 WHERE
 so.type = N'U'
 AND permissions(so.id) & 4096 <> 0
 AND ObjectProperty(so.id, N'IsMSShipped') = 0
 ORDER BY user_name(so.uid), object_name(so.id)";

 private const string SQL2000_GetTableColumns = @"
 SELECT
 clmns.[name] AS [Name],
 usrt.[name] AS [DataType],
 ISNULL(baset.[name], N'') AS [SystemType],
 CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS INT) AS [Length],
 CAST(clmns.xprec AS TINYINT) AS [NumericPrecision],
 CAST(clmns.xscale AS INT) AS [NumericScale],
 CASE CAST(clmns.isnullable AS BIT) WHEN 1 THEN 'YES' ELSE 'NO' END AS [Nullable],
 defaults.text AS [DefaultValue],
 CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS INT) AS [Identity],
 CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS INT) AS IsRowGuid,
 CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS INT) AS IsComputed,
 CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS INT) AS IsDeterministic,
 CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)) AS [IdentitySeed],
 CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(stbl.[name]) + '.' + QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)) AS [IdentityIncrement],
 cdef.[text] AS ComputedDefinition,
 clmns.[collation] AS Collation,
 CAST(clmns.colid AS int) AS ObjectId,
 isnull(prop.value, '') AS ColumnDesc
 FROM
 dbo.sysobjects AS tbl
 INNER JOIN dbo.sysusers AS stbl ON stbl.[uid] = tbl.[uid]
 INNER JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
 LEFT JOIN dbo.systypes AS usrt ON usrt.xusertype = clmns.xusertype
 LEFT JOIN dbo.sysusers AS sclmns ON sclmns.uid = usrt.uid
 LEFT JOIN dbo.systypes AS baset ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
 LEFT JOIN db.syscomments AS defaults ON defaults.id = clmns.cdefault
 LEFT JOIN dbo.syscomments AS cdef ON cdef.id = clmns.id AND cdef.number = clmns.colid
 LEFT OUTER JOIN sysproperties prop ON clmns.id = prop.id AND clmns.colid = prop.smallid
 WHERE
 (tbl.[type] = 'U' OR tbl.[type] = 'S')
 AND stbl.[name] = 'dbo'
 AND tbl.[name] = @TableName
 ORDER BY
 clmns.colorder";

 #endregion

 #endregion

 #region Properties

 public string ConnectString { get; set; }

 #endregion

}

　　　　　　代码说明：
　　　　　　　　这里是获取数据库2000元数据的类，如果其他数据库可参照文章1。
　　　　2.2　　ColumnInfo.cs

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

/// <summary>
///ColumnInfo 的摘要说明
/// </summary>
public class ColumnInfo
{
 public ColumnInfo()
 {
 //
 //TODO: 在此处添加构造函数逻辑
 //
 }

 /// <summary>
 /// 列名
 /// </summary>
 public string Name { get; set; }
 /// <summary>
 /// 数据类型
 /// </summary>
 public string DataType { get; set; }
 /// <summary>
 /// 长度
 /// </summary>
 public int Length { get; set; }
 /// <summary>
 /// 是否允许空
 /// </summary>
 public bool Nullable { get; set; }
 /// <summary>
 /// 1 标识
 /// </summary>
 public int Identity { get; set; }
 /// <summary>
 /// 1 标识种子
 /// </summary>
 public int IdentitySeed { get; set; }
 /// <summary>
 /// 标识增量
 /// </summary>
 public int IdentityIncrement { get; set; }
 /// <summary>
 /// 说明
 /// </summary>
 public string ColumnDesc { get; set; }
 /// <summary>
 /// 默认值
 /// </summary>
 public string DefaultValue { get; set; }

}

　　　　　　代码说明：
　　　　　　　　数据库字段Model。
　　　　2.3　　Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<html>
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=gb2312">
 <meta name="Generator" content="Microsoft Word 11 (filtered)">
 <title>数据库文档</title>
 <style>
 <!--
 /* Font Definitions */
 @font-face
 {
 font-family: 宋体;
 panose-1: 2 1 6 0 3 1 1 1 1 1;
 }
 @font-face
 {
 font-family: 楷体_GB2312;
 panose-1: 2 1 6 9 3 1 1 1 1 1;
 }
 @font-face
 {
 font-family: "\@宋体";
 panose-1: 2 1 6 0 3 1 1 1 1 1;
 }
 @font-face
 {
 font-family: "\@楷体_GB2312";
 panose-1: 2 1 6 9 3 1 1 1 1 1;
 }
 /* Style Definitions */p.MsoNormal, li.MsoNormal, div.MsoNormal
 {
 margin: 0cm;
 margin-bottom: .0001pt;
 text-align: justify;
 text-justify: inter-ideograph;
 font-size: 10.5pt;
 font-family: "Times New Roman";
 }
 p.MsoFooter, li.MsoFooter, div.MsoFooter
 {
 margin: 0cm;
 margin-bottom: .0001pt;
 layout-grid-mode: char;
 font-size: 9.0pt;
 font-family: "Times New Roman";
 }
 /* Page Definitions */@page Section1
 {
 size: 595.3pt 841.9pt;
 margin: 72.0pt 90.0pt 72.0pt 90.0pt;
 layout-grid: 15.6pt;
 }
 div.Section1
 {
 page: Section1;
 }
 -- ></style>
</head>
<body lang="ZH-CN" style='text-justify-trim: punctuation'>
 <div class="Section1" style='layout-grid: 15.6pt'>
 
  
 
  
 
  
 
 数据库文档
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 
  
 <div align="center">
 <table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style='margin-left: 34.6pt;
 border-collapse: collapse; border: none'>
 <tr style='height: 15.6pt'>
 <td width="85" valign="top" style='width: 64.0pt; border: solid windowtext 1.0pt;
 padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
 版本号
 </td>
 <td width="131" valign="top" style='width: 98.6pt; border: solid windowtext 1.0pt;
 border-left: none; padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
 日期
 </td>
 <td width="132" valign="top" style='width: 98.65pt; border: solid windowtext 1.0pt;
 border-left: none; padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
 负责人
 </td>
 <td width="132" valign="top" style='width: 98.65pt; border: solid windowtext 1.0pt;
 border-left: none; padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
 备注
 </td>
 </tr>
 <tr style='height: 15.6pt'>
 <td width="85" valign="top" style='width: 64.0pt; border: solid windowtext 1.0pt;
 border-top: none; padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
 0.1
 </td>
 <td width="131" valign="top" style='width: 98.6pt; border-top: none; border-left: none;
 border-bottom: solid windowtext 1.0pt; border-right: solid windowtext 1.0pt;
 padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
 2009-9-9
 </td>
 <td width="132" valign="top" style='width: 98.65pt; border-top: none; border-left: none;
 border-bottom: solid windowtext 1.0pt; border-right: solid windowtext 1.0pt;
 padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
 农民伯伯
 </td>
 <td width="132" valign="top" style='width: 98.65pt; border-top: none; border-left: none;
 border-bottom: solid windowtext 1.0pt; border-right: solid windowtext 1.0pt;
 padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
  
 </td>
 </tr>
 <tr style='height: 15.6pt'>
 <td width="85" valign="top" style='width: 64.0pt; border: solid windowtext 1.0pt;
 border-top: none; padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
  
 </td>
 <td width="131" valign="top" style='width: 98.6pt; border-top: none; border-left: none;
 border-bottom: solid windowtext 1.0pt; border-right: solid windowtext 1.0pt;
 padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
  
 </td>
 <td width="132" valign="top" style='width: 98.65pt; border-top: none; border-left: none;
 border-bottom: solid windowtext 1.0pt; border-right: solid windowtext 1.0pt;
 padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
  
 </td>
 <td width="132" valign="top" style='width: 98.65pt; border-top: none; border-left: none;
 border-bottom: solid windowtext 1.0pt; border-right: solid windowtext 1.0pt;
 padding: 0cm 5.4pt 0cm 5.4pt; height: 15.6pt'>
 
  
 </td>
 </tr>
 </table>
 </div>

 <asp:Repeater ID="rptData" runat="server">
 <HeaderTemplate>
 </HeaderTemplate>
 <ItemTemplate>
  
  

 
 表  
 名：<%#Container.DataItem%>
 
 表说明：
 <table class="MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style='border-collapse: collapse;
 border: none'>
 <tr>
 <td width="130" valign="top" style='width: 97.7pt; border: solid windowtext 1.0pt;
 background: #E0E0E0; padding: 0cm 5.4pt 0cm 5.4pt'>
 
 字段名
 </td>
 <td width="81" valign="top" style='width: 60.95pt; border: solid windowtext 1.0pt;
 border-left: none; background: #E0E0E0; padding: 0cm 5.4pt 0cm 5.4pt'>
 
 数据类型
 </td>
 <td width="50" valign="top" style='width: 37.3pt; border: solid windowtext 1.0pt;
 border-left: none; background: #E0E0E0; padding: 0cm 5.4pt 0cm 5.4pt'>
 
 长度
 </td>
 <td width="67" valign="top" style='width: 49.95pt; border: solid windowtext 1.0pt;
 border-left: none; background: #E0E0E0; padding: 0cm 5.4pt 0cm 5.4pt'>
 
 允许空
 </td>
 <td width="67" valign="top" style='width: 49.95pt; border: solid windowtext 1.0pt;
 border-left: none; background: #E0E0E0; padding: 0cm 5.4pt 0cm 5.4pt'>
 
 默认值
 </td>
 <td width="88" valign="top" style='width: 65.85pt; border: solid windowtext 1.0pt;
 border-left: none; background: #E0E0E0; padding: 0cm 5.4pt 0cm 5.4pt'>
 
 其他
 </td>
 <td width="86" valign="top" style='width: 64.4pt; border: solid windowtext 1.0pt;
 border-left: none; background: #E0E0E0; padding: 0cm 5.4pt 0cm 5.4pt'>
 
 字段说明
 </td>
 </tr>
 <%# ColumnInfo(Container.DataItem.ToString()) %>
 </table>
 </ItemTemplate>
 <FooterTemplate>
 </FooterTemplate>
 </asp:Repeater>

 
  
 </div>
</body>
</html>

　　　　　　代码说明：
　　　　　　　　如果大家觉得截图上就是你要的文档格式，这里不用修改直接运行就行了，要是有自己的格式的话需要制作模版，参照文章2，对应修改就行。
　　　　2.4　　Default.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections;
using System.Collections.Generic;
using System.Text;

public partial class _Default : System.Web.UI.Page
{

 private const string connstr = "Data Source=SERVER;Initial Catalog=Test;User ID=sa;Password=sa";
 private readonly SqlSchemaProvider provider = new SqlSchemaProvider(connstr);

 protected void Page_Load(object sender, EventArgs e)
 {
 if (!IsPostBack)
 {
 this.rptData.DataSource = provider.GetTables();
 this.rptData.DataBind();
 }
 }

 /// <summary>
 ///
 /// </summary>
 /// <param name="obj"></param>
 /// <returns></returns>
 public string OutputNullable(bool nullable)
 {
 return nullable ? "是" : "否";
 }

 public string ColumnInfo(string tablename)
 {
 StringBuilder result = new StringBuilder();
 IList<ColumnInfo> columns = provider.GetTableColumns(tablename);

 foreach (ColumnInfo column in columns)
 {
 result.AppendFormat(@"<tr>
 <td width=103 valign=top style='width:77.4pt;border:solid windowtext 1.0pt;
 border-top:none;padding:0cm 5.4pt 0cm 5.4pt'>
 {0}
 </td>
 <td width=84 valign=top style='width:63.0pt;border-top:none;border-left:none;
 border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
 padding:0cm 5.4pt 0cm 5.4pt'>
 {1}
 </td>
 <td width=51 valign=top style='width:38.25pt;border-top:none;border-left:
 none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
 padding:0cm 5.4pt 0cm 5.4pt'>
 {2}
 </td>
 <td width=71 valign=top style='width:53.55pt;border-top:none;border-left:
 none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
 padding:0cm 5.4pt 0cm 5.4pt'>
 {3}
 </td>
 <td width=71 valign=top style='width:53.55pt;border-top:none;border-left:
 none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
 padding:0cm 5.4pt 0cm 5.4pt'>
 {4}
 </td>
 <td width=95 valign=top style='width:71.3pt;border-top:none;border-left:none;
 border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
 padding:0cm 5.4pt 0cm 5.4pt'>
 {5}
 </td>
 <td width=92 valign=top style='width:69.05pt;border-top:none;border-left:
 none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt;
 padding:0cm 5.4pt 0cm 5.4pt'>
 {6}
 </td>
</tr>", column.Name, column.DataType, column.Length, OutputNullable(column.Nullable),
 OutputValue(column.DefaultValue),
 OutputIdentity(column.Identity, column.IdentitySeed, column.IdentityIncrement),
 OutputValue(column.ColumnDesc));
 }

 return result.ToString();
 }

 //自动增长(1) 
 // 不用于复制

 /// <summary>
 /// 输出其他
 /// </summary>
 /// <param name="obj"></param>
 /// <returns></returns>
 public string OutputIdentity(int identity, int seed, int increment)
 {
 if (identity == 1 && seed == 1 && increment == 1)
 return "自动增长(1) ";
 else
 return " ";
 }

 private string OutputValue(string obj)
 {
 if (string.IsNullOrEmpty(obj))
 return " ";
 else
 return obj;
 }

}

　　　　代码说明：
　　　　　　这里是嵌套输出的表格，注意处理为空的情况，否则表格可能不完整，大家可以根据实际情况进行修改。

　　三、结果

　　　　大家直接把页面上的表格全选然后复制到Word里面即可，这里注意了，我试过直接另存为word，但是版式不对，但是直接复制到Word里面是可以的。

　　四、下载
　　　　DB2Word2009-9-9.rar

结束语
　　目前我碰到两次这样的情况，不过以后不担心了 :)

posted @ 2009-09-09 23:39 农民伯伯阅读(2881) 评论(12) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

农民伯伯

第100篇博文纪念 | C# 根据数据库表结构生成DOC数据库文档

公告