How to Programmatically Generate C# Classes from SQL Server Tables?

How to Generate a Class from a Database Table in SQL Server

Creating simple entities as classes from SQL Server table objects is possible without using ORM. This method provides a straightforward way to generate class structures that align with the table schema.

Steps:

1. Set Table Name: Declare a variable @TableName to hold the name of the target table.
2. Generate Class Structure: Construct a string @Result with the starting bracket and class name based on the table name.
3. Iterate Over Columns: Retrieve column information (name, data type, nullability) from the sys.columns and sys.types system tables, filtered by the object_id of the target table.
4. Build Class Properties: For each column, append a class property declaration to @Result. Properties include the column name, data type, and optional nullable character (?) if the column allows null values. Data types are converted to C# equivalents for compatibility.
5. Generate Class Code: Complete the class structure with a closing bracket and print the resulting code. This provides a C# class that mirrors the schema of the specified table.

Example:

Consider a table named "Person" with columns "Name" (string) and "Phone" (nullable string):

declare @TableName sysname = 'Person'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name = 'varchar' 
            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

Output:

public class Person
{
    public string Name { get; set; }
    public string? Phone { get; set; }
}

The above is the detailed content of How to Programmatically Generate C# Classes from SQL Server Tables?. For more information, please follow other related articles on the PHP Chinese website!