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
The problem is the way ou build your query here:
string query = "select * from " + tableName + " where " + fieldName + "=\"" + name.Trim() + "\"";
Though MySQL support string values in double quotes it is best to use single quotes as you might have unintended side effect. You might also need to properly quote any field values before selecting for it if 'name' may contain a quote.