Spitting out reports from a database is quite a common task in web development these days. There are numerous ways to do this, and today we'll be taking a look at how to do this using LINQ and MVC 3. Returning a LINQ query as CSV is a pretty easy task and this tutorial will show you how to create an Extension Method so you can do this on any LINQ query.
1. First, we've got new MVC 3 web application (LinqCsvDemo) and a Class Library (AdventureWorksData) that will facilitate our data access. As per the name of our data project, we'll be using the Adventure Works database. For this demo, we'll be pulling a report on products that are loaded in the database.
2. Now, let's add a new class to the AdventureWorksData project and call it ProductReports.cs. In this class we'll add a single method called GetProductsReport with the following code:
This method just creates an AdventureWorksEntities object and queries the Products. We'll just grab a few bits of data for this demo, but this could be a very detailed product report, its all up to your needs. We return the query as an IQueryable.
3. Next, we'll add the View and Controller we'll be using. We'll call these ReportView and ReportController (why be fancy). For this very simple demo, there will be no need for a model as we're calling the AdventureWorksData project for our data directly and we'll be working with the IQueryable it returns. We'll also add an ExtensionMethods folder and add a class here called LinqAsCsv.
4. Now, we'll just do some quick housekeeping to get the demo running. First, we'll update the Global.asax to point to our Report controller as the default:
Next we'll update the ReportController Index method to return our ReportView (you could of course call your View 'Index' and be done with it, but I opted to name it something else....oh well):
Now, run this and you should get a white page with text on it saying ReportView (which is just the default text from view we created).
5. Next we'll flesh out the Extension Method. I've built this with 2 methods, one called 'AsCsvString' and one called 'AsCsvByteArray'. The reason for this is I wanted the user to have some options when using this extension method. 'AsCsvByteArray' is perfect if you want to return a CSV file that the user can download and start using. 'AsCsvString' is perfect if you need to do some other type of processing of the data, either way you have some options.
5a. First off, we'll change the namespace to System.Linq. The reason for this is, I want this extension method to feel as part of the other methods, so there will be no need to include using statements and so forth. Also, add 'static' to the class declaration.
5b. Next, we'll add the main method that does the processing, and a helper method that does some formatting:
The ProcessItems method is the main worker here. As you can see it takes in an IQueryable and returns a string. Since it accepts an IQueryable, it will work on any LINQ query. The string it returns is the delimited text which can be saved as a CSV.
First, we create a new StringBuilder object (you'll need to add a using System.Text to the class), as well as a PropertyInfo:
The items.ElementType.GetProperties() gets all the public properties for the IQueryable using reflection, and returns them as an Array. We'll use this to get all the data out of the query.
Now, we'll build our header row:
We use the awesome Join method for strings which "concatenates all the elements of a string array, using the specified separator between each element". For us, we'll use a pipe '|' for our delimiter and use a lamba to get all the property names and return those as an array (properties.Select(p => p.Name).ToArray()). Then, we add that to the StringBuilder using AppendLine and our FormatLine method. The FormatLine is simply a 'clean up' method that will escape quotes (and anything else you need to do for each row in your CSV, have fun with it).
We are also using a pipe '|' to get around the fact that the rows may already contain a comma ',', so the pipe is easier to work with in Excel (IMO).
Next we'll do the same thing for each item in our IQueryable:
Again, using the string.Join method and a Lambda, but this time we are getting the value of the property, rather than its name (properties.Select(p => p.GetValue(item, null)).ToArray()).
Finally, return the StringBuilder, and you're done:
5c. Now, we'll add the two methods that we can call off our query:
These are pretty straight forward. The 'AsCsvString' method simply returns the string result of the ProcessItems method. The 'AsCsvByteArray' returns the ProcessItems string as a byte for downloading.
6. Now we're ready to start wiring up our MVC app to download a CSV. First, add a reference to the AdventureWorksData project, and add a 'using AdventureWorksData' statement to your ReportController class. Also, add the connection string from the App.config in the AdventureWorksData project to your web.config in the MVC application.
7. In our ReportController, we'll add a new method called GetProductReport that will return the CSV:
This method creates a new ProductReport object and calls its GetProductsReport() method, which returns an IQueryable of the products we set up earlier. Next, it returns a File using the 'AsCsvByteArray' extension method, a "text/csv" file type, and a name, in this case "AdventureWorks_ProductReport.csv".
8. Finally, update the ReportView to include an ActionLink to get the data:
9. Now really the finally, run it, click the link and watch the magic happen.
Clicking the link, will give you a prompt to open or save the CSV. Open it and you'll see the data with the pipe delimiter.
Run this through the 'Text To Columns' in Excel, using a '|' as the delimiter and you'll get a nice neat XLS.
That's it, now you have a fancy new extesion method to return any IQueryable as a CSV.