Technical Implementation of Generating C# Entity Classes from SQL Server Database Tables

Technical Background and Requirement Analysis

In software development, there is often a need to map database table structures to entity classes in programming languages. This mapping forms the foundation of data persistence layer design, particularly when ORM frameworks are not used, making manual creation of entity classes a necessary step. Taking SQL Server databases as an example, each table contains multiple columns, with each column having specific data types and nullability constraints.

Core Implementation Principles

The core of generating entity classes lies in querying the database's system table information and mapping SQL data types to corresponding C# types. SQL Server provides sys.columns and sys.types system views, which contain column definitions and data type information for tables.

Data Type Mapping Mechanism

Data type mapping is a critical component of the generation process. SQL Server data types must be accurately converted to their C# counterparts, for example:

• nvarchar, varchar, char, nchar map to string
• int maps to int, bigint maps to long
• bit maps to bool
• datetime, datetime2 map to DateTime
• decimal, money, numeric map to decimal
• uniqueidentifier maps to Guid

Nullable Type Handling

For nullable columns, nullable identifiers ? need to be added after C# types. However, it's important to note that string types (such as string) are reference types in C# and do not require additional nullable identifiers. Only value types require the ? suffix when nullable.

Complete Implementation Code

The following T-SQL script implements the functionality of generating C# entity classes from specified tables:

declare @TableName sysname = 'TableName'
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 '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 'double'
            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 'float'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'long'
            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

Code Analysis and Optimization

The core logic of this script includes:

1. Obtaining table column information through sys.columns and sys.types system views
2. Using CASE statements for data type mapping
3. Handling spaces in column names by replacing them with underscores to comply with C# naming conventions
4. Determining whether to add nullable identifiers based on column nullability
5. Generating property definitions in column order

Practical Application Example

For the example Person table, the generated C# entity class is as follows:

public class Person
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Phone { get; set; }
}

Extended Applications and Considerations

In practical development, the following extension features can be considered:

• Adding data annotation support, such as [Required], [MaxLength], etc.
• Supporting constructor generation
• Adding XML documentation comments
• Handling complex data type mappings

It's important to note that this method relies on SQL Server system views, which may vary across different SQL Server versions. Additionally, custom data types require additional mapping processing.

Comparison with Alternative Methods

Compared to using third-party tools or ORM frameworks, this T-SQL-based implementation offers the following advantages:

• No dependency on external tools, executed directly in the database environment
• High flexibility, allowing customization of generation logic based on specific requirements
• High execution efficiency, avoiding network transmission overhead

However, this approach also requires developers to have strong T-SQL programming skills and manually handle some edge cases.