My Technical Notes

Monday, 3 June 2013

SQL Script For Generating CSharp Properties

In order to use, change the value of the @table_name variable (declared near the top):


-- change the following variable @table_name to whichever table you are targetting
DECLARE @table_name NVARCHAR(MAX) = 'Users'

; WITH sql_csharp_datatype(sql_data_type, sql_is_nullable, csharp_type)
AS
(
    SELECT 'int' sql_data_type, 'NO'  sql_is_nullable, 'int' csharp_type
    
    UNION SELECT 'int', 'YES' , 'int?'
    UNION SELECT 'nvarchar', NULL, 'string'
    UNION SELECT 'varchar', NULL, 'string'
    UNION SELECT 'bit', 'NO', 'bool'
    UNION SELECT 'bit', 'YES', 'bool?'
),
column_data AS
(
    
    SELECT LOWER(LEFT(COLUMN_NAME, 1)) + RIGHT(COLUMN_NAME, LEN(COLUMN_NAME) -1) AS public_name,
           '_' + LOWER(LEFT(COLUMN_NAME, 1)) + RIGHT(COLUMN_NAME, LEN(COLUMN_NAME) -1) AS private_name,
           (SELECT d.csharp_type 
            FROM sql_csharp_datatype d 
            WHERE d.sql_data_type = DATA_TYPE AND (d.sql_is_nullable IS NULL OR d.sql_is_nullable = IS_NULLABLE)) AS property_type 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @table_name
)
SELECT
    'private ' + property_type + ' ' + private_name + ';' AS private_property,
    'public ' + property_type + ' ' + public_name + ' { get { return ' + private_name + '; } set { ' + private_name + ' = value; } }'
FROM column_data

No comments: