Cooking POCOs recipe: SQL based DTOs

Do you have a lot of POCOs in your projects? I do and it's quite tedious and boring for me to add such stuff by hand. Well ReSharper makes this task a bit easier but anyway a lot of typing has to be done. Most of my projects have two general kinds of POCOs: DTOs and Events or Commands. In this post I'll show you my recipe on cooking DTOs that are used to transfer data that is going to be fetched using SQL (TSQL to be exact).

SQL based DTOs recipe

Many of my DTOs are based on the SQL queries and thus they can be derived from some database table (or at least some fields of it). So simple solution is to create a TSQL script that generates the DTO class from database schema. The script is below:

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

All you have to do is set your inputs (table, schema and resulting class name) and invoke the script. It will quickly give you a nice DTO like this:

public class EmployeeDto
{
        public int Id { get; set; }
        public int AccountId { get; set; }
        public string ExternalId { get; set; }
        public string EmailAddress { get; set; }
        public bool IsActive { get; set; }
        public string UniqueId { get; set; }
        public bool IsBuiltIn { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public bool IsAuthorizedOnAllDepartments { get; set; }
}

The script of course is not fully complete but it's easy to extend and adjust it based on your needs. Other kind of POCOs are Events and Commands. I have a completely different solution for them so I'll show it in my next post. Otherwise the post will be getting pretty long :)