在使用三层架构的时候,老是要为自定义实体写很多的get set方法,真是不爽, 实在不想动手写,就想办法写了个存储过程,以帮助我自动生层实体代码,供打架参考。
if ( object_id ( ' generatecode ' )) is not null drop proc generatecode
go
CREATE proc generatecode
@table varchar ( 50 )
as
declare @sqlstr varchar ( 2000 )
create table #tmp
(
column_name varchar ( 20 ),
type_name varchar ( 20 ),
length varchar ( 10 )
)
create table # text (n_text varchar ( 1000 ))
select @sqlstr = '
insert into #tmp (column_name,type_name,length) select name,type_name,a.prec
from syscolumns a ,master.dbo.spt_datatype_info b
where id = object_id(N ''' + @table + ''' )
and OBJECTPROPERTY(id, N '' IsUserTable '' ) =1
and a.xtype=b.ss_dtype
and a.length = isnull(b.fixlen, a.length)
AND isnull(b.AUTO_INCREMENT,0) = isnull(ColumnProperty (a.id, a.name, '' IsIdentity '' ),0)
AND (b.ODBCVer is null or b.ODBCVer = 2)
'
execute ( @sqlstr )
declare @name varchar ( 20 )
declare @type varchar ( 20 )
declare cur cursor scroll for select column_name,type_name from #tmp
open cur
fetch next from cur into @name , @type
while @@fetch_status = 0
begin
insert into # text values ( ' private ' + case when @type = ' varchar ' or @type = ' nvarchar ' or @type = ' char ' or @type = ' text ' or @type = ' ntext ' then ' String ' + @name + ' ; '
when @type = ' int ' or @type = ' int identity ' then ' int ' + @name + ' ; '
when @type = ' datetime ' or @type = ' smalldatetime ' then ' DateTime ' + @name + ' ; '
when @type = ' bit ' then ' bool ' + @name + ' ; '
when @type = ' decimal ' or @type = ' numeric ' or @type = ' money ' or @type = ' smallmoney ' then ' decimal ' + @name + ' ; '
when @type = ' float ' then ' double ' + @name + ' ; '
when @type = ' binary ' or @type = ' image ' or @type = ' varbinary ' or @type = ' timestamp ' then ' byte[] ' + @name + ' ; '
when @type = ' real ' then ' single ' + @name + ' ; '
end
)
fetch next from cur into @name , @type
end
fetch absolute 1 from cur into @name , @type
while @@fetch_status = 0
begin
insert into # text values ( ' public ' + case when @type = ' varchar ' or @type = ' nvarchar ' or @type = ' char ' or @type = ' text ' or @type = ' ntext ' then ' String ' + upper ( @name )
when @type = ' int ' or @type = ' int identity ' then ' int ' + upper ( @name )
when @type = ' datetime ' or @type = ' smalldatetime ' then ' DateTime ' + upper ( @name )
when @type = ' bit ' then ' bool ' + upper ( @name )
when @type = ' decimal ' or @type = ' numeric ' or @type = ' money ' or @type = ' smallmoney ' then ' decimal ' + @name
when @type = ' float ' then ' double ' + @name
when @type = ' binary ' or @type = ' image ' or @type = ' varbinary ' or @type = ' timestamp ' then ' byte[] ' + @name
when @type = ' real ' then ' single ' + @name
end
)
insert into # text values ( ' { ' )
insert into # text values ( ' get{return this. ' + @name + ' ;} ' )
insert into # text values ( ' set{this. ' + @name + ' = value;} ' )
insert into # text values ( ' } ' )
fetch next from cur into @name , @type
end
close cur
deallocate cur
select * from # text
drop table #tmp
drop table # text
GO
exec generatecode ' userinfo '
if ( object_id ( ' generatecode ' )) is not null drop proc generatecode
go
CREATE proc generatecode
@table varchar ( 50 )
as
declare @sqlstr varchar ( 2000 )
create table #tmp
(
column_name varchar ( 20 ),
type_name varchar ( 20 ),
length varchar ( 10 )
)
create table # text (n_text varchar ( 1000 ))
select @sqlstr = '
insert into #tmp (column_name,type_name,length) select name,type_name,a.prec
from syscolumns a ,master.dbo.spt_datatype_info b
where id = object_id(N ''' + @table + ''' )
and OBJECTPROPERTY(id, N '' IsUserTable '' ) =1
and a.xtype=b.ss_dtype
and a.length = isnull(b.fixlen, a.length)
AND isnull(b.AUTO_INCREMENT,0) = isnull(ColumnProperty (a.id, a.name, '' IsIdentity '' ),0)
AND (b.ODBCVer is null or b.ODBCVer = 2)
'
execute ( @sqlstr )
declare @name varchar ( 20 )
declare @type varchar ( 20 )
declare cur cursor scroll for select column_name,type_name from #tmp
open cur
fetch next from cur into @name , @type
while @@fetch_status = 0
begin
insert into # text values ( ' private ' + case when @type = ' varchar ' or @type = ' nvarchar ' or @type = ' char ' or @type = ' text ' or @type = ' ntext ' then ' String ' + @name + ' ; '
when @type = ' int ' or @type = ' int identity ' then ' int ' + @name + ' ; '
when @type = ' datetime ' or @type = ' smalldatetime ' then ' DateTime ' + @name + ' ; '
when @type = ' bit ' then ' bool ' + @name + ' ; '
when @type = ' decimal ' or @type = ' numeric ' or @type = ' money ' or @type = ' smallmoney ' then ' decimal ' + @name + ' ; '
when @type = ' float ' then ' double ' + @name + ' ; '
when @type = ' binary ' or @type = ' image ' or @type = ' varbinary ' or @type = ' timestamp ' then ' byte[] ' + @name + ' ; '
when @type = ' real ' then ' single ' + @name + ' ; '
end
)
fetch next from cur into @name , @type
end
fetch absolute 1 from cur into @name , @type
while @@fetch_status = 0
begin
insert into # text values ( ' public ' + case when @type = ' varchar ' or @type = ' nvarchar ' or @type = ' char ' or @type = ' text ' or @type = ' ntext ' then ' String ' + upper ( @name )
when @type = ' int ' or @type = ' int identity ' then ' int ' + upper ( @name )
when @type = ' datetime ' or @type = ' smalldatetime ' then ' DateTime ' + upper ( @name )
when @type = ' bit ' then ' bool ' + upper ( @name )
when @type = ' decimal ' or @type = ' numeric ' or @type = ' money ' or @type = ' smallmoney ' then ' decimal ' + @name
when @type = ' float ' then ' double ' + @name
when @type = ' binary ' or @type = ' image ' or @type = ' varbinary ' or @type = ' timestamp ' then ' byte[] ' + @name
when @type = ' real ' then ' single ' + @name
end
)
insert into # text values ( ' { ' )
insert into # text values ( ' get{return this. ' + @name + ' ;} ' )
insert into # text values ( ' set{this. ' + @name + ' = value;} ' )
insert into # text values ( ' } ' )
fetch next from cur into @name , @type
end
close cur
deallocate cur
select * from # text
drop table #tmp
drop table # text
GO
exec generatecode ' userinfo '