DevDisasters

Keep It Simple

Simple problems are often solved by simple solutions. Other times, simple problems are solved with an end user running a Web site from Visual Studio on their machine.

• By Mark Bowytz
• 08/20/2014

As the newly hired guy on a development team at the university, Jay was handed a pile of work that had queued up since the previous individual in his role had suddenly departed.

His first inherited application seemed to be a relatively simple affair: an in-house written add-on to the university payroll system to load municipality tax code. The way the process worked was that every few weeks or so, the university would receive one or more .csv files that included details of how much needed to be deducted for every employee. That data needed to be inserted into the database from an external tax service that handled all the messy local income tax details the university staff wanted to avoid.

Ordinarily, it's only a few rows in response to a single university employee's move to a different area of the city, but when it came to a quarterly full-on, all-employee refresh, the file could get fairly large -- upward of 100,000 records because the relationship was one employee to several rows of data. To add to the fun, Jay learned from the payroll system users that they expected every data load would require some degree or another of special attention to get the data fed into the database properly.

The payroll group found this extra manual work to be a huge pain and it was Jay's job to relieve that pain.

Not So Simple After All?
Going through the (curiously named) "database inserter" project code, he ran across this little beauty:

// This method is needed 
public static DataTable ReadCsvFile() {
  return readCsvFile(GetOutputFileNameFromGuid(HttpContext.Current.Request.QueryString["guid"])); 
}

"Wha …? GetOutputFileNameFromGuid? Where does a GUID come into this?," Jay wondered aloud, "Files are sent as Year-Month-UNIVERSITYPAYROLL.CSV ... OK, I'm officially confused."

Jay halted his investigation. If he was going to be able to really dig into this problem, he'd have to see how this data load was done -- straight from the source: the users.

This Is How We Do It ...
Jay made his way through the cube farm that made up the administrative services of the university and found Anne -- his user contact for the project.

He readied his notebook and pen, eager to take notes on Anne's process for doing the municipal tax data load. Anne, who was either excited to explain her process (or just really over-caffeinated), jumped into explaining her process rapid fire.

"Oh yeah. It's pretty simple, actually. First, I open up Visual Studio and once I pull down the latest version of the 'database inserter' project files from TFS, I run the page in debug mode. Next, I get a GUID from this site http://www.guidmaker.com/, put the municipal tax file in the directory for the 'database inserter' project, rename it to that GUID I got with a .csv and add 'guid=' with that GUID to the URL in Internet Explorer. Finally, I refresh the page, and let it chew on the file. Sometimes it takes five minutes, sometimes a couple hours -- really, it all depends on how many records there are. And, if there's a badly formatted record with an extra comma or something, it'll get hung. And let me tell you, that's a real pain! We won't find out until the next morning!"

Jay felt shell-shocked. In fact, he didn't make it past writing "1." on his page. "So ... y-you're a developer?"

Anne chuckled. "Ha! Yeah, I wish! Then I could make the real big bucks! Nah, the guy before you trained us all in how to do the data load like this ages ago until he could get the kinks worked out ... which I'm guessing is up to you now!"

Jay nervously chuckled in reply, "Ha! Yep, looks that way!"

The meeting wrapped up soon after and Jay navigated his way out of the cube maze and made it back to his desk. On his way, he was able to think about the process, the code, and everything that Anne had gotten used to doing and took the next logical step: He created a brand-new project in the payroll code branch aptly named, "Municipal Tax CSV Loader," and had a working solution by the end of the day, integrated into the already-existing payroll Web site, ready to test.

Anne was right -- it was pretty simple after all!