真爱无限的知识驿站

GO  

/****** 对象:  StoredProcedure [dbo].[pro_GenerateModel]    脚本日期: 08/04/2012 11:26:43 ******/  

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pro_GenerateModel]') AND type in (N'P', N'PC'))  

DROP PROCEDURE [dbo].[pro_GenerateModel]  

SET ANSI_NULLS ON  

GO  

SET QUOTED_IDENTIFIER ON  

GO  

/*****************************************************  

** DECRIPTION: 生成（c#model类）代码的存储过程  

** VERSION      AUTH          DATE          Defect No           DESC  

** --------  ------------  ------------  -----------------   ------------------------------  

** V000.0.1    pukuimin     08/04/2012                          新建程序  

** --------  ------------  ------------  -----------------   -------------------------------  

*******************************************************/  

CREATE procedure [dbo].[pro_GenerateModel](  

                    @TbName NVARCHAR(200) ---表名  

)  

as  

begin  

DECLARE @TableName     NVARCHAR(200)  --表名  

DECLARE @ConstructParams     VARCHAR(8000)  --构造参数  

DECLARE @ConstructGetValue     VARCHAR(8000) --构造赋值  

DECLARE @FieldPropertys     VARCHAR(8000) --属性和字段  

SELECT @TableName = @TbName,@ConstructParams = '',@FieldPropertys='',@ConstructGetValue=''  

  

if isnull(@TableName,'')=''  

begin  

    print '表名不能为空！'  

    return 0  

end   

----输出存储过程名  

--PRINT 'CREATE PROCEDURE dbo.pro_get_'+dbo.fun_get_UpperFirst(@tablename)+'('  

SELECT   

----构造参数  

@ConstructParams = @ConstructParams + dbo.fun_get_tabspace(3)+dbo.fun_get_cssdt_by_sqldt(DATA_TYPE)+' '+dbo.fun_get_LowerFirst(COLUMN_NAME)+','+CHAR(10),   ---- CHAR(10)换行符  

----构造赋值  

@ConstructGetValue = @ConstructGetValue + dbo.fun_get_tabspace(2)+'this.'+dbo.fun_get_UpperFirst(COLUMN_NAME)+' = '+dbo.fun_get_LowerFirst(COLUMN_NAME)+';'+CHAR(10),  

---属性和字段  

@FieldPropertys = @FieldPropertys + dbo.fun_get_tabspace(2)+  

'private '+dbo.fun_get_cssdt_by_sqldt(DATA_TYPE)+' '+dbo.fun_get_LowerFirst(COLUMN_NAME)+  

    (CASE   

        WHEN DATA_TYPE='NVARCHAR' OR DATA_TYPE='VARCHAR' OR DATA_TYPE='CHAR'OR DATA_TYPE='NCHAR' OR DATA_TYPE='NTEXT' OR DATA_TYPE='TEXT' OR DATA_TYPE='OUT'  

        THEN ' = ""'  

        when data_type='uniqueidentifier' or DATA_TYPE='image' or DATA_TYPE='variant'  

        then ' = null'  

    ELSE  

        ' = '+dbo.fun_get_cssdt_by_sqldt(DATA_TYPE)+'.MinValue'  

    END)  

+';'+  

CHAR(10)+dbo.fun_get_tabspace(2)+'/// <summary>'+  

CHAR(10)+dbo.fun_get_tabspace(2)+'/// '+dbo.fun_get_comment(@tablename,COLUMN_NAME)+  

CHAR(10)+dbo.fun_get_tabspace(2)+'/// <summary>'+  

CHAR(10)+dbo.fun_get_tabspace(2)+'public '+dbo.fun_get_cssdt_by_sqldt(DATA_TYPE)+' '+dbo.fun_get_UpperFirst(COLUMN_NAME)+  

CHAR(10)+dbo.fun_get_tabspace(2)+'{'+  

CHAR(10)+dbo.fun_get_tabspace(3)+'get { return '+dbo.fun_get_LowerFirst(COLUMN_NAME)+';}'+  

CHAR(10)+dbo.fun_get_tabspace(3)+'set { '+dbo.fun_get_LowerFirst(COLUMN_NAME)+' = value;}'+  

CHAR(10)+dbo.fun_get_tabspace(2)+'}'+CHAR(10)  

FROM INFORMATION_SCHEMA.COLUMNS  

WHERE TABLE_NAME = @TableName   

set @ConstructParams=LEFT(@ConstructParams,LEN(@ConstructParams)-2) ----去掉 ‘,’ 和 换行符  

set @ConstructGetValue=LEFT(@ConstructGetValue,LEN(@ConstructGetValue)-1)  

set @FieldPropertys=LEFT(@FieldPropertys,LEN(@FieldPropertys)-1)  

print dbo.fun_get_tabspace(1)+'/// <summary>'  

print dbo.fun_get_tabspace(1)+'/// '+@TableName  

print dbo.fun_get_tabspace(1)+'/// <summary>'  

print dbo.fun_get_tabspace(1)+'[Serializable]'  

print dbo.fun_get_tabspace(1)+'public class '+@TableName+'Model'  --      

print dbo.fun_get_tabspace(1)+'{'  

print dbo.fun_get_tabspace(2)+'/// <summary>'  

print dbo.fun_get_tabspace(2)+'/// 无参构造函数 '  

print dbo.fun_get_tabspace(2)+'/// <summary>'  

print dbo.fun_get_tabspace(2)+'public '+@TableName+'Model(){}'  

print dbo.fun_get_tabspace(2)+'/// <summary>'  

print dbo.fun_get_tabspace(2)+'/// 有参构造函数 '  

print dbo.fun_get_tabspace(2)+'/// <summary>'  

print dbo.fun_get_tabspace(2)+'public '+@TableName+'Model('  

print @ConstructParams  

print dbo.fun_get_tabspace(2)+')'  

print dbo.fun_get_tabspace(2)+'{'  

print @ConstructGetValue  

print dbo.fun_get_tabspace(2)+'}'  

print @FieldPropertys  

print dbo.fun_get_tabspace(1)+'}'  

end  

/*  

-- 调用：

exec [pro_GenerateModel] 'stuinfo'  

*/