Using FormFlow in Microsoft Bot Framework (Creating lead in CRM)

Let us continue with our previous posts on using Microsoft Bot Framework

Getting Started with Microsoft Bot Framework.

Using Dialogs in Microsoft Bot Framework

To better understand the FormFlow refer to the below article.

Here we will update our Bot Application to use the FormFlow instead of Dialog.

As FormFlow are bound to a model, we will create a model class first.

Create a new folder named Model and a new class named LeadModel in it.

The class has an enum for selecting Product Type. It defines property for capturing Name and Description.

Here we define a static method named BuildForm that returns the IForm of type LeadModel.

The message defines the initial message that will be shown to the user and oncompletion we are defining a callback method that will be called on completion which we will be using to create lead record in CRM.

Next update the MessageController.cs to call this Lead Model instead of dialog which we did in our previous post.

The conversation between the Bot and the User.

The lead record created in CRM.

Source Code –

using Microsoft.Bot.Builder.FormFlow;
using System;
using Microsoft.Bot.Builder.Dialogs;
using System.Threading.Tasks;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk;

namespace Bot_Application1.Models

public enum InterestOptions { Product1 = 1, Product2 = 2, Product3 = 3};

public class LeadModel
public InterestOptions Product;
public string Name;
public string Description;
public static IForm<LeadModel> BuildForm()
return new FormBuilder<LeadModel>()
.Message("Welcome to the CRM bot !")
private static async Task CreateLeadInCRM(IDialogContext context, LeadModel state)
await context.PostAsync("Thanks for showing your interest we will contact you shortly.");
Entity lead = new Entity("lead");
lead.Attributes["subject"] = "Interested in product " + state.Product.ToString();
lead.Attributes["lastname"] = state.Name;
lead.Attributes["description"] = state.Description;

public static OrganizationServiceProxy GetOrganizationService()
IServiceManagement<IOrganizationService> orgServiceManagement =
ServiceConfigurationFactory.CreateManagement<IOrganizationService>(new Uri(""));
AuthenticationCredentials authCredentials = new AuthenticationCredentials();
authCredentials.ClientCredentials.UserName.UserName = "";
authCredentials.ClientCredentials.UserName.Password = *****";
AuthenticationCredentials tokenCredentials = orgServiceManagement.Authenticate(authCredentials);
return new OrganizationServiceProxy(orgServiceManagement, tokenCredentials.SecurityTokenResponse);

using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using Microsoft.Bot.Builder.Dialogs;
using Microsoft.Bot.Connector;
using Microsoft.Bot.Builder.FormFlow;
using Bot_Application1.Models;

namespace Bot_Application1
public class MessagesController : ApiController
/// <summary>
/// POST: api/Messages
/// Receive a message from a user and reply to it
/// </summary>
public async Task Post([FromBody]Activity activity)
if (activity.Type == ActivityTypes.Message)
// call MyDialog
// await Conversation.SendAsync(activity, () => new Dialogs.MyDialog());

// Call our FormFlow by calling MakeRootDialog
await Conversation.SendAsync(activity, MakeRootDialog);

var response = Request.CreateResponse(HttpStatusCode.OK);
return response;

internal static IDialog<LeadModel> MakeRootDialog()
return Chain.From(() => FormDialog.FromForm(LeadModel.BuildForm));

private Activity HandleSystemMessage(Activity message)
if (message.Type == ActivityTypes.DeleteUserData)
// Implement user deletion here
// If we handle user deletion, return a real message
else if (message.Type == ActivityTypes.ConversationUpdate)
// Handle conversation state changes, like members being added and removed
// Use Activity.MembersAdded and Activity.MembersRemoved and Activity.Action for info
// Not available in all channels
else if (message.Type == ActivityTypes.ContactRelationUpdate)
// Handle add/remove from contact lists
// Activity.From + Activity.Action represent what happened
else if (message.Type == ActivityTypes.Typing)
// Handle knowing tha the user is typing
else if (message.Type == ActivityTypes.Ping)

return null;

The next posts in this series

Hope it helps

Update Cell value in Excel Spreadsheet using C# (Open XML and EPPlus library)

Sharing a sample code that updates a particular cell’s value in Excel Spreadsheet.

Here we are updating cell B4

First using Open XML SDK

public static void UpdateExcelUsingOpenXMLSDK(string fileName)
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(fileName, true))
// Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = spreadSheet.WorkbookPart;
// get sheet by name
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();

// get worksheetpart by sheet id
WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;

// The SheetData object will contain all the data.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();

Cell cell = GetCell(worksheetPart.Worksheet, "B", 4);

cell.CellValue = new CellValue("10");
cell.DataType = new EnumValue<CellValues>(CellValues.Number);

// Save the worksheet.

// for recacluation of formula
spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;


private static Cell GetCell(Worksheet worksheet,
string columnName, uint rowIndex)
Row row = GetRow(worksheet, rowIndex);

if (row == null) return null;

var FirstRow = row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, columnName +
rowIndex, true) == 0).FirstOrDefault();

if (FirstRow == null) return null;

return FirstRow;

private static Row GetRow(Worksheet worksheet , uint rowIndex)
Row row = worksheet.GetFirstChild<SheetData>().
Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
if (row == null)
throw new ArgumentException(String.Format("No row with index {0} found in spreadsheet", rowIndex));
return row;

Now the same code using EPPlus

public void UpdateExcelUsingEPPlus(string fileName)
FileInfo fileInfo = new FileInfo(fileName);
ExcelPackage p = new ExcelPackage(fileInfo);
ExcelWorksheet myWorksheet = p.Workbook.Worksheets["Sheet1"];
myWorksheet.Cells[4, 2].Value = 10;


Hope it helps..

Scheduling a Web Job (console application) using Azure Scheduler

Let us a create a simple console application that writes Hello World to the console which we would be scheduling using Azure Scheduler.

Have kept the example simple so that we can understand the configuration part.

Right click on the project and Publish it as Azure WebJob

We will set the mode as “Run On Demand”

For the pubish target we have selected Microsoft Azure App Service web app under which this Web Job will run.

Select the App Service or configure a new one, we have selected the existing App Service below

Click on Validate to check the connection, if everything is fine Click Next and Publish the web role.

Our TriggeredWebJob inside CRMAzureWebJob App Service

We can run it on Demand by clicking on Run.

Clicking on Logs

Will provide the details of the Web Jobs

Now to schedule the Web Job we need to create a Scheduler Job, for this go to Scheduler Job Collections and click on Add

Create a new Job inside Job Collection and provide the required details like Name, Pricing Tier, Resource Group etc.

Next step is to configure the Action settings, wherein we will trigger our Web Job.

For this we need to define Action as Https, Method as Post and the URL

Format of the URL should be like –


To get the above details open the Web Job properties

Replace the appropriate values


Last step is to schedule it

Here we have selected it to run immediately, every 2 minutes for total of 10 occurrences.

Finally click on Create which will create the job

We can monitor our Job

Going back to our Web Job Logs we can see it ran 3 times with interval of 2 minutes till that point

The other option to schedule a Web Job without using Azure Scheduler is to use the Cron Job but this requires minimum Basic tier subscription. Basically, the Web App must be configured for “Always On”, which requires a Basic or Standard plan.

And in Free tier

Hope it helps..

Sample Code to consume UNData API using JavaScript

Sharing a sample code that can be used to consume UNData API

The below code fetches the population data for country India.

Sample Code

var xmlHttp;
// creatng the xmlHttp object
xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");
// Calling the web service using post and true means asynchronous call"POST", "", false);

// Setting the request header to let the web service find the soap request we would be sending
xmlHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");

xmlHttp.setRequestHeader("SOAPAction", "");

var soapRequest = "<!--?<span class="hiddenSpellError" pre="" data-mce-bogus="1"-->xml version='1.0' encoding='utf-8'?>"+
"<soap:Envelope xmlns:soap='' xmlns:xsi='' xmlns:xsd=''>"+
"<GetCompactData xmlns=''>"+
"<message:QueryMessage xsi:schemaLocation='' xmlns:generic='' xmlns:message='' xmlns:query='' xmlns:xsi=''>"+
"<message:Sender id='online-help' />"+
"<query:Dimension id='INDICATOR'>SP_POP_TOTL</query:Dimension>"+
"<query:Dimension id='AGE'>_T</query:Dimension>"+
"<query:Dimension id='SEX'>_T</query:Dimension>"+
"<query:Dimension id='LOCATION'>_T</query:Dimension>" +
"<query:Dimension id='REF_AREA'>DEU</query:Dimension>" +
"<query:Dimension id='SCENARIO'>H</query:Dimension>"+
"</soap:Body>" +

var xmlDoc = jQuery.parseXML(xmlHttp.responseText);
var popValue = xmlDoc.getElementsByTagName('ns1:Obs')['0'].attributes['OBS_VALUE'].nodeValue;
alert("Total Population " + popValue);


<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap=""
<Sender id="1B0">
<Name xml:lang="en">United Nations Statistics Division</Name>
<Source xml:lang="en">
United Nations Population Division World Population Prospects</Source>
<ns1:DataSet DATASET_NAME="World Population Prospects"
DATASET_SOURCE="United Nations Population Division"
DATASET_DESC="The United Nations World Population Prospects contains estimates and projections for every country in the world, including estimates and projections of demographic indicators such as birth rates, deaths rates, infant mortality rates and life expectancy." UNDATA_LASTUPDATE="20 Aug 2013" UNDATA_NEXTUPDATE="2015 release" METADATA=""><ns1:Series AGE="_T" FREQ="A" INDICATOR="SP_POP_TOTL" LOCATION="_T" SEX="_T" REF_AREA="DEU" SCENARIO="H"><ns1:Obs TIME_PERIOD="2015" OBS_VALUE="83178.615" UNIT="NUMBER" UNIT_MULT="3" TIME_SPAN="2015" /></ns1:Series></ns1:DataSet></CompactData></GetCompactDataResult></GetCompactDataResponse></soap:Body></soap:Envelope>

Hope it helps.

Sample code to consume UNData API in C#


Recently we were working on an application that had to fetch country specific details. For this we used the UNData API.

We can make use of SDMX Browser in helping us to write a query

Create a console app\windows application and add web reference to the following web service

Below is the sample C# Code

string queryMsg = @"<message:QueryMessage
<message:Sender id='online-help'/>
<query:Dimension id='INDICATOR'>SP_POP_TOTL</query:Dimension>
<query:Dimension id='AGE'>_T</query:Dimension>
<query:Dimension id='SEX'>_T</query:Dimension>
<query:Dimension id='LOCATION'>_T</query:Dimension>
<query:Dimension id='REF_AREA'>IND</query:Dimension>
NSIEstatV20Service service = new NSIEstatV20Service();
System.Xml.XmlDocument queryMessageDocument = new XmlDocument();
System.Xml.XmlNode queryMessage = queryMessageDocument.DocumentElement;
var result = service.GetCompactData(queryMessage);

The output

The query here basically returns the Total population of India

Hope it helps..

.NET Framework 4.5.2 missing in Visual Studio 2013.

Hi even after installing .NET Framework 4.5.2, it was missing from Visual Studio 2013’s Target framework.

Had to download and install it from the following location

Hope it helps..