T-SQL to create an instance of an object with the properties set

Here’s some T-SQL to create a SELECT statement that will create the properties with values. I use this for generating test data from existing DB’s. Right now I’ve got it setup to handle int, char, varchar, smalldatetime, datetime and bit. I’ll add others as needed.

@TableName is the name of the table where the data is stored. Simply set the table name hit F5 then cut and paste the generated script into the T-SQL that gets the data your looking for. This can be all rows in the table or a single row.

The code:

declare @TableName varchar(100)
declare @ColumnName varchar(100)
declare @DataType varchar(100)
declare @Sql varchar(max)

declare @Crlf char(2)

set @TableName = 'Clients'
set @Sql = CHAR(39)

declare cols_temp cursor for 
    SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = @TableName
        and data_type in ('int', 'char', 'varchar', 'smalldatetime', 'datetime', 'bit')
        
open cols_temp

FETCH NEXT FROM cols_temp INTO @ColumnName,@DataType

WHILE @@FETCH_STATUS = 0
BEGIN
    IF LEN(@Sql) > 1
        SET @Sql = @Sql + ' + ' + CHAR(39) + ', '
    
    IF @DataType = 'int'
        SET @Sql = @Sql + @ColumnName + ' = ' + CHAR(39) + ' + CAST(' + @ColumnName + ' As varchar)'

    IF @DataType in ('char', 'varchar')
        SET @Sql = @Sql + @ColumnName + ' = "' + CHAR(39) + ' + COALESCE(' + @ColumnName + ', '''') + ' + CHAR(39) + '"' + CHAR(39)
        
    IF @DataType = 'bit'
        SET @Sql = @Sql + @ColumnName + ' = '' + CASE WHEN ' + @ColumnName + '=1 THEN ''true'' ELSE ''false'' END'
        
    IF @DataType = 'smalldatetime'
        SET @Sql = @Sql + @ColumnName + ' = new DateTime('' + CAST(YEAR(' + @ColumnName + ') As varchar) + '', '' + CAST(MONTH(' + @ColumnName + ') As varchar) + '', '' + CAST(DAY(' + @ColumnName + ') As varchar)  + '', 0, 0, 0)'''
    
    IF @DataType = 'datetime'
        SET @Sql = @Sql + @ColumnName + ' = new DateTime('' + CAST(YEAR(' + @ColumnName + ') As varchar) + '', '' + CAST(MONTH(' + @ColumnName + ') As varchar) + '', '' + CAST(DAY(' + @ColumnName + ') As varchar)  + '', '' + CAST(DATEPART(HH,' + @ColumnName + ') As varchar) + '', '' + CAST(DATEPART(N,' + @ColumnName + ') As varchar) + '', '' + CAST(DATEPART(S,' + @ColumnName + ') As varchar) + '')'''
        
    FETCH NEXT FROM cols_temp INTO @ColumnName,@DataType
END

SELECT @Sql

close cols_temp

deallocate cols_temp

Sample uses to add to rows to a collection:

SELECT 
    'clientsCollection.Add(new Client() {ClientKey = ' + CAST(ClientKey As varchar) 
        + ', FirstName = "' + COALESCE(FirstName, '') + '"' 
        + ', LastName = "' + COALESCE(LastName, '') + '"' 
        + ', MiddleInitial = "' + COALESCE(MiddleInitial, '') + '"' 
        + ', Birth = new DateTime(' + CAST(YEAR(Birth) As varchar) 
            + ', ' + CAST(MONTH(Birth) As varchar) 
            + ', ' + CAST(DAY(Birth) As varchar)  + ', 0, 0, 0) });'
FROM Clients

This generates the following:

clientsCollection.Add(new Client() { ClientKey = 52, FirstName = "Paul", LastName = "McCartney", MiddleInitial = "", Birth = new DateTime(1945, 10, 15, 0, 0, 0) });
clientsCollection.Add(new Client() { ClientKey = 43, FirstName = "George", LastName = "Harrison", MiddleInitial = "D", Birth = new DateTime(1943, 12, 17, 0, 0, 0) });
clientsCollection.Add(new Client() { ClientKey = 84, FirstName = "Ringo", LastName = "Starr", MiddleInitial = "A", Birth = new DateTime(1949, 12, 5, 0, 0, 0) });

Sample to create a specific instance:

SELECT 
    'Client testClient = new Client() {ClientKey = ' + CAST(ClientKey As varchar) + ', FirstName = "' + COALESCE(FirstName, '') + '"' + ', LastName = "' + COALESCE(LastName, '') + '"' + ', MiddleInitial = "' + COALESCE(MiddleInitial, '') + '"' + ', Birth = new DateTime(' + CAST(YEAR(Birth) As varchar) + ', ' + CAST(MONTH(Birth) As varchar) + ', ' + CAST(DAY(Birth) As varchar)  + ', 0, 0, 0) });'
FROM Clients
WHERE ClientKey = 52

This generates the following (I formatted the code):

Client testClient = new Client() {
    ClientKey = 52, 
    FirstName = "Paul", 
    LastName = "McCartney", 
    MiddleInitial = "", 
    Birth = new DateTime(1945, 10, 15, 0, 0, 0) 
};

Have fun!!!