** THIS SITE IS AN ARCHIVE ** - New Content is at:

Keywords

Phrase

Advanced SharePoint Workflows Using HTTP Calls To LightSwitch

Dec 25

Written by: Michael Washington
12/25/2014 7:45 PM

You can create advanced enterprise applications when you incorporate Visual Studio LightSwitch Cloud Business Apps and SharePoint Workflows into your solutions. Microsoft SharePoint workflows are powerful, but the non-code version created with the SharePoint Designer do not allow you to implement complex data bound business rules without making web service HTTP calls. You can make these calls to Visual Studio LightSwitch Cloud Business Apps.

In this article we will enhance the vacation request application that we started in An End-To-End LightSwitch SharePoint Workflow Application. In that version, a vacation was automatically approved if it was more than 7 days away. In this version, we will create a table that will contain the vacation days for each employee. The workflow will automatically approve any vacation as long as a employee has vacation days. It will deduct the days used from the table. Only when the employee runs out of days will a manager have to approve the vacation.

The Application

Users of your SharePoint site can create vacation requests by opening the custom list.

They click the new item link to add a new request.

They enter their requested days and click the Save button.

If they have enough vacation days, the vacation is automatically approved.

They also receive an email.

If the employee does not have enough vacation days, the status is set to Pending and the members of the Vacation Approvers group receive an email that instructs them to access the LightSwitch Cloud Business App to approve it.

The member of the Vacation Approver group logs into the LightSwitch Cloud Business App.

Selects the request…

… and approves (or denies) it.

The LightSwitch Application

The first step is to add a table to the LightSwitch Cloud Business App (from the pervious tutorial) to track the number of vacation days each employee has.

We call the table VacationDays and we use the structure in the image above.

We now need to create a screen to manage the vacation days.

We right-click on the Screens folder and select Add Screen.

Select the Common Screen Set and the VacationDaysSet data source.

The screens will be created.

When we run the application…

We can navigate to the new screens using the main menu drop down.

The screens will allow you to manage the vacation days.

Creating The Workflow Connector

We will now create a file handler that will allow the SharePoint Workflow to communicate with the LightSwitch Cloud Business App.

Right-click on the Server project and select Add then New Item.

Create a Generic Handler.

Use the following code:

using Microsoft.LightSwitch.Server;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace LightSwitchApplication
{
    public class WorkflowConnector : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            // Get the parameters passed by the Workflow
            string paramPassword = 
                Convert.ToString(context.Request.QueryString["Password"]);
            string paramTitle = 
                Convert.ToString(context.Request.QueryString["Title"]);
            DateTime paramStartDate = 
                Convert.ToDateTime(context.Request.QueryString["StartDate"]);
            DateTime paramEndDate = 
                Convert.ToDateTime(context.Request.QueryString["EndDate"]);
            string paramUserName = 
                Convert.ToString(context.Request.QueryString["UserName"]).ToLower();
            string strResponse = "Not Found";
            // Simple security
            if (paramPassword == "pass14")
            {
                int intId = -1;
                int intDays = 0;
                // Connect to the database
                // Do this using ADO.NET because we don't have an active 
                // LightSwitch Dataworkspace at this point
                string connString =
                    System.Web.Configuration.WebConfigurationManager
                    .ConnectionStrings["_IntrinsicData"].ConnectionString;
                SqlConnection conn = new SqlConnection(connString);
                string sql = 
                    "Select Id, Days FROM VacationDaysSet where UserName=@UserName;";
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@UserName", paramUserName);
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    intId = Convert.ToInt32(reader[0]);
                    intDays = Convert.ToInt32(reader[1]);
                }
                reader.Close();
                if (intId > -1) // Found Days
                {
                    // Get the User's Days
                    int intDaysAvailable = intDays;
                    // Determine how many days are needed
                    int intDaysRequested = (paramEndDate - paramStartDate).Days;
                    // See if there are enough days
                    if (intDaysAvailable >= intDaysRequested)
                    {
                        // Deduct the days
                        int intNewDays = (intDays - intDaysRequested);
                        string sql2 = 
                            "UPDATE VacationDaysSet SET Days=@VacationDays where ID=@Id;";
                        SqlCommand cmd2 = new SqlCommand(sql2, conn);
                        cmd2.Parameters.AddWithValue("@VacationDays", intNewDays);
                        cmd2.Parameters.AddWithValue("@Id", intId);
                        cmd2.CommandType = CommandType.Text;
                        cmd2.ExecuteNonQuery();
                        strResponse = "Approved";
                        context.Response.StatusCode = 201;
                    }
                    else // Not enough days
                    {
                        strResponse = "User does not have enough days.";
                    }
                }
                else // Did not find a record for the user
                {
                    strResponse = "User has no days available.";
                }
                conn.Close();
            }
            else
            {
                // Bad Password
                strResponse = "Bad Password";
            }
            // Create JavaScriptSerializer
            System.Web.Script.Serialization.JavaScriptSerializer jsonSerializer =
                new System.Web.Script.Serialization.JavaScriptSerializer();
            // Output as JSON
            context.Response.Write(jsonSerializer.Serialize(strResponse));
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

This will set the Response.StatusCode to 201 (“CREATED”) if the vacation is approved. Otherwise it will return a 200 code (“OK”).

You will need to add a reference, in the Server project, to System.Configuration and System.Web.Extensions.

Next, open the Web.config.

Add the following entry to allow the WorkflowConnector.ashx handler to be called by unauthenticated clients:

(put under the configuration node)

  <location path="WorkflowConnector.ashx">
    <system.web>
      <authorization>
        <allow users="?" />
      </authorization>
    </system.web>
  </location>

(note: you can authenticate a workflow call using the methods described by Andrew Connell in his Pluralsight course: SharePoint 2013 Workflow - Web Services)

Deploy The Application

At this point you will want to deploy the LightSwitch application to production (see: Creating A LightSwitch SharePoint 2013 Multi-Tenant Provider-Hosted Application for details). The reason is that the SharePoint Workflow needs the WorkflowConnector.ashx handler at a location that it can reach.

Update The Workflow

We open the original workflow.

We delete the first action and insert an HTTP action.

We construct the action to call the WorkflowConnector.ashx, passing the values from the current list item.

The image above shows the complete workflow after the alterations.

If you have Visio installed you can select the Visual Designer…

To view and design the SharePoint Workflow.

Deploying To Production

You can download the LightSwitch application (available on the download page), deploy it to production, create the SharePoint list and workflow and it will work.

When you deploy the application to production, you will want to follow the method in this article:

Creating A LightSwitch SharePoint 2013 Multi-Tenant Provider-Hosted Application

In addition, you can have the application automatically create the needed SharePoint Vacation Request list in your production site by using the method described in this article:

Implementing Documents in a SharePoint 2013 Cloud Business App (LightSwitch)