-
-
Save ducthinhdeveloper/c440f3951bad0f1ee74c4538d05318e6 to your computer and use it in GitHub Desktop.
TSQL script to generate POCO/DTO from database table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
select tbl.name tbl, 'public class '+tbl.name+'{
'+ replace(replace(replace(replace( props,'','') ,'','
'),'<','<'),'>','>')+'
}' CSClass
from sys.tables tbl cross apply
(
SELECT (
SELECT 'public virtual ' + CASE WHEN ISNULL(cols.is_nullable,0)=0 THEN 'Nullable<'+res.CSType+'> '+cols.name+' {get;set;}' ELSE res.CSType 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