Born to Thrive

I truly believe that probably every developer has developed some application that had to work with files. Applications use files for logging, for storing data or for reading data. A few years ago it was more common to store data than it is nowadays. Now developers tend to store everything in a relational database, but nevertheless from time to time a developer has a file with some data that he or she needs to process. It might be a file with some macroeconomic data, or a file with data ready for migration, or a log file for analysis, etc. So most common approach when working with a text file is to write a console application that uses System.IO classes to read a file line by line and parse each line into columns and then proceed according to requirements. This solution is very powerful, yet any change requires recompiling a code and there is almost no reusability - you can't use your method that finds all names within a file that starts with "N" to also find a sum of all expenses within other file, so a developer ends up writing 2 separate methods. Of course, you can import that data into a database and run a SQL query to get your results but that's not always feasible - probably you wouldn't want to import huge log files to database to get a number of null argument exceptions in your application within a range of dates. All you need is a small app that will open a file, scan each log within a given date range, find all argument null exceptions and make a count. You don't want to import the logs into a table and then probably apply some index and then finally run a select statement. Wouldn't it be ideal to be able to run the same select command against a text file without loading it to a database? And that's where data providers like ODBC and Microsoft OLE DB come in handy (working with those providers by the way is one of the exam requirements). Let's do a simple application that will join data from two text files (1 with loan information and 1 with payments) and will show each loan account number with the sum of all payments and the output will be sorted ascending by total payment amount. Also we'll see how to output account numbers of delinquent loans with the date of their last payment. Please note that I put queries in app.config to avoid hard-coding them, but for simplicity I put them in appSettings section whereas normally you would want to create a custom config section in which you would define a query text, parameters, etc. The QueryProvider is a static class that helps reads information from the application configuration file, and class TextFileQuerer is the class that queries the text files and outputs the information as specified.
Text files:

[App.config]

[QueryProvider.cs]

[TextFileQuerer.cs]

[Program.cs]

Here is the application in action:

And it outputs what we wanted with only a few lines of code and using SQL commands that most developers are familiar with and without really hard-coding the logic and without putting a lot of code that would take a line and parse it into columns and track column names and types, etc. Really flexible and powerful solution. I used OleDb but we could also use ODBC provider. Unfortunately, there are some limitations of this solution (or rather the aforementioned text file query providers) like JOIN keyword is not being recognized so you can't do LEFT JOIN. However, for many text-file based operations the text file query providers can provide a very quick and flexible solution that will allow to save a lot of time spent on parsing, writing objects to hold temporary results, hardcoding logic, etc. I will the best way to see the power of text file query providers would be by trying to write the SumOfAllPayments method without using the query, and comparing the total amount of time spent on writing both solutions.