Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Generate C# class code for table Expand / Collapse
Author
Message
Posted Friday, November 9, 2007 11:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 5, 2008 2:49 PM
Points: 57, Visits: 14
Comments posted to this topic are about the item Generate C# class code for table


Post #420651
Posted Friday, November 16, 2007 2:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 10:30 AM
Points: 231, Visits: 198
Great Article.
Post #422950
Posted Friday, November 16, 2007 3:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 12:14 AM
Points: 1, Visits: 37
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......
Post #422966
Posted Friday, November 16, 2007 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 5:36 AM
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
Post #423094
Posted Friday, November 16, 2007 10:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 15, 2011 7:34 AM
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.


Post #423131
Posted Friday, November 16, 2007 10:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 4:05 AM
Points: 2, Visits: 62
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
Post #423146
Posted Friday, November 16, 2007 5:15 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:09 PM
Points: 442, Visits: 531
Nicely done. Thanks.


There is no "i" in team, but idiot has two.
Post #423273
Posted Wednesday, April 16, 2008 10:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 4, 2014 2:07 PM
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)
Post #485799
Posted Thursday, July 30, 2009 6:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 8, 2010 8:32 AM
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.
Post #762861
Posted Wednesday, June 8, 2011 3:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:18 AM
Points: 1,180, Visits: 2,650
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

Post #1121591
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse