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

// ==============================================================================
//
//  作 者：农民伯伯
//  邮 箱：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

}