Wednesday, May 11, 2011

Return any LINQ query as a CSV using ASP.NET MVC 3

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:

public IQueryable GetProductsReport()

        {

            AdventureWorksEntities ave = new AdventureWorksEntities();

            var query = from p in ave.Products

                        select new

                        {

                            Name = p.Name,

                            SellStartDate = p.SellStartDate,

                            SellEndDate = p.SellEndDate,

                            Cost = p.StandardCost,

                            Color = p.Color,

                            Orders = p.TransactionHistories.Count,

                            ReorderPoint = p.ReorderPoint

                        };

            return query;

        }

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:

public static void RegisterRoutes(RouteCollection routes)

        {

            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");



            routes.MapRoute(

                "Default", // Route name

                "{controller}/{action}/{id}", // URL with parameters

                new { controller = "Report", action = "Index", id = UrlParameter.Optional } // Parameter defaults

            );

        }

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):

public class ReportController : Controller

    {

        public ActionResult Index()

        {

            return View("ReportView");

        }

    }

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.

namespace System.Linq

{

    public static class LinqAsCsv

    {



    }

}

5b. Next, we'll add the main method that does the processing, and a helper method that does some formatting:

// Processes the IQueryable and returns a string of items as CSV

        private static string ProcessItems(IQueryable items)

        {

            var csvBuilder = new StringBuilder();

            var properties = items.ElementType.GetProperties();



            // Build the header row

            string header = string.Join("|", properties.Select(p => p.Name).ToArray());

            csvBuilder.AppendLine(FormatLine(header));



            // Build the data rows

            foreach (var item in items)

            {

                string line = string.Join("|", properties.Select(p => p.GetValue(item, null)).ToArray());

                csvBuilder.AppendLine(FormatLine(line));

            }



            return csvBuilder.ToString();

        }

// Formats each line

        private static string FormatLine(string line)

        {

            return string.Format("\"{0}\"", line.Replace("\"", "\\\""));

        }

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[]:

var csvBuilder = new StringBuilder();

            var properties = items.ElementType.GetProperties();

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:

string header = string.Join("|", properties.Select(p => p.Name).ToArray());

            csvBuilder.AppendLine(FormatLine(header));

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:

foreach (var item in items)

            {

                string line = string.Join("|", properties.Select(p => p.GetValue(item, null)).ToArray());

                csvBuilder.AppendLine(FormatLine(line));

            }

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:

return csvBuilder.ToString();

5c. Now, we'll add the two methods that we can call off our query:

// Returns IQueryable as a string in CSV format

        public static string AsCsvString(this IQueryable items)

        {

            return ProcessItems(items);

        }



        // Returns IQueryable as a byte[] in CSV format

        public static byte[] AsCsvByteArray(this IQueryable items)

        {

            return System.Text.Encoding.UTF8.GetBytes(ProcessItems(items));

        }

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:

public ActionResult GetProductReport()

        {

            ProductReports pr = new ProductReports();

            var query = pr.GetProductsReport();

            return File(query.AsCsvByteArray(), "text/csv", "AdventureWorks_ProductReport.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:

@{

    View.Title = "ReportView";

    Layout = "~/Views/Shared/_Layout.cshtml";

}



<h2>ReportView</h2>



@Html.ActionLink("Get Product Report", "GetProductReport", "Report")

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.

Enjoy!

5 comments:

  1. I love it,Excellent article.I am decide to put this into use one of these days.Thank you for sharing this.To Your Success!
    _____________________________________________________________________________

    Rc Helicopter Parts|Rc Helicopter|Mini Rc Helicopter

    ReplyDelete
  2. Given so much information in it. its very useful .perfect explanation about Dot net framework.Thanks for your valuable information. dot net training in chennai | best dot net training in chennai

    ReplyDelete
  3. It is really a great work and the way in which u r sharing the knowledge is excellent.Thanks for helping me to understand basic concepts. As a beginner in Dot Net programming your post help me a lot.Thanks for your informative article. dot net training in velachery | dot net training institute in velachery

    ReplyDelete