Skip to main content

Posts

Showing posts from June, 2019

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 + ' ' + Column…

SQL Script to Create Enums For Table Columns

While working on a data migration project, using C# and Azure Functions, I found myself needing to create enums to use in my code for the columns.  Some of the tables where vary large (over 200 columns) and doing this manually would have taken a while to do it.  So, I wrote the below SQL script to create the enums for me.  To use this simply replace the table and schema variables with the schema and table name you want to generate the enums for.


declare @Table sysname = '<INSERT TABLE NAME HERE>'; declare @Schema sysname = 'INSERT SCHEMA NAME HERE'; declare @Result varchar(max) = 'public enum ' + @Table + 'Columns' + ' {' select @Result = CONCAT(@Result, ' ',ColName, ' = ', SUM(ColNum), ',') from ( select COLUMN_NAME as ColName, SUM(ORDINAL_POSITION - 1) as ColNum from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @Table AND TABLE_SCHEMA = @Schema group by CO…