用存储过程实现自动生成实体

USE [HrMarket2007]
GO
/****** Object:  StoredProcedure [dbo].[CreateEntity]    Script Date: 12/17/2014 10:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[CreateEntity]
( 
@tablename nvarchar(100)
)
as

if not object_id('tempdb..#temp_table') is null drop table #temp_table

declare @cSharp varchar(max);			--拼最后的C#语句
declare @fieldstotalcount int;			--查看表中有多少个字段
declare @namespace varchar(20);			--命名空间
declare @entityname varchar(100);		--实体名称/也是表的简称
declare @primarykey varchar(20);		--寻找主键

set @namespace='dbo';
if(charindex('.',@tablename))>0
begin
	set @namespace=SUBSTRING(@tablename,1, len(@tablename)-charindex('.',reverse(@tablename)));
	set @entityname=SUBSTRING(@tablename,len(@namespace)+2,len(@tablename));
end;

/*寻找主键*/
set @primarykey=(
select c.name as 字段名 from sysindexes i
inner join sysindexkeys k on i.id = k.id and i.indid = k.indid  
inner join sysobjects o on i.id = o.id  
inner join syscolumns c on i.id=c.id and k.colid = c.colid  
where o.xtype = 'U' and o.name=@entityname
and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name));


with A as(select name,usertype from syscolumns Where ID=OBJECT_ID(@tablename))
select ROW_NUMBER() over (order by (select 0)) as num,name,usertype into #temp_table from A;

set @fieldstotalcount=(select count(1) from #temp_table);

set @cSharp =
'select ''using System;'' 
union all select ''using System.Collections.Generic;''
union all select ''using System.Linq;''
union all select ''using System.Runtime.Serialization;''
union all select ''using System.Security.Permissions;''
union all select ''using System.Text;''
union all select ''using System.Threading.Tasks;''
union all select ''using Drision.Framework.DAL;''
union all select ''using Drision.Framework.Interfaces;''
union all select '' ''
union all select ''namespace HrMarket.Entity.'+@namespace+'''
union all select ''{''
union all select ''    [DrxTable]''
union all select ''    public class '+@entityname+' : BaseEntity''
union all select ''    {''
union all select ''        public '+@entityname+'() {}''
'


begin try
	
	if(@fieldstotalcount=0) return;
	
	set @cSharp = @cSharp + 'union all select '' ''
	union all select ''        #region DB字段''
	union all select '' ''
	'
	
	declare @i int;
	set @i=1;
    while(@i<=@fieldstotalcount)
		begin
			declare @fieldName nvarchar(100);
			declare @fieldType nvarchar(100);
			
			set @fieldName=(select name from #temp_table where num=@i);
			if(@fieldName=@primarykey)
				begin
					set @cSharp = @cSharp + 'union all select ''        [DrxColumn(IsPrimaryKey = true)]''';
				end
			else
				begin
					set @cSharp = @cSharp + 'union all select ''        [DrxColumn]''';
				end
				
			set @fieldType=(select case usertype 
				when 0 then 'string' 
				when 7 then 'int' 
				when 10 then 'long' 
				when 12 then 'DateTime?' 
				when 20 then 'byte[]'
				else '这是新的字段类型，需补充' end from #temp_table where num=@i);
			
			set @cSharp = @cSharp + 'union all select ''        public '+@fieldType+' '+@fieldName+' { get; set; }''';
			
			set @i=@i+1;
		end
	
	set @cSharp = @cSharp + 'union all select ''        #endregion'''
	set @cSharp = @cSharp + 'union all select ''    }''
		union all select ''}''
	';
	exec(@cSharp)
	
	--select @primarykey
end try

begin catch
    select error_number() AS ErrorNumber;
end catch;