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.

 

CREATE TABLE [dbo].[RM00101]([CUSTNMBR] [char](15) NOT NULL Primary Key, [CUSTNAME] [char](65) NOT NULL, [CUSTCLAS] [char](15) NOT NULL,

[CPRCSTNM] [char](15) NOT NULL, [CNTCPRSN] [char](61) NOT NULL, [STMTNAME] [char](65) NOT NULL, [SHRTNAME] [char](15) NOT NULL,

[ADRSCODE] [char](15) NOT NULL, [UPSZONE] [char](3) NOT NULL, [SHIPMTHD] [char](15) NOT NULL, [TAXSCHID] [char](15) NOT NULL,

[ADDRESS1] [char](61) NOT NULL, [ADDRESS2] [char](61) NOT NULL, [ADDRESS3] [char](61) NOT NULL, [COUNTRY] [char](61) NOT NULL,

[CITY] [char](35) NOT NULL, [STATE] [char](29) NOT NULL, [ZIP] [char](11) NOT NULL, [PHONE1] [char](21) NOT NULL,

[PHONE2] [char](21) NOT NULL, [PHONE3] [char](21) NOT NULL, [FAX] [char](21) NOT NULL, [PRBTADCD] [char](15) NOT NULL,

[PRSTADCD] [char](15) NOT NULL, [STADDRCD] [char](15) NOT NULL, [SLPRSNID] [char](15) NOT NULL, [CHEKBKID] [char](15) NOT NULL,

[PYMTRMID] [char](21) NOT NULL, [CRLMTTYP] [smallint] NOT NULL, [CRLMTAMT] [numeric](19, 5) NOT NULL,

[CRLMTPER] [smallint] NOT NULL, [CRLMTPAM] [numeric](19, 5) NOT NULL, [CURNCYID] [char](15) NOT NULL, [RATETPID] [char](15) NOT NULL,

[CUSTDISC] [smallint] NOT NULL, [PRCLEVEL] [char](11) NOT NULL, [MINPYTYP] [smallint] NOT NULL, [MINPYDLR] [numeric](19, 5) NOT NULL,

[MINPYPCT] [smallint] NOT NULL, [FNCHATYP] [smallint] NOT NULL, [FNCHPCNT] [smallint] NOT NULL, [FINCHDLR] [numeric](19, 5) NOT NULL,

[MXWOFTYP] [smallint] NOT NULL, [MXWROFAM] [numeric](19, 5) NOT NULL, [COMMENT1] [char](31) NOT NULL, [COMMENT2] [char](31) NOT NULL,

[USERDEF1] [char](21) NOT NULL, [USERDEF2] [char](21) NOT NULL, [TAXEXMT1] [char](25) NOT NULL, [TAXEXMT2] [char](25) NOT NULL,

[TXRGNNUM] [char](25) NOT NULL, [BALNCTYP] [smallint] NOT NULL, [STMTCYCL] [smallint] NOT NULL, [BANKNAME] [char](31) NOT NULL,

[BNKBRNCH] [char](21) NOT NULL, [SALSTERR] [char](15) NOT NULL, [DEFCACTY] [smallint] NOT NULL, [RMCSHACC] [int] NOT NULL,

[RMARACC] [int] NOT NULL, [RMSLSACC] [int] NOT NULL, [RMIVACC] [int] NOT NULL, [RMCOSACC] [int] NOT NULL, [RMTAKACC] [int] NOT NULL,

[RMAVACC] [int] NOT NULL, [RMFCGACC] [int] NOT NULL, [RMWRACC] [int] NOT NULL, [RMSORACC] [int] NOT NULL, [FRSTINDT] [datetime] NOT NULL,

[INACTIVE] [tinyint] NOT NULL, [HOLD] [tinyint] NOT NULL, [CRCARDID] [char](15) NOT NULL, [CRCRDNUM] [char](21) NOT NULL,

[CCRDXPDT] [datetime] NOT NULL, [KPDSTHST] [tinyint] NOT NULL, [KPCALHST] [tinyint] NOT NULL, [KPERHIST] [tinyint] NOT NULL,

[KPTRXHST] [tinyint] NOT NULL, [NOTEINDX] [numeric](19, 5) NOT NULL, [CREATDDT] [datetime] NOT NULL, [MODIFDT] [datetime] NOT NULL,

[Revalue_Customer] [tinyint] NOT NULL, [Post_Results_To] [smallint] NOT NULL, [FINCHID] [char](15) NOT NULL, [GOVCRPID] [char](31) NOT NULL,

[GOVINDID] [char](31) NOT NULL, [DISGRPER] [smallint] NOT NULL, [DUEGRPER] [smallint] NOT NULL, [DOCFMTID] [char](15) NOT NULL,

[Send_Email_Statements] [tinyint] NOT NULL, [USERLANG] [smallint] NOT NULL, [GPSFOINTEGRATIONID] [char](31) NOT NULL, [INTEGRATIONSOURCE] [smallint] NOT NULL,

[INTEGRATIONID] [char](31) NOT NULL, [ORDERFULFILLDEFAULT] [smallint] NOT NULL, [CUSTPRIORITY] [smallint] NOT NULL, [CCode] [char](7) NOT NULL,

[DECLID] [char](15) NOT NULL, [RMOvrpymtWrtoffAcctIdx] [int] NOT NULL, [SHIPCOMPLETE] [tinyint] NOT NULL, [CBVAT] [tinyint] NOT NULL,

[INCLUDEINDP] [tinyint] NOT NULL, [DEX_ROW_TS] [datetime] NOT NULL, [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL )

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.

Create Procedure GeneratePOCOOClass

(

@tableName nvarchar(100)

)

As

Begin

--Declare @tableName nvarchar(100) = 'RM00101'

DECLARE @newLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

Declare @columnName nvarchar(50)

Declare @dataType nvarchar(50)

Declare @charecterLength int

Declare @pocoObject nvarchar(max) = ''

Declare tbl_ColumnCursor Cursor For

Select      COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH

from  INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME = @tableName

Set         @pocoObject = 'class ' + @tableName + @newLineChar + '{' + @newLineChar

Open tbl_ColumnCursor

Fetch Next From tbl_ColumnCursor Into @ColumnName, @dataType, @charecterLength

While @@FETCH_STATUS = 0

Begin

Select @pocoObject = @pocoObject + 'public ' +

case

when @dataType like '%char%' and @charecterLength  = 1 Then

'char '

When (@dataType like '%char%') Or (@dataType = 'xml') Or (@dataType like '%text%') Then

'string '

When @dataType in ('varbinary', 'timestamp', 'image', 'rowversion', 'binary') Then

'byte[] '

When @dataType = 'bigint' Then

'Int64 '

When @dataType = 'bit' Then

'bool '

When @dataType in ('date', 'smalldatetime', 'datetime', 'datetime2') Then

'DateTime '

When @dataType = 'datetimeoffset' Then

'DateTimeOffset '

When @dataType in ('decimal', 'money', 'smallmoney', 'numeric') Then

'decimal '

When @dataType = 'float' Then

'double '

When @dataType = 'int' Then

'int '

When @dataType = 'real' Then

'single '

When @dataType = 'smallint' Then

'Int16 '

When @dataType = 'time' Then

'TimeSpan '

When @dataType = 'tinyint' Then

'byte '

When @dataType = 'uniqueidentifier' Then

'Guid '

Else @newLineChar

End + @columnName + '{ get; set; }'+ @newLineChar

--Set @pocoObject = @pocoObject + @columnName + @newLineChar

--Select @pocoObject

Fetch Next From tbl_ColumnCursor Into @ColumnName, @dataType, @charecterLength

End

Close tbl_ColumnCursor

Deallocate tbl_ColumnCursor

Set         @pocoObject = @pocoObject + '}' + @newLineChar

Select @pocoObject

End

GO

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:

Exec GeneratePOCOOClass 'RM00101'

class RM00101

{

public string CUSTNMBR{ get; set; }

public string CUSTNAME{ get; set; }

public string CUSTCLAS{ get; set; }

public string CPRCSTNM{ get; set; }

public string CNTCPRSN{ get; set; }

public string STMTNAME{ get; set; }

public string SHRTNAME{ get; set; }

public string ADRSCODE{ get; set; }

public string UPSZONE{ get; set; }

public string SHIPMTHD{ get; set; }

public string TAXSCHID{ get; set; }

public string ADDRESS1{ get; set; }

public string ADDRESS2{ get; set; }

public string ADDRESS3{ get; set; }

public string COUNTRY{ get; set; }

public string CITY{ get; set; }

public string STATE{ get; set; }

public string ZIP{ get; set; }

public string PHONE1{ get; set; }

public string PHONE2{ get; set; }

public string PHONE3{ get; set; }

public string FAX{ get; set; }

public string PRBTADCD{ get; set; }

public string PRSTADCD{ get; set; }

public string STADDRCD{ get; set; }

public string SLPRSNID{ get; set; }

public string CHEKBKID{ get; set; }

public string PYMTRMID{ get; set; }

public Int16 CRLMTTYP{ get; set; }

public decimal CRLMTAMT{ get; set; }

public Int16 CRLMTPER{ get; set; }

public decimal CRLMTPAM{ get; set; }

public string CURNCYID{ get; set; }

public string RATETPID{ get; set; }

public Int16 CUSTDISC{ get; set; }

public string PRCLEVEL{ get; set; }

public Int16 MINPYTYP{ get; set; }

public decimal MINPYDLR{ get; set; }

public Int16 MINPYPCT{ get; set; }

public Int16 FNCHATYP{ get; set; }

public Int16 FNCHPCNT{ get; set; }

public decimal FINCHDLR{ get; set; }

public Int16 MXWOFTYP{ get; set; }

public decimal MXWROFAM{ get; set; }

public string COMMENT1{ get; set; }

public string COMMENT2{ get; set; }

public string USERDEF1{ get; set; }

public string USERDEF2{ get; set; }

public string TAXEXMT1{ get; set; }

public string TAXEXMT2{ get; set; }

public string TXRGNNUM{ get; set; }

public Int16 BALNCTYP{ get; set; }

public Int16 STMTCYCL{ get; set; }

public string BANKNAME{ get; set; }

public string BNKBRNCH{ get; set; }

public string SALSTERR{ get; set; }

public Int16 DEFCACTY{ get; set; }

public int RMCSHACC{ get; set; }

public int RMARACC{ get; set; }

public int RMSLSACC{ get; set; }

public int RMIVACC{ get; set; }

public int RMCOSACC{ get; set; }

public int RMTAKACC{ get; set; }

public int RMAVACC{ get; set; }

public int RMFCGACC{ get; set; }

public int RMWRACC{ get; set; }

public int RMSORACC{ get; set; }

public DateTime FRSTINDT{ get; set; }

public byte INACTIVE{ get; set; }

public byte HOLD{ get; set; }

public string CRCARDID{ get; set; }

public string CRCRDNUM{ get; set; }

public DateTime CCRDXPDT{ get; set; }

public byte KPDSTHST{ get; set; }

public byte KPCALHST{ get; set; }

public byte KPERHIST{ get; set; }

public byte KPTRXHST{ get; set; }

public decimal NOTEINDX{ get; set; }

public DateTime CREATDDT{ get; set; }

public DateTime MODIFDT{ get; set; }

public byte Revalue_Customer{ get; set; }

public Int16 Post_Results_To{ get; set; }

public string FINCHID{ get; set; }

public string GOVCRPID{ get; set; }

public string GOVINDID{ get; set; }

public Int16 DISGRPER{ get; set; }

public Int16 DUEGRPER{ get; set; }

public string DOCFMTID{ get; set; }

public byte Send_Email_Statements{ get; set; }

public Int16 USERLANG{ get; set; }

public string GPSFOINTEGRATIONID{ get; set; }

public Int16 INTEGRATIONSOURCE{ get; set; }

public string INTEGRATIONID{ get; set; }

public Int16 ORDERFULFILLDEFAULT{ get; set; }

public Int16 CUSTPRIORITY{ get; set; }

public string CCode{ get; set; }

public string DECLID{ get; set; }

public int RMOvrpymtWrtoffAcctIdx{ get; set; }

public byte SHIPCOMPLETE{ get; set; }

public byte CBVAT{ get; set; }

public byte INCLUDEINDP{ get; set; }

public DateTime DEX_ROW_TS{ get; set; }

public int DEX_ROW_ID{ get; set; }

}

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
New CPE Accredited Courses Now Available for Dynamics AX, GP, and NAVEARN CREDITS TODAY