Export Telerik MVC Grid to CSV Dynamically based on a View Model

We may have seen examples from Telerik where they had exported data to CSV but Column Headers and Column Values are assigned manually to StreamWriter object.  For this post we will improve further on that by doing it dynamically, which makes sense when you are generating your grid in dynamic fashion as well.  This is good as it saves you time as you will not hard code all of the Columns you need as well as giving that flexibility later when you want to add or remove items from the Gird View without doing some code changes, this will also gives you the option to save user preferences.

Lets start.

First lets assume you have a grid, dynamic or not but in this example I am generating my columns dynamically.   Normally it would look like this for dynamic column generation.

@(Html.Telerik().Grid<YourViewModel>()
.Name("grdTransactions")
.Columns(c =>
{
    //Dynamic Grid View Preferences
    foreach (var g in Model.InvoiceTransactionGridPreferences)
    {
        c.Bound(g.ColumnName)
        .Title(g.ColumnHeader)
        .Width(g.Width)
        .ReadOnly(g.IsReadOnly)
        .Groupable(g.IsGroupable)
        .Filterable(g.IsFilterable)
        .Encoded(false)
        .Format(g.Format)
        .HtmlAttributes(new { @class = g.Class });
    }
})
.DataBinding(d => d
    .Ajax()
    .OperationMode(GridOperationMode.Client)
    .Select("SelectTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod })
    .Update("UpdateTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod }))
.Pageable(p => p.PageSize(Model.PageSize))
.Sortable()
.Scrollable()
.Filterable()
.Groupable();

or not dynamic like such

@(Html.Telerik().Grid<YourViewModel>()
.Name("grdTransactions")
.Columns(c =>
{
   c.Bound(o => o.Description).Width(100);
   c.Bound(o => o.Nett).Width(100);
   c.Bound(o => o.GST).Width(100);
   c.Bound(o => o.Gross).Width(100);

})
.DataBinding(d => d
    .Ajax()
    .OperationMode(GridOperationMode.Client)
    .Select("SelectTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod })
    .Update("UpdateTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod }))
.Pageable(p => p.PageSize(Model.PageSize))
.Sortable()
.Scrollable()
.Filterable()
.Groupable();

Next is to copy the javascript that was suggested in Telerik’s documentation on MVC grid for exporting CSV here (http://demos.telerik.com/aspnet-mvc/grid/customcommand), we adjusted it slightly to add the parameters we need.  So it looks like this now.

<script type ="text/javascript">
    function onDataBound() {

        var grid = $(this).data('tGrid');

        // Get the export link as jQuery object
        var $exportLink = $('#export');

        // Get its 'href' attribute - the URL where it would navigate to
        var href = $exportLink.attr('href');

        // Update the 'page' parameter with the grid's current page
        href = href.replace(/page=([^&]*)/, 'page=' + grid.currentPage);

        // Update the 'orderBy' parameter with the grids' current sort state
        href = href.replace(/orderBy=([^&]*)/, 'orderBy=' + (grid.orderBy || '~'));

        // Update the 'filter' parameter with the grids' current filtering state
        href = href.replace(/filter=([^&]*)/, 'filter=' + (grid.filterBy || '~'));

        // Pass other remaining parameters you may have
        href = href.replace(/accountId=([^&]*)/, 'accountId=' + $('#AccountId').val());
        href = href.replace(/batchId=(.*)/, 'batchId=' + $('#InvoicePeriod').val());

        // Update the 'href' attribute
        $exportLink.attr('href', href);

        InvoiceActions.invoiceGridDataBound();
    }
</script> 

@(Html.Telerik().Grid<YourViewModel>()
.Name("grdTransactions")
.Columns(c =>
{
    //Dynamic Grid View Preferences
    foreach (var g in Model.InvoiceTransactionGridPreferences)
    {
        c.Bound(g.ColumnName)
        .Title(g.ColumnHeader)
        .Width(g.Width)
        .ReadOnly(g.IsReadOnly)
        .Groupable(g.IsGroupable)
        .Filterable(g.IsFilterable)
        .Encoded(false)
        .Format(g.Format)
        .HtmlAttributes(new { @class = g.Class });
    }
})
.DataBinding(d => d
    .Ajax()
    .OperationMode(GridOperationMode.Client)
    .Select("SelectTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod })
    .Update("UpdateTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod }))
.Pageable(p => p.PageSize(Model.PageSize))
.Sortable()
.Scrollable()
.Filterable()
.Groupable()
.ClientEvents(c => c.OnDataBound("onDataBound"))
.ToolBar(t => t
    .Custom()
    .HtmlAttributes(new { id = "export" })
    .Text("Export to CSV")
    .Action("ExportToCSV", "YourController", new { page = 1, orderBy = "~", filter = "~", accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod})));

Now go to your controller then add the following Action for exporting your csv.

public ActionResult ExportToCSV(int page, string orderBy, string filter, int? accountId, int? invoicePeriod)
{
    //Get Related Transaction, make sure that your Query Method retruns and IEnumerable<T> of the view model you are using
    var transactions = yourQuery.GetTransactions(accountId, invoicePeriod)
        .AsQueryable()
        .ToGridModel(page, int.MaxValue, orderBy, string.Empty, filter)
        .Data
        .Cast<YourViewModel>();

    //Convert To IEnumarable<T> to CSV
    string csvData = transactions.ToCsv<YourViewModel>();

    //Get all Column Headers Dynamically
    var queriedColumns = transactions.GetType()
                .GetInterfaces()
                .Where(x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(IEnumerable<>))
                .Single()
                .GetGenericArguments()
                .Single();

    var columns = queriedColumns.GetProperties();

    string[] columnNames = columns.Select(column => column.Name).ToArray();

    var columnNamesString = string.Join(",", columnNames);

    //Prepare Output
    MemoryStream output = new MemoryStream();
    StreamWriter stringWriter = new StreamWriter(output, Encoding.UTF8);

    stringWriter.Write(columnNamesString);
    stringWriter.WriteLine();
    stringWriter.Write(csvData);

    stringWriter.Flush();
    output.Position = 0;

    return File(output, "text/comma-separated-values", "Export.csv");
}

If you notice I used an extension method ToCsv, thanks to this post (http://mikehadlow.blogspot.com.au/2008/06/linq-to-csv.html) it saved me from coding.  You will also notice that the GridModel extension from Telerik will handle all the sorting, filtering and grouping so you just need to just pass it hence you have the URL builder JavaScript earlier.

public static class IEnumerableToCSV
    {
        public static string ToCsv<T>(this IEnumerable<T> items)
           where T : class
        {
            var csvBuilder = new StringBuilder();
            var properties = typeof(T).GetProperties();
            foreach (T item in items)
            {
                string line = string.Join(",", properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray());
                csvBuilder.AppendLine(line);
            }
            return csvBuilder.ToString();
        }

        private static string ToCsvValue<T>(this T item)
        {
            if (item == null) return "\"\"";

            if (item is string)
            {
                return string.Format("\"{0}\"", item.ToString().Replace("\"", "\\\""));
            }
            double dummy;
            if (double.TryParse(item.ToString(), out dummy))
            {
                return string.Format("{0}", item);
            }
            return string.Format("\"{0}\"", item);
        }
    }

Approval Moderation in Sequential Workflow Project

Have you ever wondered how to automate Sharepoint approval process in a Sequential Workflow Project? Well look no futher this article is about that.

Lets start with a scenario to be clear, say we have a product list where someone in your company approves every product listing based on thresholds, that threshold measure can be a price range on a certain product type. Now that person usually declines desk product types when the price indicated by the contributor is below $200 and above $500, at this point the approver will do it manually by looking whether the product have violated the known threshold, this is an easy task for 10 items but imagine managing 300 of them. That is why we will be automating that based on definitions that we will set on a separate list. That list that will be creating will define threshold for different product type so if it hits any of the barriers I will decline the list item and the ones that does not will be auto approved. Sound simple? read further.

You might be already thinking that we can achieve that by using the Column Validation of a List. Well not quite as there are some limitations and one important one is the support for boolean operator. So a formula like this would work

=[Product Price] < 500

but this will not

=[Product Price] < 500 AND [Product Price] > 500

Now well you might think again that we can achieve that on the Sharpoint Designer. Well not quite as well as the Sharepoint Designer will only give you 1 search condition for a list lookup. So for example you need to lookup for a price threshold which meets the condition in multiple columns like for example a product type and maximum date of validity then it will be impossible.

So our last resort is using Visual Studio! So in this post I will explain to you how this will be achieved in the simplest manner. All you need is to follow this steps and I can assue you can make more complex scenarios after this one.

1. Create Lists for Product and Product Type

Product Type List

Product List

Take note that the column Product Type Column in Product List is a lookup on Product Type List

2. Enable Versioning in Product

As you can see we need to require content approval, we need to create a version each time (this ensures that you can roll back) and choose “only users who can approve items” this makes sure only the authorized can approve the items manually

3. Create a Task List

This is a library type in Sharepoint and the default one would do.

4. Create a Sequential Workflow Project

Choose Sequential Workflow under Sharepoint -> 2010

Follow the wizard

Choose the List you will invoke the workflow from and the associated Task List and History List

Trigger it on create and update

Now you will be presented with a Workflow Diagram

You can create more complex stuff by choosing items on the toolbar

but for this instance we will be making this straightforward and do everything on the onWorkflowActivated section. Now double click that and you will be presented with a code behind. Now copy the code below.

private void onWorkflowActivated1_Invoked(object sender, ExternalDataEventArgs e)
{

    SPLinqDataContext dc = new SPLinqDataContext(workflowProperties.SiteUrl);

    EntityList<ProductTypeItem> ProductType = dc.GetList<ProductTypeItem>("Product Type");

    string sProductType = GetLinkedListItemString(workflowProperties.Item["Product Type"].ToString());
    double dPrice = double.Parse(workflowProperties.Item["Product Price"].ToString());

    var Result = (from p in ProductType
                    where p.Title == sProductType && p.MaxDateValidity >= DateTime.Now
                    select p).SingleOrDefault();

    if (Result.MinPrice > dPrice)
    {
        workflowProperties.Item.ModerationInformation.Status = SPModerationStatusType.Denied;
        workflowProperties.Item.ModerationInformation.Comment = "Price Below Threshold - Saved on Approval Comment";
        workflowProperties.Item["Notes"] = "Price Below Threshold - Saved on Notes Field";
        workflowProperties.Item.Update();
    }
    else if (Result.MaxPrice < dPrice)
    {
        workflowProperties.Item.ModerationInformation.Status = SPModerationStatusType.Denied;
        workflowProperties.Item.ModerationInformation.Comment = "Price Above Threshold - Saved on Approval Comment";
        workflowProperties.Item["Notes"] = "Price Above Threshold - Saved on Notes Field";
        workflowProperties.Item.Update();
    }
    else
    {
        workflowProperties.Item.ModerationInformation.Status = SPModerationStatusType.Approved;
        workflowProperties.Item.Update();
    }

}

private string GetLinkedListItemString(string sItem)
{
    if (sItem.Contains("#"))
    {
        return sItem.Substring(sItem.LastIndexOf("#") + 1);
    }
    else
    {
        return sItem;
    }
}

If you notice I am uisng here LINQ to Sharepoint which you can refer to this post for a full tutorial, this makes my life easier in querying Sharepoint Lists

Now to explain some points on the code above:

• To get and set item values on the current list item that is being processed use workflowProperties.Item["Column Name"]
• To set Approval Status use workflowProperties.Item.ModerationInformation.Status
• Since we are querying LINQ style, the search conditions can be as complex as you want and you are just limited by your imagination
• You can also see that we use the Approval Comment field and the Notes Custom Field we created, I just wnat to demonstrate to you how to save comments on Approval fields as well as a field in your list.
• If you use a column that is coming from a linked list it will show in this format ID;#Value (i.e. “1;#Test”)

Now other than that I guess the code above is straightforward as the only thing it does is when price is above or below threshold levels then it will decline the list entry and leave a note.

Run you project then put in your data.

5. Run your code

First Add items in Product Type to define thresholds

Add a new product that will violate your threshold

Now you can see the progress once its submitted

Then once its finished, you will see the note and the final approval status

Converting System.Linq.IQueryable interface to System.Data.DataSet

Just a quick tip, in case you need to convert a System.Linq.IQueryable interface to System.Data.DataSet then check at the codes below

private DataSet LINQToDataSet({Your LINQ Data Context} myDB, IQueryable item)
{
    SqlCommand cmd = myDB.GetCommand(item) as SqlCommand;

    DataTable oDataTable = new DataTable();
    SqlDataAdapter oDataAdapter = new SqlDataAdapter(cmd);
    oDataAdapter.Fill(oDataTable);

    DataSet oDataSet = new DataSet();
    oDataSet.Tables.Add(oDataTable);
    return oDataSet;
}

Usage:

var oTableResult = (from p in myDB.SampleTable
                where p.SampleColumn == "Test Value"
                select p);

LINQToDataSet(myDB, oTableResult);

Guide to Creating Dynamic LINQ Queries

Are you new to LINQ and trying to figure out how to create dynamic LINQ queries like the same way you do with ADO.Net?  Well it’s not that hard at all and all you need to do is use a combination of the “Where” Extension method and “IQueryable” interface.

So for example we have a Customer Table where we want to filter it by Customer ID, First Name, Last Name, Customer Type, and Active.

Now below is a piece of code that will generate the dynamic LINQ queries

public static List<Customer> GetFilteredData(int? CustomerID, string FirstName, string LastName, string CustomerType, bool Active)
{
    DataClasses1DataContext oDB = new DataClasses1DataContext();
    IQueryable<Customer> oDataQuery = oDB.Customers;

    //Filter by Integer
    if (CustomerID != null)
    {
        oDataQuery = oDataQuery.Where(a => a.CustomerID == CustomerID);
    }

    //Filter by containing words
    if (FirstName.Trim().Length > 0)
    {
        oDataQuery = oDataQuery.Where(a => a.FirstName.Contains(FirstName));
    }

    //Filter by containing words
    if (LastName.Trim().Length > 0)
    {
        oDataQuery = oDataQuery.Where(a => a.LastName.Contains(LastName));
    }

    //Filter by a Foreign Key Relationship
    if (CustomerType.Trim().Length > 0)
    {
        oDataQuery = oDataQuery.Where(a => a.CustomerTypeID == (from b in oDB.CustomerTypes where b.CustomerType1 == CustomerType select b.CustomerTypeID).FirstOrDefault());
    }

    //Filter by boolean
    oDataQuery = oDataQuery.Where(a => a.Active == Active);

    return oDataQuery.ToList();
}

Now let’s dissect what just happened:

1. First you had created your LINQ to SQL Data Context; in our sample we just simply call it oDB.

2. Next we declared an IQueryable interface called oDataQuery and initialized it with your table properties in the Database; in this case we had named it Customers.  At this point nothing had happened yet and no data is being queried, you are just initializing the object type.

3. Now let’s create the where clause dynamically by filtering every step of the way starting from Customer ID to Active Columns.  At this point nothing has been queried on the database and you are just creating a dynamic query on the background. This is the big difference between the two interfaces “IQueryable” and “IEnumerable”.
In “IEnumerable” it gets all of the records first in the associated table then it is the application side that filters it out.

To get a better understanding here are the queries that are generated on the background every step of the way.

At this line

oDataQuery = oDataQuery.Where(a => a.CustomerID == CustomerID);

This query gets generated

SELECT [t0].[CustomerID], [t0].[LastName], [t0].[FirstName], [t0].[CustomerTypeID], [t0].[Active], [t0].[Birthdate]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[CustomerID]) = @p0

Then at this line

oDataQuery = oDataQuery.Where(a => a.FirstName.Contains(FirstName));

This query gets generated

SELECT [t0].[CustomerID], [t0].[LastName], [t0].[FirstName], [t0].[CustomerTypeID], [t0].[Active], [t0].[Birthdate]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[FirstName] LIKE @p0) AND (([t0].[CustomerID]) = @p1)

So you see how the next condition is appenededon the where clause.  This goes on until you had finalized the whole query and in our sample it will look like this

SELECT [t0].[CustomerID], [t0].[LastName], [t0].[FirstName], [t0].[CustomerTypeID], [t0].[Active], [t0].[Birthdate]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[Active] = 1) AND ([t0].[CustomerTypeID] = ((
    SELECT TOP (1) [t1].[CustomerTypeID]
    FROM [dbo].[CustomerTypes] AS [t1]
    WHERE [t1].[CustomerType] = @p0
    ))) AND ([t0].[LastName] LIKE @p1) AND ([t0].[FirstName] LIKE @p2) AND (([t0].[CustomerID]) = @p3

It is your [if] statement that controls whether a new where clause will be added

4. Finally you have to output it as something like a list so we will use the .ToList() method to get your desired query results.

Still not convinced, I had run a profiler at the background to see what queries are performed and it only shows the final query that was generated by LINQ

How to use LINQ to Sharepoint

LINQ or Language Integrated Query is a very useful language which reduces the complexity of accessing and integrating information that is not natively defined using OO technologies, with LINQ it adds native data querying capabilities to .NET languages which makes a developers life really easy.  Since it was launched I started using it as it saves me a lot of time in coding my applications.

Now a part of what I am doing is developing custom web parts for Sharepoint and sometimes you need to access data to present that information to your users and it would be good if LINQ can be used to query what you want to make life easier.  Well that is possible and can be implemented really easily all you have to do is reference LINQ library from your project and generate entity classes, which provide an object oriented interface to the Microsoft SharePoint Foundation content databases which can be done by using spmetal.

Now I will give you a step by step example on how to achieve this.

First definitely you need to have your list to play with, so in this example I will have Employees, Department and Position.  I guess it’s logical how you would relate this lists together.

For the Employees list we have the following columns shown in the image below, take note that the Last Name column is the Title column which was renamed.  Position is a lookup in the Positions list.

For Position, same thing we renamed title to Position Title and Department is a Lookup in Department list.

For Department, again same thing we renamed title to Department Name.

Now you have the list we need, and all we need to do next is to generate the Entity reference code using SPMetal.  You can find it in the installation of your Sharepoint in the folder C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN.  Now to generate the Entities you need you can run this command spmetal.exe /web:http://YourSharepointURL/YourTeamSite /namespace:YourProjectNamespace /code:FileNameYouWant.cs.

Once it’s finished you will see the file generated in the bin folder of Sharepoint.  And now you can add that to your project.

Now you also need to add a reference to Microsoft.SharePoint.Linq.dll which is in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI folder.

Now you are ready to code.  For this sample we will use a GridView to show the data you need so here is a sample.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    EnableModelValidation="True">
    <Columns>
        <asp:BoundField DataField="Title" HeaderText="Last Name"/>
        <asp:BoundField DataField="FirstName" HeaderText="First Name"/>
        <asp:BoundField DataField="Position" HeaderText="Position" />
        <asp:BoundField DataField="PositionDescription"
            HeaderText="Position Description" />
        <asp:BoundField DataField="Department" HeaderText="Department" />
    </Columns>
</asp:GridView>

And for the code behind we use this.

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Linq;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Linq;

namespace Demo.VisualWebPart1
{
    public partial class VisualWebPart1UserControl : UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }

        private void BindGrid()
        {
            SPLinqDataContext dc = new SPLinqDataContext(SPContext.Current.Web.Url);
            EntityList<EmployeesItem> Employees = dc.GetList<EmployeesItem>("Employees");

            var EmployeeQuery = from e in Employees.ToList()
                                select new
                                {
                                    e.Title,
                                    e.FirstName,
                                    Position = e.Position.Title,
                                    PositionDescription = e.Position.Description,
                                    Department = e.Position.Department.Title
                                };
            GridView1.DataSource = EmployeeQuery;
            GridView1.DataBind();
        }
    }
}

If you notice we use .Title a lot, this is because when we reused and renamed the Title column that SharePoint defaulted to, Sharepoint still retains the name internally it is still referred to as Title instead of the renamed column name.  And if you notice becuase of LINQ its easy to drill down to the sub lists that the primary list is related to, so rather than creating a join to the other list we just use this for example

e.Position.Description

also you notice there is a lot of Title Colums, its easy to create an alias for it by doing this

Department = e.Position.Department.Title

and finally if you have been using LINQ for some time you might ask why we used

Employees.ToList() 

rather than just using

Employees

Then here is a really good explanation about it -> http://blog.sharepointsite.co.uk/2010/06/inefficient-queries-spmetal-join-issue.html

Happy coding!