Cloud Database? NoSQL? Nah, just use CSVs and CsvHelper
KISS - Keep it Simple, Stupid. While I don't like calling people stupid, I do like to Keep it Super Simple!
I was talking to Jeff Fritz on my team about a new system we're architecting. I suggested CosmosDB or perhaps Azure Table Storage. Then we considered the amount of data we were storing (less than 100 megs) and Jeff said...let's just use CSV files and CsvHelper.
First I was shocked. SHOCKED I SAY.
Then I was offended
But finally I was hey...that's a good idea.
A fine idea in fact. Why use more moving parts than needed? Sure we could use XML or JSON, but for our project we decided rather than even bother with an admin site that we'd use Excel for administration! It edits CSV files nicely thank you very much.
Can you parse CSV files yourself? Sure, but it'll start getting complex as you move between data types, think about quotes, deal with headers, whitespace, encoding, dates, etc. CSV files can be MUCH more complex and subtle than you'd think. Really.
Here's what CsvHelper can do for you:
var csv = new CsvReader( textReader );
var records = csv.GetRecords<MyClass>();
Here you just get an array of some class - if your class's structure maps 1:1 with your CSV file. If not, you can map your class with a projection of the types in the CSV file.
public sealed class PersonMap : CsvClassMap<Person>
Map( m => m.Id );
Map( m => m.Name );
References<AddressMap>( m => m.Address );
public sealed class AddressMap : CsvClassMap<Address>
Map( m => m.Street );
Map( m => m.City );
Map( m => m.State );
Map( m => m.Zip );
And finally, just want to export a CSV from an Enumerable that mirrors what you want? Boom.
var csv = new CsvWriter( textWriter );
csv.WriteRecords( records );
Or do it manually if you like (hardcode some, pull from multiple sources, whatever):
var csv = new CsvWriter( textWriter );
foreach( var item in list )
csv.WriteField( "a" );
csv.WriteField( 2 );
csv.WriteField( true );
It won't replace SQL Server but it may just replace one-table SQLite's and "using a JSON file as a database" for some of your smaller projects. Check out CsvHelper's site and excellent docs here along with the CsvHelper GitHub here.
Sponsor: Check out JetBrains Rider: a new cross-platform .NET IDE. Edit, refactor, test and debug ASP.NET, .NET Framework, .NET Core, Xamarin or Unity applications. Learn more and download a 30-day trial!
Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.
Its ORM implementation is great, a better interface than CSVHelper IMO (read/write entire record, and very customable)
There is something wrong with your cetrificate. When I enter the page by using this URL: https://feeds.hanselman.com/~/456706218/0/scotthanselman~Cloud-Database-NoSQL-Nah-just-use-CSVs-and-CsvHelper.aspx then there is certificate warning (Could not verify identity feeds.hanselman.com) and the certificate is for *.feedblitz.com
Read the data using CsvHelper, process the stream of data using ordinary C# code and bulk load it into SQL Server.
The bulk copy interface doesn't work with IEnumerable<T> though and expects a data reader. The code in the file below converts back to an IDataReader.
I ask because I found this so interesting. KISS is the rule.
I use CSV when I'm forced to as people tend to open it in Excel and then break the formatting (e.g. adding commas at the end of lines) and they generally have no idea that it's a text file.
CsvHelper has indeed be quite helpful. We use it to parse data from MIMS (a medication information database in Australia and other places) for use in our software. They have a new set of files monthly so our app just parses the files as they come in, maps them over to useful objects and calls the job done.
But anyway, I agree we should KISS. Storing data in a CSV file is not that bad in some circumstances.
Yes, I assumed @Torleif was insinuating there was an issue from a security perspective though; my point was, how is it any less secure to store hashes in a CSV file than a database?
As to read/write/sync/etc, that's definitely something of value in databases. But yeah, that's why I specified "tiny". Not really an issue when the site will have maximum 30 users, and not really anything happening other than logging in and out.
CsvHelper is great, but I wish they would add Async variants for the methods already and stop walking around the issue. https://github.com/JoshClose/CsvHelper/issues/202
@Pato You could have an encoding issue with *any* type of file or data. Not just CSV.
@MarkAdamson Sounds like you're doing something wrong- Excel does not allow you to "break" CSV files by saving commas at the end of contents. It'll quote the contents if end your content with a comma, as it should. It would be a pretty $hitty spreadsheet application if it didn't work with CSV files. Is your file extension .csv?
One place CSV (and INI) fall down is any kind of hierarchy and references. As soon as you introduce those, you end up kludging together all sorts of monstrosities to make it work. For those purposes (and again, small-ish projects) I use JSON blobs.
Of course, as soon as data requirements get "large" (for some definition of that word), and I need indexing, multi-thread read/write updates, transactions, etc. it is time to move to a real database of some sort.
But for small-ish data requirements: zipped up CSVs and simple JSON blobs are a great choice.
I have to make an mappingclass just because I had two decimal fields, and also needed to turn all Fields to Properties?
Neither this blog, or the documentation states that one need to call AutoMap() before one map the single fields, I was hoping that the automap that CsvHelper creates automatically was used before applying my map..
But to handle other decimal separators an attribute would be nicer.. (atleast for my usecase)
Otherwise, super library.
And there is a catch: Many cultures use ',' as decimal separator. So be careful when you have columns containing floats.
Then I implemented a very tiny application to help me learn more vocabulary in German. I started coding and eventually I had to implement persistence, so, just a simple table in some DBMW and... hold on, why so complicated? Why just not a simple text file?
I did so and now I have a very tiny simple, fast application that can be easily installed anywhere :).
Comments are closed.