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

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

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

//==============================================================================
//
// 作者：农民伯伯
// 邮箱：[email protected]
// 博客：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

#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

#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;
    }