Skip to main content

Updated Code To Get Class Definition From Table Structure

My previous post on Script to Create MetadataType classes was to work with LINQ-To-SQL and partial classes. EF 4.1 introduces Code First which uses POCOs and here is the code to create a class definition from a table’s structure.


declare @TableName varchar(256) = 'BusinessTypes'
declare @EntityName varchar(256) = 'BusinessType'
declare @TableSchema varchar(256) = 'dbo'

declare @ColumnName varchar(256)
    , @DataType varchar(256)
    , @NewDataType varchar(256)
    , @MaxLength int
    , @Nullable varchar(5)
declare @Lines table (Line varchar(1000))

insert into @Lines select 'public class ' + @EntityName
insert into @Lines select '{'

declare @DataTypes table (SqlDataType varchar(1000), DataType varchar(1000))

insert into @DataTypes (SqlDataType, DataType) values ('bit', 'bool')
insert into @DataTypes (SqlDataType, DataType) values ('char', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('datetime', 'DateTime')
insert into @DataTypes (SqlDataType, DataType) values ('decimal', 'decimal')
insert into @DataTypes (SqlDataType, DataType) values ('int', 'int')
insert into @DataTypes (SqlDataType, DataType) values ('money', 'decimal')
insert into @DataTypes (SqlDataType, DataType) values ('ntext', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('nvarchar', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('smalldatetime', 'DateTime')
insert into @DataTypes (SqlDataType, DataType) values ('timestamp', 'byte[]')
insert into @DataTypes (SqlDataType, DataType) values ('uniqueidentifier', 'Guid')
insert into @DataTypes (SqlDataType, DataType) values ('varchar', 'string')

declare cols cursor for 
    where table_Name = @TableName and table_schema = @Tableschema

open cols

fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable
while @@FETCH_STATUS = 0
    select @NewDataType = DataType from @DataTypes where SqlDataType = @DataType
    if @DataType in ('varchar', 'char') and @Nullable = 'NO'
        insert into @Lines select char(9) + '[Required]'
    if @DataType in ('varchar', 'char')
        insert into @Lines select char(9) + '[StringLength(' + CAST(@MaxLength as varchar) + ')]'
    insert into @Lines select char(9) + 'public ' + @NewDataType + ' ' + @ColumnName + ' { get; set; }'
    insert into @Lines select char(9) + ''
    fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable

close cols
deallocate cols
insert into @Lines select '}'

select * FROM @Lines



Unknown said…

Hello there I am so delighted I found your blog, I really found you by mistake, while I was searching on Askjeeve for something else, Anyways I am here now and would just like to say many thanks for a incredible post and a all round entertaining blog (I also love the theme/design), I don't have time to look over it all at the moment but I have book-marked it and also included your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the awesome work. gmail log in

Popular posts from this blog

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 (txtB

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. UserProfile Contains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table. webpages_Membership Stores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table. webpage

Get Asp.Net Profile properties from Sql

Ever wanted to include the profile information from an Asp.Net profile in a query? It’s not that hard once you understand the structure. I’ve written a little function that does all the work. Note: I’m using Sql Server as my repository. First we need to understand how the profile data is stored. Looking at the aspnet_Profile table, we can see that it stores the information in two columns: PropertyNames and PropertyValuesString. Looking at PropertyNames we can see that it has a basic structure of Property Name, Data Type, Starting Position and Length. For example, in the string “FirstName:S:0:4:Phone:S:4:10:LastName:S:14:5:” we can see that FirstName is of type string, starts at position 0 and has a length of 4. Notice the zero base for the starting position, we need to correct for that in our function. This means in the PropertyValuesString “John2175551212Smith”, we would start with the first position and proceed 4 characters to get the name.