Skip to content

Instantly share code, notes, and snippets.

@gemyago
Last active September 4, 2023 20:23
Show Gist options
  • Save gemyago/8711676 to your computer and use it in GitHub Desktop.
Save gemyago/8711676 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 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
@djohnson001
Copy link

Thanks. This saved me a lot of time!

@slim16165
Copy link

How can I get the same result with a query which interrogates many tables?

@nilact
Copy link

nilact commented Jan 5, 2019

select tbl.name tbl, 'public class '+tbl.name+'{
'+ replace(replace(replace(replace( props,'<A>','') ,'</A>','
'),'&lt;','<'),'&gt;','>')+'
}' CSClass

 from sys.tables tbl cross apply
(
SELECT (
SELECT 'public ' + CASE WHEN ISNULL(cols.is_nullable,1)=1 and system_type_id<>167  THEN 'Nullable<'+res.CSType+'> '+cols.name+' {get;set;}' ELSE res.CSType + ' ' + cols.name+' {get;set;}'  END AS  A
FROM sys.columns cols
    JOIN (
SELECT 35 TypId,'text' TypName,'string' CSType UNION ALL
SELECT 36,'uniqueidentifier','Guid' UNION ALL
SELECT 40,'date','DateTime'UNION ALL
SELECT 41,'time','DateTime'UNION ALL
SELECT 48,'tinyint','byte' UNION ALL
SELECT 52,'smallint','short' UNION ALL
SELECT 56,'int','int' UNION ALL
SELECT 58,'smalldatetime','DateTime'UNION ALL
SELECT 60,'money','Decimal' UNION ALL
SELECT 61,'datetime','DateTime' UNION ALL
SELECT 62,'float','float'   UNION ALL
SELECT 99,'ntext','string'  UNION ALL
SELECT 104,'bit','bool'   UNION ALL
SELECT 106,'decimal','Decimal'    UNION ALL
SELECT 108,'numeric','Decimal'    UNION ALL
SELECT 127,'bigint','long' UNION ALL
SELECT 165,'varbinary','byte[]' UNION ALL
SELECT 167,'varchar','string'   UNION ALL
SELECT 175,'char','string'  UNION ALL
SELECT 231,'nvarchar','string'  UNION ALL
SELECT 239,'nchar','string' UNION ALL
SELECT 241,'xml','string'    
) res on res.TypId = cols.system_type_id
where cols.object_id = tbl.object_id
for xml path('')
) as props
)p 

I've further modified @suryapratap's code to avoid nullable for string type.

@JensBorrisholt
Copy link

select DBTable.name ClassName,
'public class ' + DBTable.name + '{'+ replace(replace(replace(replace( props,'','') ,'',''),'<','<'),'>','>')+'}' CSClass
from
sys.tables DBTable
cross apply
(
SELECT
(
SELECT 'public virtual ' +
CASE
WHEN ISNULL(cols.is_nullable,0) = 1 and res.CSType <> 'string' THEN
res.CSType+'? '
ELSE
res.CSType END + ' ' + REPLACE( cols.name, ' ', '') + ' {get;set;}' AS A
FROM sys.columns cols
JOIN (
SELECT 35 TypId,'text' TypName , 'string' CSType UNION ALL
SELECT 36 ,'uniqueidentifier' , 'Guid' UNION ALL
SELECT 40 ,'date' , 'DateTime' UNION ALL
SELECT 41 ,'time' , 'DateTime' UNION ALL
SELECT 42 ,'datetime(2)' , 'DateTime' UNION ALL
SELECT 48 ,'tinyint' , 'byte' UNION ALL
SELECT 52 ,'smallint' , 'short' UNION ALL
SELECT 56 ,'int' , 'int' UNION ALL
SELECT 58 ,'smalldatetime' , 'DateTime' UNION ALL
SELECT 60 ,'money' , 'Decimal' UNION ALL
SELECT 61 ,'datetime' , 'DateTime' UNION ALL
SELECT 62 ,'float' , 'float' UNION ALL
SELECT 99 ,'ntext' , 'string' UNION ALL
SELECT 104 ,'bit' , 'bool' UNION ALL
SELECT 106 ,'decimal' , 'Decimal' UNION ALL
SELECT 108 ,'numeric' , 'Decimal' UNION ALL
SELECT 127 ,'bigint' , 'long' UNION ALL
SELECT 165 ,'varbinary' , 'byte[]' UNION ALL
SELECT 167 ,'varchar' , 'string' UNION ALL
SELECT 175 ,'char' , 'string' UNION ALL
SELECT 231 ,'nvarchar' , 'string' UNION ALL
SELECT 239 ,'nchar' , 'string' UNION ALL
SELECT 241 ,'xml' , 'string'
) res on res.TypId = cols.system_type_id
where cols.object_id = DBTable.object_id
for xml path('')
) as props
)p

I've further modified @suryapratap's code to add support for DateTime(2) and make ude of the ? operator instead of Nullable

@TonySedg
Copy link

Thanks really useful.

If you need pocos for sql views and tables then swap out:

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

and replace with:

SELECT C.COLUMN_NAME, T.system_type_id,CASE WHEN C.IS_NULLABLE='YES' THEN 1 ELSE 0 END AS is_nullable 
FROM information_schema.columns C JOIN sys.types t ON C.DATA_TYPE = T.name 
WHERE table_name = @tableName 
ORDER BY c.ORDINAL_POSITION

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment