Dec 25

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

image

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

image221

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

image225

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

image229

They enter their requested days and click the Save button.

image233

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

image237

They also receive an email.

image241

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.

image250

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

image245

Selects the request…

image254

… and approves (or denies) it.

The LightSwitch Application

image

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.

image

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

image

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

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

image

Select the Common Screen Set and the VacationDaysSet data source.

image

The screens will be created.

image

When we run the application…

image

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

image

The screens will allow you to manage the vacation days.

Creating The Workflow Connector

image

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.

image

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”).

 

image

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

image

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

image

We open the original workflow.

image

We delete the first action and insert an HTTP action.

image

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

image

The image above shows the complete workflow after the alterations.

image

If you have Visio installed you can select the Visual Designer

image

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)

Links – Microsoft

Overview of workflows included with SharePoint

SharePoint 2013 workflow samples

Get started with workflows in SharePoint 2013

SharePoint 2013 workflow fundamentals

Links – Andrew Connell

Workflow - Andrew Connell

Andrew Connell – Pluralsight author

SharePoint 2013 workflow: Call an External Web Service

Links – LightSwitch Help Website

An End-To-End LightSwitch SharePoint Workflow Application

Deploy A LightSwitch Application To Office 365 / SharePoint Online

Exploring SharePoint 2013 Visual Studio Cloud Business Apps (LightSwitch)

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

An End-to-End AngularJS SharePoint Module using LightSwitch OData as a Back-End

Creating A SharePoint Online Testing Site

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

Download Code

The LightSwitch project is available at http://lightswitchhelpwebsite.com/Downloads.aspx

(you must have Visual Studio 2013 (or higher) installed and a SharePoint Developer site to run the code)


Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 
Microsoft Visual Studio is a registered trademark of Microsoft Corporation / LightSwitch is a registered trademark of Microsoft Corporation