Generate POCO’s from Sql Server

DECLARE tableCursor CURSOR LOCAL FOR
SELECT name, 'Foundation', name + 'Dto'
FROM sys.tables
WHERE 1 = 1

OPEN tableCursor

DECLARE @tableName NVARCHAR(MAX), @schemaName NVARCHAR(MAX), @className NVARCHAR(MAX)
FETCH NEXT FROM tableCursor INTO @tableName, @schemaName, @className

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE tableColumns CURSOR LOCAL FOR
    SELECT cols.name, cols.system_type_id, cols.is_nullable FROM sys.columns cols
        JOIN sys.tables tbl ON cols.object_id = tbl.object_id
        WHERE tbl.name = @tableName
    OPEN tableColumns

    PRINT 'public class ' + @className
    PRINT '{'

    DECLARE @name NVARCHAR(MAX), @typeId INT, @isNullable BIT, @typeName NVARCHAR(MAX)
    FETCH NEXT FROM tableColumns INTO @name, @typeId, @isNullable

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @typeName =
        CASE @typeId
            WHEN 36 THEN 'Guid'
            WHEN 56 THEN 'int'
            WHEN 61 THEN 'DateTime'
            WHEN 104 THEN 'bool'
            WHEN 167 THEN 'string'
            WHEN 175 THEN 'bool'
            WHEN 231 THEN 'string'
            WHEN 239 THEN 'string'
            WHEN 241 THEN 'XElement'
            ELSE 'TODO(' + CAST(@typeId AS NVARCHAR) + ')'
        END;

        IF @isNullable = 1 AND @typeId != 167 AND @typeId != 231 AND @typeId != 239 AND @typeId != 241
            SET @typeName = @typeName + '?'
        PRINT '    public ' + @typeName + ' ' + @name + ' { get; set; }'    
        FETCH NEXT FROM tableColumns INTO @name, @typeId, @isNullable
    END

    PRINT '}'
    PRINT ''

    CLOSE tableColumns
    DEALLOCATE tableColumns

    FETCH NEXT FROM tableCursor INTO @tableName, @schemaName, @className
END

CLOSE tableCursor
DEALLOCATE tableCursor

~ or ~

DECLARE @Schema NVARCHAR(MAX)

SET @Schema = 'Foundation'

DECLARE tableCursor CURSOR LOCAL FOR
SELECT TABLE_NAME, 'Foundation', TABLE_NAME + 'Dto'
FROM INFORMATION_SCHEMA.TABLES tbl
WHERE tbl.TABLE_SCHEMA = @Schema

OPEN tableCursor

DECLARE @tableName NVARCHAR(MAX), @schemaName NVARCHAR(MAX), @className NVARCHAR(MAX)
FETCH NEXT FROM tableCursor INTO @tableName, @schemaName, @className

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE tableColumns CURSOR LOCAL FOR
    SELECT cols.COLUMN_NAME, cols.DATA_TYPE, cols.IS_NULLABLE
    FROM INFORMATION_SCHEMA.COLUMNS cols
    INNER JOIN INFORMATION_SCHEMA.TABLES tbl ON cols.TABLE_NAME = tbl.TABLE_NAME
    WHERE tbl.TABLE_SCHEMA = @Schema AND
        tbl.TABLE_NAME = @tableName
    ORDER BY cols.ORDINAL_POSITION
    OPEN tableColumns

    PRINT 'public class ' + @className
    PRINT '{'

    DECLARE @name NVARCHAR(MAX), @type NVARCHAR(MAX), @isNullable NVARCHAR(MAX), @typeName NVARCHAR(MAX)
    FETCH NEXT FROM tableColumns INTO @name, @type, @isNullable

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @typeName = 
        CASE @type
            WHEN 'varchar' THEN 'string'
            WHEN 'int' THEN 'int'
            WHEN 'char' THEN 'bool'
            ELSE 'TODO(' + @type + ')'
        END;

        IF @isNullable = 'YES' AND @type != 'varchar'
            SET @typeName = @typeName + '?'
        PRINT '    public ' + @typeName + ' ' + @name + ' { get; set; }'    
        FETCH NEXT FROM tableColumns INTO @name, @type, @isNullable
    END

    PRINT '}'
    PRINT ''

    CLOSE tableColumns
    DEALLOCATE tableColumns

    FETCH NEXT FROM tableCursor INTO @tableName, @schemaName, @className
END

CLOSE tableCursor
DEALLOCATE tableCursor

Leave a Reply