Skip to content

Instantly share code, notes, and snippets.

@sitsh
Forked from gemyago/generate_dto.sql
Last active July 12, 2018 02:20
Show Gist options
  • Save sitsh/86031292d21032b7423418d71e257bbb to your computer and use it in GitHub Desktop.
Save sitsh/86031292d21032b7423418d71e257bbb to your computer and use it in GitHub Desktop.
TSQL script to generate POCO/DTO from database table
DECLARE @tableName NVARCHAR(MAX), @schemaName NVARCHAR(MAX), @className NVARCHAR(MAX)
--------------- Input arguments ---------------
SET @tableName = 'Incidents'
SET @schemaName = 'dbo'
SET @className = 'IncidentDto'
--------------- Input arguments end -----------
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
PRINT 'public class ' + @className
PRINT '{'
OPEN tableColumns
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 42 THEN 'DateTime' --support datetime2(7)
WHEN 104 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 != 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 '}'
CLOSE tableColumns
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment