Clean way to create CSV from MVC application

I am working on a cool reporting MVC app using Highcharts and Datatable.net. The app has export feature. Users may want to get an extract of the data in some form that they can use. There are multiple ways of doing this on client side but users may be seeing only a part of the data on the client. We decided to serve a comma separated value(CSV) from the server. I thought I’d share the experience of wiring it up that may be helpful.

In order to serve any type of file from the server, the controller action must return a result of the type FileContentResult as below:

public FileContentResult ExportConversion(MyFilters filters)
{
  try
  {
    var Data =
        IService.SomeMethod(“filters”).ToList();

    var bytes = GenericHelper.GetBytesForCSVFile(Data, DataPreparer.FormatCSV,
                                                   "TotalItemsPerday, SpecialItemsPerDay, SpecialItemsPercentage");

    return File(bytes, "text/csv", "FileName");

 }
 catch (Exception exception)
 {
    _logger.LogError(exception);
    throw;
 }
}

In the controller action, I am getting a list of objects from the service. Then I am passing it along to a GetBytesForCSVFIle static method in it my GenericHelper class. This method takes a converter function for an object in this case Item and applies it to a collection and gives a byte array. The object looks like below:

public class Item
{
    public int Id { get; set; }
    public decimal TotalItemsPerDay { get; set; }
    public decimal SpecialItemsPerDay { get; set; }
    public decimal SpecialItemsPercentage { get; set;
}

The GetBytesForCSVFile looks like below:

Public static class GenericHelper
{
   public static byte[] GetBytesForCSVFile<T>(List<T> Data, Converter<T, string> converter,string headerrow="")
    {
        if (Data.Any())
        {
            var convertAll = Data.ConvertAll(converter);

            convertAll.Insert(0, headerrow + Environment.NewLine);

            string completeString = String.Concat(convertAll);

            return new UTF8Encoding().GetBytes(completeString);
        }
        else
        {
            return new byte[]{};
        }
    }
}

The converter looks like below:

Public static class DataPreparer
{
    public static string FormatCSV(Item item)
    {
        return TotalItemsPerDay.ToString() + 
                      "," + SpecialItemsPerday.ToString() + 
                      "," + SpecialItemsPercentage.ToString() +"%" + Environment.NewLine;
    }
}

Creating a formatter in such a way gives us a flexibility to handle any complex object and format the way we want it. If something in the format changes, the change is isolated to this formatter. I think this is clean. I didn’t think it was great use of time to come with up universal formatter function and maintain it. It is a sort of inversion of control because we are asking a client “Hey Client, what kind of format you are looking for?” Client Says “FormatCSV type”. Our client injects that into our little helper method GetBytesForCSVFile. The method just passes the converter along to a ConvertAll method and then adds the header as a first row if we have one. It then creates one string and creates a byte array out of it.

A little tangent here… If you are interested to know when to use string builder and when to use string concatenation read this post by Jon Skeet

On the view, I added a jQuery function to call this as below:

$('#Export').click(function () {
    if ($("#myFormName").validate().form()) {
        var filters = {
            StartDate: $("#StartDate").val(),
            EndDate: $("#EndDate").val()
        };
        var url = "Export?StartDate=" + filters.StartDate + "&&EndDate=" + filters.EndDate;
       document.location = url;
    }
    else {
        return;
    }
});

That’s it! On the click of the html button with Id Export, the controller action gets called. It gets the data, formats it and sends it over the wire to user as a csv file. IMHO, this is easy,clean and maintainable. If you are using .net 4.5 , you can sprinkle some async love around. :)

I am working on a cool reporting MVC app using Highcharts and Datatable.net. The app has export feature. Users may want to get an extract of the data in some form that they can use. There are multiple ways of doing this on client side but users may be…

Read More

MVC4 SQL migrations step by step

In my earlier post , I showed how to use code first with the server where the app cant create a database.

Now, we are looking to migrate our schema on to the server. It would be nice if it we can get a nice DDL script for this that we can walk through the test envrironments to production. Our DBA friends will definitely allow this.

Let's see how to do this step by step.

Step 1: Open the Package Manager Console (Tools=> Library Package Manager => Package Manager Console)

Step 2: run Update-Package EntityFramework
This updates the EF to 4.3.1 version.

Step 3: Restart vs after the update (important!)

Step 4: run Enable-Migrations -EnableAutomaticMigrations

It generates Migrations folder with Configuration.cs class. It looks like:

namespace MvcApplication4.Migrations
{ using System; using System.Data.Entity; using System.Data.Entity.Migrations; using System.Linq;

internal sealed class Configuration : DbMigrationsConfiguration<MvcApplication4.Models.CompStoreDBContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }

    protected override void Seed(MvcApplication4.Models.CompStoreDBContext context)
    {
        //  This method will be called after migrating to the latest version.

        //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
        //  to avoid creating duplicate seed data. E.g.
        //
        //    context.People.AddOrUpdate(
        //      p => p.FullName,
        //      new Person { FullName = "Andrew Peters" },
        //      new Person { FullName = "Brice Lambson" },
        //      new Person { FullName = "Rowan Miller" }
        //    );
        //
    }
}

}

Step 5: Build the project (important!) as we have added new stuff in the project. If we dont build, the up and down method are empty. It will get clearer after next step

Step 6: run Add-Migration "InitialCreate"
It creates a file with a time stamp such as 201205261630323_InitialCreate.cs. This file looks like below:

namespace MvcApplication4.Migrations
{ using System.Data.Entity.Migrations;

public partial class InitialCreate : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "Computers",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    Descr = c.String(),
                    Type = c.String(),
                })
            .PrimaryKey(t => t.Id);

    }

    public override void Down()
    {
        DropTable("Computers");
    }
}

}

This file has Up and down methods. The up method is for applying the change and the down is there if we need to roll back. It also helps in managing the destructive db changes such as changing the datatype of decimal column to int.

Step 7: run Update-Database -Verbose
This is going to work only if you have ddl permissions on the db. If not, the next step shows a way to create a DDL script. Note : You cant create another migration untill the previous ones are applied.

Step 8: run Update-Database -Script -SourceMigration:$InitialDatabase -TargetMigration:"InitialOne"It generates a nice looking script for our model that we can take to production.

Hope this helps.. Again, dont forget to build after the Enabling the migration otherwise up and down methods get created empty!..

Update:

I used VS 2010 for this demo. If you use VS 2012, building the project after the enabling the migrations is not required.

In my earlier post , I showed how to use code first with the server where the app cant create a database. Now, we are looking to migrate our schema on to the server. It would be nice if it we can get a nice DDL script for this that we…

Read More