Skip to content

Instantly share code, notes, and snippets.

@dtsao

dtsao/DapperHelper.cs

Last active July 30, 2018 03:37
Show Gist options
  • Save dtsao/7f7b3237d5e065778b4f7408a77eb8ff to your computer and use it in GitHub Desktop.
Save dtsao/7f7b3237d5e065778b4f7408a77eb8ff to your computer and use it in GitHub Desktop.
Download ZIP
NancyFx export Dapper dynamic query results to CSV using CsvHelper
Raw
DapperHelper.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
namespace nancyfx_dapper_servicestack_text_csvhelper
{
public class DapperHelper
{
public static string DapperQueryToCsvString(IEnumerable<dynamic> dapperQueryResults)
{
if (isEmpty(dapperQueryResults)) return string.Empty;
// results not empty
StringWriter sw = new StringWriter(); // defaults to System.Text.UnicodeEncoding
DapperQueryToTextWriter(dapperQueryResults, (TextWriter)sw);
return sw.ToString();
}
public static Stream DapperQueryToCsvStream(IEnumerable<dynamic> dapperQueryResults)
{
if (isEmpty(dapperQueryResults)) return new MemoryStream();
// results not empty
MemoryStream ms = new MemoryStream();
StreamWriter sw = new StreamWriter(ms, Encoding.UTF8);
DapperQueryToTextWriter(dapperQueryResults, (TextWriter)sw);
sw.Flush();
return ms;
}
private static void DapperQueryToTextWriter(IEnumerable<dynamic> dapperQueryResults, TextWriter tw)
{
var csv = new CsvHelper.CsvWriter(tw);
var dapperRows = dapperQueryResults.Cast<IDictionary<string, object>>().ToList();
bool headerWritten = false;
foreach (IDictionary<string, object> row in dapperRows)
{
if (!headerWritten)
{
foreach (KeyValuePair<string, object> item in row)
{
csv.WriteField(item.Key);
}
csv.NextRecord();
headerWritten = true;
}
foreach (KeyValuePair<string, object> item in row)
{
csv.WriteField(item.Value);
}
csv.NextRecord();
}
csv.Flush();
}
public static bool isEmpty(IEnumerable<dynamic> queryResults)
{
if (queryResults != null && queryResults.GetEnumerator().MoveNext())
{
return false;
}
else
{
return true;
}
}
//// change StringWriter encoding to UTF-8 https://stackoverflow.com/a/1564727
//public sealed class Utf8StringWriter : StringWriter
//{
// public override Encoding Encoding => Encoding.UTF8;
//}
}
}
Raw
packages.json
<?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="CsvHelper" version="4.0.2" targetFramework="net461" />
<package id="Dapper" version="1.50.2" targetFramework="net461" />
<package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="1.0.7" targetFramework="net461" />
<package id="Microsoft.Net.Compilers" version="2.1.0" targetFramework="net461" developmentDependency="true" />
<package id="Nancy" version="1.4.1" targetFramework="net461" />
<package id="Nancy.Hosting.Aspnet" version="1.4.1" targetFramework="net461" />
<package id="Nancy.Serialization.ServiceStack" version="1.4.1" targetFramework="net461" />
<package id="ServiceStack.Text" version="3.9.11" targetFramework="net461" />
</packages>
Raw
TestModule.cs
using Dapper;
using Nancy;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
namespace nancyfx_dapper_servicestack_text_csvhelper
{
public class TestModule : NancyModule
{
public TestModule(SqlConnection conn) : base("/api")
{
Get["Suppliers"] = _ =>
{
var results = conn.Query("select top 15 * from Suppliers");
if (DapperHelper.isEmpty(results))
return HttpStatusCode.NotFound;
else
return Response.AsJson(results);
//http://localhost:50416/api/Suppliers resulting json has special characters encoded correctly
};
Get["SuppliersCsv"] = _ =>
{
var results = conn.Query("select top 15 * from Suppliers");
if (DapperHelper.isEmpty(results))
return HttpStatusCode.NotFound;
else
return Response.AsText(DapperHelper.DapperQueryToCsvString(results));
//http://localhost:50416/api/SuppliersCsv special characters are displayed incorrectly in browser
};
Get["SuppliersCsv1"] = _ =>
{
var results = conn.Query("select top 15 * from Suppliers");
if (DapperHelper.isEmpty(results))
return HttpStatusCode.NotFound;
else
return Response.AsText(DapperHelper.DapperQueryToCsvString(results), "text/html");
//http://localhost:50416/api/SuppliersCsv1 special characters are displayed incorrectly in browser
};
Get["SuppliersCsv2"] = _ =>
{
var results = conn.Query("select top 15 * from Suppliers");
if (DapperHelper.isEmpty(results))
return HttpStatusCode.NotFound;
else
return Response.AsText(DapperHelper.DapperQueryToCsvString(results), "text/html;charset=utf-8");
//http://localhost:50416/api/SuppliersCsv2 special characters are displayed correctly in browser
};
Get["SuppliersCsv3"] = _ =>
{
var results = conn.Query("select top 15 * from Suppliers");
if (DapperHelper.isEmpty(results))
return HttpStatusCode.NotFound;
else
return Response.AsText(DapperHelper.DapperQueryToCsvString(results), "text/html;charset=utf-8")
.WithHeader("Content-disposition", "attachment;filename=Export3.csv");
//http://localhost:50416/api/SuppliersCsv3 browser downloads Export.csv attachment. Excel displays special characters incorrectly.
};
Get["SuppliersCsv4"] = _ =>
{
var results = conn.Query("select top 15 * from Suppliers");
if (DapperHelper.isEmpty(results))
return HttpStatusCode.NotFound;
else
{
Stream stream = DapperHelper.DapperQueryToCsvStream(results);
stream.Position = 0;
return Response.FromStream(stream, "text/html;charset=utf-8");
//http://localhost:50416/api/SuppliersCsv4 browser displays special characters correctly.
}
};
Get["SuppliersCsv5"] = _ =>
{
var results = conn.Query("select top 15 * from Suppliers");
if (DapperHelper.isEmpty(results))
return HttpStatusCode.NotFound;
else
{
Stream stream = DapperHelper.DapperQueryToCsvStream(results);
stream.Position = 0;
return Response.FromStream(stream, "text/html;charset=utf-8")
.WithHeader("Content-disposition", "attachment;filename=Export5.csv");
//http://localhost:50416/api/SuppliersCsv5 browser download Export.csv attachement. Excel displays special characters correctly since file has the UTF8 BOM: EF BB BF
//https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/
//https://superuser.com/questions/1204233/excel-save-behaviour-of-csv-file-with-utf8-encoding-vs-utf8-bom-encoding
}
};
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment