using System;
using System.Collections.Generic;
using System.Reflection;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
namespace framework_library
{
public class dynamicInteraction
{
private string _tblPrefix = String.Empty;
///
/// Constructor
///
/// eg: PM_ or tbl
public dynamicInteraction()
{
}
///
/// Named keys (conn, connLive, connExt) resolve via ConfigResolver.
/// Any other value is treated as a raw SQL connection string (legacy).
///
private static bool IsNamedConnectionKey(string key)
{
return string.Equals(key, "conn", StringComparison.OrdinalIgnoreCase)
|| string.Equals(key, "connLive", StringComparison.OrdinalIgnoreCase)
|| string.Equals(key, "connExt", StringComparison.OrdinalIgnoreCase)
|| string.Equals(key, "Quotes", StringComparison.OrdinalIgnoreCase);
}
private static SqlCommand BeginInteractionCommand(string key)
{
if (IsNamedConnectionKey(key))
return dataTier.CommandMethods.BeginSQLCommand(key);
return dataTier.CommandMethods.BeginDynamicSQLCommand(key);
}
private static SqlCommand BeginInteractionTransaction(string key)
{
if (IsNamedConnectionKey(key))
return dataTier.TransactionalMethods.BeginSQLTransaction(key);
return dataTier.TransactionalMethods.BeginDynamicSQLTransaction(key);
}
#region interactions
///
/// Method to handle an Interaction with the database,
/// this can handle inserts, updates, deletes, verify and selects for multiple objects or single objects
///
/// by reference
public Byte[] ProcessInteraction(byte[] _interactionData, string key = "conn", bool isTransactional = false, string tablePrefix = "", bool isCollection = true)
{
SqlCommand command = new SqlCommand();
oInteraction interaction = new oInteraction();
bool isCustom = false;
Byte[] result = null;
try
{
//if (tablePrefix != String.Empty) //JasR 2015-04-20 not required as value is always sent from xData
_tblPrefix = tablePrefix;
//convert byte[] to Interaction
interaction = (oInteraction)utils.ByteArrayToObject(_interactionData);
//check if custom query being used
if (interaction.query.Trim() != String.Empty)
isCustom = true;
if (isTransactional)
command = BeginInteractionTransaction(key);
else
command = BeginInteractionCommand(key);
using (command.Connection)
{
//check which interaction is taking place
switch (interaction.interactionType)
{
#region Select Interaction
case enums.InteractionType.Select://Select Interaction
if (interaction.interStoredProc)
{
string query = buildSelectInteraction(interaction, ref command);
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
if (interaction.interStoredProcDynamicSelect)
{
//build up dynamic select for Stored Procedure
command.Parameters.Add(new SqlParameter("@Query", query));
}
command.CommandText = interaction.interStoredProcName;
}
else
{
if (!isCustom)//generic select from the db
command.CommandText = buildSelectInteraction(interaction, ref command);
else //custom select query
command.CommandText = interaction.query;
}
//CVH 2017-12-05 Set command timeout to 3 minutes. Default is 30seconds. TSP timing out on Debtors age report
//CVH 2018-12-20 Setting this to 10 minutes. CEP need to be able to run long processes. Need to find a better way to do this.
//GK 31-08-2021 Setting time out to 30 minutes
command.CommandTimeout = 1800;
//return dataset
interaction.interSet = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (isCollection)
{
//convert dataset to ArrayList
interaction.interList = utils.ConvertDataTableToList(interaction.interSet.Tables[0], interaction.interType);
//clear dataSet for performance
interaction.interSet.Clear();
}
//set interaction to successful
interaction.interSucess = true;
break;
#endregion
#region Insert Interaction
case enums.InteractionType.Insert://Insert Interaction
if (!isCustom)//generic insert into db from an object or object collection
{
if (interaction.interStoredProc)
{
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
command.CommandText = interaction.interStoredProcName;
interaction.interInt = dataTier.ReturnMethods.ReturnIntValue(ref command);
interaction.interSucess = true;//successful
//clear parameters
command.Parameters.Clear();
}
else
{
//build query for insert
command.CommandText = buildInsertInteraction(interaction, ref command);
if (interaction.interObjects.Count > 0)//multiple insert of objects
{
//enumerate object collection for insert
foreach (Object obj in interaction.interObjects)
{
//build parameters
buildParamsInteraction(obj, interaction.identityField, ref command);
//save data
interaction.interSucess = dataTier.SavingMethods.AddRecord(ref command);
//clear parameters
command.Parameters.Clear();
}
}
else//single insert of a object
{
//build parameters
buildParamsInteraction(interaction.interObject, interaction.identityField, ref command);
//save record and return id
interaction.interInt = dataTier.ReturnMethods.ReturnIntValue(ref command);
if (interaction.interInt > 0)
interaction.interSucess = true;//successful
//clear parameters
command.Parameters.Clear();
}
}
}
else//custom query to perform a save
{
//set command text from custom query
command.CommandText = interaction.query;
//save record and return id
interaction.interInt = dataTier.ReturnMethods.ReturnIntValue(ref command);
if (interaction.interInt > 0)
interaction.interSucess = true;//successful
}
break;
#endregion
#region Update Interaction
case enums.InteractionType.Update://Update Interaction
if (!isCustom)//generic update to the db from an object or object collection
{
if (interaction.interStoredProc)
{
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
command.CommandText = interaction.interStoredProcName;
interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
if (interaction.interBool)//verify if update successful
interaction.interSucess = true;
//clear parameters
command.Parameters.Clear();
}
else
{
if (interaction.interObjects.Count > 0)//multiple update of objects
{
//enumerate object collection for update
foreach (Object obj in interaction.interObjects)
{
foreach (PropertyInfo prop in obj.GetType().GetProperties())
{
if (prop.Name.ToLower() == interaction.identityField.ToLower())
{
string value = prop.GetValue(obj, null).ToString();
if (value == "0")
{
//build query for insert
command.CommandText = buildInsertInteraction(interaction, ref command);
}
else
{
interaction.whereClause = interaction.identityField + " = " + prop.GetValue(obj, null).ToString();
//build query for update
command.CommandText = buildUpdateInteraction(interaction, ref command);
}
//build parameters
buildParamsInteraction(obj, interaction.identityField, ref command);
//update record
interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
if (interaction.interBool)//verify if update successful
interaction.interSucess = true;
//clear parameters
command.Parameters.Clear();
break;
}
}
}
}
else //single update of an object to the db
{
//build query for update
command.CommandText = buildUpdateInteraction(interaction, ref command);
//build parameters
buildParamsInteraction(interaction.interObject, interaction.identityField, ref command);
//update record
interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
if (interaction.interBool)//verify if update successful
interaction.interSucess = true;
//clear parameters
command.Parameters.Clear();
}
}
}
else//custom update to the db
{
command.CommandText = interaction.query;
//update record
interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
if (interaction.interBool)//verify if update successful
interaction.interSucess = true;
}
break;
#endregion
#region Delete Interaction
case enums.InteractionType.Delete://Delete Interaction
if (!isCustom)//generic delete from an object
if (interaction.interStoredProc)
{
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
command.CommandText = interaction.interStoredProcName;
}
else
{
command.CommandText = buildDeleteInteraction(interaction);
}
else//custom delete from the db
command.CommandText = interaction.query;
//delete record
interaction.interBool = dataTier.RemoveMethods.Delete(ref command);
if (interaction.interBool)//verify if delete successful
interaction.interSucess = true;
break;
#endregion
#region Verify Interaction
case enums.InteractionType.Verify://Verify Interaction
if (!isCustom)//generic verify from an object
if (interaction.interStoredProc)
{
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
command.CommandText = interaction.interStoredProcName;
}
else
{
command.CommandText = buildVerifyInteraction(interaction);
}
else//custom verify to the db
command.CommandText = interaction.query;
//verify record
interaction.interBool = dataTier.ReturnMethods.RecordExists(ref command);
interaction.interSucess = true;
break;
#endregion
#region Select Distinct Interaction
case enums.InteractionType.SelectDistinct://Select Interaction
if (!isCustom)//generic select from the db
command.CommandText = buildSelectDistinctInteraction(interaction);
else //custom select query
command.CommandText = interaction.query;
//return dataset
interaction.interSet = dataTier.ReturnMethods.ReturnDataSet(ref command);
//convert dataset to ArrayList
interaction.interList = utils.ConvertDataTableToList(interaction.interSet.Tables[0], interaction.interType);
//set interaction to successful
interaction.interSucess = true;
break;
#endregion
}
}
}
catch (Exception ex)
{
if (isTransactional)
//roll back transaction
command.Transaction.Rollback();
dataTier.CommandMethods.CompleteSQLCommand(ref command);
//append exception
interaction.interExceptions.Add(ex.Message);
//force result to false
interaction.interSucess = false;
}
finally
{
if (isTransactional)
//commit transaction
dataTier.TransactionalMethods.CompleteSQLTransaction(ref command);
else
dataTier.CommandMethods.CompleteSQLCommand(ref command);
result = utils.ObjectToByteArray(interaction);
}
return result;
}
///
/// Method to handle an Interaction with the database,
/// this can handle inserts, updates, deletes, verify and selects for multiple objects or single objects
///
/// by reference
public oInteraction ProcessInteraction(oInteraction _interaction, string key = "conn", bool isTransactional = false, string tablePrefix = "", bool isCollection = true)
{
SqlCommand command = new SqlCommand();
oInteraction interaction = new oInteraction();
bool isCustom = false;
try
{
//if (tablePrefix != String.Empty) //JasR 2015-04-20 not required as value is always sent from xData
_tblPrefix = tablePrefix;
interaction = _interaction;
//check if custom query being used
if (interaction.query.Trim() != String.Empty)
isCustom = true;
if (isTransactional)
command = BeginInteractionTransaction(key);
else
command = BeginInteractionCommand(key);
using (command.Connection)
{
//check which interaction is taking place
switch (interaction.interactionType)
{
#region Select Interaction
case enums.InteractionType.Select://Select Interaction
if (interaction.interStoredProc)
{
string query = buildSelectInteraction(interaction, ref command);
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
if (interaction.interStoredProcDynamicSelect)
{
//build up dynamic select for Stored Procedure
command.Parameters.Add(new SqlParameter("@Query", query));
}
command.CommandText = interaction.interStoredProcName;
}
else
{
if (!isCustom)//generic select from the db
command.CommandText = buildSelectInteraction(interaction, ref command);
else //custom select query
command.CommandText = interaction.query;
}
//CVH 2017-12-05 Set command timeout to 3 minutes. Default is 30seconds. TSP timing out on Debtors age report
//CVH 2018-12-20 Setting this to 10 minutes. CEP need to be able to run long processes. Need to find a better way to do this.
//GK 31-08-2021 Setting time out to 30 minutes
command.CommandTimeout = 1800;
//return dataset
interaction.interSet = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (isCollection)
{
//convert dataset to ArrayList
interaction.interList = utils.ConvertDataTableToList(interaction.interSet.Tables[0], interaction.interType);
//clear dataSet for performance
interaction.interSet.Clear();
}
//set interaction to successful
interaction.interSucess = true;
break;
#endregion
#region Insert Interaction
case enums.InteractionType.Insert://Insert Interaction
command.CommandTimeout = 1800;
if (!isCustom)//generic insert into db from an object or object collection
{
if (interaction.interStoredProc)
{
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
command.CommandText = interaction.interStoredProcName;
interaction.interInt = dataTier.ReturnMethods.ReturnIntValue(ref command);
interaction.interSucess = true;//successful
//clear parameters
command.Parameters.Clear();
}
else
{
//build query for insert
command.CommandText = buildInsertInteraction(interaction, ref command);
if (interaction.interObjects.Count > 0)//multiple insert of objects
{
//enumerate object collection for insert
foreach (Object obj in interaction.interObjects)
{
//build parameters
buildParamsInteraction(obj, interaction.identityField, ref command);
//save data
interaction.interSucess = dataTier.SavingMethods.AddRecord(ref command);
//clear parameters
command.Parameters.Clear();
}
}
else//single insert of a object
{
//build parameters
buildParamsInteraction(interaction.interObject, interaction.identityField, ref command);
//save record and return id
interaction.interInt = dataTier.ReturnMethods.ReturnIntValue(ref command);
if (interaction.interInt > 0)
interaction.interSucess = true;//successful
//clear parameters
command.Parameters.Clear();
}
}
}
else//custom query to perform a save
{
//set command text from custom query
command.CommandText = interaction.query;
//save record and return id
interaction.interInt = dataTier.ReturnMethods.ReturnIntValue(ref command);
if (interaction.interInt > 0)
interaction.interSucess = true;//successful
}
break;
#endregion
#region Update Interaction
case enums.InteractionType.Update://Update Interaction
if (!isCustom)//generic update to the db from an object or object collection
{
if (interaction.interStoredProc)
{
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
command.CommandText = interaction.interStoredProcName;
//CVH 2019-05-08 Setting this to 10 minutes. CEP need to be able to run long processes. Need to find a better way to do this.
//GK 31-08-2021 Setting time out to 30 minutes
command.CommandTimeout = 1800;
interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
if (interaction.interBool)//verify if update successful
interaction.interSucess = true;
//clear parameters
command.Parameters.Clear();
}
else
{
if (interaction.interObjects.Count > 0)//multiple update of objects
{
//enumerate object collection for update
foreach (Object obj in interaction.interObjects)
{
foreach (PropertyInfo prop in obj.GetType().GetProperties())
{
if (prop.Name.ToLower() == interaction.identityField.ToLower())
{
string value = prop.GetValue(obj, null).ToString();
if (value == "0")
{
//build query for insert
command.CommandText = buildInsertInteraction(interaction, ref command);
}
else
{
interaction.whereClause = interaction.identityField + " = " + prop.GetValue(obj, null).ToString();
//build query for update
command.CommandText = buildUpdateInteraction(interaction, ref command);
}
//build parameters
buildParamsInteraction(obj, interaction.identityField, ref command);
//update record
interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
if (interaction.interBool)//verify if update successful
interaction.interSucess = true;
//clear parameters
command.Parameters.Clear();
break;
}
}
}
}
else //single update of an object to the db
{
//build query for update
command.CommandTimeout = 1800;
command.CommandText = buildUpdateInteraction(interaction, ref command);
//build parameters
buildParamsInteraction(interaction.interObject, interaction.identityField, ref command);
//update record
interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
if (interaction.interBool)//verify if update successful
interaction.interSucess = true;
//clear parameters
command.Parameters.Clear();
}
}
}
else//custom update to the db
{
command.CommandText = interaction.query;
//GK 31-08-2021 Setting time out to 30 minutes
command.CommandTimeout = 1800;
//update record
interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
if (interaction.interBool)//verify if update successful
interaction.interSucess = true;
}
break;
#endregion
#region Delete Interaction
case enums.InteractionType.Delete://Delete Interaction
if (!isCustom)//generic delete from an object
if (interaction.interStoredProc)
{
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
command.CommandText = interaction.interStoredProcName;
}
else
{
command.CommandText = buildDeleteInteraction(interaction);
}
else//custom delete from the db
command.CommandText = interaction.query;
//delete record
interaction.interBool = dataTier.RemoveMethods.Delete(ref command);
if (interaction.interBool)//verify if delete successful
interaction.interSucess = true;
break;
#endregion
#region Verify Interaction
case enums.InteractionType.Verify://Verify Interaction
if (!isCustom)//generic verify from an object
if (interaction.interStoredProc)
{
command.CommandType = CommandType.StoredProcedure;
//build Param
buildParametersOnCommand(interaction, ref command);
command.CommandText = interaction.interStoredProcName;
}
else
{
command.CommandText = buildVerifyInteraction(interaction);
}
else//custom verify to the db
command.CommandText = interaction.query;
//verify record
interaction.interBool = dataTier.ReturnMethods.RecordExists(ref command);
interaction.interSucess = true;
break;
#endregion
#region Select Distinct Interaction
case enums.InteractionType.SelectDistinct://Select Interaction
if (!isCustom)//generic select from the db
command.CommandText = buildSelectDistinctInteraction(interaction);
else //custom select query
command.CommandText = interaction.query;
//return dataset
interaction.interSet = dataTier.ReturnMethods.ReturnDataSet(ref command);
//convert dataset to ArrayList
interaction.interList = utils.ConvertDataTableToList(interaction.interSet.Tables[0], interaction.interType);
//set interaction to successful
interaction.interSucess = true;
break;
#endregion
}
}
}
catch (Exception ex)
{
if (isTransactional)
//roll back transaction
command.Transaction.Rollback();
dataTier.CommandMethods.CompleteSQLCommand(ref command);
//append exception
interaction.interExceptions.Add(ex.Message);
//force result to false
interaction.interSucess = false;
}
finally
{
if (isTransactional)
//commit transaction
dataTier.TransactionalMethods.CompleteSQLTransaction(ref command);
else
dataTier.CommandMethods.CompleteSQLCommand(ref command);
}
return interaction;
}
public class DeclarationOrderComparator : IComparer
{
int IComparer.Compare(Object x, Object y)
{
PropertyInfo first = x as PropertyInfo;
PropertyInfo second = y as PropertyInfo;
if (first.MetadataToken < second.MetadataToken)
return -1;
else if (first.MetadataToken > second.MetadataToken)
return 1;
return 0;
}
}
#region private interaction Methods
///
/// Method to Build Parmateres on a Command Object
///
///
///
private void buildParametersOnCommand(oInteraction interaction, ref SqlCommand command)
{
try
{
List paramList = new List();
foreach (oDynamicParam param in interaction.interStoredProcParams)
{
if (param.paramObject.GetType() == typeof(Byte[]))
{
paramList.Add(new SqlParameter("@" + param.paramDisplayName, SqlDbType.Image, ((Byte[])param.paramObject).Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, param.paramObject));
}
else if (param.paramObject.GetType() == typeof(DateTime))
{
paramList.Add(new SqlParameter("@" + param.paramDisplayName, utils.fixDate(param.paramObject)));
}
else if (param.paramObject.GetType() == typeof(String))
{
if (interaction.interStoredProc)
{
paramList.Add(new SqlParameter("@" + param.paramDisplayName, param.paramObject.ToString()));
}
else
{
paramList.Add(new SqlParameter("@" + param.paramDisplayName, utils.formatSqlString(param.paramObject.ToString())));
}
}
else
{
paramList.Add(new SqlParameter("@" + param.paramDisplayName, param.paramObject));
}
}
command.Parameters.Clear();
command.Parameters.AddRange(paramList.ToArray());
}
catch (Exception ex)
{
throw ex;
}
}
///
/// Build Select Query from an Interaction
///
///
private string buildSelectInteraction(oInteraction interaction, ref SqlCommand command)
{
string query = string.Empty;
string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
//ArrayList tableFields = constructor.GetTableFields(tableName, ref command, true);
DataTable sqltables = new DataTable();
try
{
if (interaction.includeDynamics)
sqltables = constructor.GetTables().Tables[0];
query += "SELECT ";
if (interaction.interRecordLimit > 0)
query += "TOP " + interaction.interRecordLimit + " ";
query += " * FROM " + tableName + " ";
if (interaction.whereClause != String.Empty)
query += "WHERE " + interaction.whereClause + " ";
if (interaction.orderClause != String.Empty)
query += "Order BY " + interaction.orderClause;
//build column list
//foreach (PropertyInfo prop in interaction.interType.GetProperties())
//{
// foreach (oTable field in tableFields)
// {
// if (field.fieldName.ToLower() == prop.Name.ToLower())
// {
// query += tableName + ".[" + prop.Name + "], ";
// break;
// }
// }
// //handle dynamic creation of related fields
// if (interaction.includeDynamics)
// {
// if (prop.PropertyType == typeof(int) && (prop.Name.Contains("Id") || prop.Name.Contains("ID")) && prop.Name != interaction.identityField)
// {
// string field = prop.Name.Replace("ID", "").Replace("Id", "");
// string TableName = String.Empty;
// foreach (DataRow row in sqltables.Rows)
// {
// if (row["tableName"].ToString().ToLower() == _tblPrefix + field.ToLower())
// {
// TableName = _tblPrefix + field;
// break;
// }
// else if (row["tableName"].ToString().ToLower() == _tblPrefix + field.Remove(field.Length - 1, 1).ToLower() + "ies")
// {
// TableName = _tblPrefix + field.Remove(field.Length - 1, 1) + "ies";
// break;
// }
// else if (row["tableName"].ToString().ToLower() == _tblPrefix + field.ToLower() + "es")
// {
// TableName = _tblPrefix + field + "es";
// break;
// }
// else if (row["tableName"].ToString().ToLower() == _tblPrefix + field.ToLower() + "s")
// {
// TableName = _tblPrefix + field + "s";
// break;
// }
// }
// if (TableName != String.Empty)//we have a valid table
// {
// string fieldName = String.Empty;
// foreach (oTable table in constructor.GetTableFields(TableName, ref command))
// {
// if (table.fieldName.ToLower() == field.ToLower())//we have a valid field
// {
// fieldName = table.fieldName;
// break;
// }
// }
// if (fieldName != String.Empty)
// query += "(SELECT TOP 1 " + field + " FROM " + TableName + " WHERE " + interaction.identityField + " = " + prop.Name + ") [" + field + "], ";
// }
// }
// }
//}
//if (interaction.includeDynamics)
//{
// foreach (String subQuery in CustomSubQueries(interaction.interType.Name, ref command))
// {
// query += subQuery + ", ";
// }
// foreach (String subQuery in interaction.interCustomSubQueries)
// {
// query += subQuery + ", ";
// }
//}
//if (query.Contains(","))
// //remove trailing comma
// query = query.Remove(query.Length - 2, 1);
//if (!interaction.interStoredProcDynamicSelect)//check if not a dynamic stored procedure select being used
//{
//query += "FROM " + tableName + " ";
//if (interaction.whereClause != String.Empty)
// query += "WHERE " + interaction.whereClause + " ";
//if (interaction.orderClause != String.Empty)
// query += "Order BY " + interaction.orderClause;
//}
}
catch (Exception ex)
{
throw ex;
}
return query;
}
///
/// Build Insert Query from an Interaction
///
///
private string buildInsertInteraction(oInteraction interaction, ref SqlCommand command)
{
string query = string.Empty;
string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
Type type = interaction.interType;
PropertyInfo[] properties = type.GetProperties();
ArrayList tableFields = constructor.GetTableFields(tableName, ref command);
Array.Sort(properties, new DeclarationOrderComparator());
try
{
query += "INSERT INTO " + tableName + " ";
query += "(";
//build column list
foreach (PropertyInfo prop in properties)
{
foreach (oTable field in tableFields)
{
if (field.fieldName.ToLower() == prop.Name.ToLower())
{
if (prop.Name.ToLower() != interaction.identityField.ToLower())
query += "[" + prop.Name + "], ";
break;
}
}
}
//remove trailing comma
query = query.Remove(query.Length - 2, 1);
query += ") ";
query += "VALUES (";
//build values list
foreach (PropertyInfo prop in properties)
{
foreach (oTable field in tableFields)
{
if (field.fieldName.ToLower() == prop.Name.ToLower())
{
if (prop.Name.ToLower() != interaction.identityField.ToLower())
query += "@" + prop.Name + ", ";
break;
}
}
}
//remove trailing comma
query = query.Remove(query.Length - 2, 1);
query += ") ";
query += "SELECT SCOPE_IDENTITY() AS Value ";
}
catch (Exception ex)
{
throw ex;
}
return query;
}
///
/// Build Update Query from an Object
///
///
private string buildUpdateInteraction(oInteraction interaction, ref SqlCommand command)
{
string query = string.Empty;
string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
ArrayList tableFields = constructor.GetTableFields(tableName, ref command);
try
{
query += "UPDATE " + tableName + " ";
query += "SET";
//build column list
foreach (PropertyInfo prop in interaction.interType.GetProperties())
{
foreach (oTable field in tableFields)
{
if (field.fieldName.ToLower() == prop.Name.ToLower())
{
if (prop.Name.ToLower() != interaction.identityField.ToLower() && prop.Name.ToLower() != "recordid")
query += "[" + prop.Name + "] = @" + prop.Name + ", ";
break;
}
}
}
//remove trailing comma
query = query.Remove(query.Length - 2, 1);
if (interaction.whereClause != String.Empty)
//add where clause
query += "WHERE " + interaction.whereClause;
}
catch (Exception ex)
{
throw ex;
}
return query;
}
///
/// Build Verify Query from an Object
///
///
private string buildVerifyInteraction(oInteraction interaction)
{
string query = string.Empty;
string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
try
{
query += "Select " + interaction.identityField + " FROM " + tableName + " ";
//add where clause
query += "WHERE " + interaction.whereClause;
}
catch (Exception ex)
{
throw ex;
}
return query;
}
///
/// Build Delete Query from an Interaction
///
///
private string buildDeleteInteraction(oInteraction interaction)
{
string query = string.Empty;
string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
try
{
query += "DELETE FROM " + tableName + " ";
if (interaction.whereClause != String.Empty)
//add where clause
query += "WHERE " + interaction.whereClause;
}
catch (Exception ex)
{
throw ex;
}
return query;
}
///
/// Build Select Query from an Interaction
///
///
private string buildSelectDistinctInteraction(oInteraction interaction)
{
string query = string.Empty;
string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
try
{
query += "SELECT DISTINCT(" + interaction.interDistinct + ") ";
query += "FROM " + tableName + " ";
if (interaction.whereClause != String.Empty)
query += "WHERE " + interaction.whereClause + " ";
if (interaction.orderClause != String.Empty)
query += "Order BY " + interaction.orderClause;
}
catch (Exception ex)
{
throw ex;
}
return query;
}
///
/// Method to Build Command Parameters from an Object
///
///
///
///
private void buildParamsInteraction(object interObject, string identityField, ref SqlCommand command)
{
try
{
//build value parameter list for the command
foreach (PropertyInfo prop in interObject.GetType().GetProperties())
{
if (prop.Name.ToLower() != identityField.ToLower() && prop.Name.ToLower() != "recordid")
{
//check the type
if (prop.PropertyType == typeof(Byte[]))
{
Byte[] myData = (Byte[])prop.GetValue(interObject, null);
command.Parameters.Add(new SqlParameter("@" + prop.Name, SqlDbType.Image, myData.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, myData));
}
else if (prop.PropertyType == typeof(DateTime))
command.Parameters.Add(new SqlParameter("@" + prop.Name, utils.fixDate(prop.GetValue(interObject, null))));
else if (prop.PropertyType == typeof(String))
command.Parameters.Add(new SqlParameter("@" + prop.Name, prop.GetValue(interObject, null)?.ToString() ?? ""));
else
command.Parameters.Add(new SqlParameter("@" + prop.Name, prop.GetValue(interObject, null)));
}
}
}
catch (Exception ex)
{
throw ex;
}
}
///
/// Generic list of SubQuery Strings
///
///
///
private List CustomSubQueries(string objectName, ref SqlCommand command)
{
List result = new List();
DataSet data = new DataSet();
try
{
string query = "SELECT * FROM evo_CustomSubQueries WHERE objectName = '" + objectName + "' ";
command.CommandText = query;
data = dataTier.ReturnMethods.ReturnDataSet(ref command);
if (data.Tables.Count > 0 && data.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in data.Tables[0].Rows)
{
result.Add(row["SQL"].ToString());
}
}
}
catch (Exception)
{
//do nothing incase this table does not exist in SQL, this is custom to PM PRo
}
return result;
}
#endregion
#endregion
}
}