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
最后可将代码另存,适当调整 ,也可批处理生成。