GNR8 - Simple C# Class Generator

• Download demo project - 803.82 KB
• Download source - 981.09 KB

Introduction

GNR8 (pardon the txt-spk name - it just sprang to mind) is a simple C# WinForms application that connects to an SQL Server database, lists all of the tables and then allows the user to select one to all of those tables and generate classes based on a customisable template.

Background

As I'm sure is the case with many similar applications, I created this one because I'm inherently lazy: I don't want to have to keep producing a basic class that begins its existence almost identically to every other class that I produce to handle tables.

I should also point out that there is another code generator recently added by hdv212 (Database Helper v 1.0.0) which does a not dissimilar job but produces more output than I would normally require and in a rather different fashion. That is not to detract from it in any way.

Using GNR8

There are three distinct parts to GNR8: the first two of which are required, the third is entirely optional.

To begin with, the application has to be connected to a database so as to list the available tables for processing.

Note that all of the choices will be stored for use the next time you start the program.

The code required to do this is fairly straightforward and various examples can be found freely all over the interweb; however the following will return a DataTable of available servers (and can be found in Connect.cs at line 192).

using System.Data.Sql;

DataTable servers = SqlDataSourceEnumerator.Instance.GetDataSources();

I can then add the resultant data to a DropDownList and select the one I wish to query. Curiously not all available servers do get listed but you can type in the name of the server of interest and, if available, it will connect. Further, local servers don't (always?) get listed but you can skip to the instance name and any connected local databases will get listed.

The Initial Catalog DropDownList is driven by: (this can be found in Connect.cs at line 241)

using SQLDMO;

SQLServerClass server = new SQLServerClass();

This is derived from an interop assembly, SQLDMO (supplied), which, having established a connection, allows us to interrogate the server and list all of the available catalogs. They are added to a collection which then acts as the DataSource for the list.

Collection<string> catalogs = new Collection<string>();

// Get the objects.
foreach (Database2 db in server.Databases)
{
    if (!db.SystemObject)
    {
        catalogs.Add(db.Name);
    }
}

// Add all of the found objects to the combo.
this.cboCatalog.DataSource = catalogs;

Note that you can type in a known database name rather than search and then either Test the connection (which leaves you at this dialog) or select OK and, provided that the connection details are correct, this dialog will close and the main dialog (as per the opening image above) will be displayed.

Note that the Connect dialog defaults to using Integrated Security (Trusted Connection). Naturally, if you need to supply a user id and password instead, uncheck this and enter the requisite details.

Back on the main screen and, for the moment, ignoring the rather mundane toolbar icons, the next thing is to select one or more tables and press the 'Process' button which results in the following screen:

You do not need to enter a namespace or creator name; neither do you need to select an output (Save To) folder as the path to the EXE file will be used if left untouched: your choices will be saved (in the application config file) for next use.

At this point, press the 'Generate' button and, provided the template file is found (see next) your classes will be created, saved and ready to use; if not you will be prompted to stop and create a template file prior to the screen loading.

GNR8 relies on a template containing text and tokens to work. It can consist of any text you require; GNR8 isn't really interested in what is in the file, only the tokens which it finds and replaces either with pre-determined data (for the built in tokens) or with data that you supply against custom tokens.

The following is a sample from the supplied template showing what might be inside a template file. As I said, it can comprise of any text you deem appropriate and GNR8 will ignore anything that is not wrapped in curly braces. These are the tokens that will be replaced with other text.

#region Construction
/// <summary>
/// Initializes a new (no-args) instance of the {OBJECTNAME} class.
/// </summary>
public {OBJECTNAME}()
{
}

/// <summary>
/// Initializes a new instance of the {OBJECTNAME} class.
/// </summary>
public {OBJECTNAME}({CTORPARAMETERS})
{
    {CTORBODY}
}
#endregion

In this example, there are a number of default tokens such as {OBJECTNAME} and GNR8 will replace this with the table name. Below is the full list (accessible from one of the fabulous toolbar buttons) with token name and meaning. Whilst here they are immutable, feel free to alter the code to suit.

The program now parses each selected table and produces a class file using the available information supplied. For each object, a ProcessObjects object (sue me: I have no imagination) is instantiated and takes care of producing a class for the table.

I have chosen the path of least resistance to parse and replace my tokens. Assuming that a valid ProcessObject is created, we cut to the chase:

internal void ProcessObject()
{
    // Sets Ctor detail and parameters.
    this.CreateConstructor();

    // Process Custom tokens first.
    this.CustomTokens();

    // Process default Tokens.
    this.DefaultTokens();

    // Save to disk.
    File.WriteAllText(this.FullPath, this.TEMPLATE, Encoding.Default);
}

This breaks down the process into logical and sequential units and I'll come to the CustomTokens a little later.

CreateConstructor queries the table to retrieve a list of column names used to create a parameter/signature list for the constructors and does so using the following which returns a DataTable populated with table information.

OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader(CommandBehavior.KeyInfo);
DataTable dataTable = oleDbDataReader.GetSchemaTable();

A simple loop through the rows and columns extracts the names which are used to build the lists: we are seeking both the name and DataType which we get with:

foreach (DataRow dataRow in dataTable.Rows)
{
    // Get the name of the column.
    string name = dataRow[0].ToString();

    foreach (DataColumn dataColumn in dataTable.Columns)
    {
        // Contains the actual data type.
        string column =
            dataRow[dataColumn]
                .ToString()
                .Replace("System.", string.Empty);

        if (dataColumn.ColumnName == "DataType")
        {
            parameters += column + " " + name + ", ";
            ctor += "this." + name + " = " + name + ";" + Environment.NewLine;
        }
    }
}

The main meat and potatoes of the whole kit and caboodle is, irritatingly enough and in contrast to the rest, a snap...

private void DefaultTokens()
{
    this.TEMPLATE =
        this.TEMPLATE
            .Replace(Template.AUTHOR, this.Creator)
            .Replace(Template.CTORBODY, this.CtorDetail)
            .Replace(Template.CTORPARAMETERS, this.CtorPrameters)
            .Replace(Template.DATECREATED, DateTime.Today.ToShortDateString())
            .Replace(Template.NAMESPACE, this.NameSpaceName)
            .Replace(Template.OBJECTNAME, this.ObjectName)
            .Replace(Template.PRIMARYKEY, this.PrimaryKey())
            .Replace(Template.PROPERTIES, CreateProperties());
}

Okay; so when we instantiate the class, we read the template in as a string in one hit (that's all it is and is easy to deal with) with:

this.TEMPLATE = File.ReadAllText(templatePath);

We then simply replace tokens with other text. "Template.AUTHOR" (et al) are resource strings that contain the name of the token to be replaced and the properties contain other data passed across to replace the tokens with.

Although I am sure that there are other ways of doing this, I liked the idea of using string.Replace to do all of the work and being able to do it, essentially, with one line of code (ignore the fact that I've broken it over a number of lines for readability).

Finally, we write the text out to the file name given in the process dialog. And then rinse and repeat for each table.

It's Almost Over...

That's it: it's one of those tools you are most likely to use the first time you create a database or because you have to add a table when the business sponsor says 2 days before go-live, "Oh, did I forget to mention?"

CUSTOM TOKENS: The Optional Bit

One of the features I decided to build in was the ability to add your own tokens to the mix. You can call these whatever you like and populate them with whatever you like. They are all saved into an XML file and parsed in CustomTokens. I built a helper class to parse the file and one of the things I like about XML is that there are about a gazillion ways to do everything. Still, what I have got works and looks like:

Since this is optional functionality, I'll leave it to you to peruse and dissect the code. Suffice it to say that you can add/edit and delete entries which are displayed in a DataGridView populated by a DataTable derived from the XML data.

DataSet set = new DataSet();

// Read the file.
set.ReadXml(FullPath_To_XML_File);

// Make sure that the document contains some data!
if (set.Tables.Count != 0)
{
    // Get the table.
    table = set.Tables[0];
}

return table;

The code above shows why dealing with XML is such a snap. The table is passed to the grid with a static method via the Helper class:

DataGridView.DataSource = XmlHelper.Load();

Whilst the XmlHelper class is, essentially, designed for dealing only with key-value pairs pertinent to GNR8, I'm sure it could be adapted to other uses and feel free to do so.

The Toolbar: Attractive, Huh?

Left to right
Connect:	Display the connection dialog to pick a new server, catalog or both.
Refresh:	Refresh the current view.
Generate:	Create a new Template.txt file in the application path.
Custom:	Display the custom tokens dialog.
Default:	Display the default tokens.
About:	Yup, the ubiquitous About box.
Exit:	Close GNR8.

Property Generation

Just a note about the property snippets that GNR8 produces at present; they are of the short form...

public virtual TYPE Foo { get; set; }

... where TYPE would be the specific type.

Conclusion

GNR8, as stated way up at the top (and a long time ago), is a simple app for a simple job: what I wanted was a quik'n'dirty app that would create a set of starter classes to save me having to type in all those pesky property snippets: I'm happy to churn out differing stored procedures (aren't we all?) and other gubbins but want the basic table handler done and dusted. The template I've supplied (and that can be generated from one of my pretty toolbar buttons) is an example only: you should create your own.

History

Version 1.0 - 4^th July, 2008

This is very much a first release: I can already see where improvements can be made and I will implement them as time allows. If you do find areas that you improve/change/fix, please let me know and I will update here and acknowledge your contribution.

Some of the changes would be the ability to select the output language (dare I say VB? apparently, yes I do) or specify and use, for instance, an Oracle or MySql server and to specify different templates. Ideas, on a postcard, please...

Copyright © 2008, Mark Merrens.