Tuesday, July 14, 2009

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!!!

No comments: