根据mysql生成数据库设计文档,第100篇博文纪念 | C# 根据数据库表结构生成DOC数据库文档(1)...
//==============================================================================作 者：农民伯伯//邮 箱：over140@gmail.com//博 客：http://over140.cnblogs.com///时 间：2009-9-9//描 述：获取SQL SERVER 元数据==============================================================================usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Linq;publicsealedclassSqlSchemaProvider
{#regionConstructorpublicSqlSchemaProvider(stringconnectstring)
{
ConnectString=connectstring;
}#endregion#regionGetTableColumnspublicIListGetTableColumns(stringtableName)
{
IListresult=newList();
SqlConnectionStringBuilder scsb=newSqlConnectionStringBuilder(ConnectString);using(SqlConnection conn=newSqlConnection(scsb.ConnectionString))
{
conn.Open();
SqlCommand cmd=newSqlCommand(SQL2000_GetTableColumns, conn);
cmd.Parameters.Add(newSqlParameter("@DatabaseName", scsb.InitialCatalog));
cmd.Parameters.Add(newSqlParameter("@SchemaName","dbo"));
cmd.Parameters.Add(newSqlParameter("@TableName", tableName));
SqlDataReader reader=cmd.ExecuteReader(CommandBehavior.CloseConnection);while(reader.Read())
{
result.Add(newColumnInfo()
{
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();
}returnresult;
}publicIListGetTables()
{
IListresult=newList();
SqlConnectionStringBuilder scsb=newSqlConnectionStringBuilder(ConnectString);using(SqlConnection conn=newSqlConnection(scsb.ConnectionString))
{
conn.Open();
SqlCommand cmd=newSqlCommand(SQL2000_GetTables, conn);
SqlDataReader reader=cmd.ExecuteReader(CommandBehavior.CloseConnection);while(reader.Read())
{
result.Add(reader.GetString(0));
}
reader.Close();
}returnresult;
}#regionType MapsprivatestringGetCSharpType(stringtype)
{if(string.IsNullOrEmpty(type))return"string";stringreval=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"p_w_picpath":case"varbinary":
reval="System.Byte[]";break;case"Variant":
reval="Object";break;default:
reval="string";break;
}returnreval;
}#endregion#endregion#regionSQL Templates#regionGetTableColumnsprivateconststringSQL2000_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
) 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)";privateconststringSQL2000_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#regionPropertiespublicstringConnectString {get;set; }#endregion}
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！