Skip to main content

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

Comments

Popular posts from this blog

Migrating Legacy Apps to the New SimpleMembership Provider

Asp.Net MVC4 uses the new SimpleMembership provider, changes the table structure and adds a new hashing algorithm. The reasons for the changes can be found in this article by Jon Galloway. This article shows how to migrate your existing apps to the new provider.I’m assuming that you stored your passwords in the unrecoverable SHA-1 format. If you didn’t, then you’ll have to change a couple of things. All of my apps are done this way so… I’m also assuming that you have created the basic skeleton of the new app and ran it once so the correct tables will be created.First, we’ll look at the new tables. Previously, we had all of those aspnet_xxxxxx tables. Here’s the new ones.UserProfileContains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table.webpages_MembershipStores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table.webpages_OAuthMembershipStor…

JavaScript function to automatically add slashes to date

In converting an old Windows app to a browser app, the user wanted to be able to enter dates without the slashes. Here's a simple jscript: 1:// Function to convert short date string (MMddyy) 2:// or (MMddyyyy) to a date string (mm/dd/yyyy). 3:// txtBox is the actual textbox control 4:// with the value to be processed. 5:function FixShortDate(txtBox) { 6:if (txtBox == null) { 7:return'' } 8: 9:var re = new RegExp(/(\d{6})(\d{2})?/); 10: 11:if (re.test(txtBox.value)) 12: { 13:if (txtBox.value.length == 8) { 14: txtBox.value = txtBox.value.substring(0, 2) + '/' + txtBox.value.substring(2, 4) + '/' + txtBox.value.substring(4, 8) 15: } 16: 17:if (txtBox.value.length == 6) { 18:if (txtBox.value.substring(4, 6) < 20)