Updating CRM records using LINQ

April 17th, 2014

Recently, I have been working on a application to import data into MS CRM. The data would come from a spreadsheet, and I would either insert or update records depending on whether or not certain criteria already existed.

To start off, I used the CRM SDK code generation tool (CrmSvcUtil.exe) to create my CRM classes. I also configured my application to use the XRM client, as outlined here. Now that everything was set up I was ready to begin retrieving, and manipulating records. My first couple of queries were simple, and worked fine the first time:

READ:

var account = xrm.AccountSet.Where(a => a.ex_AccId == record.AccId).FirstOrDefault();

CREATE:

var order = new Crm.SalesOrder
{
	Name = orderName,
	DateFulfilled = record.Date,
	ShipTo_Name = record.FirstName + " " + record.LastName,
	ShipTo_Telephone = record.PrimaryTelephone,
	ShipTo_Line1 = record.Address1,
	ShipTo_City = record.City,
	ShipTo_StateOrProvince = record.State,
	ShipTo_PostalCode = record.Zip
};

xrm.AddObject(order);
xrm.SaveChanges();

However, when I tried to update a record I ran into some issues. Let’s say for example, if there exists an account with the same Name and Address I want to update the record with the phone number of the import record. I had something like this:

var account = xrm.AccountSet.Where(a => a.ShipTo_Name == record.FirstName + " " + record.LastName && a.ShipTo_Line1 == record.Address1).FirstOrDefault();

if (account != null)
{
	account.ShipTo_Telephone = record.PrimaryTelephone;
	xrm.SaveChanges();
}

I wasn’t receiving any errors, but when I looked at my test record in CRM – it hadn’t been updated. I had even set a breakpoint just after the above code, and the local reference to the object did still have the new phone number on it – but why wasn’t the update submitted to CRM?

Usually, when using LINQ to query a data model this works just fine, but I came to find out that CRM is a bit different. The CRM service does have a LINQ provider, but its features are limited – and the feature that I expected to be there is actually what ended up causing my problem. When updating records using LINQ to CRM, changing an object’s property does not mark the record as “dirty”. So even though you think you updated a record, the change was actually never saved.

To fix this, we need to notify CRM that the record has changed before we save, using UpdateObject:

xrm.UpdateObject(account);
xrm.SaveChanges();

Once I made this change for my updates, they all worked well. Hopefully this helps anyone else who comes across this issue!

Table To Enum Using T4 Template

April 10th, 2014

Most developers have used enums at some point in their development process. They are basically strongly typed compile-time constants and allow you to assign a symbolic name to an integer value, removing the use of magic numbers, which leads to more maintainable code. Most projects tend to have several enums that mirror database lookup tables and these values tend to get out of sync over time and can be tedious to maintain.

In a previous project, I created enums dynamically from a database table using a number of classes in the System.Reflection.Emit namespace and use a separate project to generate an assembly that housed the enums. Though not overly complex, it involved a number of extra steps and I wanted to refactor. I finally got around to refactoring using the Text Template Transformation Toolkit (T4).

T4 is basically Microsoft’s code generation tool that’s built into Visual studio, so I decided to use it to simplify the creation of the various enums I needed. There are dozens of great articles on T4 such as T4 Code Generation – Best Kept Visual Studio Secret, Code Generation and T4 Text Templates, T4 and ASP.NET MVC and Creating your first code generator so I won’t go into detail here, but it’s well worth a few minutes of your time if you aren’t familiar with them.

To start, I created a new Text Template in Visual Studio 2013 and added the assembly and namespace directives that were needed to connect to a SQL database and specified the output file type.

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".cs" #>

I then added a code block to set the connection, create a list of tables to generated enums for and the logic to loop through the tables.

<#
// Connection info
ServerName = "(local)";
DatabaseName = "AdventureWorks2012";

// List of entities to create enums for
entities.Add(new Entity {Name="Person.AddressType", TextColumn="Name", ValueColumn="AddressTypeID"});
entities.Add(new Entity {Name="Person.ContactType", TextColumn="Name", ValueColumn="ContactTypeID"});
entities.Add(new Entity {Name="Person.PhoneNumberType", TextColumn="Name", ValueColumn="PhoneNumberTypeID"});

// Add a namespace around the generated enums (could be added directly to the template as well)
WriteLine("namespace AdWorksPerson");
WriteLine("{");

// Generate enums
foreach(var entity in entities)
{
#>
    public enum <#= ParseEntityField(entity.Name) #>
    {
    <#
        CreateEnum(entity);
    #>
    }

<#
}
// Namespace closing brace
WriteLine("}");
#>

And finally, I added a class code block with the fields and methods that will used by the other code blocks in the template.

<#+
// Class Feature Block (#+) avoids repeating common code

private string ServerName;
private string DatabaseName;

private struct Entity
{
    public string Name;
    public string TextColumn;
    public string ValueColumn;
}

private List<Entity> entities = new List<Entity>();

private string CreateConnectionString()
{
    var sb = new SqlConnectionStringBuilder();
    sb.DataSource = ServerName;
    sb.InitialCatalog = DatabaseName;
    sb.IntegratedSecurity = true;

    return sb.ConnectionString;
}

private string ParseEntityField(string name)
{
    // Remove the dot, left bracket, right bracket, space
    // and slash characters from the fieldname.
    var pattern = @"[\.\[\]\s/-]*";
    var regex = new Regex(pattern, RegexOptions.None);
    return regex.Replace(name, string.Empty);
}

private void CreateEnum(Entity e)
{
    using(var con = new SqlConnection(CreateConnectionString()))
    {
        var cmd = con.CreateCommand();
        var sql = string.Format("SELECT {0}, {1} FROM {2} ORDER BY {0}", e.TextColumn, e.ValueColumn, e.Name);
        cmd.CommandText = sql;
        con.Open();

        using(var rdr = cmd.ExecuteReader())
        {
            PushIndent("\t");
            while (rdr.Read())
            {
                var option = string.Format("{0} = {1},", ParseEntityField(rdr[e.TextColumn].ToString()), rdr[e.ValueColumn].ToString());
                WriteLine(option);
            }
            PopIndent();
        }
    }
}
#>

Now, to generate the enums simply save the template or right-click the template name in Solution Explorer and choose Run Custom Tool, and a file (csharp in this case) will be created based on the name of the template and the output directive. The output generated from the template is shown below.

namespace AdventureWorks
{
    public enum PersonAddressType
    {
    Archive = 6,
    Billing = 1,
    Home = 2,
    MainOffice = 3,
    Primary = 4,
    Shipping = 5,
    }

    public enum PersonContactType
    {
    AccountingManager = 1,
    AssistantSalesAgent = 2,
    AssistantSalesRepresentative = 3,
    CoordinatorForeignMarkets = 4,
    ExportAdministrator = 5,
    InternationalMarketingManager = 6,
    MarketingAssistant = 7,
    MarketingManager = 8,
    MarketingRepresentative = 9,
    OrderAdministrator = 10,
    Owner = 11,
    OwnerMarketingAssistant = 12,
    ProductManager = 13,
    PurchasingAgent = 14,
    PurchasingManager = 15,
    RegionalAccountRepresentative = 16,
    SalesAgent = 17,
    SalesAssociate = 18,
    SalesManager = 19,
    SalesRepresentative = 20,
    }

    public enum PersonPhoneNumberType
    {
    Cell = 1,
    Home = 2,
    Work = 3,
    }
}

One of the first question you may have, as I did, when using T4 is how do I access app/web config settings from the template. You cannot do it directly as T4 runs under a different context than that of the project. See T4 templates using own project .config file, T4 templates for Web.config/App.config AppSettings or T4 Template For AppSettings Access In Config Files for information on using config files.

T4 allows a mixture of text and code blocks and can generate any type of text file such as classes, web pages, stored procedures, etc. These types of boilerplate code are often needed repeatedly and code generation can be used to reduce time and the chance of errors.

Using InsertAfter with HTML Agility Pack

April 3rd, 2014

I recently had an issue while working in C# where I needed to parse some HTML. It wasn’t complex but I also don’t believe in reinventing the wheel so I decided to use the HTML Agility Pack. While it hasn’t been updated in a while it was more than sufficient for my needs. Below is what I needed to do.

Given the following HTML:

<ul>
	<li>First Item</li>
    <!-- I want to add an item here -->
	<li>Second Item</li>
</ul>

I wanted to add a third item where the comment appears in the HTML above. The HTML Agility Pack has a method “InsertAfter”. The intellisense gives the following:

(HtmlNode newChild, HtmlNode refChild):HtmlNode
Inserts the specified node immediately after the specified reference node.
newChild: The node to insert. May not be null.
refChild: The node that is the reference node. The newNode is placed after the refNode.

The relevant C# code I was using looked like this:

foreach (var listItem in listItems)
{
  if (listItem.InnerText == "Set Second Item")
  {
    // This line kept failing with the error message below.
    listItem.InsertAfter(HtmlNode.CreateNode("</pre>
<ul>
	<li>Third Item</li>
</ul>
<pre>"), listItem);
  }
}

I kept getting an error message that read:

“Node <li> </li>” was not found in the collection. Parameter name: node

I couldn’t figure out what was wrong until I stumbled across a StackOverflow post. All I had to do to fix the error was change the offending line to this:

listItem.ParentNode.InsertAfter(HtmlNode.CreateNode("
<ul>
	<li>Third Item</li>
</ul>
<pre>"), listItem);

The documentation/intellisense on this didn’t seem clear nor did it seem intuitive but the “InsertAfter” method needs to be called on the parent of the node you want to insert after (hence the ParentNode), and the reference node (refChild) is the node that should have the node inserted after.

Hopefully this helps someone else.

You can see a full example of the working code on .NET Fiddle and actually run it. If you can’t access the source code on .NET Fiddle, it’s below as well.

using System;
using System.IO;
using HtmlAgilityPack;

namespace ConsoleApplication1
{
    public class Program
    {
        public static void Main(string[] args)
        {
            const string htmlFragment = "
<ul>
	<li>Set First Item</li>
	<li>Set Second Item</li>
	<li>Last Item</li>
</ul>
<pre>";
            var doc = new HtmlDocument();
            doc.LoadHtml(htmlFragment);
            var listItems = doc.DocumentNode.SelectNodes("//li");

            foreach (var listItem in listItems)
            {
                if (listItem.InnerText == "Set Second Item")
                {
                    // This line kept failing.
                    // listItem.InsertAfter(HtmlNode.CreateNode("<li>Third Item</li>"), listItem);

                    // But this one works!
                    listItem.ParentNode.InsertAfter(HtmlNode.CreateNode("<li>Third Item</li>"), listItem);
                }
            }

            var stringWriter = new StringWriter();
            doc.Save(stringWriter);
            stringWriter.Close();

            Console.WriteLine(stringWriter.ToString());
            Console.ReadKey();

        }
    }
}

Extracting Data Using the Bing Ads API (v9)

March 20th, 2014

Most advertising platforms offer reporting tools to review ad performance. While helpful, sometimes we need to be able to extract data for use locally – maybe for archiving/warehousing purposes, custom reporting, etc. This post will guide you through creating and downloading a custom report with the Bing Ads API, so that you may implement your own custom solution.

First, we need to create a developer account and obtain a Developer Token. This is required to access your data using an external application.

The next step is authentication – there are currently two methods, depending on what kind of credentials you have.

  • Username/Password: This was the original way to set up a Bing Ads account. If you use this method to login, you will be able to authorize access to your data using the same credentials. This can actually be beneficial if you wish to extract data using a service (no browser required).
  • Microsoft Account: In recent years, especially with the addition of many cloud applications, Microsoft has implemented universal accounts to access all of your applications with the same credentials. On top of that, they have followed suit with most other web platforms and are requiring users to be authenticated using OAuth. OAuth is a secure way to authorize third-party applications to access your data (with your permission), without having to know your credentials. If you use this login method, you will not be providing a username/password, but an Authentication Token obtained with OAuth.

Once you’ve determined your login method and/or retrieved your Authentication Token, you are ready to begin. First, we need to add a service reference to our project. There are several web services available depending on what you’d like to do; we will be using the Reporting Service. This service defines two operations:

We will be using both of these operations. Once you’ve added your service reference, make sure to also include the name in your using statement.

using BingReporting;

Now, we can work on generating our report. The body for SubmitGenerateReport contains a ReportRequest, a list of possible report types can be found here. In this report, we want to extract ad performance data, so we will be using the CampaignPerformanceReportRequest. This request requires at least a column set, scope, and time range, but you can also specify aggregation, and filters. Below is my request:

private static string GenerateReport()
{
	var service = new ReportingServiceClient();
	var reportId = "";
	var request = new CampaignPerformanceReportRequest()
	{
		Aggregation = ReportAggregation.Daily,
		Columns = new CampaignPerformanceReportColumn[]
		{ 
			CampaignPerformanceReportColumn.CampaignName,
			CampaignPerformanceReportColumn.TimePeriod,
			CampaignPerformanceReportColumn.Impressions,
			CampaignPerformanceReportColumn.Clicks,
			CampaignPerformanceReportColumn.Spend,
			CampaignPerformanceReportColumn.AdDistribution
		},
		Scope = new AccountThroughCampaignReportScope()
		{
			AccountIds = new long[] { 1234567 }
		},
		Time = new ReportTime()
		{
			CustomDateRangeStart = new Date() { Day = 1, Month = 10, Year = 2013 },
			CustomDateRangeEnd = new Date() { Day = 31, Month = 1, Year = 2014 }
		}
	};

	//I am using the classic username/password authentication, so the Authentication Token parameter is null
	service.SubmitGenerateReport(null, null, "1234567", "12345678", "ABCDEFG123456789", "Password", "Username", request, out reportId);

	return reportId;
}

Once you submit your request for a report, the method returns a reportId that you can use to download the completed report – this is where the second method comes in. PollGenerateReport will return a report status, and a download URL. I used a while loop to make sure the report was ready, and then saved the zip file locally. Extracting the CSV from the zip file is easily done with the .NET 4.5 addition of System.IO.Compression.ZipFile class.

private static string PollReport(string reportId)
{
	ReportingServiceClient service = new ReportingServiceClient();
	ReportRequestStatus status = new ReportRequestStatus();

	//Check status until successful
	while (status.Status != ReportRequestStatusType.Success)
	{
		service.PollGenerateReport(null, null, "1234567", "12345678", "ABCDEFG123456789", "Password", "Username", reportId, out status);
	}

	//Download file
	using (var client = new WebClient())
	{
		client.DownloadFile(status.ReportDownloadUrl, @"C:\TEMP\BingReport.zip");
	}

	//Extract file
	System.IO.Compression.ZipFile.ExtractToDirectory(@"C:\TEMP\BingReport.zip", @"C:\TEMP");
}

Now you have access to a CSV with all of the ad performance data specified in your request, ready for you to parse, save, and query as needed. There are several methods and tools available to help parse CSV files, I like to use CSV Helper but you can use whatever works for you. Further, Microsoft has also provided some C# examples for Bing Ads that you may find helpful for other tasks, I encourage you to check them out along with the full Version 9 documentation.

CRM 2013 Online: “Flashing” Subgrid Issue

March 13th, 2014

When recently setting up some customizations for the Account entity in CRM 2013, we ran into a situation where one of the subgrids that was added to the form would constantly refresh itself, causing a “flashing” effect that made it very difficult to use the subgrid at all. We couldn’t determine what caused it to start behaving like this in the first place, and the error that CRM was producing didn’t help us much:

<CrmScriptErrorReport>
  <ReportVersion>1.0</ReportVersion>
  <ScriptErrorDetails>
   <Message>Object expected</Message>
   <Line>34</Line>
   <URL>/_common/JsProvider.ashx?IsRefreshForm=true&ids=1759665639-1968334880-202661451-1314140682-1793205875-1547461133-1992083491-1000289424-159335543-1194605645-1380222012-1039841082-1376927455-1030632819-1181113682-1625018438-513446117-1460109179-1088147315-706656670-1025323794-1710907082-342519603-330369912-232397045-468302605-1405388298-1731109441-2029667886-202888856-542820515-1946640899-1603536461-1220618931-511599809-1264077421-131822024-1188909594-783705869-374257838-1463979649-1391165063-1359517414-1781702827-2050812538-512064351&ver=-1466743897</URL>
   <PageURL>/main.aspx?etc=2&extraqs=%3f_gridType%3d2%26etc%3d2%26id%3d%257bD3FACB8C-86F0-E211-8B1E-B4B52F67D6E6%257d%26rskey%3d310532865&pagemode=iframe&pagetype=entityrecord&rskey=310532865</PageURL>
   <Function>anonymous(result,command){if(result.get_success()&&!isNullOrEmptyString(result.get_returnValue())){var$v_0=XUI.Xml.LoadXml(result.get_returnValue());this.SetProperty("pagingCookie",XUI.Xml.GetText(XUI.Xml.SelectSingleNode($v_0,"gridXml/pagingCookie",null)</Function>
   <CallStack>
    <Function>anonymous(result,command){if(result.get_success()&&!isNullOrEmptyString(result.get_returnValue())){var$v_0=XUI.Xml.LoadXml(result.get_returnValue());this.SetProperty("pagingCookie",XUI.Xml.GetText(XUI.Xml.SelectSingleNode($v_0,"gridXml/pagingCookie",null)));this.setGridData(XUI.Xml.GetText(XUI.Xml.SelectSingleNode($v_0,"gridXml/gridHtml",null)),true);var$v_1=newArray(3);$v_1[0]="effectiveFetchXml";$v_1[1]="isFetchXmlNotFinal";$v_1[2]="fetchXmlForFilters";$v_1[3]="viewts";for(var$v_2=this.GetParameter("viewts"),$v_4=0;$v_4<$v_1.length;$v_4++){var$v_5=XUI.Xml.SelectSingleNode($v_0,String.format("gridXml/gridParams/div[@id='{0}']",$v_1[$v_4]),null);$v_5&&this.SetParameter(XUI.Xml.GetText($v_5.attributes.getNamedItem("id")),XUI.Xml.GetText($v_5))}var$v_3=this.GetParameter("viewts");this.cacheValidation($v_2,$v_3)}elsethis.displayFailedMessage();this.fireEvents(command);this.$2N_3(null);this.$2k_3=newDate;this.$1I_3=false;Mscrm.Utilities.addControlMarker(this.get_id(),"Subgrid","FinishLoadTimestamp",(newDate).getTime().toString())}</Function>
   </CallStack>
  </ScriptErrorDetails>
  <ClientInformation>
   <BrowserUserAgent>Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; WOW64; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3; .NET4.0E; .NET CLR 1.1.4322)</BrowserUserAgent>
   <BrowserLanguage>en-US</BrowserLanguage>
   <SystemLanguage>en-US</SystemLanguage>
   <UserLanguage>en-US</UserLanguage>
   <ScreenResolution>1920x1080</ScreenResolution>
   <ClientName>Web</ClientName>
   <ClientTime>2014-02-13T12:12:11</ClientTime>
  </ClientInformation>
  <ServerInformation>
    <OrgLanguage>1033</OrgLanguage>
    <OrgCulture>1033</OrgCulture>
    <UserLanguage>1033</UserLanguage>
    <UserCulture>1033</UserCulture>
    <OrgID>{DFA41968-48A9-4746-A73E-6B06A16E0150}</OrgID>
    <UserID>{78C8EC3A-D810-4730-9443-9881DCC9D89A}</UserID>
    <CRMVersion>6.0.1.462</CRMVersion>
  </ServerInformation>
</CrmScriptErrorReport>

Other than indicating that the cause was likely not some custom JavaScript on the page, using the IE debugger wasn’t very useful, either, since it drops you into a minified JsProvider.ashx:

This Screenshot Is No Help

A bit of research led me to a thread on the MS Dynamics CRM community site, where this issue was being discussed: “CRM 2013 Sub-grid constantly refreshes”. While a few users claimed that clearing their temporary Internet files solved the problem, what really did the trick for us was to temporarily point the subgrid at a different entity, publish, then point it back to the original entity and view, then publish again, as recommended by one of the users on the first page of the thread.

We still have not been able to reproduce the issue, which could allow us to pinpoint what triggers it in the first place, but I wanted to be sure to spread this fix around, for those users who may skip over the post with the solution. It’s quick and simple, and possibly not something that would immediately occur to a user. Hopefully this problem is addressed in the next CRM rollup.

Creating a Cascading Picklist Using Kendo UI

March 6th, 2014

I’ve been using bit more of the KendoUI for a customer lately and one of the things that I’ve had to do is to create cascading combo boxes. I’ve always found the Telerik RAD controls for ASP.NET documentation to be complete in the sense that the all methods, properties, events, etc., are well documented. What I’ve found to be a bit lacking are practical, real world examples.

One thing that gets me is that sometimes there are “magic strings” and those string values must match other string values. Unfortunately the documentation doesn’t always point those strings out. There are a few strings when creating cascading combo boxes that must match, and I’ve attempted to document (with comments) what must match.
In the example there are two combo boxes: Service Category and Service. When you select a value in the Service Category combo box, the values in the Service combo box must be filtered to the appropriate service category. The values look like this:

  • Service Category “Cleaning” has the following associated Service values:
    Wash & Rinse
    Wash, Rinse & Blast
  • Service Category “Shipping” has the following associated Service values:
    Standard
    Two Day
    Overnight

So, if I select a Service Category of “Cleaning”, the Service combo box should only have “Wash & Rinse” and “Wash, Rinse & Blast”. So how do we make this work using the Kendo UI? There are three bits of code that we need: the HTML, the JavaScript and the CSS. The HTML and CSS really doesn’t do anything special – the real work is in the JavaScript. First is the HTML:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Kendo Example</title>
    <link href="styles/kendo.common.min.css" rel="stylesheet" />
    <link href="styles/kendo.default.min.css" rel="stylesheet" />
    <link href="styles/custom.css" rel="stylesheet" />
    <script type="text/javascript" language="javascript" src="js/jquery-1.9.1.min.js"></script>
    <script type="text/javascript" language="javascript" src="js/kendo.all.min.js"></script>
    <script type="text/javascript" language="javascript" src="js/custom.js"></script>
</head>
<body>
    <div>Service Category:</div><input type="text" id="serviceCategoryInput"/>
    <br />
    <div>Service:</div><input id="serviceInput" />
</body>
</html>

Next the CSS:

body
{
    font-size: larger;
    font-family: "Segoe UI";
    margin: 20px;
}

div 
{
    display: inline-block;
    width: 150px;
    text-align: left;
}

input 
{
    margin: 20px;
    text-align: left;
}

Finally, the javascript. I’ve commented on where there are strings that have names that must match in order for things to work as expected.

$(document).ready(function () {
    /** Define the Service Category input. The code just creates 
     ** a kendo combo box out of the input defined in the HTML with
     ** the ID of "serviceCategoryInput. **/
    $("#serviceCategoryInput").kendoComboBox({
        // This name must match a JSON element attribute in the data source.
        dataTextField: "serviceCategory",

        // This name must match a JSON element attribute in the data source.
        dataValueField: "serviceCategoryId",
        dataSource: [

            // The left hand side of the colon (the name) must match either the string
            // defined above as "serviceCategory" or "serviceCategoryId".
            { serviceCategory: "Cleaning", serviceCategoryId: 1 },
            { serviceCategory: "Shipping", serviceCategoryId: 2 }
        ],
        filter: "contains",
        suggest: true,
        placeholder: "Please select the service category..."
    });

    /** Set the width and margin **/
    $("#serviceCategoryInput").parent().css('width', "400px");
    $("#serviceCategoryInput").parent().css('margin', "10px");

    $("#serviceInput").kendoComboBox({
        // The string associated with the cascadeFrom element must match
        // the name of the control in the HTML that is the parent control.
        cascadeFrom: "serviceCategoryInput",

        // The cascadeFromField must match one of the values in the data source
        // from the parent control.
        cascadeFromField: "serviceCategoryId",

        // The dataTextField and dataValueField names must match a name in the JSON
        // in the data source.
        dataTextField: "service",
        dataValueField: "serviceid",
        dataSource: [
            { service: "Wash & Rinse", serviceid: 1, serviceCategoryId: 1},
            { service: "Wash, Rinse & Blast", serviceid: 2, serviceCategoryId: 1 },
            { service: "Standard", serviceid: 3, serviceCategoryId: 2 },
            { service: "Two Day", serviceid: 4, serviceCategoryId: 2 },
            { service: "Overnight", serviceid: 5, serviceCategoryId: 2 }
        ],
        filter: "contains",
        suggest: true,
        placeholder: "Please select the service..."
    });

    /** Set the width and margin **/
    $("#serviceInput").parent().css('width', "400px");
    $("#serviceInput").parent().css('margin', "10px");
});

Once you put all that together, it should run. Hopefully it helps on two fronts: one, a working example of a cascading combo box and insight on where string values must match when creating the cascading combo box.

Using ASP.NET Web API with KendoGrid

February 27th, 2014

To use ASP.NET MVC with KendoUI, you have two options: 1) Use the MVC Server Wrappers, which are quite handy or 2) Use JavaScript. I’ve used the KendoUI Grid with the MVC server wrappers, but wanted to switch to an HTML Grid and JavaScript, and leverage the Web API to return the data in JSON format for a new project.

There are decent examples on the KendoUI site for binding the grid to local data and remote data (using ODATA), but there wasn’t much in the way of examples using the Web API, especially with support for server-side sorting, paging and filtering. I did manage to find a good post on Using KendoUI with ASP.NET Web API by the Kendo Team (well worth a few minutes of your time), but ran into issues binding, sorting and filtering the grid, which is what I will address with this post.

When server-side paging, sorting and filtering are enabled, Kendo passes the appropriate parameters to the controller as shown in Figure 1.


Figure 1. Ajax KendoGrid request parameters

If you are using MVC, Telerik provides a DataSourceRequest class in the Kendo.Mvc.dll which handles the parameter binding for you. However, with the Web API, the controllers work differently from MVC controllers in many ways, especially with parameter binding. As a workaround to the problem Telerik provided a custom ModelBinder for the DataSourceRequest class for Web API (mentioned in the above post) called DataSourceRequestModelBinder.cs which you can get from the Kendo UI Code Library.

With the model binder change in place, my Web API controller looks like Listing 1 below. I am using the Chinook sample database and Dapper to retrieve customer data just to keep things simple as the data access option isn’t important.

public DataSourceResult Get([ModelBinder(
    typeof(DataSourceRequestModelBinder))] DataSourceRequest request)
{
    var db = new SqlConnection(ConfigurationManager
                     .ConnectionStrings["Development"].ConnectionString);
    var customers = db.Query<Customer>(@"
        SELECT  customerid, firstname, lastname, company, address, city
                ,state, postalcode, phone, email
        FROM    dbo.customer
        ORDER   BY lastname"
    );
    return customers.ToDataSourceResult(request);
}

Listing 1. CustomerController Get() method

 

The markup is very basic with just the appropriate CSS and JavaScript references and a div to house the Kendo grid.

<!DOCTYPE html>
<html>
  <head>
    <title>HTML K-Grid w/WebApi</title>
    <link href="Content/kendo.common.min.css" rel="stylesheet" />
    <link href="Content/themes/base/kendo.default.min.css" rel="stylesheet" />
    <link href="Content/site.css" rel="stylesheet" />
    <script src="Scripts/modernizr-2.6.2.js"></script>
  </head>
  <body>
    <div id="Grid"></div>

    <script src="Scripts/jquery-1.8.2.js"></script>
    <script src="Scripts/kendo.web.min.js"></script>
    <script src="Scripts/kendo.aspnetmvc.min.js"></script>
    <script src="Scripts/customer.js"></script>
  </body>
</html>

Listing 2. customer.html

 

The related customer.js file that sets up the grid and handles the Ajax call is fairly simple as well with a dataSource, schema and columns defined. Paging, filtering and sorting are enabled.

$(function () {
    $("#Grid").kendoGrid({
        dataSource: {
            transport: {
                read: {
                    url: "/api/customer",
                    type: "GET"
                }
            },
            schema: {
                model: {
                    id: "CustomerId",
                    fields: {
                        FirstName: { type: "string" },
                        LastName: { type: "string" },
                        Address: { type: "string" },
                        City: { type: "string" },
                        State: { type: "string" }
                    }
                }
            },
            sort: [{ field: "LastName", dir: "asc" }],
            pageSize: 10,
            serverPaging: true,
            serverFiltering: true,
            serverSorting: true
        },
        columns: [
            { field: "FirstName", title: "First Name", filterable: true },
            { field: "LastName", title: "Last Name", filterable: true },
            { field: "Address", filterable: true },
            { field: "City", filterable: true },
            { field: "State", filterable: false}
        ],
        pageable: true,
        sortable: true,
        filterable: true
    });
});

Listing 3. customer.js file

 

With the HTML page, JavaScript file and custom model binder in place, I attempted to load the page using the “read” transport and got a blank grid.


Figure 2. Blank Customer Grid

I verified that the Ajax request was being made and that my controller was getting called. The next check was to see what payload was being returned (see Figure 3).


Figure 3. Customer JSON payload

In the response, the JSON payload is wrapped in an “Data” object. I believe this data object is added by the Web API for security purposes when returning arrays in JSON (Phil Haacked wrote an interesting post called JSON Hijacking a while back). So I need to let Kendo know about that object wrapper using the dataSource schema as follows.

schema: {
    data: "Data", // service returns... {"Data":[{"CustomerId":12,...
    ...
}

Specifying the data property worked and the grid was bound correctly, but the Pager had no data (see Figure 4).


Figure 4. Customer Grid with Blank Pager

This is because you also have to declare the total property on the schema as well. The total is included in the JSON payload and returned by the DataSourceRequest object as listed below, but not bound automatically when using the Web API.

"... Total":59,"AggregateResults":null,"Errors":null}

So, with the following change to the dataSource schema, everything appeared to be working and the Pager displays as expected.

schema: {
    data: "Data",
    total: "Total",
}


Figure 5. Customer grid with working pager

At this point I thought everything was working correctly, but when I tried to sort or filter the data, it didn’t work. This was odd because it works just fine if you use the server wrappers to bind the grid. I suspected that I needed to specify a dataSource type, but the KendoUI documentation for dataSource list “odata” as the only supported value for the type property. Clearly, I am not using odata so there had to be another option. After researching unsuccessfully for a while online, I decided to debug locally on another project that had an implementation of a KendoGrid bound using the server wrappers.

What was interesting as I interrogated the KendoGrid dataSource object in Chrome’s developer tools tools and compared the Web API (Figure 6) vs. the server wrappers (Figure 7), was that the dataSource with the server wrappers had what seem to be a type specified for the transport of aspnetmvc-ajax (transport: c.transports.aspnetmvc-ajax.u.data.RemoteTransport.extend.init). So I look at the transport directly and it had it as well.


Figure 6. KendoGrid dataSource bound with Web API


Figure 7. KendoGrid dataSource bound with server wrappers

In seems the Kendo.Mvc.Extensions uses the aspnetmvc-ajax transport settings for the Ajax binding and it’s handled automatically if you’re using the server wrappers, but not with the Web API. I suspected using aspnetmvc-ajax as the type instead of odata might work, so I made an additional change to the KendoGrid JavaScript as follows:

dataSource: {
    type: "aspnetmvc-ajax", // needed for Sort and Filter to work w/HTML grid
    ...
}

With the type added, filtering and sorting works just fine and the experience is the same as using the server wrappers. This works, but it’s not documented. I may follow up with the Kendo Team at some point on this issue to see if it will be addressed in a future build.

KendoGridFilter


Figure 8. Customer Grid Filtered

Hopefully, this may save you some time in your web development with ASP.NET Web API and KendoUI. It can be frustrating and time consuming working with a product when the examples are limited and the documentation could use some refinements, but KendoUI is great framework for web development and the more I use it, the more I like it.

 


 

A copy of the sample code is available on Github.

Creating a Cascading Picklist Using Kendo UI

February 6th, 2014

I’ve been using bit more of the KendoUI for a customer lately and one of the things that I’ve had to do is to create cascading combo boxes. I’ve always found the Telerik RAD controls for ASP.NET documentation to be complete in the sense that the all methods, properties, events, etc., are well documented. What I’ve found to be a bit lacking are practical, real world examples. I think the Kendo UI documentation is better in this regard, but there’s one area that I think it is a bit deficient and honestly, it’s not just the Kendo UI documentation, it’s technical documentation in general. That area is the situation where the string names of things are very important and a string value when set must match somewhere else. Put another way: if I name something “input1″ in code over here, that same string must appear in this code over there, and if they don’t match, things don’t work. I found a situation with cascading combo boxes that I want to demo.

The example that I’m creating is a simple one and will illustrate how to create a cascading combo box and where certain string names must match. In the example there are two combo boxes: Service Category and Service. When you select a value in the Service Category combo box, the values in the Service combo box must be filtered to the appropriate service category. The values look like this:

  • Service Category “Cleaning” has the following associate Service values:
    • Wash & Rinse
    • Wash, Rinse & Blast
  • Service Category “Shipping” has the following associated Service values:
    • Standard
    • Two Day
    • Overnight

So, if I select a Service Category of “Cleaning”, the Service combo box should only have “Wash & Rinse” and “Wash, Rinse & Blast”. So how do we make this work using the Kendo UI? There are three bits of code that we need: the HTML, the JavaScript and the CSS. The HTML and CSS really doesn’t do anything special – the real work is in the JavaScript. First is the HTML:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Kendo Example</title>
    <link href="styles/kendo.common.min.css" rel="stylesheet" />
    <link href="styles/kendo.default.min.css" rel="stylesheet" />
    <link href="styles/custom.css" rel="stylesheet" />
    <script type="text/javascript" language="javascript" src="js/jquery-1.9.1.min.js"></script>
    <script type="text/javascript" language="javascript" src="js/kendo.all.min.js"></script>
    <script type="text/javascript" language="javascript" src="js/custom.js"></script>
</head>
<body>
    <div>Service Category:</div><input type="text" id="serviceCategoryInput"/>
    <br />
    <div>Service:</div><input id="serviceInput" />
</body>
</html>

Next the CSS:

body
{
    font-size: larger;
    font-family: "Segoe UI";
    margin: 20px;
}

div 
{
    display: inline-block;
    width: 150px;
    text-align: left; 
}

input 
{
    margin: 20px;
    text-align: left;
}

Finally, the javascript. I’ve commented on where there are strings that must match as well as drawn some colored lines between items that match. Unfortunately, I’ve encountered an issue with the syntax highlighter WordPress plugin that won’t allow me to actually embed the JavaScript code so I’ve used an image. The image does include all the JavaScript code.

How to resolve: LINQ to Entities does not recognize the method int.Parse

December 2nd, 2013

When you convert a string to an int in a LINQ Query this runtime error pops up:

System.NotSupportedException:
LINQ to Entities does not recognize the method int.Parse(System.String)

or the reverse:

LINQ to Entities does not recognize the method System.String ToString()

Now you scratch your head, why can’t I cast a database field as string to an integer in the view model or vice versa? There is a method called SqlFunctions.StringConvert that works converting a double to a string. Out the box LINQ to Entities does not support Convert.ToInt32 or ToString(). However, you can do this you just have to implement a custom function in Entity Framework. Below is a brief tutorial on how to accomplish this.

Add the following xml to your .edmx file. (Just do a ‘Find in Files’ in Visual Studio “</Schema>” and place it before this node):


<Function Name="IntParse" ReturnType="Edm.Int32">
 <Parameter Name="stringvalue" Type="Edm.String" />
 <DefiningExpression>
 cast(stringvalue as Edm.Int32)
 </DefiningExpression>
</Function>

Add the following static function to your auto-generated Entity Framework class file:

...
using System.Data.Objects.DataClasses;

public partial class Entity_Class : DbContext
{
 public Entity_Class() : base("name=Entity_DBName") {}

[EdmFunction("Entity_DBModel", "ParseInt")]
 public static int IntParse(string val)
 {
 return int.Parse(val);
 }
...

You are ready to go, just add the new function to your LINQ Query:

var viewModelResult = from e in DataContext.Entity_Table
 select new Entity_ViewModel
 {
 Property_Id = e.Id,
 Property_As_Int = Entity_Class.IntParse(h.Column_As_String)
 };

You now have a simple clean solution to this common Entity Framework problem. Please comment below if you have any questions about Custom Functions in Entity Framework.