SQLSERVER 生成实体类

MSSQL 生成实体类

 

SET NOCOUNT ON

DECLARE @TableName VARCHAR(50) = 'Basal_Msa'	--需要生成实体类的表名，例如：Basal_Customer

IF OBJECT_ID('tempdb..#TempColumn','U') IS NOT NULL 
BEGIN
    DROP TABLE #TempColumn
END

--表字段列表
SELECT
	col.column_id ColumnSeq,
	tb.name TableName,
	col.name ColumnName,
	sy.name TypeName,
	col.max_length ColumnLength,
	des.value AS ColumnDesc,
	col.is_nullable AS IsNullable,
	CASE WHEN pk.COLUMN_NAME = col.name THEN 1 ELSE 0 END IsPK
	INTO #TempColumn
FROM sys.tables tb
INNER JOIN sys.columns col ON col.object_id = tb.object_id
INNER JOIN sys.types sy ON col.system_type_id = sy.system_type_id AND col.user_type_id = sy.user_type_id
LEFT JOIN sys.extended_properties des ON des.major_id = col.object_id AND des.minor_id = col.column_id
LEFT JOIN information_schema.key_column_usage pk ON pk.table_name = tb.name AND pk.column_name = col.name	--关联出主键信息
WHERE tb.name = @TableName
ORDER BY col.column_id

--数据库字段与C#字段映射
DECLARE @SqlColumnTypeMapCSharp TABLE(SqlColumnType VARCHAR(50),CSharpMap VARCHAR(50))
INSERT INTO @SqlColumnTypeMapCSharp(SqlColumnType,CSharpMap)
SELECT 'Int','int' UNION ALL
SELECT 'BigInt','long' UNION ALL
SELECT 'DateTime','DateTime' UNION ALL
SELECT 'Bit','bool' UNION ALL
SELECT 'VarChar','string' UNION ALL
SELECT 'NVarChar','string' UNION ALL
SELECT 'Char','string' UNION ALL
SELECT 'Text','string' UNION ALL
SELECT 'NText','string' UNION ALL
SELECT 'Image','string' UNION ALL
SELECT 'Float','float' UNION ALL
SELECT 'Decimal','decimal'

DECLARE @ColumnSeq INT				--列顺序
DECLARE @ColumnName VARCHAR(100)		--列名
DECLARE @TypeName VARCHAR(50) 		--字段类型
DECLARE @ColumnLength INT			--字段长度
DECLARE @ColumnDesc NVARCHAR(500)	--字段描述
DECLARE @IsNullable BIT				--是否可为空
DECLARE @IsPK INT					--是否为主键（0：否 1：是）
DECLARE @CharpTypeName VARCHAR(50)	--C#字段名称
DECLARE @CSharpFiledNull BIT = 0		--C#字段是否为空
DECLARE @MaxLength INT				--用于临时统计的变量
DECLARE @TableDesc NVARCHAR(200)	--表名注释

DECLARE @ClassInfo NVARCHAR(MAX) = ''

DECLARE @ClassExtendInfo NVARCHAR(MAX) = ''	--针对int类型数据，增加扩展字段

DECLARE @EditProcParam NVARCHAR(MAX) = ''

SELECT 
	DISTINCT @TableDesc = ISNULL(CAST(f.value AS NVARCHAR(200)),@TableName)
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype= b.xusertype
INNER JOIN sysobjects d ON a.id= d.id AND d.xtype= 'U' AND d.name<> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault= e.id
LEFT JOIN sys.extended_properties g ON a.id= G.major_id AND a.colid= g.minor_id
LEFT JOIN sys.extended_properties f ON d.id= f.major_id AND f.minor_id= 0 
WHERE d.name = @TableName

SET @ClassInfo += '/// <summary>
/// '+ @TableDesc +'
/// </summary>' 
+ CHAR(10) 
+'[Serializable]'
	+ CHAR(10)
	+ 'public class '+ REPLACE(@TableName,'_','') + 'Info' +
	+ + CHAR(10) + '{'

WHILE EXISTS(SELECT 1 FROM #TempColumn)
BEGIN
    
	SELECT 
		@ColumnSeq = tmp.ColumnSeq,@TableName = tmp.TableName,@ColumnName = tmp.ColumnName,@TypeName = tmp.TypeName,@ColumnLength = tmp.ColumnLength,@ColumnDesc = CONVERT(NVARCHAR(500),tmp.ColumnDesc),@IsNullable = tmp.IsNullable,@IsPK = tmp.IsPK
	FROM #TempColumn tmp WHERE tmp.ColumnSeq = (SELECT MIN(ti.ColumnSeq) FROM #TempColumn ti)


	--获取C#字段名称
	SELECT @CharpTypeName = sc.CSharpMap FROM @SqlColumnTypeMapCSharp sc WHERE sc.SqlColumnType = @TypeName

	SET @CSharpFiledNull = 0
	IF @IsNullable = 1 AND @CharpTypeName IN ('int','float','decimal','DateTime')
	BEGIN
	    SET @CSharpFiledNull = 1
	END

	SET @ClassInfo += 
	'
	/// <summary>
    /// '+ CASE ISNULL(@ColumnDesc,'') WHEN '' THEN @ColumnName ELSE @ColumnDesc END  +'
    /// </summary>
    public '+ @CharpTypeName + CASE @CSharpFiledNull WHEN 1 THEN '?' ELSE '' END + ' '+ @ColumnName +' { get; set; }' + CHAR(10)

	IF @TypeName = 'INT' AND @IsPK = 0
	BEGIN
		--DECLARE @CommonlyColumn VARCHAR(MAX) = 'ItemId,ProdOrderId,StationId,OpeId,ResourceId,ResId,UserId'
		DECLARE @ClassExtendColumnName VARCHAR(100) = @ColumnName	--字段名
		DECLARE @ClassExtendColumnDesc NVARCHAR(500) = @ColumnDesc	--字段描述

		IF LEN(@ClassExtendColumnName) > 2 AND SUBSTRING(@ClassExtendColumnName,LEN(@ClassExtendColumnName)-1,LEN(@ClassExtendColumnName)-2) = 'Id'
		BEGIN
		    SET @ClassExtendColumnName = LEFT(@ClassExtendColumnName,LEN(@ClassExtendColumnName)-2)
		END
		IF  LEN(@ClassExtendColumnDesc) > 2 AND SUBSTRING(@ClassExtendColumnDesc,LEN(@ClassExtendColumnDesc)-1,LEN(@ClassExtendColumnDesc)-2) = 'Id'
		BEGIN
		    SET @ClassExtendColumnDesc = LEFT(@ClassExtendColumnDesc,LEN(@ClassExtendColumnDesc)-2)
		END

		SET @ClassExtendInfo += 
	'
	/// <summary>
	/// '+ CASE ISNULL(@ClassExtendColumnDesc,'') WHEN '' THEN @ClassExtendColumnName ELSE @ClassExtendColumnDesc END  +'
	/// </summary>
	public string '+ @ClassExtendColumnName+'Name' +' { get; set; }' + CHAR(10)
	END
	

	---------------获取 新增/编辑 存储过程参数信息 开始----------------------
	DECLARE @Spec VARCHAR(200) = ''		--为了对仗工整，需要补的空格
	DECLARE @ParamLength INT			--存储过程参数字符长度
	DECLARE @ParamTypeLenInfo VARCHAR(20)	--参数类型长度（如果是VarChar或NVarChar类型，则需要定义字段长度）
	DECLARE @SpecLength INT				--空格字符串长度（及@Spec字符串长度）
	IF @MaxLength IS NULL	
	BEGIN
	    SELECT @MaxLength = MAX(LEN(tmp.ColumnName + UPPER(tmp.TypeName))) FROM #TempColumn tmp
		SELECT @MaxLength = @MaxLength + LEN(CAST(MAX(CASE tmp.TypeName WHEN 'VarChar' THEN tmp.ColumnLength WHEN 'NVarChar' THEN tmp.ColumnLength / 2 END) AS VARCHAR(10))) FROM #TempColumn tmp WHERE tmp.TypeName IN ('VarChar','NVarChar')
		SET @MaxLength = @MaxLength + 4	--补4个空格
	END
	SET @ParamTypeLenInfo = (CASE @TypeName WHEN 'VarChar' THEN '('+ CAST(@ColumnLength AS VARCHAR(10)) +')' WHEN 'NVarChar' THEN '('+ CAST(@ColumnLength / 2 AS VARCHAR(10)) +')' ELSE '' END) --如果是VarChar或NVarChar类型，则需要定义字段长度
	SELECT @ParamLength = LEN(@ColumnName + UPPER(@TypeName) + @ParamTypeLenInfo), @SpecLength = 0
	--遍历
	WHILE (@ParamLength + @SpecLength < @MaxLength)
	BEGIN
	    SELECT @Spec = @Spec + ' ',@SpecLength = @SpecLength + 1
		IF @SpecLength > 100
			THROW 50000, '陷入无尽循环', 1;
	END
	
	IF @ColumnName NOT IN ('CreateBy','CreateTime','ModifyTime')
	SET @EditProcParam += '@'+ @ColumnName + ' ' + UPPER(@TypeName) + 
		@ParamTypeLenInfo +  ',' + 
		@Spec +'--' + CASE ISNULL(@ColumnDesc,'') WHEN '' THEN @ColumnName ELSE @ColumnDesc END + CHAR(10)
	---------------获取 新增/编辑 存储过程参数信息 结束----------------------

	DELETE #TempColumn WHERE ColumnSeq = (SELECT MIN(ti.ColumnSeq) FROM #TempColumn ti)

END

SET @ClassInfo += '}'

--打印实体类：
PRINT @ClassInfo

--！！！若print不全时，可用此方法打印全部字符串！！！
--SELECT @ClassInfo FOR XML PATH('')

--打印扩展字段
PRINT CHAR(10) + CHAR(10) + '    #region 扩展字段'+ CHAR(10)
PRINT @ClassExtendInfo
PRINT CHAR(10) + '    #endregion'

--打印 新增/编辑 存储过程参数信息：
PRINT CHAR(10) + '-------------------获取 新增/编辑 存储过程参数信息 开始--------------------------' + CHAR(10)
PRINT @EditProcParam
PRINT '-------------------获取 新增/编辑 存储过程参数信息 结束--------------------------'


SET NOCOUNT OFF