public SQLDMLStatement GetInsertScriptForTypedEntity(IDatabaseTenant Tenant, object Entity, List <string> ParameterNames, List <object> ParameterValues, DMLStatemtType dMLStatemtType, List <ISQLDMLStatementVariable> uniqueKeys)
{
string DMLStatementPrefix = "";
string paramNames = "";
int expectedParamCount;
object[] ParamNamesArray = ParameterNames.ToArray();
object[] ParameterValuesArray = ParameterValues.ToArray();
expectedParamCount = ParameterNames.Count;
string fieldList = "";
int i = 0;
foreach (var param in ParamNamesArray)
{
fieldList += (i > 0 ? ", " : "Tenant, ") + (string)param;
paramNames += (i > 0 ? "," : "") + "@" + (string)param;
i++;
}
if (dMLStatemtType == DMLStatemtType.Insert)
{
DMLStatementPrefix = $"insert into {Entity.GetType().Name} ({fieldList})";
}
else if (dMLStatemtType == DMLStatemtType.Delete)
{
DMLStatementPrefix = $"delete from {Entity.GetType().Name} ";
}
else if (dMLStatemtType == DMLStatemtType.Update)
{
DMLStatementPrefix = $"update {Entity.GetType().Name} set ";
}
else if (dMLStatemtType == DMLStatemtType.Select)
{
DMLStatementPrefix = $"select * from {Entity.GetType().Name} ";
}
else if (dMLStatemtType == DMLStatemtType.SelectAll)
{
DMLStatementPrefix = $"select * from {Entity.GetType().Name} ";
}
string[] paramaterNames;
if (paramNames.Length > 0)
{
paramaterNames = paramNames.Split(',');
}
else
{
paramaterNames = new string[] { }
};
return(_GetDMLScript(Tenant, DMLStatementPrefix, paramaterNames, ParameterValuesArray, expectedParamCount, dMLStatemtType, uniqueKeys));
}
private SQLDMLStatement _GetDMLScript(IDatabaseTenant Tenant, string DMLStatementPrefix, object[] ParameterNames, object[] ParameterValues, int expectedParamCount, DMLStatemtType DMLStatemtType, List <ISQLDMLStatementVariable> uniqueKeys)
{
if (expectedParamCount != ParameterValues.GetUpperBound(0) + 1)
{
throw new Exception("Number of parameters supplied for statement differs from expected");
}
if (DMLStatemtType == DMLStatemtType.SelectAll && false)
{
ParameterNames = AppendValueToBeginningOfArray(new object[0], "@Tenant");
ParameterValues = AppendValueToBeginningOfArray(new object[0], Tenant.Code);
}
else
{
ParameterNames = AppendValueToBeginningOfArray(ParameterNames, "@Tenant");
ParameterValues = AppendValueToBeginningOfArray(ParameterValues, Tenant.Code);
}
if (ParameterNames.GetUpperBound(0) != ParameterValues.GetUpperBound(0))
{
throw new Exception("CODE LOGIC ERROR: Param names and value count mismatch");
}
string paramlist = "";
string paramlist2 = "";
string dmlSuffix = "";
int j = 0;
foreach (var param in ParameterValues)
{
if (ParameterNames[j].ToString().Contains("@@"))
{
if (ParameterValues[j].ToString().Contains("'"))
{
throw new Exception("injection attempt");
}
else
{
DMLStatementPrefix = DMLStatementPrefix.Replace(ParameterNames[j].ToString(), ParameterValues[j].ToString());
}
}
else if (DMLStatementPrefix.Contains(ParameterNames[j].ToString()))
{
}
else if (DMLStatemtType == DMLStatemtType.Insert)
{
paramlist += ParameterNames[j] + ",";
}
else if (DMLStatemtType == DMLStatemtType.Delete)
{
bool considerUniqueKeys = true;
considerUniqueKeys = uniqueKeys != null;
if (considerUniqueKeys)
{
if (uniqueKeys.Where(u => u.Name == ParameterNames[j].ToString()).Count() > 0)
{
paramlist += (paramlist.Length > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
else
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
else if (DMLStatemtType == DMLStatemtType.Select || DMLStatemtType == DMLStatemtType.SelectAll)
{
bool considerUniqueKeys = true;
considerUniqueKeys = uniqueKeys != null;
if (considerUniqueKeys)
{
if (uniqueKeys.Where(u => u.Name == ParameterNames[j].ToString()).Count() > 0)
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
else
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
else if (DMLStatemtType == DMLStatemtType.Update)
{
bool isUniqueKey = false;
bool considerUniqueKeys = (uniqueKeys != null);
if (considerUniqueKeys)
{
if (uniqueKeys.Where(u => u.Name == ParameterNames[j].ToString()).Count() > 0)
{
isUniqueKey = true;
}
}
if (isUniqueKey && considerUniqueKeys)
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
else if (!isUniqueKey && considerUniqueKeys)
{
paramlist2 += (paramlist2.Length > 0 ? " , " : " ");
paramlist2 += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j].ToString();
}
else if (ParameterNames[j].ToString().Substring(0, 1) == "$")
{
paramlist2 += (paramlist2.Length > 0 ? " , " : " ");
paramlist2 += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j].ToString().Replace("$", "@");
}
else
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
j++;
}
if ((paramlist.Length > 0) && (paramlist.EndsWith(",")))
{
paramlist = paramlist.Substring(0, paramlist.Length - 1);
}
if (paramlist.Length > 0)
{
dmlSuffix += (DMLStatemtType == DMLStatemtType.Insert ? $" values ({paramlist})" : "");
dmlSuffix += (DMLStatemtType == DMLStatemtType.Delete ? $" where {paramlist}" : "");
dmlSuffix += (DMLStatemtType == DMLStatemtType.Select ? $" where {paramlist}" : "");
dmlSuffix += (DMLStatemtType == DMLStatemtType.SelectAll ? $" where {paramlist}" : "");
dmlSuffix += (DMLStatemtType == DMLStatemtType.Update ? paramlist2 + $" where {paramlist}" : "");
}
List <ISQLDMLStatementVariable> sqlVars = new List <ISQLDMLStatementVariable>();
int i = 0;
foreach (var val in ParameterValues)
{
string paramNam = "";
paramNam = ParameterNames[i].ToString().Replace("$", "@");
if (DMLStatementPrefix.Contains(paramNam) || dmlSuffix.Contains(paramNam))
{
sqlVars.Add(new SQLDMLStatementVariable {
Name = paramNam, Value = val
});
}
i++;
}
SQLDMLStatement dmlStatement = new SQLDMLStatement
{
PreparedStatement = DMLStatementPrefix + dmlSuffix,
Variables = sqlVars,
StatemtType = DMLStatemtType
};
//if (expectedParamCount != dmlStatement.ParameterCount - 1)
// throw new Exception("CODE LOGIC ERROR : Param Count Mismatch");
return(dmlStatement);
}
internal static SQLDMLStatement GetDMLStatementForGenericEntity(IDatabaseTenant Tenant, object Entity, DMLStatemtType dMLStatemtType, List <string> querProperytParametersToUse)
{
List <object> propValues = GetObjectPropertyValues(Entity);
List <string> propNames = GetObjectPropertyNames(Entity);
List <ISQLDMLStatementVariable> uniqueKeys = null;
if (dMLStatemtType == DMLStatemtType.SelectAll && querProperytParametersToUse == null)
{
uniqueKeys = new List <ISQLDMLStatementVariable>
{
new SQLDMLStatementVariable {
Name = "@Tenant", Value = "TR1"
}
};
}
else if (querProperytParametersToUse == null)
{
EntityDescriber ed = new EntityDescriber(Entity);
bool primaryKeyIdFieldValueSupplied = ed.PrimaryKeyProvidedOnEntity();
if (primaryKeyIdFieldValueSupplied && (dMLStatemtType == DMLStatemtType.Update || dMLStatemtType == DMLStatemtType.Delete))
{
uniqueKeys = GetUniqueKeyNameValuePairs(Entity, true, false);
}
else if (primaryKeyIdFieldValueSupplied == false && (dMLStatemtType == DMLStatemtType.Update || dMLStatemtType == DMLStatemtType.Delete))
{
uniqueKeys = GetUniqueKeyNameValuePairs(Entity, false, true);
}
else
{
uniqueKeys = GetUniqueKeyNameValuePairs(Entity, false, true);
}
}
else
{
uniqueKeys = GetFilterNameValuePairs(Entity);
}
List <ISQLDMLStatementVariable> uniqueKeysFiltered = new List <ISQLDMLStatementVariable>();
if (querProperytParametersToUse != null)
{
querProperytParametersToUse.Add("Tenant");
foreach (var key in uniqueKeys)
{
if (querProperytParametersToUse.Contains(key.Name.Replace("@", "")))
{
uniqueKeysFiltered.Add(key);
}
}
}
else
{
uniqueKeysFiltered = uniqueKeys;
}
SQLDMLScripts dmlScripts = new SQLDMLScripts();
return(dmlScripts.GetInsertScriptForTypedEntity(Tenant, Entity, propNames, propValues, dMLStatemtType, uniqueKeysFiltered));
}