Click here to monitor SSC
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
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

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



vblllove
vblllove
SSC Veteran
SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)SSC Veteran (234 reputation)

Group: General Forum Members
Points: 234 Visits: 209
Great Article.
k_maynard
k_maynard
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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 (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 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
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 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
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

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

There is no "i" in team, but idiot has two.
TicketNetwork Engineer
TicketNetwork Engineer
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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.
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 2823
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