Create POCO Object from SQL (Tip #4)

As a developer, I like to make my life easier whenever possible. As such, I’ve created this tip, which is an example of how you can use sql to write code and make your life a little easier.  Here, we will head back to the “INFORMATION_SCHEMA” views, but this time we will look at columns.

I personally love to code first entity framework, but I also don’t like tools creating my relationships for me or even allowing entity framework to handle the table creations for me.  I know that this is a little contradictory to what I just said above, but that is just me.  Personally, I know how I like my classes structured and I want to be in control of all the moving pieces.

I have worked with a lot of entity framework “plain-old” CLR objects (POCO) and the code is pretty much identical every time. It does get tedious to type out each time however, so I am going to give a demonstration of how you can take the columns of a wide table and run a sql script against it to create your initial class.  From that starting point you can add contractors and relationships to other objects.  This will create a starting point for your POCO objects.

In most of my posts, I tend to gravitate towards using Dynamics GP for a few reasons. Namely because I have some background knowledge of it, but also because the tables fit in well due to the non-existence of foreign keys, the large number of columns, and because it is sometimes hard for a developer to find references.  With that said I will use the customer master table (RM00101) to create a POCO object from.  The “create table” syntax for this table is below.


Now this table has 103 columns and if you wanted to create a POCO object with every column in it, this would take some time, or at least it would take me some time. I know that even with generating the POCO object you will need to change the names since GP has every table and column name in upper case (not to mention their column names are not the friendliest), but again this is a starting point for you.

Below is the sql script that will create a stored procedure that you can call with your table name — it will create a class for you from that table.  At the end of this document I’ve included a link to where you can download this script if you so desire.

In the script you can see I am selecting Column_Name, Data_Type, and Character_Maximum_Length from “INFORMATION_SCHEMA.COLUMNS”. This will give me all the information required to create the properties of my object. After the cursor is created, I create the class declaration before entering the cursor loop.  In the cursor loop, I create all the properties of the object.  In the loop I look at the sql data type and match it to the .net data type.  Then after the loop I close my cursor and clean things up and close the class. Once these steps are completed, you are good to go.

Here is the example of the call and the resulting class from the call:

I know I could have probably done something better than a cursor, but for our purposes today it works.  I hope you find this useful and it helps save you some time — I know it does for me! Questions or comments: enter them below!

Download the full SQL script.

May 6, 2013