SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate C# class code for table


Generate C# class code for table

Author
Message
cadebryant
cadebryant
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 14
Comments posted to this topic are about the item Generate C# class code for table



vblllove
vblllove
SSC-Addicted
SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)

Group: General Forum Members
Points: 452 Visits: 211
Great Article.
k_maynard
k_maynard
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 44
I agree, generating all these classes is a real drag.

Personally, i favour a tool called CodeSmith (www.codesmithtools.com) for this function. It comes with numerous templates but you can also build your own - and though you write the templates using .Net VB/C# the output can be for anything.

This means that as well as being able to generate the class for a table, you can generate the basic stored procedures (Get, List, Add, Update, Delete) for the table, and any additional classes you need - at least to a generic level.

Where you can get really clever is by setting up your template to point to a new database and generate all the sql and classes for each table in turn......
Steve S. Yang
Steve S. Yang
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 20
Thanks for this posting. It is great! If you can add other data types such as
when data_type like 'uniquidentifier%' then 'Guid '
when data_type in ('money','decimal','float') then 'float '
and so on

then it will be perfect.


steve yang
Erzsebet Szabo
Erzsebet Szabo
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 47
I guess it would be easy to add the XML comments required for the public properties to the script. That would make it perfect.



Fazio Maurizio
Fazio Maurizio
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 92
Thanks for this posting.
I have modify the script for the case sensitive database, applying the UPPER function to DATATYPE in the CASE WHEN, for each WHEN.
Example:
WHEN UPPER(DATA_TYPE) LIKE '%CHAR%' THEN 'string '

Maurizio Fazio
Dave-3000
Dave-3000
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1782 Visits: 571
Nicely done. Thanks.

There is no "i" in team, but idiot has two.
TicketNetwork Engineer
TicketNetwork Engineer
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 164
Greate SP, I modified it to create a function to populate the fields. You supply the database connectivity part. Just change the data type of sort on the #tmp table to double, add this:

WHEN DATA_TYPE LIKE '%MONEY%' THEN 'double '

to the other switch statements and paste in the following:


INSERT INTO @temp
SELECT 4.1, '#region Private Method' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 4.2, 'private void Load' + @table_name + '(int id)' + CHAR(13)
+ CHAR(10) + '{' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 4.3, 'string sql = "SELECT * FROM ' + @table_name +
' WHERE = " + id.ToString();' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 4.4, '// Add code to query the database and populate dt'
+ @table_name + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 4.5, 'if (dt' + @table_name + '.Rows.Count > 0)'
+ CHAR(13) + CHAR(10) + '{' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 4.6,
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'this._' + COLUMN_NAME +
' = dt' + @table_name + '.Rows[0]["' + COLUMN_NAME + '"].ToString();'
WHEN DATA_TYPE LIKE '%INT%' THEN 'int.TryParse(dt' + @table_name +
'.Rows[0]["' + COLUMN_NAME + '"].ToString(), out _' + COLUMN_NAME + ');'
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime.TryParse(dt' +
@table_name + '.Rows[0]["' + COLUMN_NAME + '"].ToString(), out _'
+ COLUMN_NAME + ');'
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'this._' + COLUMN_NAME +
' = (byte[])dt' + @table_name + '.Rows[0]["' + COLUMN_NAME + '"];'
WHEN DATA_TYPE LIKE '%MONEY%' THEN 'double.TryParse(dt' + @table_name +
'.Rows[0]["' + COLUMN_NAME + '"].ToString(), out _' + COLUMN_NAME + ');'
WHEN DATA_TYPE = 'BIT' THEN 'bool.TryParse(dt' + @table_name + '.Rows[0]["' +
COLUMN_NAME + '"].ToString().Replace("1", "true").Replace("0", "false"), out _'
+ COLUMN_NAME + ');'
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'this._' + COLUMN_NAME +' = dt' +
@table_name + '.Rows[0]["' + COLUMN_NAME + '"].ToString();'
ELSE 'this._' + COLUMN_NAME +' = dt' + @table_name + '.Rows[0]["' +
COLUMN_NAME + '"];'
END + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 4.7, '}' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 4.8, '}' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 4.9, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 5.1, 'private DataTable dt' + @table_name + ';'+ CHAR(13) + CHAR(10)
swingnchad1
swingnchad1
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 13
Very handy and free! Just a couple requests for some sp guru out there:
Can anyone modify this to output in VB as well?
It would also be nice to change the output of the field names to a different format than what is in the DB. Example: _firstName instead of _first_name.
nigel.
nigel.
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3941 Visits: 2923
Good stuff.

One minor gripe, you shouldn't be using leading underscores for identifiers. It's generally considered bad practice.

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search