/// <summary> /// Creates a new Request in the database, with corresponding /// QuestionResponses and References /// </summary> /// <param name="reqContent"> /// RequestContent containing /// QuestionResponseContents, ReferenceContents and Keywords /// </param> public long create(RequestContent reqContent) { using (var trans = new TransactionScope()) { // Insert new Request entity Request req = createRequestEntity(reqContent); _db.Requests.InsertOnSubmit(req); _db.SubmitChanges(); // For each QuestionResponse associated with the request foreach (QuestionResponseContent qrContent in reqContent.questionResponseList) { // Set the new RequestID qrContent.requestID = req.RequestID; // Insert new QuestionResponse entity QuestionResponse qr = createQuestionResponseEntity(qrContent); _db.QuestionResponses.InsertOnSubmit(qr); _db.SubmitChanges(); // For each Keyword associated with the // QuestionResponse foreach (String kw in qrContent.keywords) { int kwId = getKeywordIdAndActivate(kw); // Check if already associated with Question var kq = (from kqs in _db.KeywordQuestions where kqs.KeywordID == kwId && kqs.RequestID == req.RequestID && kqs.QuestionResponseID == qr.QuestionResponseID select kqs) .SingleOrDefault(); if (kq != null) { continue; } kq = new KeywordQuestion { KeywordID = kwId, RequestID = req.RequestID, QuestionResponseID = qr.QuestionResponseID }; _db.KeywordQuestions.InsertOnSubmit(kq); _db.SubmitChanges(); } // For each Reference associated with the // QuestionResponse foreach (ReferenceContent refContent in qrContent.referenceList) { // Set the new RequestID and // QuestionResponseID refContent.requestID = req.RequestID; refContent.questionResponseID = qr.QuestionResponseID; // Insert new Reference entity _db.References.InsertOnSubmit( createReferenceEntity(refContent)); _db.SubmitChanges(); } } trans.Complete(); return req.RequestID; } }
/// <summary> /// Edit a request properties, question-response pairs and references /// for a given request ID. /// </summary> /// <param name="reqContent"> /// Request content to modify in the database. /// </param> public void edit(RequestContent reqContent) { if (reqContent.requestID == -1) { throw new Exception("Invalid request."); } using (var trans = new TransactionScope()) { // Check if the request exists int exists = (from reqs in _db.Requests where reqs.RequestID == reqContent.requestID select reqs.RequestID).Count(); if (exists < 1) { throw new Exception("Request does not exist."); } // Update the Request entity Request req = createRequestEntity(reqContent); _db.SubmitChanges(); // Retrieve the list of current QuestionResponseIDs for // the request List<long> currQrIds = getQuestionResponseIds(reqContent.requestID); // Check all QuestionResponses being updated against // those already in the database foreach (QuestionResponseContent qrContent in reqContent.questionResponseList) { qrContent.requestID = reqContent.requestID; if (currQrIds.Remove(qrContent.questionResponseID)) { // QuestionResponse already exists in the database // Update QuestionResponse, References and Keywords QuestionResponse qr = createQuestionResponseEntity(qrContent); _db.SubmitChanges(); // Retrieve all Keyword IDs for the // given QuestionResponse List<int> currKwIds = getKeywordIds( reqContent.requestID, qrContent.questionResponseID); // Check all Keywords for the QuestionResponse foreach (String kw in qrContent.keywords) { int kwId = getKeywordIdAndActivate(kw); if (!currKwIds.Remove(kwId)) { // Set Keyword for QuestionResponse // Check if already associated with Question var kq = (from kqs in _db.KeywordQuestions where kqs.KeywordID == kwId && kqs.RequestID == req.RequestID && kqs.QuestionResponseID == qr.QuestionResponseID select kqs) .SingleOrDefault(); if (kq != null) { continue; } kq = new KeywordQuestion { KeywordID = kwId, RequestID = reqContent.requestID, QuestionResponseID = qrContent.questionResponseID }; _db.KeywordQuestions.InsertOnSubmit(kq); _db.SubmitChanges(); } } // Remaining Keywords not associated anymore foreach (int kwId in currKwIds) { KeywordQuestion kq = getKeywordQuestionEntity( kwId, reqContent.requestID, qrContent.questionResponseID); _db.KeywordQuestions.DeleteOnSubmit(kq); _db.SubmitChanges(); } // Retrieve all Reference IDs for the given // QuestionResponse List<long> currRefIds = getReferenceIds( reqContent.requestID, qrContent.questionResponseID); // Check all References for the QuestionResponse foreach (ReferenceContent refContent in qrContent.referenceList) { refContent.requestID = req.RequestID; refContent.questionResponseID = qr.QuestionResponseID; Reference r = createReferenceEntity(refContent); if (refContent.referenceID == -1) { // Insert new Reference into database _db.References.InsertOnSubmit(r); } else { currRefIds.Remove(refContent.referenceID); } _db.SubmitChanges(); } // Remaining References do not exist anymore foreach (long refId in currRefIds) { Reference r = getReferenceEntity( refId, reqContent.requestID, qrContent.questionResponseID); _db.References.DeleteOnSubmit(r); _db.SubmitChanges(); } } else { // Add new QuestionResponse QuestionResponse qr = createQuestionResponseEntity(qrContent); _db.QuestionResponses.InsertOnSubmit(qr); _db.SubmitChanges(); qrContent.questionResponseID = qr.QuestionResponseID; // Add new Keywords to QuestionResponse foreach (String kw in qrContent.keywords) { int kwId = getKeywordIdAndActivate(kw); // Check if already associated with Question var kq = (from kqs in _db.KeywordQuestions where kqs.KeywordID == kwId && kqs.RequestID == req.RequestID && kqs.QuestionResponseID == qr.QuestionResponseID select kqs) .SingleOrDefault(); if (kq != null) { continue; } // Set Keyword for QuestionResponse kq = new KeywordQuestion { KeywordID = kwId, RequestID = req.RequestID, QuestionResponseID = qr.QuestionResponseID }; _db.KeywordQuestions.InsertOnSubmit(kq); _db.SubmitChanges(); } // Add all References for the QuestionResponse foreach (ReferenceContent refContent in qrContent.referenceList) { refContent.requestID = req.RequestID; refContent.questionResponseID = qr.QuestionResponseID; Reference r = createReferenceEntity(refContent); _db.References.InsertOnSubmit(r); _db.SubmitChanges(); } } } // Remove QuestionResponses that no longer exist foreach (long qrId in currQrIds) { // Delete all Keyword associations List<int> currKwIds = getKeywordIds( reqContent.requestID, qrId); foreach (int kwId in currKwIds) { KeywordQuestion kq = getKeywordQuestionEntity( kwId, reqContent.requestID, qrId); _db.KeywordQuestions.DeleteOnSubmit(kq); _db.SubmitChanges(); } // Delete all References List<long> currRefIds = getReferenceIds( reqContent.requestID, qrId); foreach (long refId in currRefIds) { Reference r = getReferenceEntity( refId, reqContent.requestID, qrId); _db.References.DeleteOnSubmit(r); _db.SubmitChanges(); } // Delete QuestionResponse QuestionResponse qr = getQuestionResponseEntity( reqContent.requestID, qrId); _db.QuestionResponses.DeleteOnSubmit(qr); _db.SubmitChanges(); } trans.Complete(); } }