SQL脚本直接生成代码

实体生成工具种类多，核心原理并无多大不同，经常用MSSQL，便收集整理了下适合用的：

 

按自己规范生成：

/**
*
* 功能描述：数据库内直接生成实体
* 用    法：Exec SP_GenMCode 表名或视图名
*
*/
 CREATE PROC SP_GenMCode(@obj SYSNAME)        
--生成表实体 参数为表名  
AS        
BEGIN        
 DECLARE @name VARCHAR(200)      
 ,@nameType VARCHAR(200)      
,@value VARCHAR(200)        
,@reader VARCHAR(MAX)      
 SET NOCOUNT ON        
 IF OBJECT_ID(@obj) IS NULL        
 RETURN 0        
       
 SELECT TOP 1 @value=CAST([value] AS NVARCHAR(200))        
 FROM fn_listextendedProperty (        
 'MS_Description', 'Schema', 'dbo', 'table',@obj , NULL, NULL)        
        
 print'    /// <summary>'        
 print'    /// '+ISNULL(@value,@obj)+'实体类'        
 print'    /// </summary>      
    /// <remarks>此类由SP_GenMCode自动生成，简明用SP_GenCode</remarks>'        
 print'    /// <history>'        
 print'    ///     <date>'+CONVERT(VARCHAR(10),GETDATE(),120)+'</date>'        
 print'    ///     <programmer>'+SUBSTRING(USER_NAME(), CHARINDEX( '\',USER_NAME())+1,50)+'</programmer>'        
 print'    ///     <document></document>'        
 print'    /// </history>'        
 print'    [Serializable]'        
 print'    public partial class '+@obj+':BaseModel<'+@obj+'>'        
 print'    {'        
 PRINT'        #region Vars'        
 PRINT'        #endregion'        
 PRINT''        
 PRINT'        #region Constructs'        
 PRINT''      
 PRINT'        /// <summary>'      
 PRINT'        /// 默认构造'      
 PRINT'        /// </summary>'      
 PRINT'        public '+@obj+'():base("'+DB_NAME()+'","'+@obj+'"){}'        
 PRINT'        #endregion'        
 PRINT''        
 PRINT'        #region Properties'        
 PRINT''          
 IF  OBJECT_ID('tempdb..#tempColumn') IS NOT NULL DROP TABLE #tempColumn        
 select A.[name],dbo.fn_GetType(B.[name])AS nameType,[value]        
 INTO #tempColumn        
 from sys.columns A        
 join dbo.systypes B on B.xtype=A.system_type_id        
 LEFT JOIN fn_listextendedProperty (        
 'MS_Description', 'Schema', 'dbo', 'table',@obj , 'COLUMN', null) C        
 ON  C.[objname]COLLATE Latin1_General_CI_AS=A.[name]        
 where [object_id]=object_id(@obj)        
 ORDER BY Column_id         
 WHILE EXISTS(SELECT  1 FROM #tempColumn)        
 BEGIN          
  SELECT TOP 1 @name=[name]        
  ,@nameType=nameType        
  ,@value=CAST([value] AS NVARCHAR(200))        
  FROM #tempColumn       
      
  print'        #region '+ISNULL(@value,@name)        
  PRINT''        
  print'        private ' +@nameType+' auto'+@name+';'        
        
  print'        /// <summary>'        
  print'        /// Gets or sets '+ISNULL(@value,'The '+@name)        
  print'        /// </summary>'        
  print'        public virtual '+@nameType+' '+@name        
  print'        {'        
  print'            get {    '  
 print'  if(default('+@nameType+')==this.auto'+@name+'){               '  
   print' this.GetValue("'+@name+'",ref this.auto'+@name+');'  
  print'     }   '               
  print'                     return this.auto'+@name+';       
                 }'        
  print'            set {        
                       this.SetValue("'+@name+'", value,ref  this.auto'+@name+') ;'      
 IF columnproperty(object_id(@obj),@name,'isidentity')=1 --是否标识列做为简单主列 生成HashCode      
PRINT'                    this.Id=this.auto'+@name+';'      
print'                  }'        
  print'        }'        
  PRINT'        #endregion'        
  PRINT''        
  DELETE  #tempColumn WHERE [name]=@name        
 END        
 IF  OBJECT_ID('tempdb..#tempColumn') IS NOT NULL         
 DROP TABLE #tempColumn        
 PRINT'        #endregion'        
 PRINT''        
 PRINT'        #region Methods'        
 PRINT''      
      
 --PRINT'        /// <summary>      
 --       ///实现接口ICreateModel内部创建对象值      
 --       ///</summary>      
 --       /// <param name="reader">The reader</param>      
 --       /// <returns>当前实体对象</returns>      
 --       protected override '+@obj+' InternalCreateModel(System.Data.IDataReader reader)      
 --       {      
 --         if (reader != null)      
 --           {      
 --               for (int i = 0; i < reader.FieldCount; i++)      
 --               {      
 --                   if (reader.IsDBNull(i))      
 --                   {      
 --                       continue;      
 --                   }      
 --                   this.ChangedProperties[reader.GetName(i)] = reader.GetValue(i);      
 --               }      
 --           }      
 --           return this;      
                 
 --       }'        
 PRINT'        #endregion'        
 print'    }'             
RETURN 0        
END

 简单版：

CREATE PROC dbo.SP_GenCode(@obj SYSNAME)    
--生成表实体 参数为表名    
AS    
BEGIN    
 DECLARE @name VARCHAR(200) ,@nameType VARCHAR(200),@value VARCHAR(200)    
 SET NOCOUNT ON    
 IF OBJECT_ID(@obj) IS NULL    
 RETURN 0    
 SELECT TOP 1 @value=CAST([value] AS NVARCHAR(200))    
 FROM fn_listextendedProperty (    
 'MS_Description', 'Schema', 'dbo', 'table',@obj , NULL, NULL)    
    
 print'    /// <summary>'    
 print'    /// '+ISNULL(@value,@obj)+'实体'    
 print'    /// </summary>'    
 print'    /// <history>'    
 print'    ///     <date>'+CONVERT(VARCHAR(10),GETDATE(),120)+'</date>'    
 print'    ///     <programmer>'+SUBSTRING(USER_NAME(), CHARINDEX( '\',USER_NAME())+1,50)+'</programmer>'    
 print'    ///     <document></document>'    
 print'    /// </history>'    
 print'    [Serializable]'    
 print'    public partial class '+@obj+''    
 print'    {'    
 PRINT'        #region Vars'    
 PRINT'        #endregion'    
 PRINT''    
 PRINT'        #region Constructs'    
 PRINT''    
 PRINT'        #endregion'    
 PRINT''    
 PRINT'        #region Properties'    
 PRINT''    
    
 IF  OBJECT_ID('tempdb..#tempColumn') IS NOT NULL DROP TABLE #tempColumn    
 select A.[name],dbo.fn_GetType(B.[name])AS nameType,[value]    
 INTO #tempColumn    
 from sys.columns A    
 join dbo.systypes B on B.xtype=A.system_type_id    
 LEFT JOIN fn_listextendedProperty (    
 'MS_Description', 'Schema', 'dbo', 'table',@obj , 'COLUMN', null) C    
 ON  C.[objname]COLLATE Latin1_General_CI_AS=A.[name]    
 where [object_id]=object_id(@obj)    
 ORDER BY Column_id    
    
    
 WHILE EXISTS(SELECT  1 FROM #tempColumn)    
 BEGIN    
    
  SELECT TOP 1 @name=[name]    
  ,@nameType=nameType    
  ,@value=CAST([value] AS NVARCHAR(200))    
  FROM #tempColumn    
  print'        #region '+ISNULL(@value,@name)    
  PRINT''    
  print'        private ' +@nameType+' auto'+@name+';'    
    
  print'        /// <summary>'    
  print'        /// Gets or sets The '+ISNULL(@value,@name)    
  print'        /// </summary>'    
  print'        public virtual '+@nameType+' '+@name    
  print'        {'    
  print'            get { return this.auto'+@name+'; }'    
  print'            set { this.auto'+@name+' = value; }'    
  print'        }'    
  PRINT'        #endregion'    
  PRINT''    
  DELETE  #tempColumn WHERE [name]=@name    
 END    
 IF  OBJECT_ID('tempdb..#tempColumn') IS NOT NULL     
 DROP TABLE #tempColumn    
 PRINT'        #endregion'    
 PRINT''    
 PRINT'        #region Methods'    
 PRINT''    
 PRINT'        #endregion'    
 print'    }'    
     
RETURN 0    
END

列类型转换Function：

 

CREATE FUNCTION [dbo].[fn_GetType]      
(      
--数据库类型与C#类型之前转换，用于生成代码      
--Created By  2011-08-24      
@SqlType SYSNAME      
)      
RETURNS VARCHAR(20)      
AS      
BEGIN      
 RETURN(      
 SELECT CASE       
 WHEN @SqlType in('bit') THEN 'bool'      
 WHEN @SqlType in('bigint') THEN 'long'      
 WHEN @SqlType in('int') THEN 'int'      
 WHEN @SqlType in('smallint') THEN 'short'     
 WHEN @SqlType in('char','varchar','text','nchar','nvarchar','ntext','xml') THEN 'string'      
 WHEN @SqlType in('date','datetimeoffset','datetime2','smalldatetime','datetime') THEN 'DateTime'      
      
 WHEN @SqlType in('money','smallmoney','decimal') THEN 'decimal'      
 WHEN @SqlType in('float','real') THEN 'double'      
 WHEN @SqlType in('uniqueidentifier') THEN 'Guid'      
 ELSE 'object'      
 END )     
END 

最后可将代码另存，适当调整 ，也可批处理生成。

转载于:https://www.cnblogs.com/stevenmoli/archive/2012/08/24/2654539.html