C# POCO Gen


POCO Generator

By: Yuvalsol

I was reading recently about t4templates and poco generation in C# and came across this nice article.

POCO generating application for SQL Server

Download source – 63 KB

Download POCO Generator – 57.9 KB

Introduction

There are plenty of ways to generate POCO classes from a database. The hard way is to handwrite them. This may be good for introductory/one-or-two classes scenarios but is not applicable for production. There are codegen tools, like CodeSmith. The tool detects changes to the database and generates the appropriate POCO classes. There are script tools like T4 (Text Template Transformation Toolkit), which Visual Studio supports. The solution that I opt to create is a stand-alone application, the POCO Generator, that traverses the SQL Server, and generates POCOs from various data objects. There are 5 types of database objects that the POCO Generator can handle:

  • Tables
  • Views
  • Stored Procedures
  • Table-valued Functions
  • User-Defined Table Types (TVP)

The first part of this article will describe how to use the POCO Generator. The second part will detail the implementation of retrieving the schema of the various data objects.

SQL Server Connection

The first window you’ll see is the SQL Server connection window. The Server, Authentication, Login & Password text boxes are pretty much self-explanatory. If the All checkbox is checked, the application will traverse over all the databases in the specified SQL Server instance. However, the Database dropdown allows to pick a specific database. Click on the Refresh button to pull all the database names from the SQL Server, uncheck the All checkbox and pick a database. The constructed connection string will appear in the Connection String textbox while you fill the various textboxes. If the checkbox next to it is checked, the textbox will be enabled and it will allow you to write a connection string directly. Once you are ready, hit the Connect button.

POCO Generator

The SQL Server tree lists all the databases on that instance and each database lists its data objects – tables, views, procedures, functions & TVPs. The checkboxes on the tree are for picking specific objects for exporting to files. The upper right side of the window shows the current generated POCO, based on what is selected in the tree. The panel at the bottom lets you manipulate how the POCO looks and handles exporting to files. As you change these options, the POCO panel will be refreshed and you’ll see immediately how the POCO looks.

POCO

The POCO section manages the structure of the POCO.

  • Properties/Data Members – Normally, a POCO is constructed with properties but this option gives an option to use data members instead.
  • Virtual Properties – Adds a virtual modifier to the properties.
  • Partial Class – Adds a partial modifier to the class.
  • Struct Types Nullable – All the struct types will become nullable (int?, DateTime?) even if they are not nullable in the database.
  • Comments & Without null – A comment, for each property, of the original SQL Server type and whether it is nullable. Without null removes the nullable comments.
  • using – Adds using statements at the beginning of the POCO.
  • Namespace – Wraps the POCO with the specified namespace.
Class Name

By default, the name of the POCO class is the name of the data object, whether it is a C# valid name or not. The Class Name section manipulates that name.

  • Singular – Changes the name from plural to singular. Applicable only for tables, views & TVPs. I tried to do my best here, working with the singular rules of English grammar, but obviously it’s not fool-proof.
  • Include DB – Adds the database name.
  • DB Separator – Adds the specified separator after the database name.
  • Include Schema – Adds the schema name.
  • Ignore dbo Schema – If the schema name is “dbo“, doesn’t add the schema name.
  • Schema Separator – Adds the specified separator after the schema name.
  • Words Separator – Adds the specified separator between words in the class name. Word are defined as 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, UPPER CASE, lower case – Changes the case of the class name.
  • Replace, With, Ignore Case – Performs a search and replace on the class name.
  • Fixed Name – Ignores all the previous options and set the name of the class to the specified fixed name.
  • Prefix & Suffix – Adds prefix and suffix texts to the class name.
ORM Annotations

ORM Annotations section adds various ORM attributes to the POCO class and its properties. Applicable only for tables. I tried not to go overboard here and picked only two ORMs. I also tried to implement only the most necessary attributes for each ORM.

  • EF Code-First – Adds Entity Framework Code-First attributes.
    • Table attribute on the class declaration. [Table("Production.Product")]
    • Key attribute on primary key properties. [Key]
    • Column attribute for composite primary key properties with the Order value set to the order of the key in the composite primary key. [Column(Order = 1)]
    • MaxLength attribute on string properties. [MaxLength(50)]
    • Timestamp attribute on timestamp properties. [Timestamp]
  • Column – Adds Column attribute, with Name and TypeName values, for each property. [Column(Name = "ProductID", TypeName = "int")]
  • Required – Adds Required attribute for properties that are not nullable. [Required]
  • PetaPoco – Adds PetaPoco attributes.
    PetaPoco.TableName attribute on the class declaration.[PetaPoco.TableName("Production.Product")]
    PetaPoco.PrimaryKey attribute on the class declaration. [PetaPoco.PrimaryKey("ProductID")]
  • Explicit Columns – Adds PetaPoco.ExplicitColumns attribute on the class declaration and addsPetaPoco.Column attribute for each property. [PetaPoco.ExplicitColumns] [PetaPoco.Column]
Export to Files

Exports one or more POCOs to one or more files.

  • Folder – Specifies the folder to export to.
  • Append to File – Useful if you want to export multiple POCOs to a single file. If this option is not checked, the POCO Generator will export each POCO to a different file.
  • Export Button – If there are checked checkboxes on the SQL Server tree, the POCO Generator will export just them. Otherwise, it will export the current selected POCO.
Other Buttons
  • Copy Button – Copies the current selected POCO to the clipboard.
  • Type Mapping – A popup of SQL Server to .NET type mapping.

SQL Server Data Type Mappings

Filter Results

You can filter the results in each group (Tables, Views, …) by right-click on a group and choose Filter from the context menu. In the filter popup choose what name and what schema you want to include or exclude.

Stored Procedures with Many Result Sets

There is no way to determine if a stored procedure returns more than one result set. During the process of retrieving the schema of a stored procedure, only the first result set is returned. There is no way to get to the schema of any result set after the first one.

The “solution” to this problem is more of a hack than anything else. In the stored procedure, remark the first select query and alter the stored procedure. Then, go to the UI and right-click on the stored procedure. Click on Refresh from the context menu. Once the new POCO shows up, copy it or export it for further use. Continue with this process up to the last result set. When you’re done, undo the remarks and restore the stored procedure.

To Do List

There are things that I didn’t implement. I felt they are too much for what is otherwise no more than an educational tool, at least for me. And of course, they take time and effort to do, which at this point I don’t have. The first thing that comes to mind is command line feature. With command line capabilities, POCO Generator can be integrated into the build process. I also wanted to make it database-blind, meaning it won’t target just SQL Server. Another feature to do is a plugin feature which can extend the number of ORMs the POCO Generator can support. Another thing that I wanted to do is to support foreign keys between POCOs, e.g. tables that act as header and lines. In EF, a foreign key will look something like this:

Hide Copy Code

public class Header
{
    [Key]
    public int HeaderId { get; set; }

    public ICollection<Line> Lines { get; set; }
}

public class Line
{
    [Key]
    public int LineId { get; set; }

    [ForeignKey("HeaderId")]
    public Header ParentHeader { get; set; }
}

Schemas

The process of retrieving schema of SQL Server data objects is mainly done through GetSchema methods fromDbConnection class. The class DbConnection, which SqlConnection inherits from, has several GetSchemamethods which do exactly as their name suggests. They return the schema information from the specified data source. You can pass, to the GetSchema method, the type of object that you’re looking for and list of restrictions which are usually used to filter on database name, schema name and the name of the object. A full list of object types and restricts can be found on these MSDN pages. Schema Collections and Schema Restrictions.

Tables & Views

The schema type for both tables and views is “Tables“. For tables, put the string BASE TABLE” on the last restriction which is a table type restriction. For views, put the string VIEW” on the table type restriction.

Tables:

Hide Copy Code

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    DataTable allTables = connection.GetSchema("Tables", 
        new string[] { database_name, null, null, "BASE TABLE" });
    DataTable specificTable = connection.GetSchema("Tables", 
        new string[] { database_name, schema_name, table_name, "BASE TABLE" });
}

and Views:

Hide Copy Code

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    DataTable allViews = connection.GetSchema("Tables", 
        new string[] { database_name, null, null, "VIEW" });
    DataTable specificView = connection.GetSchema("Tables", 
        new string[] { database_name, schema_name, view_name, "VIEW" });
}

User-Defined Table Types (TVP)

TVP schema can’t be retrieved through GetSchema methods or at least not retrieved reliably. Getting TVP schemas require a little querying on the SQL Server side. This first query gets all the TVPs on the database.

Hide Copy Code

select 
    tvp_schema = ss.name, 
    tvp_name = stt.name, 
    stt.type_table_object_id 
from sys.table_types stt 
inner join sys.schemas ss on stt.schema_id = ss.schema_id

and for each TVP, we get its list of columns. @tvp_id parameter is the type_table_object_id column from the previous query.

Hide Copy Code

select 
    sc.*, 
    data_type = st.name 
from sys.columns sc 
inner join sys.types st on sc.system_type_id = st.system_type_id and sc.user_type_id = st.user_type_id
where sc.object_id = @tvp_id

Stored Procedures & Table-valued Functions

The schema type for both stored procedures and functions is “Procedures“. For stored procedures, put thestring PROCEDURE” on the last restriction which is a routine type restriction. For functions, put the stringFUNCTION” on the routine type restriction.

Stored Procedures:

Hide Copy Code

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    DataTable allProcedures = connection.GetSchema("Procedures", 
        new string[] { database_name, null, null, "PROCEDURE" });
    DataTable specificProcedure = connection.GetSchema("Procedures", 
        new string[] { database_name, schema_name, procedure_name, "PROCEDURE" });
}

and Functions:

Hide Copy Code

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    DataTable allFunctions = connection.GetSchema("Procedures", 
        new string[] { database_name, null, null, "FUNCTION" });
    DataTable specificFunction = connection.GetSchema("Procedures", 
        new string[] { database_name, schema_name, function_name, "FUNCTION" });
}

For each routine, we need to get its parameters. The schema type is “ProcedureParameters“.

Hide Copy Code

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    DataTable routineParameters = connection.GetSchema("ProcedureParameters", 
        new string[] { database_name, routine_schema, routine_name, null });
}

At this point, we can filter out anything that is not a Table-valued function, meaning we need to remove Scalar functions. A scalar function has a single return parameter which is the result of the function and that’s how we find them.

Once we have the routine parameters, we will build an empty SqlParameter for each one. An emptySqlParameter is a parameter with DBNull.Value set as its value. For a TVP parameter, we will build a parameter with SqlDbType.Structured type and an empty DataTable as its value.

This is a very abridged code snippet of how a SqlParameter is built.

Hide Shrink Copy Code

SqlParameter sqlParameter = new SqlParameter();

// name
sqlParameter.ParameterName = parameter_name;

// empty value
sqlParameter.Value = DBNull.Value;

// type
switch (data_type)
{
    case "bigint": sqlParameter.SqlDbType = SqlDbType.BigInt; break;
    case "binary": sqlParameter.SqlDbType = SqlDbType.VarBinary; break;
    ....
    case "varchar": sqlParameter.SqlDbType = SqlDbType.VarChar; break;
    case "xml": sqlParameter.SqlDbType = SqlDbType.Xml; break;
}

// size for string type
// character_maximum_length comes from the parameter schem
if (data_type == "binary" || data_type == "char" || 
data_type == "nchar" || data_type == "nvarchar" || 
data_type == "varbinary" || data_type == "varchar")
{
    if (character_maximum_length == -1 || character_maximum_length > 0)
        sqlParameter.Size = character_maximum_length;
}

// direction
if (parameter_mode == "IN")
    sqlParameter.Direction = ParameterDirection.Input;
else if (parameter_mode == "INOUT")
    sqlParameter.Direction = ParameterDirection.InputOutput;
else if (parameter_mode == "OUT")
    sqlParameter.Direction = ParameterDirection.Output;

Now, we are ready to get the columns of the routine. When it comes to routines, we will useSqlDataReader.GetSchemaTable() method to get the routine schema withCommandBehavior.SchemaOnly flag.

For stored procedures, we can use CommandType.StoredProcedure.

Hide Copy Code

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand())
    {
        command.Connection = connection;
        command.CommandText = string.Format("[{0}].[{1}]", routine_schema, routine_name);
        command.CommandType = CommandType.StoredProcedure;

        // for each routine parameter, build it and add it to command.Parameters

        using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
        {
            DataTable schemaTable = reader.GetSchemaTable();
        }
    }
}

and for Table-valued functions, we need to construct a query that selects all the columns from the function.

Hide Copy Code

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand())
    {
        command.Connection = connection;
        command.CommandType = CommandType.Text;

        command.CommandText = string.Format("select * from [{0}].[{1}](", routine_schema, routine_name);
        
        // for each routine parameter, build it and add it 
        // to command.Parameters and add its name to command.CommandText
        
        command.CommandText += ")";

        using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
        {
            DataTable schemaTable = reader.GetSchemaTable();
        }
    }
}

Happy Coding Smile

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s