How to make a custom SQL call with ctDynamicsSL.


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. 

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);
	}
}

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

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
}