public bool fnc_insert(Dictionary <string, object> dic)
{
string columnNames = "";
string columnValues = "";
string cmd;
int i;
object value;
for (i = 0; i <= dic.Count - 1; i++)
{
value = dic.ElementAt(i).Value;
if (Functions.IsNull(value))
{
columnValues = columnValues + " Null " + ",";
}
else if (Functions.fnc_isNumeric(value))
{
columnValues = columnValues + value + ",";
}
else
{
columnValues = columnValues + "'" + value + "',";
}
columnNames = columnNames + dic.ElementAt(i).Key + ",";
}
if (!string.IsNullOrEmpty(columnNames))
{
columnNames = columnNames.Remove(columnNames.Length - 1, 1);
columnValues = columnValues.Remove(columnValues.Length - 1, 1);
}
cmd = " insert into " + this.TableName + "(" + columnNames + ")values(" + columnValues + ")";
using (var entity = new Models.logisticEntities())
{
try
{
entity.Database.ExecuteSqlCommand(cmd);
return(true);
}
catch (Exception ex)
{
SharedVariables.logs.Error("Error in fnc_insert", ex);
return(false);
}
}
}
public bool fnc_update(Dictionary <string, object> dic, string whereCondition)
{
string cmd = "";
int i;
object value;
for (i = 0; i <= dic.Count - 1; i++)
{
value = dic.ElementAt(i).Value;
if (Functions.IsNull(value))
{
cmd = cmd + dic.ElementAt(i).Key + "=" + " Null " + ",";
}
else if (Functions.fnc_isNumeric(value))
{
cmd = cmd + dic.ElementAt(i).Key + "=" + value.ToString() + ",";
}
else
{
cmd = cmd + dic.ElementAt(i).Key + "='" + value.ToString() + "',";
}
}
if (!string.IsNullOrEmpty(cmd))
{
cmd = cmd.Remove(cmd.Length - 1, 1);
}
cmd = " update " + this.TableName + " set " + cmd + " where " + whereCondition;
using (var entity = new Models.logisticEntities())
{
try
{
entity.Database.ExecuteSqlCommand(cmd);
return(true);
}
catch (Exception ex)
{
SharedVariables.logs.Error("Error in fnc_update", ex);
return(false);
}
}
}
public bool fnc_save(System.Collections.ObjectModel.ReadOnlyCollection <IWebElement> items)
{
int i;
string whereCondition = "";
Dictionary <string, object> dic;
bool retVal = true;
for (i = prp_skipRowTop; i <= items.Count - 1 - prp_skipRowBottom; i++)
{
if (this.prp_skipRowIndecies != null && this.prp_skipRowIndecies.Contains(i))
{
continue;
}
whereCondition = this.fnc_getWhereCondition(items[i]);
dic = this.fnc_getColumnsValue(items[i]);
using (var entity = new Models.logisticEntities())
{
var exists = entity.Database.SqlQuery <int?>("select top 1 1 from " + this.TableName + " where " + whereCondition).FirstOrDefault();
if (exists.HasValue)
{
if (!this.fnc_update(dic, whereCondition))
{
retVal = false;
}
}
else
{
if (!this.fnc_insert(dic))
{
retVal = false;
}
}
}
}
return(retVal);
}
public Dictionary <string, object> fnc_getColumnsValue(IWebElement item)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
int i, j;
htmlColumn htmlCol;
object value;
string whereCondition;
string selectStr;
htmlTableReference tableReference;
for (i = 0; i <= this.Columns.Count - 1; i++)
{
if (!(this.Columns[i] is Models.htmlColumn))
{
continue;
}
htmlCol = (Models.htmlColumn) this.Columns[i];
value = htmlCol.fnc_getColumnValueFromControl(item);
dic.Add(htmlCol.ColumnName, value);
}
if (this.prp_tableReferences != null)
{
for (i = 0; i <= this.prp_tableReferences.Length - 1; i++)
{
if (!string.IsNullOrEmpty(this.prp_tableReferences[i].prp_parentTableName) &&
this.prp_tableReferences[i].prp_parentRefColumns != null &&
this.prp_tableReferences[i].prp_parentIdColumns != null
&& !string.IsNullOrEmpty(this.prp_tableReferences[i].prp_childTableName) &&
this.prp_tableReferences[i].prp_childRefColumns != null &&
this.prp_tableReferences[i].prp_childIdColumns != null
)
{
tableReference = this.prp_tableReferences[i];
whereCondition = "";
selectStr = "";
#region prepare where string from parentTable
for (j = 0; j <= tableReference.prp_childRefColumns.Length - 1; j++)
{
if (!dic.ContainsKey(tableReference.prp_childRefColumns[j]))
{
throw new Exception("TableSoucre" + this.TableName + " does not have column " + tableReference.prp_childRefColumns[j] + " which is specified in tableRefernce");
}
value = dic[tableReference.prp_childRefColumns[j]];
if (Functions.IsNull(value)) //throw new Exception("identifierColumn Value is null (colName = " + htmlCol.ColumnName + ")");
{
whereCondition = whereCondition + tableReference.prp_parentRefColumns[j] + " is null " + " and ";
}
else if (Functions.fnc_isNumeric(value))
{
whereCondition = whereCondition + tableReference.prp_childRefColumns[j] + "=" + value + " and ";
}
else
{
whereCondition = whereCondition + " " + tableReference.prp_parentRefColumns[j] + " = '" + value + "' and ";
}
}
if (!string.IsNullOrEmpty(whereCondition))
{
whereCondition = whereCondition.Remove(whereCondition.Length - 4, 4);
}
#endregion
#region prepare select string from parentTable
for (j = 0; j <= tableReference.prp_parentIdColumns.Length - 1; j++)
{
selectStr += tableReference.prp_parentIdColumns[j] + ", ";
}
if (!string.IsNullOrEmpty(selectStr))
{
selectStr = selectStr.Remove(whereCondition.Length - 2, 2);
}
#endregion
using (var entity = new Models.logisticEntities())
{
DataTable dt = new DataTable();
Functions.sb_fillDatatable((SqlConnection)entity.Database.Connection
, "select top 1 " + selectStr + " from" + tableReference.prp_parentTableName + " where " + whereCondition
, dt);
if (dt.Rows.Count > 0)
{
for (j = 0; j <= tableReference.prp_parentIdColumns.Length - 1; j++)
{
dic[tableReference.prp_childIdColumns[j]] = dt.Rows[0][tableReference.prp_parentIdColumns[j]];
}
}
}
}
}
}
return(dic);
}
