Hi everyone,
I have created a script task to create new records from a csv.
I am trying to implement a method to search the db for a matching record and then update it (in case it exists).
The second part (the 'else' part, it's working fine)
Does anybody know how to filter and search a DB table? What am I getting wrong?
void saveLine(string line, int rowNumber){
if(rowNumber!=0)
{
string lineFormatted = line.Replace("\"", "");
string[] lineSplitted = lineFormatted.Split(',');
var LRNMBL = lineSplitted[0];
var LRRIGA = lineSplitted[1];
var LRDTBL = lineSplitted[3];
var LRCANA = lineSplitted[6];
// Creation of query instance with "Contact" root schema.
// An EntitySchemaQuery instance that accesses the UsrTestFTPCall table of the database.
var esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "UsrVedniteTestate");
// Adding columns to the query.
esq.AddColumn("UsrLRNMBL");
esq.AddColumn("UsrLRRIGA");
esq.AddColumn("UsrLRDTBL");
esq.AddColumn("UsrLRCANA");
// Filter the query data. according to LRNMBL and
var esqFirstFilter = esq.createColumnFilterWithParameters(Terrasoft.ComparisonType.EQUAL,"UsrLRNMBL", LRNMBL);
var esqSecondFilter = esq.createColumnFilterWithParameters(Terrasoft.ComparisonType.EQUAL,"UsrLRRIGA", LRRIGA);
esq.filters.logicalOperation = Terrasoft.LogicalOperatorType.AND;
esq.filters.add("esqFirstFilter", esqFirstFilter);
//esq.Filters.Add(esqSecondFilter);
// Get the result of the query.
var entities = esq.GetEntityCollection(UserConnection);
// If the data is received.
if (entities.Count > 0)//Record exists in the db --> update
{
var recordToUpd = entities[0];
recordToUpd.SetColumnValue("UsrLRNMBL", LRNMBL);
recordToUpd.SetColumnValue("UsrLRRIGA", LRRIGA);
recordToUpd.SetColumnValue("UsrLRDTBL", LRDTBL);
recordToUpd.SetColumnValue("UsrLRCANA", LRCANA);
}
else
{
//the record doesn't exist --> Create one
var TestObj = UserConnection.EntitySchemaManager.GetInstanceByName("UsrTestFTPCall").CreateEntity(UserConnection);
//access the data position and assign it to its column
TestObj.SetDefColumnValues();
//Associate new column values to old Col values
TestObj.SetColumnValue("UsrName", rowNumber.ToString());
TestObj.SetColumnValue("UsrLRNMBL", LRNMBL);//riferimento testata di vendita
TestObj.SetColumnValue("UsrLRRIGA", LRRIGA);//riferimento riga all'interno della stessa vendita
TestObj.SetColumnValue("UsrLRDTBL", LRDTBL);
TestObj.SetColumnValue("UsrLRCANA", LRCANA);
TestObj.Save();
}
}
}
