Create C# POCO class from SQL Server

poco_gen.sql

DECLARE 
	@table varchar(100),
	@tmp varchar(max),
	@usings varchar(100),
	@namespace varchar(100),
	@lf char(2),
	@t char(1);

SET @table = 'action';
SET @namespace = 'MyProject.Models';

SET @lf = CHAR(13)+CHAR(10);
SET @t = CHAR(9);
SET	@usings = 'using System;'+@lf+'using System.Collections.Generic;'+@lf+'using System.ComponentModel.DataAnnotations;'+@lf+@lf

SELECT @tmp = Class FROM(
SELECT  
	@usings+
	'namespace '+@namespace + @lf + 
	'{'+@lf+
	@t+(Select(IIF(col.is_identity = 1,'[TableName("'+tab.name+'", "'+col.name+'")]','')) FROM sys.columns COL WHERE col.object_id = TAB.object_id AND col.is_identity = 1) + @lf +
	@t+'public class ' + tab.name + 'DTO' + @lf + 
	@t + '{'+ @lf +
		stuff(( 
              SELECT cols.Required + cols.MaxLength + @t + @t +'public '+ NullablePrefix +cols.dotnettype + NullableSuffix +' ' + cols.columnname + ' { get; set; }' + @lf AS prop
              FROM   ( 
                                SELECT     col.NAME         AS columnname, 
                                           col.user_type_id AS datatypeid, 
                                           typ.NAME         AS datatypename, 
										   iif(col.is_nullable = 1 AND typ.NAME NOT IN ('nvarchar','nchar', 'bit'),'Nullable<','') as NullablePrefix,
										   iif(col.is_nullable = 1 AND typ.NAME NOT IN ('nvarchar','nchar', 'bit'),'>','') as NullableSuffix,
                                           CASE 
                                                      WHEN typ.NAME IN ('varbinary', 
                                                                        'binary', 
                                                                        'rowversion') THEN 'Byte[]'
                                                      WHEN typ.NAME IN ('nvarchar', 
                                                                        'nchar') THEN 'String' 
                                                      WHEN typ.NAME = 'uniqueidentifier' THEN 'Guid'
                                                      WHEN typ.NAME = 'bit' THEN 'Boolean' 
                                                      WHEN typ.NAME = 'tinyint' THEN 'Byte' 
                                                      WHEN typ.NAME = 'smallint' THEN 'Int16' 
                                                      WHEN typ.NAME = 'int' THEN 'Int32' 
                                                      WHEN typ.NAME = 'bigint' THEN 'Int64' 
                                                      WHEN typ.NAME IN ('smallmoney', 
                                                                        'money', 
                                                                        'numeric', 
                                                                        'decimal') THEN 'Decimal'
                                                      WHEN typ.NAME = 'real' THEN 'Single' 
                                                      WHEN typ.NAME = 'float' THEN 'Double' 
                                                      WHEN typ.NAME IN ('smalldatetime', 
                                                                        'datetime') THEN 'DateTime'
                                           END AS dotnettype, 
                                           Iif(typ.NAME IN ('nvarchar','nchar'),@t + @t + '[MaxLength('+Cast(col.max_length AS NVARCHAR)+')]'+ @lf,'') AS [MaxLength],
                                           CASE 
                                                      WHEN col.is_nullable=1 THEN '' 
                                                      WHEN col.is_nullable=0 THEN @t + @t + '[Required]'+ @lf 
                                           END AS [Required]
                                FROM       sys.columns COL 
                                
                                INNER JOIN sys.types TYP 
                                ON         typ.user_type_id = col.user_type_id 
                                WHERE      col.object_id = TAB.object_id  ) AS cols FOR xml path (''),TYPE
		).value('.', 'varchar(max)'),1,0,'')
		+ @t + '}' + @lf + '}' [Class]
FROM sys.tables tab		
WHERE tab.NAME = @table) as res

print @tmp;

Generates a C# POCO class with data annotations from a sql server table.

Example output:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace MyProject.Models
{
	public class action {
		[Required]
		public Int32 action_id { get; set; }
		[Required]
		[MaxLength(64)]
		public String ifunc { get; set; }
		public Boolean ismenu { get; set; }
		[MaxLength(160)]
		public String descrip { get; set; }
		public Decimal levelreq { get; set; }
	}
}

• Added Custom Attribute (TableName) to class declaration.
• Added Nullable<{{type}}> for nullable fields.

Example output:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace MyProject.Models
{
	[TableName("action", "action_id")]
	public class actionDTO
	{
		[Required]
		public Int32 action_id { get; set; }
		[Required]
		[MaxLength(64)]
		public String ifunc { get; set; }
		public Boolean ismenu { get; set; }
		[MaxLength(160)]
		public String descrip { get; set; }
		public Nullable<Decimal> levelreq { get; set; }
	}
}