PROBLEM CASE

Display Washington, D.C. mall monuments with the number of visitors in a year and the landmarks’ popularity ratings. Publish this diagram to SharePoint, and use Visio Services to display real-time data.

Solution

1. Create a new database named VisioServices.
2. Create a table named tblLandmarks in the new database, and create the columns shown in Figure 2-41. Populate the columns with data as shown in Figure 2-42.

   

   Figure 2-41. Design a custom SQL table structure

   

   Figure 2-42. Populating the custom table with sample data

3. Open Visio 2013, and create a new diagram. From More Shapes Maps and Floor Plans Map, choose Landmark Shapes (US Units) and add some of the available shapes to the page. Provide Titles for all the shapes either by using the Shape Data window or the shape’s label. When you create each label, make sure it matches the values in the Landmark column in Figure 2-42.
4. Use Link Data to Shapes, choose the Microsoft SQL Server database option, and then click Next.
5. In the Data Connection wizard, enter the Server name and the Log-on credentials, either Windows Authentication or User Name and Password. Click Next to continue.

    Note  While communicating with SQL Server through Visio Services, you’ll need to configure an additional setting. If you’re using SQL Server with Windows Authentication, you’d connect using the following:

   Kerberos/Integrated Windows Authentication. This setting uses the current logged-in user account, which requires ­Kerberos delegation to authenticate to the database server. For more information on Kerberos, see http://technet.microsoft.com/en-us/magazine/ee914605.aspx.

   Secure Store Service (SSS). This setting uses either an individual user or group that’s mapped for authentication while using an office data connection file (*.ODC). SSS is explained in more detail later in this chapter.

   Unattended Service Account. This is a low-privilege Windows account mapped to a target application that is impersonated while connecting to the database. (This is also what you’d use with SQL Authentication.) We will be using this model with SSS while connecting to SQL Server as a data source.

6. From the available databases, choose VisioServices and select the tblLandmarks table. Click Next to continue.
7. The next step gives you the option to save the data connection file (.odc) as a physical file. Click on Browse to choose where to save the .odc   ⁶ file (or skip this step by clicking on Finish). Provide a friendly name for the file, and click Finish to complete the wizard.

    Note  To create a data-refreshable web drawing, you have to save the .odc file to the same SharePoint site location as the data connection library.

8. On the Data Selector screen, choose the columns and rows to include and click Next to continue. After a successful import message, click on Finish.
9. The External Data window will now load and display the data from the selected database table.
10. Make sure that the text in the Landmark column is identical to the Title of the shapes. Drag row data from the External Data window to the shape on the page. If the column text is identical to the shape text, the link will be created successfully and you can then create the data graphics for the shape.
11. Create the Data Graphics as explained earlier, and choose the Ratings data field. Deselect Use default position, and set the custom position for the Data Bar Style as horizontally Center and vertically Above Shape.
12. Add the Visitors data field, and choose the Thermometer Data Bar style. Position it Far Right horizontally and Middle vertically. Click OK to close the Data Graphics wizard.
13. Use the Automatically Link all shapes option to connect all the rows of data to the respective shapes. Choose the data column Landmark to match the shape field Shape Text, and click on Finish button.
14. Now use the Insert Legend option to insert legend and rename the page as DC Landmarks.
15. The web drawing should now look like the one in Figure 2-43.

    

    Figure 2-43. Visio drawing shapes connected to a SQL Server database table

     Tip  I created the diagram in Figure 2-43 on a blank stencil using images and shapes from Landmark Shapes (US units).

16. If the data changes and you want to make sure the drawing reflects the updates, choose either Refresh All or Refresh Data on the Data tab.
17. Choosing either of these options opens the Refresh Data window (Figure 2-44), which displays all data sources related to this diagram. You can refresh one data source or all of them together. To configure a data source, just select it and choose Configure. Close the window when you’re finished.

    

    Figure 2-44. The Refresh Data window

18. Now that you finished the diagram, publish it to the SharePoint environment. Choose File Save As SharePoint Browse (select sharepoint document library) Visio Drawing to save the file to SharePoint as Mall.vsdx.
19. Alternatively, you can save the Mall.vsdx file locally, open the SharePoint site, and upload Mall.vsdx to the Visio Library.
20. Click on the Visio drawing you uploaded, and view the drawing that is rendered in the browser.
21. If SQL Server is on the same machine, the drawing will render correctly. However, if you are accessing SQL Server from a different machine or if you are on a domain controller, you’ll see the error shown in Figure 2-45.

    

    Figure 2-45. Error refreshing the data connection in the Web drawing

    If you encounter this error, it’s because of a scenario well-known as the double-hop issue.

THE DOUBLE-HOP ISSUE

Although you can use impersonated credentials locally, Windows won’t forward impersonated credentials to a remote resource (the second hop). So the user’s identity is lost even before he reaches the back end. And in this scenario, SharePoint can’t pass the credentials of the logged-in user all the way to the back end via the services. This scenario is known as The Double-Hop problem.

To solve the problem, a mechanism is needed by which the logged-in user can be impersonated “as someone else” to use Visio services and connect to the back-end data. You can achieve this by using the Secure Store Services (SSS) in SharePoint.

PROBLEM CASE

Using the previously created Mall.vsdx, display the details of each monument by clicking the shape.

Solution

1. Create a new page under the site pages, and name it Mall.aspx.
2. Insert a Visio Web Access web part, from the Business Data Categories, on the page.
3. After you add the web part, make sure the page is in edit mode and click the link that says Click here to open the tool pane.
4. From the Visio Web Access configuration window, choose Mall.vsdx from the Visio Library document library and click OK.
5. Click OK on the configuration window.
6. Save and close the page to view the Visio diagram rendered on the web page.
7. Right-click on the browser, click View Source on the context menu, and search for the text class=“VisioWebAccess.”
8. Find the corresponding id of the td that you should find just above the div. Usually, it will be WebPartWPQ2 on a freshly created site page.
9. Open Notepad, and paste the code in Listing 2-1 into it. Save this as the file assets.js.

   Listing 2-1.  Listing 2-1. Assets.js

   <script language="javascript">
   var _application = Sys.Application;
   var _visioWebPart;
   var onShapeSelectionChanged = null;
   _application.add_load(onApplicationLoad);
   function onApplicationLoad()
   {
       // Change the control id based on the code on your page
       _visioWebPart= new Vwa.VwaControl("WebPartWPQ2");
       _visioWebPart.addHandler("diagramcomplete", onDiagramComplete);
       _visioWebPart.addHandler("shapeselectionchanged", onShapeSelectionChanged);
   }
   function onDiagramComplete()
   {
       var _page = _visioWebPart.getActivePage();
       _page.setZoom(85);
   }
   onShapeSelectionChanged = function(source, args)
   {
       var _activePage = _visioWebPart.getActivePage();
       var _shape = _activePage.getShapes();
       var _shapeItem = _shape.getItemById(args);
       var _shapeData = _shapeItem.getShapeData();
       var _description = "";
        
       for (var j = 0; j < _shapeData.length; j++)
       {
           if (_shapeData[j].label == "Details")
           {
               _description = _shapeData[j].value;
               continue;
           }
       }
       document.getElementById('landmarkDetails').firstChild.data = _description;
   }
   </SCRIPT>
   <div id="landmarkDetails" style="font-family: Verdana; font-style: bold; font-size:14pt; color:red;">landmark details...</div>

10. Upload the assets.js file into the Visio Library document library.
11. Right-click on assets.js, and by using the Copy Shortcut menu, copy the URL to the clipboard.
12. Open the Mall.aspx site page in edit mode, and add the Content Editor Web part from the Media and Content category as shown in Figure 2-57.

    

    Figure 2-57. Adding the Content Editor web part to the page

13. Edit the Content Editor web part, and paste the link to assets.js in the Content Link text box.
14. Verify the link by clicking on Test link URL below the text box. If the URL is accurate, the assets.js file will be downloaded to your disk.
15. Click Apply and OK on the editor part pane to return the page to normal mode.
16. Save and close the page.
17. You should now see that the Content Editor web part displays the text “landmark details…” in red, as shown in Figure 2-58.

    

    Figure 2-58. The Content Editor web part with the Visio Web Access web part

18. Click on any shape—for instance, on the White House—and see that the text on the Content Editor web part changes to the White House details, as shown in Figure 2-59. Try clicking on other shapes. Notice that the text changes on the Content Editor web part instantly without a page refresh.

    

    Figure 2-59. Interacting with the VWA web part and the drawing using the JavaScript Mashup API

Now that you have successfully created a simple JavaScript mashup with a Visio diagram, try to understand the code. One of the major reasons JavaScript was chosen to build the API is because of its simplicity.

PROBLEM CASE

One of the leading fast-food chains (let’s call it  “Tasty Foods”) wants to collect feedback from its customers on various food items. The feedback form must be filled out by the customer using an online web application. Customers must enter their full name, food item, and e-mail address, and they must leave comments. If the customer doesn’t enter a comment, the workflow status will be rejected; if she does, the status will be approved.

There are three major components that will be used in this case:

• A SharePoint list, used as the feedback form (responsibility of the developer).
• A Visio diagram to create the workflow design (responsibility of the business analyst).
• SharePoint Designer to enable the workflow and deploy (attach) it to the list (responsibility of the developer).

Solution

1. Create a custom list, add a few columns as shown in Figure 2-60, and save it as Tasty Foods Feedback.

   

   Figure 2-60. The Tasty Foods custom list

   The Fooditem Choice column can contain items such as sandwiches, chicken, breakfast, salads, snacks and sides, beverages, coffee, desserts, shakes, etc.

2. Open Visio 2013, and create a new Visio diagram using Microsoft SharePoint 2013 Workflow (US units). You’ll find this in the Flowchart section.
3. Create a simple workflow as shown in Figure 2-61, using shapes that compare two values, send e-mail, and set the workflow status.

   

   Figure 2-61. Simple workflow designed in Visio using SharePoint templates

    Note  A new SharePoint 2013 workflow starts with an empty container called Stage, instead of a blank canvas (as was the case with SharePoint 2010). A stage acts as a container for logically related sections of the workflow. Stages allow simplification of complex workflows. Simple workflows, like the one in our example, are generally single-stage workflows.

4. From the File menu, save the diagram (as a .vsdx file) to your local file system.
5. Close Visio, and open the SharePoint Designer 2013 client.
6. Open the SharePoint site where you created the Tasty Foods Feedback custom list, as shown in Figure 2-62.

   

   Figure 2-62. Site containing Tasty Foods Feedback list opened in SharePoint Designer 2013

7. From the menu, click on File and select Import Visio 2013 Workflow, as shown in Figure 2-63.

   

   Figure 2-63. Importing a Visio 2013 workflow in SharePoint Designer

8. Provide a name for the workflow, and select Tasty Foods Feedback as the SharePoint list to which the workflow is to be associated, as shown in Figure 2-64.

   

   Figure 2-64. Specifying the workflow name and SharePoint list association

9. This opens the workflow in Visual Designer view. From the Views menu on the Workflow tab on the ribbon, select Text-Based Designer to switch to text based designer mode, as shown in the Figure 2-65.

   

   Figure 2-65. Visio workflow imported in the SharePoint Designer workflow editor

10. As you can see in Figure 2-65, two conditions are automatically created under the editor because there are two flows when comparing the data. (Refer to Figure 2-61.) This includes the send an email process step, set the workflow status, and the end the process step for both flows.
11. Modify each of the conditions to meet the requirements. For instance, under the If condition (Figure 2-65), set the value to look up from the custom list datasource and choose the field (Comments) for looking up the value. Change the equals operator to is empty.
12. If this condition is true, set the Email “these users” to Current Item:Email Address. Configure Subject and message, and then Set workflow status to Rejected from the look-up values.
13. Under the “Else” condition, set Email “these users” to Current Item:Email Address. Configure Subject and message, and then Set workflow to Approved.
14. Finally, set the Title of the Stage as “Tasty Foods Feedback Workflow” as shown in Figure 2-66.

    

    Figure 2-66. Setting up the workflow conditions in SharePoint Designer

15. From the Workflow tab on the ribbon, select Workflow Settings. From the Start Options, you can choose to start the workflow automatically either when the item is created or when it is changed. (See Figure 2-67.)

    

    Figure 2-67. Setting workflow start-up options in SharePoint Designer

16. From the Workflow tab on the ribbon, click the Check for Errors button and then save the workflow (Figure 2-68).

    

    Figure 2-68. Save and Publish the workflow to the SharePoint list

17. Publish this workflow to the list using the Publish button on the Workflow tab.

    These steps will validate and publish the workflow to the corresponding list.

18. Open the SharePoint site and the Tasty Foods Feedback list.
19. Go to List List Settings, and click on the Workflow Settings in the Permissions and Management section. You can also access the Workflow settings from List Workflow Settings (Settings section on the ribbon) Workflow Settings (from the drop down).

    Locate the Workflow you just submitted. If the workflow is published more than once, a new version is created.

20. Return to the list, and click new item, which brings up the New Item screen as shown in Figure 2-69.

    

    Figure 2-69. Adding a new item to the feedback form

21. Create two items: one with comments and one without comments. Because the workflow hasn’t been set to start automatically, you’ll have to start it manually after any item is created. Choose the item on which you’d like to run the Workflow, and click the Workflows button on the Items tab, as shown in Figure 2-70.

    

    Figure 2-70. Starting a workflow for the list item

22. This opens the Start a New Workflow window, displaying the available workflows, as Figure 2-71 shows.

    

    Figure 2-71. Starting a new workflow

23. Click on the Tasty Foods Feedback Workflow link. This will execute the rules underlying the workflow and apply any changes to the respective columns.
24. Notice that under the Tasty Foods Feedback column (Figure 2-72), the status is set based the on the comments provided by customer. The status is Approved when the customer fills in the Comments field and Rejected when the customer doesn’t add any comments.

    

    Figure 2-72. Workflow status after items are added

PROBLEM CASE

Display the status of the test environment servers whether they are online or offline. Note that current status information is in a SQL Server database and can be retrieved using Windows Communication Foundation (WCF) services. You will need to get the data and connect to the shapes.

Solution

The major components in this scenario are SQL Server, WCF Service, Visio, and Security:

• We’ll begin with a SQL Server database and then write a WCF Service to retrieve data from the database table.
• We’ll write a custom data provider and configure its settings in the Central Administration site of the SharePoint environment.
• We’ll write a VBA script to create the data source and connect the data source to the shapes in the Visio diagram.
• We’ll deploy the .vdw file to a SharePoint environment and view the output.

Retrieving server status information stored in a database table

1. Create a table named tblServerStatus with columns as shown in Figure 2-73 in the VisioServices database you created earlier.

   

   Figure 2-73. Server Status Table in SQL Server

2. Populate a few rows of this table with information as shown in Figure 2-74.

   

   Figure 2-74. Populating rows with data for the server status table in SQL Server

3. Create a stored procedure named uspGetServerDetails that will execute the SQL query to retrieve the values from the table in Figure 2-74. Listing 2-2 shows the code.

   Listing 2-2.  The uspGetServerDetails Stored Procedure

   USE [VisioServices]
   GO
   CREATE PROCEDURE [dbo].[uspGetServerDetails]
   AS
   BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;
          -- Insert statements for procedure here
          SELECT ServerName, ServerIP, ServerStatus from tblServerStatus
   END

    Note  The stored procedure code is explained toward the end of this chapter in the section “Understanding the Custom Data Provider Code.”

   Writing the WCF Service library and Hosting it in IIS

4. Open Microsoft Visual Studio 2012, choose to create a new project, and from the available templates select Visual C# WCF WCF Service Application. Then provide a proper Name and Location. Make sure you pick .NET Framework 4.5. This creates a service class (Service1.svc) and an interface (IService1.cs).
5. Rename the default Service1.svc file and the interface class to ServerStatus.svc and IServerStatus.cs, respectively. Ensure that all the references under the project reflect this name change.
6. After completing these steps, your project should look like the one in Figure 2-75.

   

   Figure 2-75. Setting up a WCF service application project in Visual Studio 2012

7. Open the Web.config file, and add an entry under the connectionStrings section (connection string to the VisioServices SQL server database) as follows:

   <connectionStrings>
       <add name="VisioServicesConnectionString" connectionString="Data Source=ServerName;
   Initial Catalog=VisioServices;Integrated Security=SSPI;User Id=userId;Password=pwd"
   providerName="System.Data.SqlClient"/>
     </connectionStrings>

    Note  The code in Listing 2-3 is explained toward the end of this chapter in the section “Understanding the Custom Data Provider Code.”

8. Open the ServerStatus.svc.cs file, and paste the code from Listing 2-3 in it.

   Listing 2-3.  WCF Service Library Class to Retrieve Server Status

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Runtime.Serialization;
   using System.ServiceModel;
   using System.Text;
   using System.Data;
   using System.Data.Sql;
   using System.Data.SqlClient;
   using System.Configuration;
   namespace WcfDataProvider
   {
     
       public class ServerStatus : IServerStatus
       {
           [OperationBehavior]
           public DataTable GetServerStatusDetails()
           {
               DataSet dataSet = new DataSet();
               SqlConnection connection = new SqlConnection
   (ConfigurationManager.ConnectionStrings["VisioServicesConnectionString"].ConnectionString);
     
               SqlCommand command = new SqlCommand("uspGetServerDetails", connection);
               command.CommandType = CommandType.StoredProcedure;
               SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
     
               dataAdapter.Fill(dataSet);
               connection.Close();
               return dataSet.Tables[0];
           }
       }
   }

    Note  It is a good practice to add a Test Project for any projects you create because this would perform the first level of unit testing for your projects.

9. Now copy the code in Listing 2-4, open the IServerStatus.cs file, and paste the code in it.

   Listing 2-4.  WCF Service Library Interface Class

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Runtime.Serialization;
   using System.ServiceModel;
   using System.Text;
   using System.Data;
   namespace WcfDataProvider
   {
       // NOTE: You can use the "Rename" command on the "Refactor" menu to change the
       // interface name "IServerStatus" in both the code and config file together.
       [ServiceContract]
       public interface IServerStatus
       {
           [OperationContract]
           DataTable GetServerStatusDetails();
       }
   }

10. Build the WCF project in release mode, and make sure you have no errors.
11. Create a local folder in your file system under C:, and name it wcfServicehost.
12. Make a folder called bin.
13. Copy the WcfDataProvider.dll file to the bin folder.
14. Make sure Network Service and IIS_IUSRS users have Read & Execute permissions on the root folder.
15. Create a file named VisioWcfServices.svc under the wcfServicehost folder, and add the following text:

    <% @ServiceHost Service="WcfDataProvider.ServerStatus" %>

16. Create a Web.config file in the same folder, and copy the code from Listing 2-5 to it.

    Listing 2-5.  WCF Data Provider Web.config File Settings

    <?xml version="1.0" encoding="utf-8"?>
      <configuration>
        <system.serviceModel>
          <services>
            <service behaviorConfiguration="WcfDataProvider.ServerStatusBehavior"
                           name="WcfDataProvider.ServerStatus">
              <endpoint address="" binding="basicHttpBinding"
                           contract="WcfDataProvider.IServerStatus" />
              <host>
                <baseAddresses>
                  <add baseAddress=" http://localhost:<portnumber>/visiowcfservices.svc " />
                </baseAddresses>
              </host>
            </service>
          </services>
          <behaviors>
            <serviceBehaviors>
              <behavior name="WcfDataProvider.ServerStatusBehavior">
                <serviceMetadata httpGetEnabled="true" />
                <serviceDebug includeExceptionDetailInFaults="false" />
               </behavior>
            </serviceBehaviors>
          </behaviors>
        </system.serviceModel>
       <connectionStrings>
          <add name="VisioServicesConnectionString" connectionString="Data Source=localhost;Initial Catalog=VisioServices;Integrated Security=SSPI;User Id=uid;Password=pwd"
    providerName="System.Data.SqlClient"/>
        </connectionStrings>
      </configuration>

    After completing the preceding steps, your folder structure and files should look similar to those in Figure 2-76.

    

    Figure 2-76. WCF service host folder

17. Open IIS, and create an application pool by providing a Name, .NET Framework version, and pipeline mode.
18. Create a new web site. Choose the app pool you just created, provide a site name, and choose the local folder where you copied the .dll file as the physical path. Under binding, provide a Port number that’s not in use.
19. Open the browser, and type the URL http://localhost:<portnumber >/ VisioWcfServices.svc; to see the output shown in Figure 2-77.

    

    Figure 2-77. Accessing the WCF service

     Note  For more information on how to create WCF services, go to http://blah.winsmarts.com/2008-4-Host_a_WCF_Service_in_IIS_7_-and-amp;_Windows_2008_-_The_right_way.aspx.

    Creating the custom data provider project

20. Open Microsoft Visual Studio 2012, create a new project, and from the available templates pick Visual C# Office/SharePoint SharePoint Solutions SharePoint 2013 - Empty Project. Make sure you choose .NET Framework 4.5. Provide a Name, Location, and Solution Name, and click OK. Create the project name as VisioDataService.
21. In the next screen, enter the SharePoint site URL for debugging and choose Deploy as a farm solution for the trust level. Click on Finish.
22. Right-click on the project, select the properties, and make sure the Assembly name and default namespace fit your situation.
23. Right-click on the project, and choose Add Service Reference.
24. From the wizard, click Discover and make sure there’s a reference to the WCF service you built earlier. Alternatively, you can enter the WCF URL (http://localhost:<portnumber>/VisioWcfServices.svc) in the Address text box. Provide the namespace as WcfDataService and click OK.
25. Right-click on the project, and choose Add Reference. From the Add Reference window .NET tab, choose the System.Web component and click OK.
26. Right-click on the project again, and choose Add Reference. From the Add Reference window Browse tab, browse to the folder C:WindowsMicrosoft.NETassemblyGAC_MSILMicrosoft.Office.Visio.Serverv4.0_15.0.0.0__71e9bce111e9429c, choose the file Microsoft.Office.Visio.Server.dll, and click OK.

    At this stage, the project structure should resemble the one in Figure 2-78.

    

    Figure 2-78. WCF consumer project structure

     Note  The code in Listing 2-6 is explained toward the end of this chapter in the section “Understanding the Custom Data Provider Code.”

27. Add the VisioDataProviderService.cs class file to the project, and copy the code in Listing 2-6 into the file. Make sure you have the correct namespaces as per your project settings.

    Listing 2-6.  Visio Custom Data Provider Service Class Code

    using System;
    using System.Data;
    using System.Threading;
    using System.Xml;
    using System.Web;
    using Microsoft.Office.Visio.Server;
    using VisioDataService.WcfDataService;
    namespace DataModules
    {
        public class VisioDataProviderService: AddonDataHandler, IAsyncResult
        {
            private object _asyncState;
            private bool _completeStatus;
              
            WaitHandle IAsyncResult.AsyncWaitHandle
            {
                get { return null; }
            }
            object IAsyncResult.AsyncState
            {
                get { return _asyncState; }
            }
            bool IAsyncResult.IsCompleted
            {
                get { return this._completeStatus; }
            }
            bool IAsyncResult.CompletedSynchronously
            {
                get { return false; }
            }
            public override IAsyncResult BeginGetData
                                    (HttpContext httpContext, AsyncCallback callback, object asyncState)
            {
                _asyncState = asyncState;
                ThreadPool.QueueUserWorkItem(new WaitCallback(GetData), callback);
                return this;
            }
            public override DataSet EndGetData(IAsyncResult asyncResult)
            {
                return this.Data;
            }
            public override void Cancel()
            {
                // Not implemented
            }
            private void GetData(object state)
            {
                AsyncCallback asyncCallback = (AsyncCallback)state;
                try
                {
                    ServerStatusClient oServerStatus = new ServerStatusClient();
                    DataTable dt = oServerStatus.GetServerStatusDetails();
                    this.Data.Reset();
                    this.Data.Tables.Add(dt);
                    this.Data.AcceptChanges();
                }
                catch (Exception ex)
                {
                    this.Error = new AddonDataHandlerException(ex.Message);
                }
                asyncCallback(this);
                _completeStatus = true;
                  
            }
        }
    }

28. Now build the project. Once the build is successful, right-click on the VisioDataService project, and then click on Publish and select a folder to create the WSP file in the selected folder.

     Tip  If you are performing this step on your local development machine, try the Deploy option from the project properties.

29. Deploy the WSP using either STSADM or Windows PowerShell commands to add the solution to your SharePoint site.
30. Open Central Administration Application Management Manage Service Applications Visio Graphics Service.
31. Click on Trusted Data Providers and then on Add a new Trusted Data Provider.
32. Under Trusted Data Provider ID, enter the class name and fully qualified assembly name separated by a comma (in my case, it is DataModules.VisioDataProviderService,VisioDataProviderService, Version=1.0.0.0, Culture=neutral, PublicKeyToken=<public key token>).
33. Under Trusted Data Provider Type, enter the value 6 (for Visio Custom Data Providers).
34. Under Trusted Data Provider Description, enter a valid description, such as Visio Custom Data Services Provider, and click OK.
35. Your service provider is now ready and will display in the available service provider list.
36. Open the Web.config file that belongs to your SharePoint web application, and paste the settings in Listing 2-7 under the system.serviceModel section.

    Listing 2-7.  Service Model Section Setting Values Under the web.config File

    <bindings>
          <basicHttpBinding>
            <binding name="BasicHttpBinding_IServerStatus" closeTimeout="00:01:00"
                openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
                allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
                maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
                messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
                useDefaultWebProxy="true">
              <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
                  maxBytesPerRead="4096" maxNameTableCharCount="16384" />
              <security mode="None">
                <transport clientCredentialType="None" proxyCredentialType="None"
                    realm="" />
                <message clientCredentialType="UserName" algorithmSuite="Default" />
              </security>
            </binding>
          </basicHttpBinding>
        </bindings>
        <client>
          <endpoint address=" http://localhost:<portnumber>/VisioWcfServices.svc "
              binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_IServerStatus"
              contract="WcfDataService.IServerStatus" name="BasicHttpBinding_IServerStatus" />
        </client>

    Designing the Visio Diagram

37. Open Visio 2013, and create a blank diagram. Choose the Network Servers - 3D (US units) stencil from More Shapes Network.
38. From the Insert tab, Add a Container block. From the diagram parts (under Servers-3d), and add three servers: Web Server, Database Server, and Application Server. Name the Container block as Test Servers, as shown in Figure 2-79.

    

    Figure 2-79. Designing a Visio diagram for test servers

    Right now, there is no data source configured. While your Visio diagram is open, press Alt+F11 to open Microsoft Visual Basic for Applications. In the Project window, double-click on ThisDocument under Visio Objects to open the code view. Paste the code in Listing 2-8 into the code area.

    Listing 2-8.  Using Visual Basic for Applications to Retreive Data from a SQL Server Data Source

    Sub LoadData()
        Dim diagramServices As Integer
        Dim vsoDataRecordset As Visio.dataRecordset
          
        Dim connectionString As String
        Dim commandText As String
         
        diagramServices = ActiveDocument.DiagramServicesEnabled
        ActiveDocument.DiagramServicesEnabled = visServiceVersion140
        Application.ActiveWindow.Windows.ItemFromID(visWinIDExternalData).Visible = True
            
        commandText = "SELECT ServerName, ServerIP, ServerStatus FROM tblServerStatus"
        connectionString = "Provider=SQLOLEDB;Data Source=<YourDataSourceName>;Initial Catalog=VisioServices;Integrated Security=SSPI;"
        Set vsoDataRecordset = ActiveDocument.DataRecordsets.Add(
                                                connectionString, commandText, 0, "Server Status Details")
        vsoDataRecordset.DataConnection.connectionString =
                       "DataModule=DataModules.VisioDataProviderService,VisioDataProviderService;"
          
        ActiveDocument.DiagramServicesEnabled = diagramServices
    End Sub

     Note  The following code is explained toward the end of this chapter in the section “Understanding the Custom Data Provider Code.” You need to customize the connection string and the Data module assembly details in the following code.

39. The Microsoft Visual Basic for Applications window should look like the one in Figure 2-80.

    

    Figure 2-80. Microsoft Visual Basic for Applications

40. Press F5 or click on the Run button on the menu to execute the LoadData(  ) subroutine. Pressing Alt+F11 will toggle back to the Visio diagram. Under the External Data tab, the data will be loaded as shown in Figure 2-81.

    

    Figure 2-81. The Visio diagram loading external data using VBA code

41. Save the file as TestServers.vdw. Macros are not supported in vsdx file format, hence we will be using vdw format for this example.
42. Drag and drop row data to the respective shapes, or set the Text property to the shapes that match the ServerName column and use the option Automatically Link all.
43. Set the data graphics to display ServerIP and ServerName as Text, and ServerStatus as Color by Value (Figure 2-82), such that if the value is 0 (server is down) the server shows red and if the value is 1 (server is live), it shows green.

    

    Figure 2-82. Setting up data graphics for the Visio diagram

    Your final Visio diagram should look like the one in Figure 2-83.

    

    Figure 2-83. Visio diagram after applying data and data graphics

44. Save the Visio diagram.
45. Open the SharePoint site, and open the Visio Library Document library. Click on Add document, browse to the saved .vdw file, and click OK.
46. Click on the .vdw file to open the diagram in the browser to display the results from the database.
47. Change the values in the database, and notice the server status. If the cache age values on the general settings of the Visio Graphics Service are set to zero, the values reflect instantly on the diagram (Figure 2-84).

    

    Figure 2-84. Visio diagram loaded using a custom data provider

    Make sure the following are correct to avoid the error shown in Figure 2-85:

    

    Figure 2-85. External data connection refresh error

48. Assembly details are accurate when you add the custom data providers in the Central Administration.
49. The WCF services are properly hosted and can communicate with the Custom Data Providers.
50. Your configuration files (Web.config) have the right binding settings for the WCF.
51. As discussed earlier, you might encounter the double-hop issue while connecting to data sources via WCF. If so, you might need to set the credentials under the Secure Store Service.

UNDERSTANDING THE CUSTOM DATA PROVIDER CODE

uspGetServerDetails Stored Procedure (Listing 2-2)

This stored procedure contains a simple T-SQL statement that retrieves all rows of data from the table tblServerStatus.

SELECT ServerName, ServerIP, ServerStatus from tblServerStatus

WCF Service Library (and Interface) Class (Listings 2-3 and 2-4)

Consider this a data –access-layer class that calls the uspGetServerDetails stored procedure and returns the output as a data table.

When the WCF application project is created, a service class and corresponding interface template are added to the project. Declare the Service Contract and Operation Contract (as shown next) interface classes that will be implemented in your derived class. Because the intention is to retrieve a data table from this service, the data contract method will be of type DataTable.

[ServiceContract]
    public interface IServerStatus
    {
        [OperationContract]
        DataTable GetServerStatusDetails();
    }

Under the derived class in the implemented method GetServerStatusDetails, create the data objects needed for the output:

DataSet dataSet = new DataSet();

Next, create the SQL Connection using the connection string from Web.config:

SqlConnection connection = new SqlConnection(
    ConfigurationManager.ConnectionStrings["VisioServicesConnectionString"].ConnectionString);

Create the command object by calling the stored procedure with the help of the SQL Connection, and set the command type to StoredProcedure:

SqlCommand command = new SqlCommand("uspGetServerDetails", connection);
command.CommandType = CommandType.StoredProcedure;

Finally, create the data adapter, load the command, and fill the data set using the data adapter:

SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataSet);

Close the connection, and return the output:

connection.Close();
return dataSet.Tables[0];

Custom Data Provider Class (Listing 2-6)

Before beginning, it is important to understand some of the key concepts. First, prepare to query data from data sources that are not natively supported. Second, these operations must be asynchronous in order to perform the tasks in a timely fashion. And most importantly, the methods used must be thread safe.

Microsoft.Office.Visio.Server assembly provides these functionalities with its abstract class AddonDataHandler and its abstract methods that deal with all the necessary data-module implementations.

Add a reference to Microsoft.Office.Visio.Server because the derived class will be inheriting the AddonDataHandler abstract class. It needs to also inherit the IAsyncResult interface from the System namespace to get the result of the asynchronous operation. Though there are many methods to override, the most important to understand are BeginGetData and EndGetData.

Visio Services calls the custom data provider’s BeginGetData() method as soon as the request begins. Under the BeginData() method, create a thread and delegate a method to it. Create a callback method to be aware of when the thread completes its job:

ThreadPool.QueueUserWorkItem(new WaitCallback(GetData), callback);

In the GetData() method, create an instance to the WCF service object and call the GetServerStatusDetails method to retrieve the Server Status details:

ServerStatusClient oServerStatus = new ServerStatusClient();
DataTable dt = oServerStatus.GetServerStatusDetails();
this.Data.Reset();
this.Data.Tables.Add(dt);
this.Data.AcceptChanges();

After the set operation completes its job by retrieving the data, Visio Services calls the EndGetData() and returns the Data object as DataSet:

return this.Data;

VBA Code (Listing 2-8)

The VBA code is used it to create and populate a data source that can be used by the Visio diagrams. This is similar to creating a data source using macros.

The code is very simple. It begins by initializing the data objects such as recordset, connection strings, command text, etc.

Dim diagramServices As Integer
Dim vsoDataRecordset As Visio.dataRecordset
Dim dataRecordset As Visio.dataRecordset
Dim connectionString As String
Dim commandText As String

First, get to the instance of the diagram services of the active document, which is the page. Set the Visio Services Version and the visibility of the external data sources window to true:

diagramServices = ActiveDocument.DiagramServicesEnabled
ActiveDocument.DiagramServicesEnabled = visServiceVersion140
Application.ActiveWindow.Windows.ItemFromID(visWinIDExternalData).Visible = True

The following steps are optional here. They are to populate the external data source with values to facilitate a designer’s choice to bind the row data to the shapes in design mode. Connect to the SQL store, retrieve the recordset, and add the recordset to the active document data record set collection:

commandText = "SELECT ServerName, ServerIP, ServerStatus FROM tblServerStatus"
connectionString = "Provider=SQLOLEDB;Data Source=<YourDataSourceName>;Initial Catalog=VisioServices;Integrated Security=SSPI; "
Set vsoDataRecordset = ActiveDocument.DataRecordsets.Add(
                                                 connectionString, commandText, 0, "Server Status Details")

Set the custom data-provider assembly details to the recordset command string. Set and enable the diagram services to the active document:

vsoDataRecordset.DataConnection.connectionString =
"DataModule=Altsis.VisioDataService,VisioDataService;"
ActiveDocument.DiagramServicesEnabled = diagramServices