Introducing C# Toolkit for loading adhoc csv files into a database: Dfloader

Have you ever needed a simple programmatic API in C# for loading islands of data from csv files into a SQL database? Well we have been asked to do this sort of thing on a couple of different projects. So we wrote a little toolkit to allows us to manage the process of uploading adhoc csv files into a set of highly normalized tables in SQL Server.

The toolkit is open source - please do check it out at http://dfloader.codeplex.com

Sql Server TRY..CATCH

The other day had to do a giant update script on Microsoft SQL Server (this works in 2005+). And was wondering if something happens in the middle, what would happen to the transaction wrapping the sea of inserts/updates/deletes. Would all of them get rolled back, or only some?

It turns out that if you naively (although it makes sense) try to do:

BEGIN TRANSACTION
insert(1)...
insert(2)... <-- error here
insert(3)...
COMMIT TRANSACTION

contrary to intuition, although insert(2) fails, and you would think the transaction gets aborted at this point, insert(3) still goes through. in order to rollback inserts 1 through 3, you would typically need to check for @@ERROR after each of the inserts, and do some sort of GOTO, which will let you jump to some error handling code section, which will perform ROLLBACK TRANSACTION.

Obviously this gets to be pretty, if not excruciatingly painful, to insert the check for @@ERROR after every single statement!

Thankfully there is a much cleaner way of doing this: TRY..CATCH

BEGIN TRANSACTION

BEGIN TRY

     insert(1)...

     insert(2)... ERROR

     insert(3)... -- this never happens

     insert(4)...

     etc...

     -- if we got here there were no errors above

     print 'Committing transaction...'

     COMMIT TRANSACTION

END TRY

BEGIN CATCH

     -- on insert(2) we drop through here - nice!

     print 'Rolling back transaction...'

    ROLLBACK TRANSACTION

END CATCH;

Hope you find this useful... sure better than copy/pasting IF @@ERROR <> 0 a hundred times!

Excel Substitute function

2 small things that make fairly regular data munging tasks a little easier:

Excel

When dealing with lots of adhoc data and relational databases, you want to be able to generate insert/update SQL statements in excel, based on some data in the spreadsheet.

Typically you will have some template SQL statement with placeholders for data that varies from row to row. Here is one approach to accomplishing this in Excel with relative ease.

Put your SQL template in cell A1. Lets say my template looks like this:

insert into some_table (one, two) values (@some_value, '@some_string_value')

The funny looking  @ denote placeholder where we want to insert our variable content.
This turns out to be fairly easy to do in Excel, if you use SUBSTITUTE() function.

Just use the template above with SUBSTITUTE:

=SUBSTITUTE(SUBSTITUTE($A$1, "@some_value", B1), "@some_string_value", B2)

Fun Visualization/Animation Javascript Links

1. Cool Animations: http://paperjs.org/examples/
2. Data Driven Documents: http://d3js.org/

Not your Fathers C++ (C++11 rocks)

from 2012 Lang.Next conference (a bunch of great video recordings of talks on various languages can be found after jump)

http://channel9.msdn.com/Events/Lang-NEXT/Lang-NEXT-2012/-Not-Your-Father-s-C-

Web conference software

Once in a while we have a need to do web conferences. This need does not come up very often; for most purposes we are quite happy with Skype. However if you have a real conference going on (ie multiple people), or you need to share your desktop with someone who does not have Skype installed a tool like Webex is quite handy, however it costs money. Being so very thrifty, we did a little searching and found this little nugget: http://vyew.com; this little guy has a quite capable free version of web conferencing. We were able to share a desktop quite nicely. The only thing you need installed on host/client PC is java; which most of us should have by now. Let's just hope it does not get bought and killed like http://dimdim.com

nuget

Discovery of the week dependency and package management for .net: NUGET

Actually using it on one of our WPF .NET 4 project, after 2 hours of using it so far looks looks pretty solid, with pretty good tooling support in VS 2010.

One caveat, the Package Manager Console is kind of neat, but seems to be inferior to nuget.exe (especially when creating new packages).

A very solid, newer (may 2011) in depth video on use and features of nuget get is on channel 9