正文
一、目标
下图是我们要实现的目标：
二、实现
原理非常简单，首先制作样式模版，可以参照文章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>();
SqlCommand cmd = new SqlCommand(SQL2000_GetTables, conn);
result.Add(reader.GetString(0));
#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";
case "smallint":
reval = "Int16";
case "bigint":
reval = "Int64";
case "float":
reval = "double";
case "bit":
reval = "bool";
case "decimal":
case "smallmoney":
case "money":
case "numeric":
reval = "decimal";
case "binary":
reval = "System.Byte[]";
case "real":
reval = "System.Single";
case "datetime":
case "smalldatetime":
case "timestamp":
reval = "System.DateTime";
case "tinyint":
reval = "System.Byte";
case "uniqueidentifier":
reval = "System.Guid";
case "image":
case "varbinary":
case "Variant":
reval = "Object";
default:
return reval;
#region SQL Templates
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";
#region Properties
public string ConnectString { get; set; }
}
本文转自over140 51CTO博客，原文链接：http://blog.51cto.com/over140/584153，如需转载请自行联系原作者
![七牛云-C#SDK-上传-前期准备[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)