SQL tip: generating C# directly from SQL

Hand-coding C# properties for every table is one of those tasks that's equal parts repetitive and error-prone. Typos, missed columns, or duplicating work every time the schema changes can quickly get messy.

With a bit of SQL, you can generate fully-formed C# property definitions straight from your database schema. That means your models always reflect your tables, and repetitive boilerplate work disappears.

We'll walk through this process, using SQL’s CONCAT function with metadata tables to dynamically produce C# code. With this pattern you can scaffold DTOs and C# objects, DataTable creation logic, row-to-object mapppers, parameter signatures for stored procedures and bindings for Dapper or gRPC objects.

A simple (but powerful) example

Let's say you have a table called stage_GREG_20_EntityMaster. You want to build a C# object class or DTO matching its structure.

Here's a SQL query that inspects the columns and dynamically generates the C# property declarations you need:

select
    -- VO creator   datetimeoffset
    [VO creator] =
    CONCAT(CASE
    when data_type in ('uniqueidentifier') then 'public Guid'
    when data_type in ('varchar', 'nvarchar','char') then 'public string?'
    when data_type in ('date', 'datetime') then 'public DateTime'
    when data_type in ('datetimeoffset', 'datetime') then 'public DateTime'
    when data_type in ('int', 'smallint') then 'public int'
    when data_type in ('bigint') then 'public long'
    when data_type in ('bit') then 'public bool'
    when data_type in ('decimal', 'numeric') then 'public decimal'
    when data_type like 'numeric%' then 'public double'
    when data_type in ('float') then 'public double'
    else 'UNKNOWN'
    END,
    ' ', UPPER(LEFT(Column_name, 1)), SUBSTRING(COLUMN_NAME, 2, LEN(COLUMN_NAME)-1), ' { get; set; }')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'stage_GREG_20_EntityMaster'
  AND TABLE_SCHEMA = 'dbo'

What this does:

Looks up every column in stage_GREG_20_EntityMaster
Maps the SQL type to the closest C# type (nullability included)
Generates the property line in standard C# format, eg:

public int EntityId { get; set; }
public string? RegistrationNumber { get; set; }
public string? Name { get; set; }
public string? EntityType { get; set; }
public string? Status { get; set; }
public bool IsActive { get; set; }
public DateTime RegistrationDate { get; set; }
public DateTime DissolutionDate { get; set; }

Now you can copy-paste this straight into a C# class file:

public class GregEntity
{
public int EntityId { get; set; }
public string? RegistrationNumber { get; set; }
public string? Name { get; set; }
public string? EntityType { get; set; }
public string? Status { get; set; }
public bool IsActive { get; set; }
public DateTime RegistrationDate { get; set; }
public DateTime DissolutionDate { get; set; }
}

Change the table name or tweak the mapping, and it's ready to generate code for any model in your database.

Extending the Pattern

This core approach can be adapted for:

Building DataTable creation logic
Generating row-to-object or object-to-row mappings
Parameter signatures for stored procs
Mapping between objects and gRPC protocol buffers

Just adjust the CONCAT output to generate the lines of code you need.

Summary

Generating code directly from SQL flips the usual process on its head: instead of chasing schema changes in C#, you let the database define the source of truth and generate your models automatically.

It’s simple, adaptable, and one of those quiet productivity boosters that pays off every time your schema evolves.

09 March 2026