using framework_library;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
namespace scheduler
{
public class xData
{
#region constants
private const string _ConnectionKey = "conn";
private const string _tablePrefix = "pal_";
#endregion
#region Dynamic Iteractions
#region Interaction Wrapper
///
/// Byte[] to process Dynamic interactions
///
///
///
///
///
///
///
private static Byte[] ProcesDynamicInteraction(byte[] interactionData, string connectionKey, bool isTransactional, string tablePrefix, bool isCollection, bool useService)
{
Byte[] result = null;
try
{
if (useService)//connect to wcf service
{
framework_service.Iframework_serviceClient serviceCaller = new framework_service.Iframework_serviceClient("Iframework_service");
interactionData = serviceCaller.ProcessInteraction(interactionData, connectionKey, isTransactional, tablePrefix, isCollection);
serviceCaller.Close();
}
else
{
dynamicInteraction dynaInteract = new dynamicInteraction();
interactionData = dynaInteract.ProcessInteraction(interactionData, connectionKey, isTransactional, tablePrefix, isCollection);
}
result = interactionData;
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#region Select Interactions Return Type: ArrayList
///
/// Strongly Typed Collection of a specified custom Type
///
///
///
///
///
///
public static ArrayList GetTypedCollection(string identityField, Type type, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
ArrayList result = new ArrayList();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interList;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// ArrayList of a specific custom type by a specific criteria
///
///
///
///
///
///
///
///
public static ArrayList GetTypedByCriteriaSpecific(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false, string connectionString = "")
{
ArrayList result = new ArrayList();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
string criteriaName = criteria;
if (criteria.Length > 7 && criteria.Substring(0, 7) == "~ISNULL")
{
string[] arr = criteriaName.Split('|');
arr[0] = arr[0].Substring(7);
criteriaName = "ISNULL(" + arr[1] + ",'" + arr[0] + "')";
}
if (criteria.Length > 9 && criteria.Substring(0, 9) == "~DATEONLY")
{
criteriaName = "CAST(" + criteriaName + " AS DATE)";
}
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value.Length > 1)
{
if (value.Length > 2 && value.Substring(0, 3) == "~<>")
interaction.whereClause += criteriaName + " <> '" + utils.formatSqlString(value.Substring(3)) + "' ";
else if (value.Length > 2 && value.Substring(0, 3) == "~<=")
interaction.whereClause += criteriaName + " <= '" + utils.formatSqlString(value.Substring(3)) + "' ";
else if (value.Length > 2 && value.Substring(0, 3) == "~>=")
interaction.whereClause += criteriaName + " >= '" + utils.formatSqlString(value.Substring(3)) + "' ";
else if (value.Length > 2 && value.Substring(0, 3) == "~()")
{
interaction.whereClause += criteriaName + " IN (";
string temp = String.Empty;
foreach (string item in value.Substring(3).Split('|'))
{
//CVH 2017-01-19 Exception, don't add , after last item
//interaction.whereClause += utils.formatSqlString(item) + ", ";
if (temp == String.Empty)
temp = "'" + utils.formatSqlString(item) + "'";
else
temp += ", '" + utils.formatSqlString(item) + "'";
}
interaction.whereClause += temp + ")";
}
else if (value.Substring(0, 2) == "~<")
interaction.whereClause += criteriaName + " < '" + utils.formatSqlString(value.Substring(2)) + "' ";
else if (value.Substring(0, 2) == "~>")
interaction.whereClause += criteriaName + " > '" + utils.formatSqlString(value.Substring(2)) + "' ";
else
interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' ";
}
else
interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interList;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// ArrayList of a specific custom type by a specific criteria limited to defined records
///
///
///
///
///
///
///
///
///
public static ArrayList GetTypedByCriteriaSpecificTop(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", int recordCount = 0, string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
ArrayList result = new ArrayList();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
if (recordCount > 0)
interaction.interRecordLimit = recordCount;
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
string criteriaName = criteria;
if (criteria.Length > 7 && criteria.Substring(0, 7) == "~ISNULL")
{
string[] arr = criteriaName.Split('|');
arr[0] = arr[0].Substring(7);
criteriaName = "ISNULL(" + arr[1] + ",'" + arr[0] + "')";
}
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value.Length > 1)
{
if (value.Length > 2 && value.Substring(0, 3) == "~<>")
interaction.whereClause += criteriaName + " <> '" + utils.formatSqlString(value.Substring(3)) + "' ";
else if (value.Length > 2 && value.Substring(0, 3) == "~<=")
interaction.whereClause += criteriaName + " <= '" + utils.formatSqlString(value.Substring(3)) + "' ";
else if (value.Length > 2 && value.Substring(0, 3) == "~>=")
interaction.whereClause += criteriaName + " >= '" + utils.formatSqlString(value.Substring(3)) + "' ";
else if (value.Length > 2 && value.Substring(0, 3) == "~()")
{
interaction.whereClause += criteriaName + " IN (";
string temp = String.Empty;
foreach (string item in value.Substring(3).Split('|'))
{
//CVH 2017-01-19 Exception, don't add , after last item
//interaction.whereClause += utils.formatSqlString(item) + ", ";
if (temp == String.Empty)
temp = "'" + utils.formatSqlString(item) + "'";
else
temp += ", '" + utils.formatSqlString(item) + "'";
}
interaction.whereClause += temp + ")";
}
else if (value.Substring(0, 2) == "~<")
interaction.whereClause += criteriaName + " < '" + utils.formatSqlString(value.Substring(2)) + "' ";
else if (value.Substring(0, 2) == "~>")
interaction.whereClause += criteriaName + " > '" + utils.formatSqlString(value.Substring(2)) + "' ";
else
interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' ";
}
else
interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interList;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// ArrayList of a distinct field custom type by a specific criteria
///
///
///
///
///
///
///
///
///
public static ArrayList GetTypedByCriteriaDistinct(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string distinctName, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
ArrayList result = new ArrayList();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.SelectDistinct;
interaction.interType = type;
interaction.interDistinct = distinctName;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interList;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// ArrayList of a specific custom type by a contains search criteria
///
///
///
///
///
///
///
///
public static ArrayList GetTypedByCriteriaContains(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false, string connectionString="")
{
ArrayList result = new ArrayList();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value != String.Empty)
{
if (interaction.whereClause == String.Empty)
interaction.whereClause = criteria + " LIKE '%" + utils.formatSqlString(value) + "%' ";
else
interaction.whereClause += " OR " + criteria + " LIKE '%" + utils.formatSqlString(value) + "%' ";
}
break;
}
}
}
}
else//single criteria
{
interaction.whereClause = criteriaNameList + " LIKE '%" + utils.formatSqlString(criteriaValueList) + "%' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interList;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// ArrayList of a specific custom type by a search criteria that begins with a value
///
///
///
///
///
///
///
///
public static ArrayList GetTypedByCriteriaBegins(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
ArrayList result = new ArrayList();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value != String.Empty)
{
if (interaction.whereClause == String.Empty)
interaction.whereClause = criteria + " LIKE '" + utils.formatSqlString(value) + "%' ";
else
interaction.whereClause += " OR " + criteria + " LIKE '" + utils.formatSqlString(value) + "%' ";
}
break;
}
}
}
}
else//single criteria
{
interaction.whereClause = criteriaNameList + " LIKE '" + utils.formatSqlString(criteriaValueList) + "%' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interList;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Strongly Typed Collection by a Custom query
///
///
///
///
///
///
public static ArrayList GetCustomCollection(string identityField, Type type, string customQuery, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
ArrayList result = new ArrayList();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.query = customQuery;
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interList;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#region Select Interactions Return Type: Dynamic DataTable
///
/// Strongly Typed Collection of a specified custom Type
///
///
///
///
///
///
public static DataTable GetDynamicTable(string identityField, Type type, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
interaction.includeDynamics = true;
if (virtualColumnList != null)
interaction.interCustomSubQueries = virtualColumnList;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a specific criteria dynamically
///
///
///
///
///
///
///
///
public static DataTable GetDynamicByCriteriaSpecific(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
interaction.includeDynamics = true;
if (virtualColumnList != null)
interaction.interCustomSubQueries = virtualColumnList;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a specific distinct criteria dynamically
///
///
///
///
///
///
///
///
public static DataTable GetDynamicByCriteriaDistinct(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string distinctName, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
interaction.includeDynamics = true;
if (virtualColumnList != null)
interaction.interCustomSubQueries = virtualColumnList;
interaction.interDistinct = distinctName;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a contains criteria dynamically
///
///
///
///
///
///
///
///
public static DataTable GetDynamicByCriteriaContains(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
interaction.includeDynamics = true;
if (virtualColumnList != null)
interaction.interCustomSubQueries = virtualColumnList;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value != String.Empty)
{
if (interaction.whereClause == String.Empty)
interaction.whereClause = criteria + " LIKE '%" + utils.formatSqlString(value) + "%' ";
else
interaction.whereClause += " OR " + criteria + " LIKE '%" + utils.formatSqlString(value) + "%' ";
}
break;
}
}
}
}
else//single criteria
{
interaction.whereClause = criteriaNameList + " LIKE '%" + utils.formatSqlString(criteriaValueList) + "%' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a contains criteria dynamically
///
///
///
///
///
///
///
///
public static DataTable GetDynamicByCriteriaBegins(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, List virtualColumnList = null, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
interaction.includeDynamics = true;
if (virtualColumnList != null)
interaction.interCustomSubQueries = virtualColumnList;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value != String.Empty)
{
if (interaction.whereClause == String.Empty)
interaction.whereClause = criteria + " LIKE '" + utils.formatSqlString(value) + "%' ";
else
interaction.whereClause += " OR " + criteria + " LIKE '" + utils.formatSqlString(value) + "%' ";
}
break;
}
}
}
}
else//single criteria
{
interaction.whereClause = criteriaNameList + " LIKE '" + utils.formatSqlString(criteriaValueList) + "%' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#region Select Interactions Return Type: DataTable
///
/// Strongly Typed Table of a specified custom Type
///
///
///
///
///
///
public static DataTable GetTypedTable(string identityField, Type type, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a specific criteria
///
///
///
///
///
///
///
///
public static DataTable GetTypedByCriteriaSpecificTable(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
string criteriaName = criteria;
if (criteria.Length > 9 && criteria.Substring(0, 9) == "~DATEONLY")
{
criteriaName = "CAST(" + criteria.Substring(9) + " AS DATE)";
}
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value.Length > 1)
{
if (value.Length > 2 && value.Substring(0, 3) == "~<>")
interaction.whereClause += criteriaName + " <> '" + utils.formatSqlString(value.Substring(3)) + "' ";
else if (value.Length > 2 && value.Substring(0, 3) == "~()")
{
interaction.whereClause += criteriaName + " IN (";
if (!value.Substring(3).Contains("|"))
interaction.whereClause += utils.formatSqlString(value.Substring(3));
else
{
string valueList = value.Substring(3);
bool first = true;
foreach (string item in valueList.Split('|'))
{
if (first)
interaction.whereClause += utils.formatSqlString(item);
else
interaction.whereClause += "," + utils.formatSqlString(item);
first = false;
}
}
interaction.whereClause += ")";
}
else if (value.Substring(0, 2) == "~<")
interaction.whereClause += criteriaName + " < '" + utils.formatSqlString(value.Substring(2)) + "' ";
else if (value.Substring(0, 2) == "~>")
interaction.whereClause += criteriaName + " > '" + utils.formatSqlString(value.Substring(2)) + "' ";
else
interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' ";
}
else
interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
{
string criteriaName = criteriaNameList;
if (criteriaNameList.Length > 9 && criteriaNameList.Substring(0, 9) == "~DATEONLY")
{
criteriaName = "CAST(" + criteriaNameList.Substring(9) + " AS DATE)";
}
interaction.whereClause = criteriaName + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a specific criteria limited to defined records
///
///
///
///
///
///
///
///
public static DataTable GetTypedByCriteriaSpecificTableTop(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", int recordCount = 0, string tablePrefix = _tablePrefix, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
if (recordCount > 0)
interaction.interRecordLimit = recordCount;
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
string criteriaName = criteria;
if (criteria.Length > 9 && criteria.Substring(0, 9) == "~DATEONLY")
{
criteriaName = "CAST(" + criteria.Substring(9) + " AS DATE)";
}
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value.Length > 1)
{
if (value.Length > 2 && value.Substring(0, 3) == "~<>")
interaction.whereClause += criteriaName + " <> '" + utils.formatSqlString(value.Substring(3)) + "' ";
else if (value.Length > 2 && value.Substring(0, 3) == "~()")
{
interaction.whereClause += criteriaName + " IN (";
if (!value.Substring(3).Contains("|"))
interaction.whereClause += utils.formatSqlString(value.Substring(3));
else
{
string valueList = value.Substring(3);
bool first = true;
foreach (string item in valueList.Split('|'))
{
if (first)
interaction.whereClause += utils.formatSqlString(item);
else
interaction.whereClause += "," + utils.formatSqlString(item);
first = false;
}
}
interaction.whereClause += ")";
}
else if (value.Substring(0, 2) == "~<")
interaction.whereClause += criteriaName + " < '" + utils.formatSqlString(value.Substring(2)) + "' ";
else if (value.Substring(0, 2) == "~>")
interaction.whereClause += criteriaName + " > '" + utils.formatSqlString(value.Substring(2)) + "' ";
else
interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' ";
}
else
interaction.whereClause += criteriaName + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
{
string criteriaName = criteriaNameList;
if (criteriaNameList.Length > 9 && criteriaNameList.Substring(0, 9) == "~DATEONLY")
{
criteriaName = "CAST(" + criteriaNameList.Substring(9) + " AS DATE)";
}
interaction.whereClause = criteriaName + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a specific distinct criteria
///
///
///
///
///
///
///
///
public static DataTable GetTypedByCriteriaDistinctTable(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string distinctName, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.SelectDistinct;
interaction.interType = type;
interaction.interDistinct = distinctName;
interaction.orderClause = orderBy;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a contains criteria dynamically
///
///
///
///
///
///
///
///
public static DataTable GetTypedByCriteriaContainsTable(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value != String.Empty)
{
if (interaction.whereClause == String.Empty)
interaction.whereClause = criteria + " LIKE '%" + utils.formatSqlString(value) + "%' ";
else
interaction.whereClause += " OR " + criteria + " LIKE '%" + utils.formatSqlString(value) + "%' ";
}
break;
}
}
}
}
else//single criteria
{
interaction.whereClause = criteriaNameList + " LIKE '%" + utils.formatSqlString(criteriaValueList) + "%' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a contains criteria dynamically
///
///
///
///
///
///
///
///
public static DataTable GetTypedByCriteriaBeginsTable(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value != String.Empty)
{
if (interaction.whereClause == String.Empty)
interaction.whereClause = criteria + " LIKE '" + utils.formatSqlString(value) + "%' ";
else
interaction.whereClause += " OR " + criteria + " LIKE '" + utils.formatSqlString(value) + "%' ";
}
break;
}
}
}
}
else//single criteria
{
interaction.whereClause = criteriaNameList + " LIKE '" + utils.formatSqlString(criteriaValueList) + "%' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Strongly Typed Table of a specified custom Type
///
///
///
///
///
///
public static DataTable GetCustomTypedTable(string customQuery, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.interactionType = enums.InteractionType.Select;
interaction.query = customQuery;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#region Select Interactions Return Type: DataSet
///
/// Strongly Typed DataSet of a specified custom Type
///
///
///
///
///
///
public static DataSet GetTypedSet(string identityField, Type type, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataSet result = new DataSet();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataSet of a specific custom type by a specific criteria
///
///
///
///
///
///
///
///
public static DataSet GetTypedByCriteriaSpecificSet(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataSet result = new DataSet();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a specific distinct criteria
///
///
///
///
///
///
///
///
public static DataSet GetTypedByCriteriaDistinctSet(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string distinctName, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataSet result = new DataSet();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
interaction.interDistinct = distinctName;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a contains criteria dynamically
///
///
///
///
///
///
///
///
public static DataSet GetTypedByCriteriaContainsSet(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataSet result = new DataSet();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value != String.Empty)
{
if (interaction.whereClause == String.Empty)
interaction.whereClause = criteria + " LIKE '%" + utils.formatSqlString(value) + "%' ";
else
interaction.whereClause += " OR " + criteria + " LIKE '%" + utils.formatSqlString(value) + "%' ";
}
break;
}
}
}
}
else//single criteria
{
interaction.whereClause = criteriaNameList + " LIKE '%" + utils.formatSqlString(criteriaValueList) + "%' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// DataTable of a specific custom type by a contains criteria dynamically
///
///
///
///
///
///
///
///
public static DataSet GetTypedByCriteriaBeginsSet(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false)
{
DataSet result = new DataSet();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.orderClause = orderBy;
//check we have criteria values
if (criteriaValueList != String.Empty || criteriaNameList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
if (value != String.Empty)
{
if (interaction.whereClause == String.Empty)
interaction.whereClause = criteria + " LIKE '" + utils.formatSqlString(value) + "%' ";
else
interaction.whereClause += " OR " + criteria + " LIKE '" + utils.formatSqlString(value) + "%' ";
}
break;
}
}
}
}
else//single criteria
{
interaction.whereClause = criteriaNameList + " LIKE '" + utils.formatSqlString(criteriaValueList) + "%' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#region Save Interactions
///
/// Save a Typed Object
///
///
///
///
///
///
public static int SaveTyped(string identityField, Type type, Object obj, string tablePrefix = _tablePrefix, bool userService = false, string connectionString = "")
{
int result = 0;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Insert;
interaction.interType = type;
interaction.interObject = obj;
interationData = utils.ObjectToByteArray(interaction);
if(connectionString=="")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interInt;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Save a Typed Collection of Objects
///
///
///
///
///
///
public static int SaveTypedCollection(string identityField, Type type, ArrayList objList, string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
int result = 0;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Insert;
interaction.interType = type;
foreach (object obj in objList)
{
interaction.interObjects.Add(obj);
}
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interInt;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Save a Typed Collection of Objects
///
///
///
///
///
///
public static bool UpdateTypedCollection(string identityField, Type type, ArrayList objList, string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
bool result = false;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Update;
interaction.interType = type;
foreach (object obj in objList)
{
interaction.interObjects.Add(obj);
}
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSucess;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// bool to update a typed obejct
///
///
///
///
///
///
///
public static bool UpdateTyped(string identityField, string identityValue, Type type, Object obj, string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
bool result = false;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Update;
interaction.interType = type;
interaction.interObject = obj;
interaction.whereClause = identityField + " = " + identityValue;
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSucess;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Run a custom query
///
///
///
///
public static bool RunCustomQuery(string query, bool userService = false)
{
bool result = false;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.interactionType = enums.InteractionType.Update;
interaction.query = query;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, _tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSucess;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#region Verify Interactions Return Type: Boolean
///
/// Verify if record exists
///
///
///
///
///
///
///
///
public static bool VerifyExists(string identityField, Type type,
string criteriaNameList, string criteriaValueList, string orderBy = "", string tablePrefix = _tablePrefix, bool userService = false, string connectionString="")
{
bool result = false;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
if (criteriaNameList.Trim() != String.Empty)
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Verify;
interaction.interType = type;
//check we have criteria values
if (criteriaValueList != String.Empty)
{
if (criteriaNameList.Contains(","))//multiple criterias provided
{
string[] criterias = criteriaNameList.Split(char.Parse(","));
string[] values = criteriaValueList.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
if (criteriaValueList.Contains(","))//multiple values
interaction.whereClause = criteriaNameList + " IN (" + criteriaValueList + ") ";
else
interaction.whereClause = criteriaNameList + " = '" + utils.formatSqlString(criteriaValueList) + "' ";
}
}
if (orderBy != String.Empty)
{ interaction.orderClause = orderBy; }
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interBool;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#region Delete Interactions Return Type: Boolean
///
/// Delete object
///
///
///
///
///
///
public static bool DeleteTyped(string identityField, string identityValue, Type type, string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
bool result = false;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Delete;
interaction.interType = type;
//check we have criteria values
if (identityField != String.Empty)
{
if (identityField.Contains(","))//multiple criterias provided
{
string[] criterias = identityField.Split(char.Parse(","));
string[] values = identityValue.Split(char.Parse(","));
int CriteriaCounter = 0;
foreach (String criteria in criterias)
{
CriteriaCounter++;
int ValueCounter = 0;
foreach (String value in values)
{
ValueCounter++;
if (ValueCounter == CriteriaCounter)
{
interaction.whereClause += criteria + " = '" + utils.formatSqlString(value) + "' ";
if (CriteriaCounter != criterias.Length)
interaction.whereClause += " AND ";
break;
}
}
}
}
else//single criteria
{
interaction.whereClause = identityField + " = '" + identityValue + "' ";
}
}
//process the interaction over wcf protocol or directly with dll
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interBool;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#region Stored Procedure Interactions
///
/// DataTable to Get a Dynamic Table by Stored Procedure
///
///
///
///
///
///
///
///
///
///
public static DataTable GetDynamicTableByProc(string identityField, Type type, string StoredProcName,
List StoredProcParams, int recordLimit, string tablePrefix = _tablePrefix,
List virtualColumnList = null, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.interStoredProc = true;
interaction.interStoredProcName = StoredProcName;
interaction.interStoredProcParams = StoredProcParams;
interaction.interStoredProcDynamicSelect = true;
interaction.includeDynamics = true;
interaction.interRecordLimit = recordLimit;
if (virtualColumnList != null)
interaction.interCustomSubQueries = virtualColumnList;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Strongly Typed Collection of a specified custom Type
///
///
///
///
///
///
public static ArrayList GetTypedCollectionByProc(string identityField, Type type, string StoredProcName,
List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
ArrayList result = new ArrayList();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.interStoredProc = true;
interaction.interStoredProcName = StoredProcName;
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interList;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Strongly Typed Table of a specified custom Type
///
///
///
///
///
///
public static DataTable GetTypedTableByProc(string identityField, Type type, string StoredProcName,
List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.interStoredProc = true;
interaction.interStoredProcName = StoredProcName;
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Strongly Typed DataSet of a specified custom Type
///
///
///
///
///
///
public static DataSet GetTypedSetByProc(string identityField, Type type, string StoredProcName,
List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false)
{
DataSet result = new DataSet();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = type;
interaction.interStoredProc = true;
interaction.interStoredProcName = StoredProcName;
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Save a Typed Object
///
///
///
///
///
///
public static int SaveTypedByProc(string identityField, Type type, Object obj, string StoredProcName,
List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
int result = 0;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Insert;
interaction.interType = type;
interaction.interObject = obj;
interaction.interStoredProc = true;
interaction.interStoredProcName = StoredProcName;
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interInt;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// bool to update a typed obejct
///
///
///
///
///
///
///
public static bool UpdateTypedByProc(string identityField, Type type, Object obj,
string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
bool result = false;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Update;
interaction.interType = type;
interaction.interObject = obj;
interaction.interStoredProc = true;
interaction.interStoredProcName = StoredProcName;
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSucess;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// bool to update a typed obejct
///
///
///
///
///
///
///
public static bool VerifyExistsByProc(string identityField, Type type, Object obj,
string StoredProcName, List StoredProcParams, string tablePrefix = _tablePrefix, bool userService = false,string connectionString="")
{
bool result = false;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = identityField;
interaction.interactionType = enums.InteractionType.Verify;
interaction.interType = type;
interaction.interObject = obj;
interaction.interStoredProc = true;
interaction.interStoredProcName = StoredProcName;
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, tablePrefix, true, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, tablePrefix, true, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSucess;
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Update SMS's allocated
///
///
///
public static bool UpdateAllocatedSMS(string customerCode, bool useService = true)
{
bool result = false;
oInteraction interaction = new oInteraction();
Byte[] interactionData;
try
{
List StoredProcParams = new List();
oDynamicParam param = new oDynamicParam();
param.paramDisplayName = "CustomerCode";
param.paramObject = customerCode;
StoredProcParams.Add(param);
interaction.interactionType = enums.InteractionType.Update;
interaction.interStoredProc = true;
interaction.interStoredProcName = "sp_UpdateSMSAllocatedForCustomer";
interaction.interStoredProcParams = StoredProcParams;
interactionData = utils.ObjectToByteArray(interaction);
interactionData = ProcesDynamicInteraction(interactionData, _ConnectionKey, false, "pal_", false, useService);
interaction = (oInteraction)utils.ByteArrayToObject(interactionData);
if (interaction.interSucess)
{
result = interaction.interSucess;
}
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
//custom stored procedures
#region Custom Stored Procedure Calls
public static bool SetAccountsLock(string connectionString = _ConnectionKey)
{
bool result = false;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
interaction.identityField = "recId";
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = typeof(oSurface);
interaction.interStoredProc = true;
interaction.interStoredProcName = "sp_SetAccountsLock";
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, connectionString, false, "pal_", false, false);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
{
result = true;
}
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Get Surface Data Count
///
///
///
/// count
public static int GetSurfaceDataCount(int surfaceId, int userLink, string filter = "", bool userService = false)
{
int result = 0;
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
List StoredProcParams = new List();
oDynamicParam param = new oDynamicParam();
param.paramDisplayName = "SurfaceID";
param.paramObject = surfaceId;
StoredProcParams.Add(param);
oDynamicParam param1 = new oDynamicParam();
param1.paramDisplayName = "UserLink";
param1.paramObject = userLink;
StoredProcParams.Add(param1);
oDynamicParam param2 = new oDynamicParam();
param2.paramDisplayName = "Filter";
param2.paramObject = filter;
StoredProcParams.Add(param2);
interaction.identityField = "recId";
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = typeof(oSurface);
interaction.interStoredProc = true;
interaction.interStoredProcName = "sp_GetSurfaceDataCount";
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
{
if (interaction.interSet.Tables.Count > 0)
{
foreach (DataRow row in interaction.interSet.Tables[0].Rows)
{
result = int.Parse(row["Count"].ToString());
break;
}
}
}
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Get Surface Data Paged
///
///
///
///
///
///
///
///
///
public static DataTable GetSurfaceDataPaged(int surfaceId, int startIndex, int MaxRows, string orderBy, string search, int userLink = 0, string filter = "", bool useService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
List StoredProcParams = new List();
oDynamicParam param = new oDynamicParam();
param.paramDisplayName = "SurfaceID";
param.paramObject = surfaceId;
StoredProcParams.Add(param);
oDynamicParam param1 = new oDynamicParam();
param1.paramDisplayName = "StartIndex";
param1.paramObject = startIndex;
StoredProcParams.Add(param1);
oDynamicParam param2 = new oDynamicParam();
param2.paramDisplayName = "MaxRows";
param2.paramObject = MaxRows;
StoredProcParams.Add(param2);
oDynamicParam param3 = new oDynamicParam();
param3.paramDisplayName = "OrderBy";
param3.paramObject = orderBy;
StoredProcParams.Add(param3);
oDynamicParam param4 = new oDynamicParam();
param4.paramDisplayName = "Search";
param4.paramObject = search;
StoredProcParams.Add(param4);
oDynamicParam param5 = new oDynamicParam();
param5.paramDisplayName = "UserLink";
param5.paramObject = userLink;
StoredProcParams.Add(param5);
oDynamicParam param6 = new oDynamicParam();
param6.paramDisplayName = "Filter";
param6.paramObject = filter;
StoredProcParams.Add(param6);
interaction.identityField = "recId";
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = typeof(oSurface);
interaction.interStoredProc = true;
interaction.interStoredProcName = "sp_GetSurfaceDataPaged";
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, useService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
{
if (interaction.interSet.Tables.Count > 0)
result = interaction.interSet.Tables[0];
}
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Get Surface Data
///
///
///
public static DataTable GetSurfaceData(int surfaceId, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
List StoredProcParams = new List();
oDynamicParam param = new oDynamicParam();
param.paramDisplayName = "SurfaceID";
param.paramObject = surfaceId;
StoredProcParams.Add(param);
interaction.identityField = "recId";
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = typeof(oSurface);
interaction.interStoredProc = true;
interaction.interStoredProcName = "sp_GetSurfaceData";
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
{
if (interaction.interSet.Tables.Count > 0)
result = interaction.interSet.Tables[0];
}
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Get Surface Data
///
///
///
public static DataTable GetSurfaceDataAll(int surfaceId, bool activeOnly, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
List StoredProcParams = new List();
oDynamicParam param = new oDynamicParam();
param.paramDisplayName = "SurfaceID";
param.paramObject = surfaceId;
StoredProcParams.Add(param);
oDynamicParam param1 = new oDynamicParam();
param1.paramDisplayName = "activeOnly";
param1.paramObject = activeOnly ? "1" : "0";
StoredProcParams.Add(param1);
interaction.identityField = "recId";
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = typeof(oSurface);
interaction.interStoredProc = true;
interaction.interStoredProcName = "sp_GetSurfaceDataAll";
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
{
if (interaction.interSet.Tables.Count > 0)
result = interaction.interSet.Tables[0];
}
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Get Surface Item Data
///
///
///
///
public static DataTable GetSurfaceItemGridData(int surfaceId, int surfaceItemId, bool userService = false)
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
List StoredProcParams = new List();
oDynamicParam param = new oDynamicParam();
param.paramDisplayName = "SurfaceID";
param.paramObject = surfaceId;
StoredProcParams.Add(param);
oDynamicParam param2 = new oDynamicParam();
param2.paramDisplayName = "SurfaceItemID";
param2.paramObject = surfaceItemId;
StoredProcParams.Add(param2);
interaction.identityField = "recId";
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = typeof(oSurface);
interaction.interStoredProc = true;
interaction.interStoredProcName = "sp_GetSurfaceItemGridData";
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Get Surface Item Data
///
///
///
///
public static DataTable GetSurfaceItemData(int surfaceId, int surfaceItemId, bool userService = false, string connectionString="")
{
DataTable result = new DataTable();
oInteraction interaction = new oInteraction();
Byte[] interationData;
try
{
List StoredProcParams = new List();
oDynamicParam param = new oDynamicParam();
param.paramDisplayName = "SurfaceID";
param.paramObject = surfaceId;
StoredProcParams.Add(param);
oDynamicParam param2 = new oDynamicParam();
param2.paramDisplayName = "SurfaceItemID";
param2.paramObject = surfaceItemId;
StoredProcParams.Add(param2);
interaction.identityField = "recId";
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = typeof(oSurface);
interaction.interStoredProc = true;
interaction.interStoredProcName = "sp_GetSurfaceItemData";
interaction.interStoredProcParams = StoredProcParams;
interationData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interationData = ProcesDynamicInteraction(interationData, _ConnectionKey, false, "pal_", false, userService);
else
interationData = ProcesDynamicInteraction(interationData, connectionString, false, "pal_", false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interationData);
if (interaction.interSucess)
result = interaction.interSet.Tables[0];
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
#endregion
#endregion
#region debtors
///
/// Get Next Receipt
///
///
public static int GetNextRecNo(bool userService = false, string connectionString = "")
{
int result = 0;
oInteraction interaction = new oInteraction();
Byte[] interactionData;
try
{
//define interaction
interaction.identityField = "recId";
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = typeof(oAccount);
//CVH 2016-09-15 This should be pal_Account
//interaction.query = "SELECT IsNull(Max(receiptNo), 0) + 1 As InvNo FROM pal_Sales ";
interaction.query = "SELECT IsNull(Max(receiptNo), 0) + 1 As InvNo FROM pal_Account ";
interactionData = utils.ObjectToByteArray(interaction);
//fetch data
if (connectionString == "")
interactionData = ProcesDynamicInteraction(interactionData, _ConnectionKey, false, _tablePrefix, false, userService);
else
interactionData = ProcesDynamicInteraction(interactionData, connectionString, false, _tablePrefix, false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interactionData);
if (interaction.interSet.Tables.Count > 0 && interaction.interSet.Tables[0].Rows.Count > 0)
{
int.TryParse(Convert.ToString(interaction.interSet.Tables[0].Rows[0][0]), out result);
}
else
{
if (interaction.interExceptions.Count > 0)
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
///
/// Get Next Sequence No
///
///
///
///
public static int GetNextSequence(DateTime dtDateOfService, int surfaceItemId, bool userService = false, string connectionString = "")
{
DataTable result = new DataTable();
int seqNo = 1;
oInteraction interaction = new oInteraction();
Byte[] interactionData;
try
{
List StoredProcParams = new List();
oDynamicParam param = new oDynamicParam();
param.paramDisplayName = "serviceDate";
param.paramObject = dtDateOfService;
StoredProcParams.Add(param);
oDynamicParam param2 = new oDynamicParam();
param2.paramDisplayName = "surfaceItemId";
param2.paramObject = surfaceItemId;
StoredProcParams.Add(param2);
interaction.identityField = "recId";
interaction.interactionType = enums.InteractionType.Select;
interaction.interType = typeof(oAccount);
interaction.interStoredProc = true;
interaction.interStoredProcName = "sp_GetNextSequence";
interaction.interStoredProcParams = StoredProcParams;
interactionData = utils.ObjectToByteArray(interaction);
if (connectionString == "")
interactionData = ProcesDynamicInteraction(interactionData, _ConnectionKey, false, "pal_", false, userService);
else
interactionData = ProcesDynamicInteraction(interactionData, connectionString, false, "pal_", false, userService);
interaction = (oInteraction)utils.ByteArrayToObject(interactionData);
if (interaction.interSucess)
{
result = interaction.interSet.Tables[0];
seqNo = Convert.ToInt32(result.Rows[0][0]);
}
else
{
Exception ex = new Exception(String.Join(",", interaction.interExceptions.ToArray()), new Exception(""));
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
return seqNo;
}
#endregion
}
}