I'm using ASP.NET Web API 2 (.NET 4.6.1) and AngularJS on the client side. My requirement is to export the tables data in either CSV or Excel format. I cannot use bcp or sqlcmd because of security reasons. Also, cannot use DTS package as the parameters will be coming from web UI and I need to pass it to stored procedure to retrieve the result and send back the file via api.
Currently, I'm trying to read all 500K records using entity framework 6 to IEnumerable type. My table has around 500K records with 40+ columns. I was trying to convert it to CSV and then return as plain text and open in browser as blob.
I dont' think it will work that fast, as we are looking at (roughly) 200MB dataset that you first pull from DB and then send it to client. You could try using DotNetZip library to zip the CSV before sneding to client...