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

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

　二、实现
原理非常简单，首先制作样式模版，可以参照文章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

}

本文转自over140 51CTO博客，原文链接：http://blog.51cto.com/over140/584153，如需转载请自行联系原作者

相关文章：