A follow up to DataTable To List
After my last post, I got a lot of good advice on Reddit, mostly on better ways of handling my SQL data. The main point people were bringing up was using ORMs. Now, I’ve heard of the major ORMs, mostly the Entity framework I guess, but I’ve never met or spoken to anyone who’s particularly excited about. I’ve heard a lot of “Good idea, bad execution”. Now I’m not sure if that’s accurate or fair, but that’s what I’ve heard.
Most of the advice I was receiving was to use something called Dapper. After a little research I realized that Dapper was written (or at least co-written) by the same Marc Gravell that wrote FastMember (the project that sped up my reflection code). Quite a coincidence I’d say, I guess this guy knows what he’s doing in C#.
Anyway, I updated my benchmark code to compare what I was doing to using Dapper. Since Dapper actually handles the execution of the command as well as returning it to a type, I had to change my tests a little bit, so it looks like everything’s slower compared to last time, but in this test I’m timing the SQL query and converting to class.
The query I’m using in Dapper is essentially this
TestTable testData = connection.Query<TestTable>(new CommandDefinition("SELECT TOP 5000 * FROM TestTable));
If you take a look on the Github page for this project you’ll see a few other examples of how this works, but this is the simplest case.
I tested this against reading the command into a SqlDataReader and parsing (something a lot of people were recommending I do instead of converting to a DataTable).
Here’s the results –
Now, I don’t know exactly how accurate this is, my benchmarking is pretty sloppy overall (They have more benchmarking on the project site, so feel free to dig a little deeper), but Dapper was faster, or at least essentially the same speed. Add to that the fact that it’s way easier to use than writing your own custom SqlDataReader parsing method for each stored procedure in your project and Dapper gets the win by a mile.
All I know if that I’m going to be giving a real shot on Monday when I get back to work and probably recommending it to the rest of the guys in my department.