CREATE PROC [dbo].[sp_createEntity] @tableName NVARCHAR(128) AS DECLARE @tableID INT; DECLARE @columnName NVARCHAR(64); DECLARE @dataType NVARCHAR(64); DECLARE @isNullable BIT; DECLARE @columnDesc NVARCHAR(256) = ''; DECLARE @columnMapDataType NVARCHAR(64)= ''; DECLARE @tempAttribute NVARCHAR(256)= ''; DECLARE @tempAttributeSummary NVARCHAR(1024)= ''; set @tableName=replace ( @tableName , '[' , '' ) set @tableName=replace ( @tableName , ']' , '' ) SET @tableID = -1; SELECT @tableID = object_id , @tableName = name FROM sys.tables WHERE LOWER(name) = LOWER(@tableName); IF ( @tableID = -1 ) BEGIN PRINT ( '输入的表名不存在!' ); END; DECLARE curtable CURSOR FOR SELECT CONVERT(NVARCHAR(50), A.name) AS columnName , CONVERT(NVARCHAR(50), LOWER(B.name)) AS dataType , A.is_nullable AS isNullable, CONVERT(NVARCHAR(256), C.value) AS columnDesc FROM sys.columns AS A LEFT JOIN sys.types AS B ON A.user_type_id = B.user_type_id LEFT JOIN sys.extended_properties AS C ON A.object_id = C.major_id AND A.column_id = C.minor_id WHERE A.object_id = @tableID; OPEN curtable; FETCH NEXT FROM curtable INTO @columnName, @dataType, @isNullable, @columnDesc; WHILE @@FETCH_STATUS = 0 BEGIN IF ( CHARINDEX(@dataType, 'datetime,smalldatetime') > 0 ) BEGIN SET @columnMapDataType = 'DateTime'; END; IF ( CHARINDEX(@dataType, 'bigint') > 0 ) BEGIN SET @columnMapDataType = 'long'; END; IF ( CHARINDEX(@dataType, 'tinyint') > 0 ) BEGIN SET @columnMapDataType = 'byte'; END; IF ( CHARINDEX(@dataType, 'int') = 1 ) BEGIN SET @columnMapDataType = 'int'; END; IF ( CHARINDEX(@dataType, 'smallint') = 1 ) BEGIN SET @columnMapDataType = 'short'; END; IF ( CHARINDEX(@dataType, 'float,real') > 0 ) BEGIN SET @columnMapDataType = 'float'; END; IF ( CHARINDEX(@dataType, 'binary,varbinary,image,timestamp') > 0 ) BEGIN SET @columnMapDataType = 'byte[]'; END; IF ( CHARINDEX(@dataType, 'bit') > 0 ) BEGIN SET @columnMapDataType = 'bool'; END; IF ( CHARINDEX(@dataType, 'decimal,smallmoney,money,numeric') > 0 ) BEGIN SET @columnMapDataType = 'decimal'; END; IF ( CHARINDEX(@dataType, 'uniqueidentifier') > 0 ) BEGIN SET @columnMapDataType = 'Guid'; END; IF ( CHARINDEX(@dataType, 'char,varchar,nchar,nvarchar,text,ntext') > 0 ) BEGIN SET @columnMapDataType = 'string'; END; IF ( CHARINDEX(@dataType, 'xml') > 0 ) BEGIN SET @columnMapDataType = 'XmlDocument'; END; IF ( @isNullable = 1 AND CHARINDEX(@columnMapDataType, 'string,XmlDocument') = 0 ) BEGIN SET @columnMapDataType = @columnMapDataType + '?'; END; SET @tempAttributeSummary = CHAR(10) + '/// <summary>' + CHAR(10) + '/// ' + @columnDesc + CHAR(10) + '/// </summary>'; SET @tempAttribute = 'public ' + @columnMapDataType + ' ' + @columnName + ' { get; set; }'; PRINT @tempAttributeSummary; PRINT @tempAttribute; FETCH NEXT FROM curtable INTO @columnName, @dataType, @isNullable, @columnDesc; END; CLOSE curtable; DEALLOCATE curtable; GO
测试:
exec sp_createEntity '表名'