Service Desk Express API – Part 2

In my first post in this series (Service Desk Express API – Part 1) I discussed the XML Initiator and demonstrated how to build an Integration Engine package that would accept an inbound XML message and raise an incident. In this post I want to take this a step further and discuss how to build the XML message (in C#) to send to the Integration Engine. To do this I have built a fairly simple web service that will provide our development interface to Service Desk Express, which during this post I will explain how it can be configured to provide any function. You can download the web service here.

You will need to create a new virtual directory in IIS called APIMagic pointing at the contents of the APIMagic directory in this zip file, with “Scripts Only” execute permissions and configured to use the .NET Framework 2.0 as shown in the screen shots below:

Before we start configuring our functions (or webmethods) that our developers will use we need to configure the web service to talk to Service Desk Express.

Configuration Settings

As discussed in the first post in this series, one of the objectives we are trying to achieve is to provide a way of retrieving data from the Service Desk Express database. To do that we need to provide details of how to connect to the database in the form of a connectionString entered in the web.config. Open the web.config file and modify the section shown below to point to your DB server using whatever SQL username and password you like:

<connectionStrings>
<add name="SDE" connectionString="Data Source=DBServerName;Initial Catalog=SDE;User ID=APIMagic;Password=APIMagicPassword;" providerName="System.Data.SqlClient" />
</connectionStrings>

The account shown here APIMagic only needs db_reader permissions to the Service Desk Express database.

The other piece of information we need to provide is where the XmlPostHandler service is located. As discussed in the first post in this series this is normally: http://appServerName/XmlPostHandler and this value needs to be entered in web.config as an applicationSetting as shown below:

<appSettings>
<add key="XmlPostHandlerURL" value="http://appServerName/XmlPostHandler/" />
</appSettings>

So once you have set these two configuration settings, save the file and you are ready to start building functions…

Before we do however, I wanted to just explain a couple of features of the webservice and how it works.

Structure and Generic Functions

Essentially, the most important thing to realise is that your developers will connect to the Service.asmx file which will in turn provide access to the Service.cs file in the App_Code directory. All of your custom functions will be written in the Service.cs file and this is the only file (other than the web.config file we modified earlier) that you will need to work with.

Supporting the Service.cs file is a number of other files (or classes): Data_Access.cs, Utility.cs, and XML_Parameter.cs. You don’t need to worry about these files but you do need to understand the generic functions that are available to you and how to use them.

Data Access Class

The Data_Access class handles all the “complex” stuff with regards retrieving data from the Service Desk Express database and building our XML documents and sending them to the Integration Engine for parsing.

Essentially it provides three functions:

• Return_Single_Value: This function will return a single string of data from the database as the result of a Select statement, e.g. SELECT CLIENT FROM _SMDBA_._CUSTOMER_ WHERE SEQUENCE = 1001.
• Return_DataSet: This function will return a dataset of data (essentially a table) from the database as the result of a Select statement, e.g. SELECT * FROM _SMDBA_._CUSTOMER_
• PostXML: This function, in conjunction with the XML_Parameter class will allow you to create and send XML messages or posts to the Integration Engine for processing.

XML Parameter Class

Fundamentally, the XML Initiator in the Integration Engine parses the nodes of the XML document posted to it. The function of the XML_Parameter class is to provide a reusable business object that allows us to define those XML nodes for any given record (e.g. an Incident).

The class has three properties: Name, Value, and Parent. To explain these properties lets consider a node from our example XML document shown in the first post in this series.

<request>
<clientID>ABARBER</clientID>
…
</request>

So to build this node our properties would be:

• Name: clientID
• Value: ABARBER
• Parent: request

So for each node of our XML document we will instantiate a new instance of the XML_Parameter class and pass in the appropriate values. More on this later so don’t panic.

So let’s build something useful

So, by way of an example, we will create three functions to demonstrate how the service is used:

• Incidents_SelectCountByClientID: A function that simply returns the number of Open/Closed/Both incidents for a given Client ID.
• Incidents_SelectByClientID: A function that returns a dataset of incidents for a given Client ID.
• Incident_InsertUpdate: A function that creates/updates an incident.

These three functions are the same three functions that are already coded in the Service.cs file and commented out.

Incidents_SelectCountByClientID

The purpose of this function is to demonstrate the Return_Single_Value function described earlier. I doesn’t really matter what the SQL query is as long as it returns a single value.

[WebMethod(Description="Function to return the count of open/closed/both incidents for a given Client ID.")]
public string Incidents_SelectCountByClientID(string sClientID, string sState)
{
try
{
string _Value = "";
string _SQL = "SELECT Count(*) FROM [_SMDBA_].[Incident] WHERE [Client ID] = '" + sClientID + "' AND [InActive:] = 0";
if (sState.ToUpper() != "B")
{
_SQL = _SQL + " AND [State:] = '" + sState + "'";
}
_Value = _da.Return_Single_Value(_SQL);
return _Value;
}
catch (Exception ex)
{
throw _u.Error_Handler("Return_Single_Value", ex);
}
}

As you can hopefully see, it wouldn’t be that complicated to change the function or create a new one for selecting the number of work orders or purchase requests for a given Client ID. All you need to do is copy and paste the above code and change the _SQL statement.

Incidents_SelectByClientID

The purpose of this function is to demonstrate the Return_DataSet function described earlier.

[WebMethod(Description = "A function that returns a dataset of incidents for a given Client ID.")]
public DataSet Incidents_SelectByClientID(string sClientID)
{
try
{
DataSet _ds = new DataSet("Results");
string _SQL = "SELECT * FROM [_SMDBA_].[Incident] WHERE [InActive:] = 0 AND [Client ID] = '" + sClientID + "'";
_ds = _da.Return_DataSet(_SQL);
return _ds;
}
catch (Exception ex)
{
throw _u.Error_Handler("Return_DataSet", ex);
}
}

Again, if we wanted a function to return all Work Orders we would simply copy and paste this function, change it’s name, and change the _SQL string to select from work orders instead.

Incident_InsertUpdate

The purpose of this function is to demonstrate the PostXML function described earlier, and for me, this is the most exciting of the functions. To post any XML file to the Integration Console all you need to do is create a list of XML Parameters and call this function as shown below:

[WebMethod(Description = "Function to either update or insert an incident record into the SDE database.")]
public bool Incident_InsertUpdate(string iIncidentNo, string sClientID, string sConfigurationID, string sServiceName,
string sSupportSubjectID, string sIncidentDescription, string iInventoryItemSequence,
string sIncidentResolution, string sUserDefinedStatusID)
{
bool _isSuccess = false;
try
{
List<XML_Parameter> _xmlParameterCollection = new List<XML_Parameter>();
_xmlParameterCollection.Add(new XML_Parameter("incidentSequence", iIncidentNo, "request"));
_xmlParameterCollection.Add(new XML_Parameter("clientID", sClientID, "request"));
_xmlParameterCollection.Add(new XML_Parameter("configurationID", sConfigurationID, "request"));
_xmlParameterCollection.Add(new XML_Parameter("serviceName", sServiceName, "request"));
_xmlParameterCollection.Add(new XML_Parameter("supportSubjectID", sSupportSubjectID, "request"));
_xmlParameterCollection.Add(new XML_Parameter("incidentDescription", sIncidentDescription, "request"));
_xmlParameterCollection.Add(new XML_Parameter("inventoryItemSequence", iInventoryItemSequence, "request"));
_xmlParameterCollection.Add(new XML_Parameter("incidentResolution", sIncidentResolution, "request"));
_xmlParameterCollection.Add(new XML_Parameter("userDefinedStatusID", sUserDefinedStatusID, "request"));
_da.Post_XML_Request("Incident_InsertUpdate.post", _xmlParameterCollection);
_isSuccess = true;
return _isSuccess;
}
catch (Exception ex)
{
throw _u.Error_Handler("Incident_InsertUpdate", ex);
}
}

Let’s take a look at how this function works. Essentially all that is happening is that for each variable that is being passed to the function (i.e. sIncidentNo, sClientID etc.), a new instance of the XML_Parameter class is being instantiated and passed the appropriate values to create an XML node. The parent node is called “request” hence why, at least in my example, all the nodes are being passed the value “request” as their parent value.

So what would happen if you wanted to pass a new variable. Well you would simply create a new variable in the function declaration and then add another XML Parameter:

e.g. _xmlParameterCollection.Add(new XML_Parameter("problemNo", sProblemNo, "request"));

What about if you wanted to create/update work orders instead. Assuming that you have already created a new Integration Engine package, copy and paste the function, change it’s name and, and this is the important bit, change the post value to something sensible that you have specified in your Integration Engine package:

e.g. _da.Post_XML_Request("WorkOrder_InsertUpdate.post", _xmlParameterCollection);

Testing

To test any of the three function you have built (or uncommented perhaps :-)) simply open a browser (ON THE SERVER WHERE THE WEBSERVICE IS INSTALLED) and type http://localhost/APIMagic/Service.asmx and you’ll be greeted with the Service.asmx webpage as shown below:

Click on any of the links and enter appropriate values and you should find it all works as shown below:

Summary

It may not be much at the moment but, simply but copying and pasting the WebMethods and changing a small amount of code (obviously coupled with some appropriate Integration Engine packages) you should be in a position to provide a genuine API to Service Desk Express.

The truth is, I have found this post very hard to write as I didn’t quite know what to explain in detail and what to glance over. Between now and when BMC decide to release their new Web Services module for Service Desk Express, I will keep updating this web service with new functions. I would really welcome any feedback a) in general and/or b) specific to this post/project whether positive or negative. I hope this has proved useful.

• C#
• Service Desk Express
• webservices