Generate C# class code for table

  • Comments posted to this topic are about the item Generate C# class code for table

  • Great Article.

  • 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......

  • 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

  • I guess it would be easy to add the XML comments required for the public properties to the script. That would make it perfect.

  • 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

  • Nicely done. Thanks.

    There is no "i" in team, but idiot has two.
  • 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)

  • 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.

  • Good stuff.

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

  • Even simpler with Auto-Implemented Properties in C# 3.0 and later you don't need the private variable.

    See http://msdn.microsoft.com/en-us/library/bb384054.aspx

  • Codesmith http://www.codesmithtools.com may be a little easier.

    We use MyGeneration http://www.mygenerationsoftware.com.

    It is a little dated, but the templates allow scripting in vbscript, VB.Net, or C#. You can also build forms to pick and choose options for your class as you need them.

    "There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker

  • nice bit of code, always like being able to have different ways to do stuff. I made my own c# program about a year ago and I use it regularly. It also creates the stored proc to go with the class. Everyone/anyone feel free to download and check it out here: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=8681&lngWId=10

  • Free tool in this link

    Generates three tier classes and sql CRUD scripts

    https://skydrive.live.com/#cid=ACCFC2483F700A04&id=ACCFC2483F700A04%2111751

    or

    http://rapidshare.com/files/2096091764/ClassCreator%201.0.zip

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply