internal static StringBuilder BuildUPDATEQuery(Hashtable pHT, ArrayList pExcludeList)
{
//test code starts here
//Start to create the query
StringBuilder vUpdateQuery = new StringBuilder("UPDATE ");
//This will add the Column names
StringBuilder vUpdate = new StringBuilder(" SET ");
//This will add the Column values
StringBuilder vWhere = new StringBuilder(" WHERE ");
string vTableName = "";
string vColumnName = "";
bool vPK = false;
bool vTBL = false;
foreach (DictionaryEntry de in pHT)
{
vColumnName = de.Key.ToString();
//Items in the exclude list need required in the SQL
if (pExcludeList != null)
{
if (pExcludeList.Contains(vColumnName))
{
continue;
}
}
if (CRUDHelper.CheckFilteredColumn(vColumnName) && !vColumnName.Contains("_PK"))
{
if (vColumnName.Contains("_FK"))
{
vColumnName = CRUDHelper.RemoveSuffix(vColumnName, "_FK");
}
//Adding column name followed by '= column values'
vUpdate.Append(vColumnName);
vUpdate.Append(" = ");
if (vColumnName.ToUpper() == "ACTIONDATE")
{
vUpdate.Append("Getdate()");
}
else
{
vUpdate.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
}
vUpdate.Append(",");
if (vColumnName.Contains("CompanyCode"))
{
//Generating where clause with companycode consulting with Anam vi
vWhere.Append("CompanyCode");
vWhere.Append(" = ");
vWhere.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
vWhere.Append(" AND ");
}
}
else if (vColumnName.Contains("_TBL"))//Property with _TBL contains table name
{
vTBL = true;
//Getting table name
vTableName = de.Value.ToString();
}
else if (vColumnName.Contains("_PK")) //Property with _PK contains primary column name
{
vPK = true; //Setting true to ensure that where clause will be applied
//Removing _PK from the property name.
vColumnName = CRUDHelper.RemoveSuffix(vColumnName, "_PK");
//Generating where clause
vWhere.Append(vColumnName);
vWhere.Append(" = ");
vWhere.Append(CRUDHelper.GetFormatedColumnValue(de.Value));
vWhere.Append(" AND ");
}
}//End of foreach (PropertyInfo pi in vPI)
vUpdate.Remove(vUpdate.Length - 1, 1);//Removing last comma
if (vPK)
{
vWhere.Remove(vWhere.Length - 5, 5);//Removing last ' AND ' from where clause
}
//Adding table name
vUpdateQuery.Append(vTableName);
//Adding update values
vUpdateQuery.Append(vUpdate.ToString());
if (vPK)
{
//Adding where clause
vUpdateQuery.Append(vWhere.ToString());
}
if (!vPK)
{
throw new ApplicationException("Error in LS_CRUD.CRUDBuilder.UPDATEQuery(...)! Primary Key property for the parameter model is not found.");
}
else if (!vTBL)
{
throw new ApplicationException("Error in LS_CRUD.CRUDBuilder.UPDATEQuery(...)! Table Name property for the parameter model is not found.");
}
else
{
return(vUpdateQuery);
}
}
