Updated!
And again! I just can’t leave well enough alone, can’t I?
I really like the Microsoft’s MVC and Entity Frameworks, and their use of Plan Ol’ C Objects (often just called POCO objects, and yes, I know that ‘objects’ is completely redundant) for data interactions. They’re simple to code, perform pretty well and most importantly, are easy to understand. However, I always run into a problem when starting a new project. Personally, I’m a database first kind of guy. I create the database, develop the initial schema and once that’s settled, I’m ready to start the code. This is completely the opposite of the Code First tooling in Visual Studio. If you do the Code First, you only create one set of objects in code and the tooling creates the database for you. But, in my case, I’ve already created the database – so what to do?
Create your own POCO generator is what. This is a pretty simple-minded POCO generator. It takes in a table name as a parameter and it’s output is a valid C# object. That said, it does cover a lot of possibilities, trying to generate as thorough of an object as possible.
drop procedure generatePOCO
go
create procedure generatePOCO @tablename sysname
as
begin
-- Class declaration
select
'[Table("' + @tablename + '")]' + char(13)
+ 'public class ' + @tablename + char(13)
+ '{'
-- create a property with annotations for each column
union all
select
-- key attribute
case
when 'ID' = UPPER(columns.name) or
(
'_ID' = RIGHT(UPPER(columns.name), 3) and
not exists (select 1 from sys.all_columns where object_id(@tablename) = all_columns.object_id and UPPER(name) = 'ID')
)
then char(9) + '[Key]' + char(13)
else ''
end +
-- hide "id" type fields using hidden attribute
case
when 'ID' = UPPER(columns.name) or '_ID' = RIGHT(UPPER(columns.name), 3)
then char(9) + '[HiddenInput(DisplayValue=false)]' + char(13)
else ''
end +
-- identity delcaration
case
when is_identity=1 then char(9) + '[DatabaseGenerated(DatabaseGeneratedOption.Identity)]' + char(13)
else ''
end +
-- maxlength attribute
case
when t.name like '%char%' and -1 <> max_length then char(9) + '[MaxLength(' + cast(max_length as varchar) + ')]'+char(13)
else ''
end +
-- default value attribute
case
when definition is not null then char(9) + '[DefaultValue' + REPLACE(definition, '''', '"') + ']' + char(13)
else ''
end +
-- Required for not nullable
case
when 0 = is_nullable then char(9) + '[Required]' + char(13)
else ''
end +
-- No special characters
case
when t.name like '%char%' and -1 <> max_length then char(9) + '[NoSpecialCharacters]'+char(13)
else ''
end +
-- property declaration
REPLACE(
char(9) + 'public '+
case
when t.name = 'bigint' then 'Int64'
when t.name = 'bit' then 'Boolean'
when t.name = 'date' then 'DateTime'
when t.name = 'datetime' then 'DateTime'
when t.name = 'datetime2' then 'DateTime'
when t.name = 'datetimeoffset' then 'Datetimeoffset'
when t.name = 'decimal' then 'Decimal'
when t.name = 'float' then 'Double'
when t.name = 'int' then 'Int32'
when t.name = 'money' then 'Decimal'
when t.name = 'numeric' then 'Decimal'
when t.name = 'real' then 'Single'
when t.name = 'smalldatetime' then 'DateTime'
when t.name = 'smallint' then 'Int16'
when t.name = 'smallmoney' then 'Decimal'
when t.name = 'sql_variant' then 'Object*'
when t.name = 'time' then 'TimeSpan'
when t.name = 'tinyint' then 'Byte'
when t.name = 'uniqueidentifier' then 'Guid'
when t.name = 'xml' then 'Xml'
when t.name like '%char%' then 'String'
when t.name like '%text%' then 'String'
else 'byte[]'
end +
case when is_nullable = 1 then '?' else '' end +
' '+
columns.name+
' { get; set; }',
'String?','String') -- replace is done since String doesn't need a '?' to allow nulls
-- terminal <cr>
+ char(13)
from sys.all_objects tables
inner join sys.all_columns columns on columns.object_id = tables.object_id
inner join sys.systypes t on t.xusertype = columns.system_type_id
left outer join sys.default_constraints dc on dc.name = OBJECT_NAME(default_object_id)
where tables.name = @tablename
-- commentary
union all
select char(9) + '// Foreign keys from this table to a master table' + char(13)
-- foreign keys referenced from this table to another
union all
select
-- required attribute as this is the dependant key
char(9) + '[Required]' + char(13) +
-- foreign key column declaration
char(9) + '[ForeignKey("' + all_columns.name + '")]' + char(13) +
-- foreign key declaration
char(9) + 'public virtual ' + OBJECT_NAME(referenced_object_id) + ' ' + OBJECT_NAME(referenced_object_id) + ' { get; set; }'
from sys.foreign_key_columns
inner join sys.all_columns on all_columns.object_id = parent_object_id and all_columns.column_id = parent_column_id
where OBJECT_ID(@tablename) = parent_object_id
-- commentary
union all
select char(9) + '// Foreign keys to this table from other tables' + char(13)
-- foreign keys to this table from other tables
union all
select char(9) + 'public virtual ICollection<' + OBJECT_NAME(parent_object_id) + '> ' + OBJECT_NAME(parent_object_id) + ' { get; set; }'
from sys.foreign_key_columns
where OBJECT_ID(@tablename) = referenced_object_id
-- close out class
union all
select '}' + char(13)
end
go
The output will look something like:
[Table("UserProfile")]
public class UserProfile
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Required]
public Int32 UserId { get; set; }
[MaxLength(112)]
[Required]
[NoSpecialCharacters]
public String UserName { get; set; }
[MaxLength(500)]
[Required]
[NoSpecialCharacters]
public String BusinessEmail { get; set; }
[MaxLength(100)]
[Required]
[NoSpecialCharacters]
public String FullName { get; set; }
[MaxLength(100)]
[Required]
[NoSpecialCharacters]
public String BusinessName { get; set; }
[MaxLength(100)]
[Required]
[NoSpecialCharacters]
public String BusinessAddress1 { get; set; }
[MaxLength(100)]
[NoSpecialCharacters]
public String BusinessAddress2 { get; set; }
[MaxLength(100)]
[NoSpecialCharacters]
public String BusinessAddress3 { get; set; }
[MaxLength(100)]
[Required]
[NoSpecialCharacters]
public String BusinessCity { get; set; }
[MaxLength(4)]
[Required]
[NoSpecialCharacters]
public String BusinessState { get; set; }
[MaxLength(20)]
[Required]
[NoSpecialCharacters]
public String BusinessZip { get; set; }
[MaxLength(100)]
[Required]
[NoSpecialCharacters]
public String BusinessPhone { get; set; }
[MaxLength(100)]
[NoSpecialCharacters]
public String BusinessFax { get; set; }
[MaxLength(100)]
[Required]
[NoSpecialCharacters]
public String PasswordQuestion { get; set; }
[MaxLength(100)]
[Required]
[NoSpecialCharacters]
public String PasswordAnswer { get; set; }
// Foreign keys from this table to a master table
// Foreign keys to this table from other tables
public virtual ICollection<webpages_UsersInRoles> webpages_UsersInRoles { get; set; }
}
for a parent table and for a child table:
[Table("webpages_UsersInRoles")]
public class webpages_UsersInRoles
{
[Required]
public Int32 UserId { get; set; }
[Required]
public Int32 RoleId { get; set; }
// Foreign keys from this table to a master table
[Required]
[ForeignKey("UserId")]
public virtual UserProfile UserProfile { get; set; }
[Required]
[ForeignKey("RoleId")]
public virtual webpages_Roles webpages_Roles { get; set; }
// Foreign keys to this table from other tables
}
The generator code examines the column definitions in Sql Server and uses the information to create the POCO object. It will check for non-null and add [Required] attributes, it will add [DefaultValue] attributes where a default is defined for the column and will emit [MaxLength] attributes for columns with a length, specifically varchar columns. It will also check for identity columns and it will add the “[Table]” attribute to the start of the class declaration to ensure EF gets the table name correct. (EF sometimes wants to pluralize table names. Maybe it’s just me.) I used the conversion table on MSDN’s SQL Server Data Type Mappings page to generate the right C# type for each column. One caveat though – I haven’t tested everything, and “user defined types” such as Geometry and Geography are all mapped as byte arrays.
There is one weakness that I have yet to be able to overcome, and that is handling cascading delete scenarios. SQL Server will automatically try to detect repeating cycles of cascades and will flag errors. Sometimes, such as the case above for “webpages_UsersInRoles” where there are multiple foreign keys, SQL Server can flag these even though the cascade won’t happen. You’ll need to use the Fluent API, with “.WillCascadeOnDelete(false)” to avoid this error. See this article for a better explanation.
There is one custom item in the generated attributes that are applied to the object properties, that is the “NoSpecialCharacters” attribute. This attribute is a one-liner that scans incoming character fields for symbols typically used in JavaScript and HTML and rejects the field if any of these are found. The definition is quite simple:
public static class StringHelpers
{
/// <summary>
/// Test for special characters in input data
/// </summary>
/// <param name="value"></param>
/// <returns>false if there are special characters</returns>
public static bool NoSpecialCharacters(this String value)
{
return (-1 == value.IndexOfAny(";<>\"[]&".ToCharArray()));
}
}
It’s not perfectly bulletproof, but it does go a long way towards preventing injection type attacks.
Accessing data through the created classes is pretty straightforward and there is a lot of information out there about how to do this. There is one thing that I would like to point out, that is that the [Required] attribute on the child foreign key relationships require accessing objects for editing and saving data in a particular manner.
// accessing data for editing
List<webpages_UsersInRoles> usersinroles = db.UserProfile
.Where(p=>p.UserName==HttpContext.User.Identity.Name)
.FirstOrDefault()
.webpages_UsersInRoles.ToList();
// Saving a new item
db.UserProfile.Where(p=>p.UserName==HttpContext.User.Identity.Name)
.webpages_UsersInRoles.Add(new_userinrole);
In the case of accessing data, the child items have to be accessed through the parent object. This sets up the foreign key relationship correctly; the same is true of saving a new object. While the child object can be created individually, it needs to be saved in relation to the correct parent object.
Mindless Passenger 