sqlserver中利用存储过程打印出指定表的实体类

本文主要是介绍sqlserver中利用存储过程打印出指定表的实体类，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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 '表名'

这篇关于sqlserver中利用存储过程打印出指定表的实体类的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！