mercredi 8 avril 2015

Error using relfection to check if entity exists - Unknown column 'Cisco Systems' in 'where clause'

So I'm using LimeSurvey API to send the surveys to users and then I have a XML that will tell me how to parse them and insert into my database the information I want. The code bellow is where I seek the users that still need to be parsed, parse their response and then insert into the database.



using (SIREntities context = new SIREntities())
{
List<persons> personList = context.persons.Where(p => p.StateId == Survey.AfterAddParticipantStatusId).ToList();
personList = personList.Where(o => o.PersonId == 6795).ToList();
foreach (persons person in personList)
{
try
{
personsurveys personSurvey = person.personsurveys.SingleOrDefault(s => s.surveyId == Survey.Surveyid);

if (personSurvey == null)
continue;

Participant participant = Service.GetParticipantProperties(personSurvey.tokenId.Value);

if (!participant.Completed.Equals("N"))
{
string responses = Service.GetResponsesByToken("json", personSurvey.token);
SurveyParser<persons> parser = new SurveyParser<persons>(person, responses, Survey.Schema, Survey.Surveyid.ToString(), context);
parser.Parse();

if (Survey.AfterResponseStatusId.HasValue)
NewStatus(person, Survey.AfterResponseStatusId.Value);

byte[] allResponses = Service.GetResponsesByTokenPdf(personSurvey.token);
if (allResponses.Length > 0)
GetAllResponsesFile(person, allResponses);

context.SaveChanges();

personsToIndex.Add(person.PersonId);
}
}
catch (Exception ex)
{
UndoChangesEntity(context, person);
LogHelper.Instance.Error(string.Format("Error parsing person {0} with error {1}", person.PersonId, ex.Message));
LogHelper.Instance.Error("StackTrace: " + ex.StackTrace);
if (ex.InnerException != null)
LogHelper.Instance.Error("InnerException: " + ex.InnerException);
}
}
}


To parse the survey I use reflection because I want to parse diferente surveys and dont want to have to code everytime there is a new survey. For that I build a XML that will tell me how to match the information between the survey and my database. For the most part everything works really good but I keep getting this one error and cant seem to understand why.


here is the code where the error occures


private List ParseAdicionalValues(XElement question, string entityNamespace, List adicionalValues, List existingValues) { existingValues = existingValues.Distinct().ToList(); if (!adicionalValues.Any()) return existingValues;



List<string> addedValues = new List<string>();

string fieldName = question.Element("namespace").Attribute("field") != null ? question.Element("namespace").Attribute("field").Value : string.Empty;
string tableName = question.Element("namespace").Attribute("table") != null ? question.Element("namespace").Attribute("table").Value : string.Empty;
string tableNamespace = question.Element("namespace").Attribute("tableNamespace") != null ?
question.Element("namespace").Attribute("tableNamespace").Value : entityNamespace;

if (string.IsNullOrEmpty(fieldName) || string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(tableNamespace))
return existingValues;

Type insertTpe = Type.GetType(tableNamespace);
if (insertTpe == null)
return existingValues;

using (var context = new SIREntities())
{
var lookupEntity = context.Set(insertTpe);
bool hasInserted = false;
ArrayList newEntities = new ArrayList();
foreach (var name in adicionalValues)
{
string query = "select * from " + tableName + " where " + fieldName + "=\"" + name.Trim() + "\"";
var foundEntity = lookupEntity.SqlQuery(query).Cast<object>();
if (foundEntity.Count() == 0)
{
hasInserted = true;
object newEntity = Activator.CreateInstance(insertTpe);
SetPropertyValue(newEntity, fieldName, name.Trim());
lookupEntity.Add(newEntity);
newEntities.Add(newEntity);
}
else
{
var castEntity = foundEntity.First();
List<string> primaryKeys = GetEntityKeyNames(context, insertTpe);
addedValues.Add(GetPropertyValue(castEntity, primaryKeys[0]));
}
}

if (hasInserted)
context.SaveChanges();

foreach (var obj in newEntities)
{
List<string> primaryKeys = GetEntityKeyNames(context, insertTpe);
string value = GetPropertyValue(obj, primaryKeys[0]);
addedValues.Add(value);
}
}

addedValues.AddRange(existingValues);
addedValues = addedValues.Distinct().ToList();
return addedValues;
}


What the code does is check if the information already exists in the database (it's a lookup table) and if it doesnt I insert it. The line where I'm getting the error is this:



if (foundEntity.Count() == 0)


The error doesnt always happen on the same answer. Running the job multiples times and the error occurs on diferent answers. The error i'm getting is the following:



2015-04-08 17:34:16.1177 - Unknown column 'Cisco Systems' in 'where clause'
2015-04-08 17:34:16.1187 - ERROR: StackTrace: at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__64()
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__63()
at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
at System.Data.Entity.Internal.Linq.InternalSet`1.<>c__DisplayClass11.<ExecuteSqlQuery>b__10()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Linq.Enumerable.<CastIterator>d__b1`1.MoveNext()
at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
at LimeSurvey.Utilities.SurveyParser`1.ParseAdicionalValues(XElement question, String entityNamespace, List`1 adicionalValues, List`1 existingValues) in XXX
at LimeSurvey.Utilities.SurveyParser`1.MultipleChoice(Type entityType, XElement question, String questionId, String entityNamespace, String insertMode) in XXX
at LimeSurvey.Utilities.SurveyParser`1.Parse() in XXX
at LimeSurvey.Utilities.Surveys.GetResponses() in XXX





Aucun commentaire:

Enregistrer un commentaire