How to call a custom SQL call using the Catalina API for Dynamics SL

How many times have you wanted to just make a SQL call from an app, website, or other system, but you don’t have direct access to a SQL connection or other easy method to call SQL?

Well the Catalina API for Dynamics SL has a secure way for you to make SQL calls over to your SL database using the Catalina common.asmx web service call.

Below is a quick tutorial on how to do this using .NET and the Catalina API for Dynamics SL (SOAP calls)

1. Make sure your client application has a reference to the Catalina Library file: ctStandardLib.dll. Note: You can find this file in the ctDynamicsSL/bin directory.

2. Make sure your client application has a web reference to the ctDynamicsSL.common web service.  In this example we will use commonRef.

[code lang=csharp]
commonRef.common _commonSvc = null;
protected commonRef.common commonSvc
{
get
{
if (_commonSvc == null)
{
_commonSvc = new ctAPISample.commonRef.common();
commonRef.ctDynamicsSLHeader oHeader = new ctAPISample.commonRef.ctDynamicsSLHeader();
oHeader.licenseExpiration = ctAPISample.Properties.Settings.Default.licenseExpiration;
oHeader.licenseKey = ctAPISample.Properties.Settings.Default.licenseKey;
oHeader.licenseName = ctAPISample.Properties.Settings.Default.licenseName;
oHeader.siteKey = ctAPISample.Properties.Settings.Default.siteKey;
oHeader.siteID = ctAPISample.Properties.Settings.Default.siteID;
oHeader.cpnyID = ctAPISample.Properties.Settings.Default.cpnyID;
oHeader.softwareName = "CTAPI";
_commonSvc.ctDynamicsSLHeaderValue = oHeader;
}
return (_commonSvc);
}
}
[/code]

3. Make sure your client application has a web reference to the ctDynamicsSL.common web service.  In this example we will use commonRef.

[code lang=csharp]
public void doCustomCall()
{
//Create an array for the paramaters for the custom procedure call
//Note: if no parameters then just make the array size 0
ctAPISample.commonRef.nameValuePairs[] customParams = new ctAPISample.commonRef.nameValuePairs[(2/*# of parmaters*/)];
customParams[0] = new ctAPISample.commonRef.nameValuePairs();
customParams[0].name = "Param1VariableName";
customParams[0].value = "Param1Value";
customParams[1] = new ctAPISample.commonRef.nameValuePairs();
customParams[1].name = "Param2VariableName";
customParams[1].value = "Param2Value";

//Set the name of custom sql procedure
String customSQLCall = "spMyCustomProcedure";

//For additional security purposes, the system requires you to create a hash string using your siteKey and the sql procedure name
String customCheckSum = ctStandardLib.ctHelper.getHash(ctAPISample.Properties.Settings.Default.siteKey, customSQLCall);

//Call the custom procedure, any results are returned in a dataset
System.Data.DataSet dsMyCustomSQLCall = commonSvc.customSQLCall(customSQLCall, customParams, customCheckSum);
//Do whatever is needed with dataset
}
[/code]