Querying Aspen InfoPlus.21 (IP.21) data from SQLplus Web Service using C#.Net

We will write a console-based SOAP client application using C#.Net to query data from Aspen InfoPlus.21 (IP.21) using the SQLplus Web Service that is hosted on your IP.21 server. Though there are other ways to achieve this, using the SQLplus Web Service allows platform independence and doesn't need any Aspen components to be installed on the client system.

Prerequisites: Ensure that your implementation of Aspen InfoPlus.21 / SQLplus Server is running the SQLplus Web Service. If it is not running, you may need to install the feature using the AspenTech installation setup disks. This code should run with any .Net Framework above version 4 and IP.21 above version 7.1. To verify that the SQLplus Web Service is available, run a simple consult by going to the following URL on your web browser. 

http://[YOUR_SERVER]/SQLPlusWebService/SQLplusWebService.asmx

Below is the C# source code that demonstrates connecting to the SQLplus Web Service, querying the database, formatting the received XML response and writing the formatted XML to a disk file.

/*

Program: Query data from AspenTech InfoPlus.21 using the SQLplus Web

         Service.

Language: C#

*/

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Net;

using System.Reflection;

using System.Text;

using System.Threading.Tasks;

using System.Web;

using System.Xml;

using System.Xml.Linq;

namespace QueryAspenIP21

{

    class Program

    {

        static void Main(string[] args)

        {

            // Replace [HOST_NAME] below with the details of your

            // IP.21 server

            const string webSvc = "http://[HOST_NAME]"

                + "/SQLPlusWebService/SQLplusWebService.asmx";

           

            const string soap12Req =

                "<?xml version=\"1.0\" encoding=\"utf-8\"?>"

                + "<soap12:Envelope xmlns:xsi="

                + "\"http://www.w3.org/2001/XMLSchema-instance\" "

                + "xmlns:xsd="

                + "\"http://www.w3.org/2001/XMLSchema\" xmlns:soap12="

                + "\"http://www.w3.org/2003/05/soap-envelope\">"

                + "<soap12:Body>"

                + "<ExecuteSQL xmlns="

                + "\"http://www.aspentech.com/SQLplus.WebService\">"

                + "<command>{0}</command>"

                + "</ExecuteSQL>"

                + "</soap12:Body>"

                + "</soap12:Envelope>";

           

            // Build the SQL query string

            const string sqlCmd = "SELECT NAME, IP_DESCRIPTION, "

                + "IP_INPUT_VALUE, IP_INPUT_TIME FROM IP_AnalogDef "

                + "WHERE NAME = 'ATCAI'";

           

            HttpWebRequest webReq = (HttpWebRequest)WebRequest.Create(

                webSvc);

           

            // Set credentials if needed

            webReq.Credentials = CredentialCache.DefaultCredentials;

            webReq.ContentType = "application/soap+xml; charset=utf-8";

            webReq.Method = "POST";

            XmlDocument soapEnvDoc;

            soapEnvDoc = new XmlDocument();

            soapEnvDoc.LoadXml(string.Format(soap12Req, sqlCmd));

            byte[] bytes;

            bytes = Encoding.UTF8.GetBytes(soapEnvDoc.OuterXml);

            webReq.ContentLength = bytes.Length;

            using (Stream stream = webReq.GetRequestStream())

            {

                stream.Write(bytes, 0, bytes.Length);

            }

            HttpWebResponse webRes = (HttpWebResponse)webReq.

                GetResponse();

            Stream dataStream = webRes.GetResponseStream();

            StreamReader reader = new StreamReader(dataStream);

            XmlDocument soapResXml = new XmlDocument();

            soapResXml.Load(reader);

            // Decode encoded values in the XML string

            soapResXml.InnerXml = HttpUtility.HtmlDecode(

                soapResXml.InnerXml);

           

            // Use LINQ for format XML for print

            XDocument beautifulXml = XDocument.Parse(soapResXml.InnerXml);

            soapResXml.InnerXml = beautifulXml.ToString();

            // Build the file path to write

            string filePath = Path.GetDirectoryName(

                Assembly.GetExecutingAssembly().Location) +

                "\\response.xml";

            using(StreamWriter stream = new StreamWriter(filePath, false,

                Encoding.GetEncoding("iso-8859-7")))

            {

                soapResXml.Save(stream);

            }

            // Clean up

            reader.Close();

            dataStream.Close();

            webRes.Close();

        }

    }

}

Once the code has executed successfully, the XML response should be written to an XML file at the same location as the executable. This is a sample of the response XML with the data.

<?xml version="1.0" encoding="ISO-8859-7"?>

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope"

  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <soap:Body>

    <ExecuteSQLResponse

    xmlns="http://www.aspentech.com/SQLplus.WebService/">

      <ExecuteSQLResult>

        <NewDataSet>

          <Table>

            <NAME>ATCAI</NAME>

            <IP_DESCRIPTION>Sine Input</IP_DESCRIPTION>

            <IP_INPUT_VALUE>9.3396207809448242</IP_INPUT_VALUE>

            <IP_INPUT_TIME>06-AUG-2020 01:35:30.3</IP_INPUT_TIME>

          </Table>

        </NewDataSet>

      </ExecuteSQLResult>

    </ExecuteSQLResponse>

  </soap:Body>

</soap:Envelope>

I hope this helps someone. I appreciate any comments or suggestions. The code is also available here in GitHub.