POCO Generator

POCO Generator traverses databases and generates POCOs from database objects, such as tables and views. POCO Generator supports SQL Server and MySQL.

There are five types of database objects that POCOs are generated from:

• Tables
• Views
• Stored Procedures
• Table-valued Functions
• User-Defined Table Types (TVP)[^1]

POCO Generator also detects primary keys, foreign keys, unique keys, indexes and more.

POCO Generator requires .NET Framework 4.6.2 Runtime.

Original article and previous version of POCO Generator on CodeProject.

[^1]: Table-valued parameters (TVPs) are declared by using user-defined table types and are scoped to stored procedures and functions. POCO Generator generates user-defined table types but uses the acronym TVP (table-valued parameter) interchangeably with user-defined table type although they are different things.

[!NOTE] I don't have much time to be actively involved with this project anymore, let alone deal with pull requests, so I'm making this repository archived and read-only. I'll try to continue develop this project in the future but no promises. Fork away.

Disclaimer

[!CAUTION] One person reported data loss after using this utility (Comments section in the original article on CodeProject Potential Data Loss). Some tables were cleared of all their records but they were able to restore them from backup. This error is NOT resolved despite my efforts to replicate and solve it. Backup your database before using this utility or use it at your own risk.

POCO Generator UI

The RDBMS tree lists all the databases on that instance and each database lists its database objects - tables, views, procedures, functions & TVPs. The checkboxes on the tree are for picking specific database objects. The upper right side of the window shows the current generated POCOs, based on what is selected on the tree. The setting panels, at the bottom, manipulate how the POCOs are constructed and handle exporting them to files.

POCO Settings

These settings determine the structure of the POCO.

Properties - Data members are constructed as properties (getter & setter).

Fields - Data members are constructed as fields.

Partial Class - Add partial modifier to the class.

Virtual Properties - Add virtual modifier to properties.

Override Properties - Add override modifier to properties.

Struct Types Nullable - struct data members will be constructed as nullable (int?) even if they are not nullable in the database.

Column Defaults - Add data member initialization based on the column's default value in the database. Default value that can't be handled properly will be commented.

Comments - Add a comment, to data members, about the original database column type and whether the column is nullable.

Comments Without null - Add a comment, to data members, about the original database column type.

using - Add using statements at the beginning of all the POCOs. If a custom namespace is set (Namespace setting), the using statements are placed outside the namespace declaration.

using Inside Namespace - If a custom namespace is set (Namespace setting), the using statements are placed inside the namespace declaration.

Namespace - Wraps all the POCOs with a custom namespace.

Inherit - Add a comma-delimited list of inherit class and interfaces.

New Line Between Members - Add empty lines between POCO data members.

Complex Types - Reverse-engineer existing Entity Framework's complex types in the database. Code-First Entity Framework prefixes the column name with the name of the complex type. More in this article Associations in EF Code First: Part 2 – Complex Types. A limitation of POCO Generator is it doesn't detect nested complex types (complex type in a complex type).

A demo with complex types at ComplexTypesDemo.

How POCO Generator detects and builds complex types:

1. For every table, select columns that
   • have underscore in the column name
   • are not part of the table's primary key
   • are not part of unique key
   • are not part of foreign key
   • are not referenced by a foreign key from another table
   • are not part of an index
   • are not identity column
2. Split the column on the first underscore. The prefix is the name of the complex type table. The suffix is the name of the complex type column.
3. Group the complex type columns by the name of the complex type table.
4. Consolidate complex types that may appear in several database tables. Two complex types are the same if they have the same complex type columns and no more. Two complex type columns are the same if they have
   • same name
   • same data type
   • same precision
   • same unsigned property
   • same nullable property
   • same computed property
   • same column default

Enum Type - Determines how enum and set type columns are generated. This setting is applicable when the RDBMS, such as MySQL, supports enum types.

If it is set to string, the data member type will be string for both enum and set type columns.

If it is set to enum ushort, enum type column will be generated as enumeration of type System.UInt16 (ushort) and set type column will be generated as bitwise enumeration of type System.UInt64 (ulong).

If it is set to enum int, enum and set type column will be generated as enumeration of type System.Int32 (int).

CREATE TABLE NumbersTable (
    Number ENUM('One', 'Two', 'Three'),
    Numbers SET('One', 'Two', 'Three')
);

When Enum Type is set to enum ushort

                                [Flags]
public enum Number : ushort     public enum Numbers : ulong
{                               {
    One = 1,                        One = 1ul,
    Two = 2,                        Two = 1ul << 1,
    Three = 3                       Three = 1ul << 2
}                               }

When Enum Type is set to enum int

                                [Flags]
public enum Number : int        public enum Numbers : int
{                               {
    One = 1,                        One = 1,
    Two = 2,                        Two = 1 << 1,
    Three = 3                       Three = 1 << 2
}                               }

Class Name Settings

The name of the POCO class is set to the name of the database object, whether it is a valid C# class name or not. These settings modify the initial class name.

Singular - Change the class name from plural to singular. Applicable only for tables, views & TVPs.

Include DB - Add the database name.

DB Separator - Add a separator after the database name.

Include Schema - Add the schema name.

Ignore dbo Schema - If the schema name is dbo, don't add the schema name.

Schema Separator - Add a separator after the schema name.

Words Separator - Add a separator between words. Word are text between underscores or in a camel case.

The class name EmployeeDepartmentHistory has 3 words in it, Employee, Department & History. The class name Product_Category has 2 words, Product & Category.

CamelCase - Change class name to camel case.

UPPER CASE - Change class name to upper case.

lower case - Change class name to lower case.

Search, Replace - Search and replace on the class name. Search is case-sensitive.

Ignore Case - Enable case-insensitive search.

Fixed Name - Set the name of the class to a fixed name.

Prefix - Add prefix text to the class name.

Suffix - Add suffix text to the class name.

Navigation Properties Settings

These settings enable navigation properties and determine how they are constructed.

Navigation Properties - Add navigation properties and constructor initialization, if necessary.

Comments - Add a comment about the underline foreign key of the navigation property.

Virtual - Add virtual modifier to the navigation properties.

Override - Add override modifier to the navigation properties.

Many-to-Many Join Table - In a Many-to-Many relationship, the join table is hidden by default. When this setting is enabled, the join table is forcefully rendered.

List, IList, ICollection, IEnumerable - When a navigation property is a collection, this setting determine what the type of collection it is. For constructor initialization, ICollection is initialized with HashSet and the other options are initialized with List.

EF Annotations Settings

These settings add Code-First Entity Framework attributes to POCO classes. More about EF annotations on this page Code First Data Annotations.

EF - Add Entity Framework main attributes.

• Table attribute to class declaration. [Table("Production.Product")]
• Key attribute to primary key properties. [Key]
• MaxLength attribu