存储过程生成表的实体类
记录在案，免得哪天忘了。
-------------------
create proc [dbo].[NPT_CreateClass]
@tabName sysname
as
declare @t table(
ColId int ,
Pub nvarchar(50),
SqlType nvarchar(20),
ColumnName nvarchar(250),
Properties nvarchar(500),
sep nvarchar(2) ,
ColLength int ,
Memo nvarchar(1500),
IsAllowNull bit ,
IsIdentity bit ,
Presion int ,
Scale int
)
declare @r table(WR nvarchar(max))
insert into @t (
ColId ,
Pub ,
SqlType ,
ColumnName ,
Properties ,
sep ,
ColLength ,
Memo ,
IsAllowNull ,
IsIdentity ,
Presion ,
Scale
)
Select
c.Column_id,
'public' as Pub,
t.Name as SqlType,
c.name as ColumnName,
Propertis='{ get;set ;}',
'//' as Sep,
ColLength=c.max_length ,
ColDescription= cast(g.value as nvarchar),
IsAllowNull=convert(bit,columnproperty(object_id,c.name,'AllowsNull') ),
IsIdentity=convert(bit,columnproperty(object_id,c.name ,'IsIdentity')),
Presion=convert(bit,columnproperty(object_id,c.name,'Precision')),
Scale=IsNull(columnproperty(object_id,c.name,'Scale'),0)
From sys.columns c
inner join sys.types t
on object_id=object_id(@tabName)
and c.user_type_id=t.user_type_id
left join sys.extended_properties g
on g.class=1 and g.major_id=c.object_id
and g.minor_id =c.column_id
order by c.Column_id asc
declare @cp cursor ,@Cid int ,@RC nvarchar(max)
set @cp=cursor for select ColId from @t
insert into @r(WR)
select N'public class '+@tabName +'{ '
open @cp
fetch next from @cp into @Cid
while @@FETCH_STATUS =0
begin
insert into @r(WR )
select '/// <summary>'
insert into @r(WR )
select '///'+IsNULL(Memo ,ColumnName )
+ N' 数据类型长度(以字节记）:'+ case t.ColLength when -1 then ':MAX' else cast(ColLength as nvarchar(10)) end
+ N';是否允许为空值:'+ case (t.IsAllowNull ) when 1 then '允许' else '不允许' end
+ N';是否是自增量列:' + case IsIdentity when 1 then '是' else '否' end
+ N';精度：'+CAST(Presion as nVARchar)
+ N';小数位数:'+CAST( Scale as nvarchar)
from @t t Where ColId =@Cid
insert into @r(WR )
select '/// </summary>'
-- insert into @r(WR )
--select N'[FriendName (LogName="'+IsNULL(Memo ,ColumnName ) +N'")]'
--from @t t Where ColId =@Cid
insert into @r(WR)
select 'public ' + SqlType +N' '+ ColumnName +N' { get ;set ;} ' from @t where ColId =@Cid
fetch next from @cp into @Cid
end
close @cp;
deallocate @cp
insert into @r(WR)
select N' public ' +@tabName +N'(){'
insert into @r(WR)
Select c.name +N'='+ case ( t.Name )
when 'char' then N'"";'
when 'date' then N'datetime.MinValue;'
when 'datetime' then N'datetime.MinValue;'
when 'datetime2' then N'datetime.MinValue;'
when 'nchar' then N'string.Empty;'
when 'ntext' then N'string.Empty;'
when 'nvarchar' then N'string.Empty;'
when 'smalldatetime' then N'datetime.MinValue;'
when 'sysname' then N'string.Empty;'
when 'text' then N'string.Empty;'
when 'uniqueidentifier' then N'Guid.Empty;'
when 'varchar' then N'string.Empty;'
else N'default('+t.name+N');' end
From sys.columns c
inner join sys.types t
on object_id=object_id(@tabName)
and c.user_type_id=t.user_type_id
left join sys.extended_properties g
on g.class=1 and g.major_id=c.object_id
and g.minor_id =c.column_id
order by c.Column_id asc
insert into @r(WR)
select N' } '
union select N'} '
select * from @r
---------------------------------------
CREATE TABLE [UC_Users](
[EMail] [nvarchar](255) NOT NULL,
[EPassword] [nvarchar](50) NOT NULL,
[EPasswordFormat] [nvarchar](30) NULL,
[EPasswordSalt] [nvarchar](1024) NULL,
[RegisteDate] [datetime] NULL,
[RegYear] AS (datepart(year,getdate())),
[RegMonth] AS (datepart(month,getdate())),
[RegDay] AS (datepart(day,getdate())),
[RegWeekday] AS (datepart(weekday,getdate())),
[RegWeekdayName] AS (case datepart(weekday,getdate()) when (1) then N'星期天' when (2) then N'星期一' when (3) then N'星期二' when (4) then N'星期三' when (5) then N'星期四' when (6) then N'星期五' when (7) then N'星期六' else N'星期天' end),
[RegSiteDomain] [nvarchar](100) NULL,
[RegSiteUrl] [nvarchar](100) NULL,
[LastLoginTime] [datetime] NULL,
[LastLoginSite] [nvarchar](200) NULL,
[UserId] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮箱地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'EMail'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'EPassword'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码保存格式' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'EPasswordFormat'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采用加密方式时的种子值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'EPasswordSalt'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'RegisteDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册年份' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'RegYear'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册月份' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'RegMonth'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'RegDay'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'星期X的数字表示' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'RegWeekday'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'星期X的名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'RegWeekdayName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册站点的域名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'RegSiteDomain'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注册时的原始URL' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'RegSiteUrl'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后一次登录时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'LastLoginTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后一次登录站点' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UC_Users', @level2type=N'COLUMN',@level2name=N'LastLoginSite'
GO
ALTER TABLE [dbo].[UC_Users] ADD DEFAULT ('clear') FOR [EPasswordFormat]
GO
ALTER TABLE [dbo].[UC_Users] ADD DEFAULT (N'') FOR [EPasswordSalt]
GO
-------------------------------------生成结果-------------------
public class UC_Users{
/// <summary>
///邮箱地址 数据类型长度(以字节记）:510;是否允许为空值:不允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public nvarchar EMail { get ;set ;}
/// <summary>
///密码 数据类型长度(以字节记）:100;是否允许为空值:不允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public nvarchar EPassword { get ;set ;}
/// <summary>
///密码保存格式 数据类型长度(以字节记）:60;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public nvarchar EPasswordFormat { get ;set ;}
/// <summary>
///采用加密方式时的种子值 数据类型长度(以字节记）:2048;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public nvarchar EPasswordSalt { get ;set ;}
/// <summary>
///注册时间 数据类型长度(以字节记）:8;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:3
/// </summary>
public datetime RegisteDate { get ;set ;}
/// <summary>
///注册年份 数据类型长度(以字节记）:4;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public int RegYear { get ;set ;}
/// <summary>
///注册月份 数据类型长度(以字节记）:4;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public int RegMonth { get ;set ;}
/// <summary>
///注册日 数据类型长度(以字节记）:4;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public int RegDay { get ;set ;}
/// <summary>
///星期X的数字表示 数据类型长度(以字节记）:4;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public int RegWeekday { get ;set ;}
/// <summary>
///星期X的名称 数据类型长度(以字节记）:6;是否允许为空值:不允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public nvarchar RegWeekdayName { get ;set ;}
/// <summary>
///注册站点的域名 数据类型长度(以字节记）:200;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public nvarchar RegSiteDomain { get ;set ;}
/// <summary>
///注册时的原始URL 数据类型长度(以字节记）:200;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public nvarchar RegSiteUrl { get ;set ;}
/// <summary>
///最后一次登录时间 数据类型长度(以字节记）:8;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:3
/// </summary>
public datetime LastLoginTime { get ;set ;}
/// <summary>
///最后一次登录站点 数据类型长度(以字节记）:400;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public nvarchar LastLoginSite { get ;set ;}
/// <summary>
///UserId 数据类型长度(以字节记）:16;是否允许为空值:允许;是否是自增量列:否;精度：1;小数位数:0
/// </summary>
public uniqueidentifier UserId { get ;set ;}
public UC_Users(){
EMail=string.Empty;
EPassword=string.Empty;
EPasswordFormat=string.Empty;
EPasswordSalt=string.Empty;
RegisteDate=datetime.MinValue;
RegYear=default(int);
RegMonth=default(int);
RegDay=default(int);
RegWeekday=default(int);
RegWeekdayName=string.Empty;
RegSiteDomain=string.Empty;
RegSiteUrl=string.Empty;
LastLoginTime=datetime.MinValue;
LastLoginSite=string.Empty;
UserId=Guid.Empty;
}
}
-----------------------------OK----------------
PS: 一直没用那个CodeSmith ，主要是没找到Key ，要不找的不能用。
using bit=System.Boolean ;
using nvarchar=System.String;
