Generate POCO classes from Database for Entity Framework Code First
Generate POCO classes from Database using SQL script using
INFORMATION_SCHEMA.COLUMNS & INFORMATION_SCHEMA.TABLES system tables.
SETET NOCOUNT off
begin
declare @UserTables table
(
SchemaName [nvarchar](200),
TableName [nvarchar](200)
)
declare @UserColumns table
(
TableName [nvarchar](200),
Name [nvarchar](200),
DataType [nvarchar](50),
isnullable [nvarchar](5),
ColLength int,
ColOrder int
)
insert into @UserTables
select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
insert into @UserColumns
select t.TableName, c.COLUMN_NAME,
case
when c.DATA_TYPE = 'varchar' then 'string'
when c.DATA_TYPE = 'nvarchar' then 'string'
when c.DATA_TYPE = 'datetime' then 'DateTime'
when c.DATA_TYPE = 'date' then 'DateTime'
when c.DATA_TYPE = 'timestamp' then 'DateTime'
when c.DATA_TYPE = 'numeric' then 'decimal'
else c.DATA_TYPE end,
c.IS_NULLABLE,
c.CHARACTER_MAXIMUM_LENGTH,
c.ORDINAL_POSITION
from @UserTables t, INFORMATION_SCHEMA.COLUMNS c
where t.TableName = c.TABLE_NAME and t.SchemaName = c.TABLE_SCHEMA
order by c.ORDINAL_POSITION
declare @EntityPriFix [nvarchar](20);
declare @SchemaName [nvarchar](200);
declare @TableName [nvarchar](200);
declare @DataType [nvarchar](20);
declare @ColumnName [nvarchar](200);
declare @isnullable [nvarchar](5)
declare @colLength [int];
set @EntityPriFix = 'POCO';
declare tableCursor cursor for
select * from @UserTables
OPEN tableCursor
print 'using System.ComponentModel.DataAnnotations;'
print 'using System.ComponentModel.DataAnnotations.Schema;'
FETCH NEXT FROM tableCursor
INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
print ' public class ' + @EntityPriFix + @TableName
print ' {'
declare columnCursor cursor for
select Name, DataType, isnullable, ColLength
from @UserColumns
where TableName = @TableName
order by ColOrder
OPEN columnCursor
FETCH NEXT FROM columnCursor
INTO @ColumnName, @DataType, @isnullable, @colLength
WHILE @@FETCH_STATUS = 0
BEGIN
if(@TableName + 'ID' = @ColumnName)
begin
print ' [Key]'
end
else
begin
if(@isnullable = 'YES')
begin
print case when @DataType <> 'string' then '//' else '' end + ' [Required]'
end
if(@TableName = @ColumnName)
begin
print ' [Column("'+ @ColumnName +'")]'
end
end
if(@DataType = 'string')
begin
print ' [MaxLength(' +
cast(@colLength as varchar(50)) + ',ErrorMessage = "' + @ColumnName + ' must be '+
cast(@colLength as varchar(50)) ++ '")]'
end
print case when @DataType p;= 'image' then '' else '' end +
' public ' + @DataType +
case when @DataType <> 'string' then ' ? ' else ' ' end +
case when Right(@ColumnName,2) = 'ID'
then substring(@ColumnName,1,len(@ColumnName)-2)+ 'Id'
else case when @ColumnName = @TableName
then @ColumnName + '_1' else @ColumnName end end + ' {get; set;}'
FETCH NEXT FROM columnCursor
INTO @ColumnName, @DataType, @isnullable, @colLength
END
CLOSE columnCursor;
DEALLOCATE columnCursor;
print ' }'
print ''
FETCH NEXT FROM tableCursor
INTO @SchemaName, @TableName
END
CLOSE tableCursor;
DEALLOCATE tableCursor;
end
Generate POCO classes from Database using SQL script using
sysObjects & syscolumns system tables.
SET NOCOUNT off
begin
declare @UserTables table
(
Id [int],
Name [nvarchar](200)
)
declare @UserColumns table
(
Id [int],
Name [nvarchar](200),
xtype int,
TypeName [nvarchar](50),
isnullable bit,
ColLength int,
ColOrder int
)
insert into @UserTables
select id, name from sysobjects where xtype = 'U'
insert into @UserColumns
select id, sc.name, ty.[type], ty.name, sc.isnullable ,
case when sc.typestat =2 then sc.length/2 else sc.length end,
sc.colorder
from syscolumns sc, systypes ty
where sc.xusertype = ty.xusertype and id in(select id from @UserTables)
declare @EntityPriFix [nvarchar](20);
declare @Id [int];
declare @Name [nvarchar](200);
declare @xtype [int];
declare @ColumnName [nvarchar](200);
declare @typeName [nvarchar](50);
declare @isnullable bit
declare @colLength [int];
set @EntityPriFix = 'POCO';
declare tableCursor cursor for
select * from @UserTables
OPEN tableCursor
print 'using System.ComponentModel.DataAnnotations;'
print 'using System.ComponentModel.DataAnnotations.Schema;'
FETCH NEXT FROM tableCursor
INTO @Id, @Name
WHILE @@FETCH_STATUS = 0
BEGIN
print ' public class ' + @EntityPriFix + @Name
print ' {'
declare columnCursor cursor for
select Name,xtype, TypeName, isnullable, ColLength
from @UserColumns where id = @Id
order by ColOrder
OPEN columnCursor
FETCH NEXT FROM columnCursor
INTO @ColumnName, @xtype, @typeName, @isnullable, @colLength
WHILE @@FETCH_STATUS = 0
BEGIN
if(@Name + 'ID' = @ColumnName)
begin
print ' [Key]'
end
else
begin
if(@isnullable = 0)
begin
print case when @xtype in(34,45) then '//' else '' end + ' [Required]'
end
if(@Name = @ColumnName)
begin
print ' [Column("'+ @ColumnName +'")]'
end
end
if(@xtype in(35,37,39,47))
begin
print ' [MaxLength(' +
cast(@colLength as varchar(50)) + ',ErrorMessage = "' + @ColumnName + ' must be '+
cast(@colLength as varchar(50)) +' characters or less' + '")]'
end
print case when @xtype in(34,45) then '//' else '' end + -- comment out image type
' public ' +
case when @xtype in(35,37,39,47) then 'string'
when @xtype in(50) then 'bool'
when @xtype in(48) then 'Byte'
when @xtype in(52,56) then 'int'
when @xtype in(63) then 'long'
when @xtype in(122,60,55,59) then 'decimal'
when @xtype in(58,61) then 'DateTime'
when @xtype in(34,45) then 'Binary'
else @typeName end +
case when @isnullable = 1 and @xtype not in(35,37,39,47) then ' ? ' else ' ' end +
case when Right(@ColumnName,2) = 'ID'
then substring(@ColumnName,1,len(@ColumnName)-2)+ 'Id'
else case when @ColumnName = @Name
then @ColumnName + '_1' else @ColumnName end end + ' {get; set;}'
FETCH NEXT FROM columnCursor
INTO @ColumnName, @xtype, @typeName, @isnullable,@colLength
END
CLOSE columnCursor;
DEALLOCATE columnCursor;
print ' }'
print ''
FETCH NEXT FROM tableCursor
INTO @Id, @Name
END
CLOSE tableCursor;
DEALLOCATE tableCursor;
end
Output will be like as below
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
public class POCOUsers
{
public int? Id { get; set; }
public int? SiteId { get; set; }
[MaxLength(255, ErrorMessage = "Username must be 255 characters or less")]
public string Username { get; set; }
[MaxLength(255, ErrorMessage = "Password must be 255 characters or less")]
public string Password { get; set; }
[MaxLength(255, ErrorMessage = "Token must be 255 characters or less")]
public string Token { get; set; }
[Required]
[MaxLength(20, ErrorMessage = "SwipeId must be 20 characters or less")]
public string SwipeId { get; set; }
[MaxLength(20, ErrorMessage = "Title must be 20 characters or less")]
public string Title { get; set; }
[MaxLength(255, ErrorMessage = "Forename must be 255 characters or less")]
public string Forename { get; set; }
[MaxLength(255, ErrorMessage = "Surname must be 255 characters or less")]
public string Surname { get; set; }
[MaxLength(255, ErrorMessage = "Phone must be 255 characters or less")]
public string Phone { get; set; }
[MaxLength(255, ErrorMessage = "Email must be 255 characters or less")]
public string Email { get; set; }
// [Required]
public DateTime? Dob { get; set; }
[Required]
[MaxLength(255, ErrorMessage = "Address1 must be 255 characters or less")]
public string Address1 { get; set; }
[Required]
[MaxLength(255, ErrorMessage = "Address2 must be 255 characters or less")]
public string Address2 { get; set; }
[Required]
[MaxLength(255, ErrorMessage = "Town must be 255 characters or less")]
public string Town { get; set; }
[Required]
[MaxLength(255, ErrorMessage = "County must be 255 characters or less")]
public string County { get; set; }
[Required]
[MaxLength(255, ErrorMessage = "Postcode must be 255 characters or less")]
public string Postcode { get; set; }
[Required]
[MaxLength(255, ErrorMessage = "Country must be 255 characters or less")]
public string Country { get; set; }
// [Required]
public int? RoleId { get; set; }
// [Required]
public int? Status { get; set; }
}
public class POCORoles
{
public int? Id { get; set; }
[MaxLength(50, ErrorMessage = "Name must be 50 characters or less")]
public string Name { get; set; }
[Required]
[MaxLength(50, ErrorMessage = "Description must be 50 characters or less")]
public string Description { get; set; }
}