客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
一、目标 二、实现 原理非常简单，首先制作样式模版，可以参照文章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 #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 } 代码说明：
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; } } 代码说明：
<%@ 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 全部评论
专题导读
热门推荐
热门话题
阅读排行榜
|
请发表评论