Create Data Classes

• Download source - 340 KB

Introduction

This is a C# class generator which will create a class file to enable reading/writing of a database in Access, SQLite, or XML format. It is intended to take the tedium out of creating such files which are pretty much boilerplate except for the field names and types. It can read in a schema in any of the above formats, allow you to edit the information, and then given a bit more information, create the C# class file. It also works in the reverse direction, allowing you to create a table in a new or existing Access or SQLite database, or a schema file for XML. Once the information is entered, you can save it as a project to make future changes easy. Projects can be saved in either Access or SQLite format - the Access 2007 runtime is available free from Microsoft and the SQLite .NET DLL is available from SourceForge (details are given in the included notes).

Before you start using the program

If you have only read this far and decided to try the program, please, please, read the file "BEFORE YOU START.txt" in the docs folder - the program needs to know where to find its database before it can work! There are other files in that folder as well that are worth reading.

Functions

• You can create the C# class files to read/write classic ADO (ADODB), ADO.NET, SQLite, and XML databases.
• You can read in a table schema from an existing Access, SQLite, or XML database.
• You can create an Access, SQLite, or XML table and database.
• You can save additional fields (e.g., calculated fields such as a List<T> from a CSV field stored in the database) with a project.
• You can save additional functions (i.e., functions in addition to the basic functions needed to read/write data) with a project.
• You can edit and save 'using directives' for each project.
• You can produce 'standard' prototype functions like clearing a form, copying a record, deleting a record, saving a record etc. These assume some standard naming of toolstrip labels and text boxes like tslblRecordNumber, txtHomeAddress, etc.
• It includes a 'Database Explorer' so you can examine the structure of tables in a database or XML schema.
• You can include user defined types in your fields as long as they can be converted to/from a recognised data type.
• You can save the data you enter as a project, so you can re-load it at a later stage to extend/change it.

How does it work?

To be able to read in data from one type of schema and write it to another needs a common data type. After trying to find something perfect, I ended up using the System.Data.DbType enumeration. There is a table included with the project which contains the enumeration member names, and each has been given a number called TypeID. As long as you can find the TypeID, you can look up the corresponding data types using that TypeID with a high level of accuracy.

Finding the correct TypeID is obviously core to the process.

The OleDbDataReader used with Access has a field called 'ProviderType' which maps to ADO constants like adSmallInt, adInteger, adSingle, and so on. This means that reading an Access table schema gives enough information to be very accurate about the type of data in a field. There is a table included with the project which maps the Access ProviderType to TypeID.

Less information is provided when reading an XML schema but XML databases only use standard system types. SQLiteDataReader provides the standard system types as well, so for both XML and SQlite, I have set up a table which maps system types to the TypeID.

If you read in an XML or SQLite schema and want to use it to create an Access table, then you will need to fine tune the data types in the Field Editor, which allows you to choose the data type by the System.Data.DbType enumeration.

Getting the fields into the program

For a program like this to be of any use, it has to be as easy as possible to get the fields into it.

If you have an existing Access, SQLite, or XML table/schema, you can read that straight into the program. If you have an existing C# class file, you can use the 'Import Fields List' form to convert a list of fields from that file. You can also import fields set out in a list consisting of the DB Type enum, then a space, then the field name, or a list with the field name first and the DB Type enum second.

Finally, you can enter the fields manually in the 'Import Fields List' form which includes a 'Quick Input' section with a combo box containing the DB Type enums and a textbox for the field name, or enter them one by one using the Field Editor.

The full details are given in a text file included with the project.

Class file produced

If we start with a class called JTestData, the program will produce a single class file called JTestData.cs containing:

• The JTestData class - this also includes a region for additional fields, so if you keep a CSV list of data in the database as a string, you can add a function in this region to convert it to a List<T>.
• JTestIndexData - a lightweight index class consisting, by default, the Key Field and the string override, although you can include other fields if you wish. This allows you to get a List<JTestIndexData> to use in a listview or combobox, allowing users to select records easily without having to read in the whole database.
• JTestUpdater - a static class which reads/writes the database and has a region Additional Functions, where you can put additional functions beyond the standard functions required to read/write the database.

Additional fields and functions are saved with the project so they are not lost when you re-create the class file.

When you first open the produced C# class in Visual Studio, it won't be formatted very well. Press Ctrl+K+D (thanks to Frank D for that shortcut) and it should be formatted in the way Visual Studio normally formats your files. There will be a few "TODO" comments in the C# source. Just check these and make sure they represent what you want.

Code example

The table tblDataTypes (referred to above) also includes the following fields:

• ADOConversionPrefix
• ADOConversionSuffix
• SQLiteConversionPrefix
• SQLiteConversionSuffix
• SQLiteUpdateSuffix

For user defined types, the prefixes/suffixes are stored with the fields rather than in tblDataTypes. When creating the class file, the program looks up these prefixes/suffixes and inserts them in the code as follows:

Reading data:

testDataADONET.JDate = Convert.ToDateTime(dataRow["JDate"]);

That is quite straightforward, it converts the object it reads from the database directly into DateTime.

testDataSQLite.JDate = DateTime.FromBinary(Convert.ToInt64(dataRow["JDate"]));
testDataSQLite.JBoolean = Convert.ToInt32(dataRow["JBoolean"]) > 0;

A little more complex, SQLite has a limited number of data types so the DateTime is stored as an INTEGER and then the program uses the .NET function DateTime.FromBinary to convert the 64 bit integer to a DateTime. Bool is also stored as an INTEGER, with false being represented by zero and true being anything else.

And a user-defined type in ADO.NET:

testDataADONET.JUserDefined = 
  (JCommon.ClassDataTypes)Convert.ToInt32(dataRow["JUserDefined"]);

This user defined type is actually an enum used in the program, which is stored as an Int32 in the database and converted to the user defined type when it is read from the database.

Writing data:

commandInsert.Parameters.Add(new OleDbParameter("@JDate",testDataADONET.JDate));

commandInsert.Parameters.Add(new SQLiteParameter(
                             "@JDate",testDataSQLite.JDate.ToBinary()));
commandInsert.Parameters.Add(new SQLiteParameter("@JBoolean", 
                             testDataSQLite.JBoolean ? 1 : 0));

As you can see, we use a .NET function to convert a DateTime to INTEGER for SQLite. Since SQLite has no boolean data type, we enter 1 for true and zero for false.

And a user-defined type in ADO.NET:

commandInsert.Parameters.Add(new OleDbParameter("@JUserDefined",
                            (Int32)testDataADONET.JUserDefined));

This converts the user defined type to an Int32 to store in the database.

The update commands are produced in the same way.

As you can see, the program uses the conversion prefixes/suffixes stored in tblDataTypes to create the commands. There is a function to add/edit data types in the program so additional types can be added or the prefixes/suffixes updated if needed. It is probably not a good idea to delete data types as they may be needed by your individual projects.

For user defined types, the conversion prefixes/suffixes are stored with the field data rather than in tblDataTypes.

Background

I wrote this program a couple of years ago to help me convert Access databases into XML and it has grown from there. I don't use XML for databases any more, but I do write quite a lot of programs that need to read/write databases and I wanted a quick way to create the necessary class files.

In earlier versions, I kept the TypeID for each field in the Tag of the ListViewItem but this was a bit flaky. From version 3 onwards, each field is saved as a JFieldsData in the project database, and is linked back to the saved project by the Parent Record Number. Until a project is saved, the Parent Record Number is not known so is zero. When a project is saved, this is updated to the actual Parent Record Number. If you don't save the project, those records with a Parent Record Number of zero will be deleted when you exit the program.

I have worked hard to iron out bugs from this latest version, and I have updated those sections that use hard coded fields like Record Number so they now use the Key Field. The program now properly honors the scope of the fields set for each project.

I have been using the program extensively and have tried to ensure I have fixed the problems that have come to light, but most programs only break when other people start using them so I would appreciate any feedback!

There is a test process built into the program which I have worked through (see Test Process.txt in the Tests folder) without problems.

It was suggested (by BigJim61) that I consider using CodeDOM and I have looked at that. While it would be quite useful for creating the basic class file, when it comes to the updater, I ended up just using code snippets so I didn't see much advantage of using CodeDOM over the WriteLine process I have used. I will have another look in the future, though, as it does look interesting.

I did keep the program's database in LocalUserAppDataPath in versions up to version 3 to make it Vista/Win7 friendly. However, after a near catastrophe with a hard drive, I have now put it under the project folder. I don't keep any data on my 'C:' drive and, knowing this, I don't back it up - you can probably work out the rest of the story!

History

• 19^th December, 2006 - First version.
• 21^st November, 2007 - First update.
• 6^th January, 2011 - Quite a major update to version 2.
• 2^nd February, 2011 - Again a major update to version 3.
• It now provides full support for SQLite.
• You can now declare a user defined field type - as long as they can be converted to one of the DB Enum types.
• There is an option to preview the class file.
• The data format for saved projects has changed substantially. I previously used a comma separated list for fields, but the addition of SQLite and user defined fields needed more information and something more robust. It also facilitates the addition of other databases, perhaps SQL, in the future. There is a function to convert saved projects from the old format to the new in the program.
• 1^st March, 2011 - Another major update to version 4.
• Individual forms are used for user input rather than pages on the main form.
• Hard coded field names weeded out.
• Class scope properly honored.
• Intensive bug testing.