Generate C# class code for table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Cade Bryant » Generate C# class code for table

15 posts, Page 1 of 2

1 2 »»

Generate C# class code for table

Rate Topic

Display Mode

Topic Options

Author

Message

cadebryant

Posted Friday, November 09, 2007 11:35 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, November 05, 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

vblllove

Posted Friday, November 16, 2007 2:58 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, August 04, 2011 3:46 PM
Points: 230, Visits: 196

Great Article.

Post #422950

kma-495907

Posted Friday, November 16, 2007 3:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, August 26, 2012 4:21 PM
Points: 1, Visits: 33

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

Steve S. Yang

Posted Friday, November 16, 2007 8:42 AM

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

Erzsebet Szabo

Posted Friday, November 16, 2007 10:00 AM

Valued 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

Fazio Maurizio

Posted Friday, November 16, 2007 10:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 03, 2013 8:24 AM
Points: 2, Visits: 46

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

Dave-148053

Posted Friday, November 16, 2007 5:15 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, September 14, 2012 11:26 AM
Points: 442, Visits: 526

Nicely done. Thanks.

There is no "i" in team, but idiot has two.

Post #423273

TicketNetwork Engineer

Posted Wednesday, April 16, 2008 10:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 8:50 AM
Points: 6, Visits: 153

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

swingnchad1

Posted Thursday, July 30, 2009 6:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 08, 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

nigel.

Posted Wednesday, June 08, 2011 3:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:57 AM
Points: 1,163, Visits: 2,600

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 »

15 posts, Page 1 of 2

1 2 »»

Permissions