学步园

/*
 利用数据库生成.net 使用的类,简化程序编写
 为考虑效率问题,没有利用数据库循环,格式不美观。(需要利用.net 工具调整)
 作者：  邹黎鹏(zlp321001@hotmail.com) 
    最后更新时间:2006-6-14
 作用:  为简化.net 对基础类的编写(具体也可以根据自己的需要生成通用的类)
    调用：     JRJ_Jeneratec# '','','','ddl_log'
*/

ALTER PROC JRJ_Jeneratec#
 @ServerName varchar(20)='', --不指定则指当前服务器
 @dbname varchar(20)='',  --不指定则指当前数据库
 @Password varchar(20)='', --密码
 @TABLENAME VARCHAR(200),    --表名
 @CodePath varchar(200)='D:/数据库生成C#类/' --文件保存路径
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @SQL VARCHAR(8000)
 DECLARE @SQL_Attribute VARCHAR(8000)
 DECLARE @SQL_Structure VARCHAR(8000)
 DECLARE @SQL_Default   VARCHAR(8000)
 DECLARE @SQL_Function  VARCHAR(8000)
 DECLARE @SQL_GetValue  VARCHAR(8000)
 DECLARE @SQL_AttributeVALUE  VARCHAR(8000)
 DECLARE @FILE_PAHT VARCHAR(200)
 DECLARE @SQL_Temp varchar(8000)
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..##'))
 DROP TABLE  ##
 CREATE TABLE ## (COLUMNS VARCHAR(8000))
 SET @SQL_Attribute=''
 SET @SQL_Structure=''
 SET @SQL_Default=''
 SET @SQL_Function=''
 SET @SQL_GetValue=''
 SET @SQL_AttributeVALUE=''
 SET @SQL='using System;'+CHAR(13)
 SET @SQL=@SQL+'namespace Model'+CHAR(13)
 SET @SQL=@SQL+'{'+CHAR(13)
 SET @SQL=@SQL+CHAR(32)+'/// <summary>'+CHAR(13)
 SET @SQL=@SQL+CHAR(32)+'/// '+@TABLENAME+' 的摘要说明。'+CHAR(13)
 SET @SQL=@SQL+CHAR(32)+'/// </summary>'+CHAR(13)
 SET @SQL=@SQL+CHAR(32)+'public class '+@TABLENAME+''+CHAR(13)
 SET @SQL=@SQL+CHAR(32)+'{'+CHAR(13)

 INSERT INTO ##
 SELECT @SQL
 SELECT @SQL_Attribute=@SQL_Attribute++CHAR(32)+'private'+CHAR(32)+
   CASE WHEN xtype IN(34,35,99,165,173,189)   THEN 'byte[]'
     WHEN xtype IN(36,98,167,175,231,239,241,231) THEN 'string'
     WHEN xtype IN(48,52,56,127)     THEN 'int'
     WHEN xtype IN(58,61)       THEN 'DateTime'
     WHEN xtype IN(59,60,62,106,108,122)   THEN 'Double'
     WHEN xtype IN(104)        THEN 'bool'
     ELSE '数据类型未知' End
   +CHAR(32)+'_'+NAME+';'+CHAR(10)
 FROM SYSCOLUMNS WHERE ID=OBJECT_ID(''+@TABLENAME+'')
 ORDER BY COLORDER

 INSERT INTO ##
 SELECT @SQL_Attribute

 SELECT @SQL_Default=@SQL_Default+CHAR(32)+
   CASE WHEN xtype IN(36,98,167,175,231,239,241,231) THEN '_'+NAME+'=""'
     WHEN xtype IN(48,52,56,127)     THEN '_'+NAME+'=0'
     ELSE '数据类型未知' End
     +';'+CHAR(13)+CHAR(32)
 FROM SYSCOLUMNS WHERE ID=OBJECT_ID(''+@TABLENAME+'')
 AND xtype IN(36,98,167,175,231,239,241,231,48,52,56,127)
 ORDER BY COLORDER
 SET @SQL_Default=@SQL_Default+CHAR(32)+'}'

 SET @SQL_Structure=@SQL_Structure+CHAR(32)+'public '+@TABLENAME+'()'
 SET @SQL_Structure=@SQL_Structure+CHAR(13)+CHAR(32)+CHAR(32)+'{'
 SET @SQL_Structure=@SQL_Structure+CHAR(13)+CHAR(32)+CHAR(32)+'//'
 SET @SQL_Structure=@SQL_Structure+CHAR(13)+CHAR(32)+CHAR(32)+'// TODO: 在此处添加构造函数逻辑'
 SET @SQL_Structure=@SQL_Structure+CHAR(13)+CHAR(32)+CHAR(32)+'//'
 SET @SQL_Structure=@SQL_Structure+CHAR(13)+CHAR(32)+''

 INSERT INTO ##
 SELECT @SQL_Structure

 INSERT INTO ##
 SELECT @SQL_Default

 SET @SQL_Function=@SQL_Function+CHAR(13)+CHAR(32)+CHAR(32)+'public '+@TABLENAME+'('
 SELECT @SQL_Function=@SQL_Function+
  CASE WHEN xtype IN(34,35,99,165,173,189)   THEN 'byte[]'
    WHEN xtype IN(36,98,167,175,231,239,241,231) THEN 'string'
    WHEN xtype IN(48,52,56,127)     THEN 'int'
    WHEN xtype IN(58,61)       THEN 'DateTime'
    WHEN xtype IN(59,60,62,106,108,122)   THEN 'Double'
    WHEN xtype IN(104)        THEN 'bool'
    ELSE '数据类型未知' End
  +CHAR(32)+NAME+','
  FROM SYSCOLUMNS WHERE ID=OBJECT_ID(''+@TABLENAME+'')
  ORDER BY COLORDER
 SET  @SQL_Function=stuff(@SQL_Function,len(@SQL_Function),1,')')

 SET @SQL_Function=@SQL_Function+CHAR(13)+CHAR(32)+CHAR(32)+'{'
 SELECT @SQL_Function=@SQL_Function+CHAR(13)+CHAR(32)+CHAR(32)+CHAR(32)+'this.'+'_'+NAME+' = '+NAME+';'
   FROM SYSCOLUMNS WHERE ID=OBJECT_ID(''+@TABLENAME+'')
 ORDER BY COLORDER
 SET @SQL_Function=@SQL_Function+CHAR(13)+CHAR(32)+CHAR(32)+'}'

 INSERT INTO ##
 SELECT @SQL_Function

 SET @SQL_AttributeVALUE=@SQL_AttributeVALUE+CHAR(13)+CHAR(32)+'//属性 '
 SELECT @SQL_AttributeVALUE=@SQL_AttributeVALUE+CHAR(13)+CHAR(32)+'public '+CHAR(32)+
   CASE WHEN xtype IN(34,35,99,165,173,189)   THEN 'byte[]'
     WHEN xtype IN(36,98,167,175,231,239,241,231) THEN 'string'
     WHEN xtype IN(48,52,56,127)     THEN 'int'
     WHEN xtype IN(58,61)       THEN 'DateTime'
     WHEN xtype IN(59,60,62,106,108,122)   THEN 'Double'
     WHEN xtype IN(104)        THEN 'bool'
     ELSE '数据类型未知' End
    +CHAR(32)+NAME+CHAR(13)+CHAR(32)+'{'+CHAR(13)+CHAR(32)
    +'get { return _'+NAME+';}'+CHAR(13)+CHAR(32)
    +'set { _'+NAME+' = value;}'+CHAR(13)+CHAR(32)
    +'}'
 FROM SYSCOLUMNS WHERE ID=OBJECT_ID(''+@TABLENAME+'')
 ORDER BY COLORDER

 INSERT INTO ##
 SELECT @SQL_AttributeVALUE

 INSERT INTO ##
 SELECT CHAR(13)+CHAR(32)+'}'+CHAR(13)+'}'

 SET @FILE_PAHT=@CodePath
 SET @FILE_PAHT=@FILE_PAHT+@TABLENAME+'.CS'
 DECLARE @file INT
 EXEC master..xp_fileexist @FILE_PAHT,@file out
 IF @file=0
 BEGIN
 --生成文件夹和目录
 SET @CodePath='MD '+@CodePath+''
 EXEC master..xp_cmdshell @CodePath
 END
 IF isnull(@ServerName,'')='' SET @ServerName=@@ServerName
 IF isnull(@dbname,'')='' SET @dbname=DB_NAME()

 EXEC('EXEC master..xp_cmdshell ''bcp " SELECT COLUMNS FROM '+@dbname+'..## " queryout "'+@FILE_PAHT+'" -c -q -S'+@ServerName+' -Usa -P'+@Password+'''')

SET NOCOUNT OFF
END