Skip to main content

SQL Script To Generate C# Model

While working on a data migration project that uses C# and Azure Functions.  I found myself in the need to generate a model of the table(s).  While doing a Google search I came across the following blog post that has a script to generate the model properties for me.  Simply replace the table name variable at the top of the script with the table name you want to generate the properties for.  You may also need to include the schema name in the table name if you have multiple tables with the same name, just under different schema's.  I did make one change to it in the select @ result line.  It was adding a blank line in between each property, so I removed the new line.


 declare @TableName sysname = 'TABLE_NAME'  
 declare @Result varchar(max) = 'public class ' + @TableName + '  
 {'  
 select @Result = @Result + '  
   public ' + (CASE WHEN ColumnName = 'RowVersion' THEN 'byte[]' ELSE ColumnType END) + NullableSign + ' ' + ColumnName + ' { get; set; }'  
 from  
 (  
   select   
     replace(col.name, ' ', '_') ColumnName,  
     column_id ColumnId,  
     case typ.name   
       when 'bigint' then 'long'  
       when 'binary' then 'byte[]'  
       when 'bit' then 'bool'  
       when 'char' then 'string'  
       when 'date' then 'DateTime'  
       when 'datetime' then 'DateTime'  
       when 'datetime2' then 'DateTime'  
       when 'datetimeoffset' then 'DateTimeOffset'  
       when 'decimal' then 'decimal'  
       when 'float' then 'float'  
       when 'image' then 'byte[]'  
       when 'int' then 'int'  
       when 'money' then 'decimal'  
       when 'nchar' then 'string'  
       when 'ntext' then 'string'  
       when 'numeric' then 'decimal'  
       when 'nvarchar' then 'string'  
       when 'real' then 'double'  
       when 'smalldatetime' then 'DateTime'  
       when 'smallint' then 'short'  
       when 'smallmoney' then 'decimal'  
       when 'text' then 'string'  
       when 'time' then 'TimeSpan'  
       when 'timestamp' then 'timestamp'  
       when 'rowversion' then 'byte[]'  
       when 'tinyint' then 'byte'  
       when 'uniqueidentifier' then 'Guid'  
       when 'varbinary' then 'byte[]'  
       when 'varchar' then 'string'  
       else 'UNKNOWN_' + typ.name  
     end ColumnType,  
     case   
       when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')   
       then '?'   
       else ''   
     end NullableSign  
   from sys.columns col  
     join sys.types typ on  
       col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id  
   where object_id = object_id(@TableName)  
 ) t  
 order by ColumnId  
 set @Result = @Result + '  
 }'  
 print @Result  

Reference:
Habilis. (2017, May 01). Creating C# model class from SQL query. Retrieved from https://habilisbest.com/creating-c-model-class-from-sql-query

Comments

Popular posts from this blog

Dynamics Set IFrame URL - D365 v8 vs. D365 v9

While doing client work, I came across a problem with setting an IFrame URL dynamically.  The underlying issue was that the sandbox instance is on v8 of Dynamics 365 and production is on v9 of Dynamics 365.  The reason for this was because this client was setup around the time that Microsoft rolled out v9.  Anyways, JavaScript that I wrote to dynamically set the URL of the IFrame wasn't working in the v9 instance.  This was because of changes that Microsoft made to how IFrames are loaded on the form and also changes to JavaScript.

Here is my v8 setup:
JavaScript runs OnLoad of contact form.  This works because of how IFrames are loaded in v8.  You can also run it on either a tab change (hide / show) or OnReadyStateComplete event of the IFrame.  Depending on your setup you will need to choose which is best for you.  For me in this case it was the OnLoad event.Here is the JavaScript: function OnLoad() { //Get memberid var value = Xrm.Page.data.entity.attributes.get…

XrmToolBox Bulk Attachment Manager - Version 2018.2.2.7 Released!

Today I have published version 2018.2.2.7 of my XrmToolBox plugin.  The purpose of this plugin is to make it easy to download and backup attachments in CRM.  This release is a major release as it is the first built version with all pieces working.  The first version only had note downloads working.

Overview of Plugin:
Current Version: 2018.2.2.7Purpose: Download Attachments and E-Mail attachmentsGitHub Link.GitHub Wiki Link.Nuget Package Link.How To Use The Tool: Launch XRMToolBox.Connect to organization.Step 1, choose what you want to download. (Once a choice is made, Step 2 becomes usable)Notes - Download attachments from note entity.E-Mail - Download attachments from emails.Both - Downloads attachments from notes and emails entities.Step 2, choose if you want to download all attachments in the system or specific attachments.  All Attachments - This will search the chosen entity to find all records that have an attachment and download them.Click on "Browse" and choose where t…

HttpWebRequest For Scribe Online Api

Scribe has made it extremely easy to work with their API.  Currently, in my limited free time, I am working on a reusable C# library to send and receive data from Scribe's API.  Once it is completed for Version 1, I will list it to GitHub, so anyone can consume it and provide feedback on improvements.  There will be a fully built out wiki with how to consume the library with references to the Scribe API documentation.  Also there will be both TDD (Test Driven Development) and BDD (Behavior Driven Development) projects.  This way you can see examples of how to consume the library as well as what I tested before release.  If you are not familiar with BDD, I recommend you check out my blog post in my Dynamics 365 blog, that gives a high level overview of it.

Anyways, back to what this post is about.  While working on the library, I came across one small issue around TLS 1.2 and making sure that the web request uses it.  Luckily, this is really easy to set in C#, when using .NET 4.5. …