/// <summary> /// Applies the given value to the given field of the given entries. /// </summary> /// <param name="entities">Entries to edit.</param> /// <param name="fieldName">Name of the field to set.</param> /// <param name="value">Value to set for all entities.</param> /// <returns>Number of entities edited.</returns> private long BulkEditStringField(IEnumerable <SrsEntry> entities, string fieldName, string value) { if (!entities.Any()) { return(0); } DaoConnection connection = null; long result = -1; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); int i = 0; result = 0; while (i < entities.Count()) { List <DaoParameter> parameters = new List <DaoParameter>(); string inStatement = string.Empty; int nMax = Math.Min(entities.Count(), i + BulkBatchCount); for (int n = i; n < nMax; n++) { SrsEntry entry = entities.ElementAt(n); string paramName = "@p" + entry.ID; inStatement += paramName + ","; parameters.Add(new DaoParameter(paramName, entry.ID)); } inStatement = inStatement.TrimEnd(new char[] { ',' }); // Add the "value" parameter. parameters.Add(new DaoParameter("@Value", value)); // Add the "LastUpdateDate" parameter. parameters.Add(new DaoParameter("@LastUpdateDate", DateTime.UtcNow.Ticks)); // Execute the query. result += connection.ExecuteNonQuery("UPDATE " + SqlHelper.Table_SrsEntry + " SET " + fieldName + "=@Value, " + SqlHelper.Field_SrsEntry_LastUpdateDate + "=@LastUpdateDate " + "WHERE " + SqlHelper.Field_SrsEntry_Id + " IN (" + inStatement + ")", parameters.ToArray()); i = nMax; } } finally { if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Removes all the given entities from the database. /// </summary> /// <param name="entities">Entities to delete.</param> /// <returns>Number of items successfuly deleted.</returns> public long BulkDelete(IEnumerable <SrsEntry> entities) { if (!entities.Any()) { return(0); } DaoConnection connection = null; long result = -1; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); int i = 0; result = 0; while (i < entities.Count()) { List <DaoParameter> parameters = new List <DaoParameter>(); string inStatement = string.Empty; int nMax = Math.Min(entities.Count(), i + BulkBatchCount); for (int n = i; n < nMax; n++) { SrsEntry entry = entities.ElementAt(n); string paramName = "@p" + entry.ID; inStatement += paramName + ","; parameters.Add(new DaoParameter(paramName, entry.ID)); } inStatement = inStatement.TrimEnd(new char[] { ',' }); // Execute the query. result += connection.ExecuteNonQuery("DELETE FROM " + SqlHelper.Table_SrsEntry + " WHERE " + SqlHelper.Field_SrsEntry_Id + " IN (" + inStatement + ")", parameters.ToArray()); i = nMax; } } finally { if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Updates the review date for all given entities. /// The review date is assumed already modified in the entity. /// </summary> /// <param name="entities">Entities to update.</param> /// <returns>Number of entities updated.</returns> public long BulkEditReviewDate(IEnumerable <SrsEntry> entities) { if (!entities.Any()) { return(0); } DaoConnection connection = null; long result = -1; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); result = 0; foreach (SrsEntry entry in entities) { // Execute the query. result += connection.ExecuteNonQuery("UPDATE " + SqlHelper.Table_SrsEntry + " SET " + SqlHelper.Field_SrsEntry_NextAnswerDate + "=@Value, " + SqlHelper.Field_SrsEntry_LastUpdateDate + "=@LastUpdateDate WHERE " + SqlHelper.Field_SrsEntry_Id + "=@Id", new DaoParameter("@Value", entry.NextAnswerDate.HasValue ? (object)entry.NextAnswerDate.Value.ToUniversalTime().Ticks : "null"), new DaoParameter("@LastUpdateDate", DateTime.UtcNow.Ticks), new DaoParameter("@Id", entry.ID)); } } finally { if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Removes the entity from the database. /// </summary> /// <param name="entity">Entity to delete.</param> /// <returns>True if the operation was successful. False otherwise.</returns> public bool Delete(SrsEntry entity) { DaoConnection connection = null; bool result = false; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); // Execute the query. result = connection.ExecuteNonQuery("DELETE FROM " + SqlHelper.Table_SrsEntry + " WHERE " + SqlHelper.Field_SrsEntry_Id + "=@Id", new DaoParameter("@Id", entity.ID)) == 1; } finally { if (connection != null) { connection.Dispose(); } } return(result); }
public bool UpdateFrequencyRankOnSingleKanaMatch(string kanaReading, int rank) { DaoConnection connection = null; try { //connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); connection = _connection; long count = (long)connection.QueryScalar( string.Format("SELECT COUNT(1) FROM {0} WHERE {1}=@kanaWriting", SqlHelper.Table_Vocab, SqlHelper.Field_Vocab_KanaWriting), new DaoParameter("@kanaWriting", kanaReading)); if (count == 1) { return(connection.ExecuteNonQuery( string.Format("UPDATE {0} SET {1}={1}+@rank WHERE {2}=@kanaWriting", SqlHelper.Table_Vocab, SqlHelper.Field_Vocab_FrequencyRank, SqlHelper.Field_Vocab_KanaWriting), new DaoParameter("@rank", rank), new DaoParameter("@kanaWriting", kanaReading)) == 1); } } finally { //if (connection != null) //{ // connection.Dispose(); //} } return(false); }
/// <summary> /// Updates the given SRS entry. /// </summary> /// <param name="entity">Entity to update.</param> /// <returns>True if the operation was sucessful. False otherwise.</returns> public bool Update(SrsEntry entity) { DaoConnection connection = null; bool result = false; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); // Create a parameter list and two string builders that will // be used to put the SQL request together. List <DaoParameter> parameters = new List <DaoParameter>(); StringBuilder sqlQueryBuilder = new StringBuilder( "UPDATE " + SqlHelper.Table_SrsEntry + " SET "); // NextAnswerDate sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_NextAnswerDate + "="); if (entity.NextAnswerDate == null) { sqlQueryBuilder.Append("null"); } else { sqlQueryBuilder.Append("@NextAnswerDate"); parameters.Add(new DaoParameter( "@NextAnswerDate", entity.NextAnswerDate.Value.ToUniversalTime().Ticks)); } sqlQueryBuilder.Append(","); // Meanings sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_Meanings + "=@Meanings,"); parameters.Add(new DaoParameter("@Meanings", MultiValueFieldHelper.Trim(entity.Meanings ?? string.Empty))); // Readings sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_Readings + "=@Readings,"); parameters.Add(new DaoParameter("@Readings", MultiValueFieldHelper.Trim(entity.Readings ?? string.Empty))); // CurrentGrade sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_CurrentGrade + "=@CurrentGrade,"); parameters.Add(new DaoParameter("@CurrentGrade", entity.CurrentGrade)); // FailureCount sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_FailureCount + "=@FailureCount,"); parameters.Add(new DaoParameter("@FailureCount", entity.FailureCount)); // SuccessCount sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_SuccessCount + "=@SuccessCount,"); parameters.Add(new DaoParameter("@SuccessCount", entity.SuccessCount)); // SuspensionDate sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_SuspensionDate + "="); if (entity.SuspensionDate == null) { sqlQueryBuilder.Append("null"); } else { sqlQueryBuilder.Append("@SuspensionDate"); parameters.Add(new DaoParameter( "@SuspensionDate", entity.SuspensionDate.Value.ToUniversalTime().Ticks)); } sqlQueryBuilder.Append(","); // AssociatedVocab sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_AssociatedVocab + "=@AssociatedVocab,"); parameters.Add(new DaoParameter( "@AssociatedVocab", entity.AssociatedVocab)); // AssociatedKanji sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_AssociatedKanji + "=@AssociatedKanji,"); parameters.Add(new DaoParameter( "@AssociatedKanji", entity.AssociatedKanji)); // MeaningNote sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_MeaningNote + "=@MeaningNote,"); parameters.Add(new DaoParameter( "@MeaningNote", entity.MeaningNote)); // ReadingNote sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_ReadingNote + "=@ReadingNote,"); parameters.Add(new DaoParameter( "@ReadingNote", entity.ReadingNote)); // ServerId sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_ServerId + "=@ServerId,"); parameters.Add(new DaoParameter( "@ServerId", entity.ServerId)); // IsDeleted sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_IsDeleted + "=@IsDeleted,"); parameters.Add(new DaoParameter( "@IsDeleted", entity.IsDeleted)); // LastUpdateDate sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_LastUpdateDate + "=@LastUpdateDate,"); parameters.Add(new DaoParameter( "@LastUpdateDate", DateTime.UtcNow.Ticks)); // Tags sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_Tags + "=@Tags"); parameters.Add(new DaoParameter( "@Tags", MultiValueFieldHelper.Trim(entity.Tags))); // We are done with the string builders. // Bring the query pieces together. string finalQuery = sqlQueryBuilder.ToString() + " WHERE " + SqlHelper.Field_SrsEntry_Id + "=@Id"; parameters.Add(new DaoParameter("@Id", entity.ID)); // Execute the query. result = connection.ExecuteNonQuery(finalQuery, parameters.ToArray()) == 1; } catch (Exception ex) { LogHelper.GetLogger(this.GetType().Name).Error( "An error occured during SRS item update.", ex); } finally { if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Inserts the given entity in the database. /// Overrides the ID property of the given entity. /// </summary> /// <param name="entity">Entity to insert.</param> public void Add(SrsEntry entity) { DaoConnection connection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); // Create a parameter list and two string builders that will // be used to put the SQL request together. List <DaoParameter> parameters = new List <DaoParameter>(); StringBuilder sqlQueryStart = new StringBuilder( "INSERT INTO " + SqlHelper.Table_SrsEntry + "("); StringBuilder sqlQueryEnd = new StringBuilder( "VALUES("); // CreationDate if (entity.CreationDate != null) { sqlQueryStart.Append(SqlHelper.Field_SrsEntry_CreationDate + ","); sqlQueryEnd.Append("@CreationDate,"); parameters.Add(new DaoParameter( "@CreationDate", entity.CreationDate.Value.ToUniversalTime().Ticks)); } // NextAnswerDate if (entity.NextAnswerDate != null) { sqlQueryStart.Append(SqlHelper.Field_SrsEntry_NextAnswerDate + ","); sqlQueryEnd.Append("@NextAnswerDate,"); parameters.Add(new DaoParameter( "@NextAnswerDate", entity.NextAnswerDate.Value.ToUniversalTime().Ticks)); } // Meanings sqlQueryStart.Append(SqlHelper.Field_SrsEntry_Meanings + ","); sqlQueryEnd.Append("@Meanings,"); parameters.Add(new DaoParameter("@Meanings", MultiValueFieldHelper.Trim(entity.Meanings ?? string.Empty))); // Readings sqlQueryStart.Append(SqlHelper.Field_SrsEntry_Readings + ","); sqlQueryEnd.Append("@Readings,"); parameters.Add(new DaoParameter("@Readings", MultiValueFieldHelper.Trim(entity.Readings ?? string.Empty))); // CurrentGrade sqlQueryStart.Append(SqlHelper.Field_SrsEntry_CurrentGrade + ","); sqlQueryEnd.Append("@CurrentGrade,"); parameters.Add(new DaoParameter("@CurrentGrade", entity.CurrentGrade)); // FailureCount sqlQueryStart.Append(SqlHelper.Field_SrsEntry_FailureCount + ","); sqlQueryEnd.Append("@FailureCount,"); parameters.Add(new DaoParameter("@FailureCount", entity.FailureCount)); // SuccessCount sqlQueryStart.Append(SqlHelper.Field_SrsEntry_SuccessCount + ","); sqlQueryEnd.Append("@SuccessCount,"); parameters.Add(new DaoParameter("@SuccessCount", entity.SuccessCount)); // SuspensionDate if (entity.SuspensionDate.HasValue) { sqlQueryStart.Append(SqlHelper.Field_SrsEntry_SuspensionDate + ","); sqlQueryEnd.Append("@SuspensionDate,"); parameters.Add(new DaoParameter("@SuspensionDate", entity.SuspensionDate.Value.ToUniversalTime().Ticks)); } // AssociatedVocab if (!string.IsNullOrWhiteSpace(entity.AssociatedVocab)) { sqlQueryStart.Append(SqlHelper.Field_SrsEntry_AssociatedVocab + ","); sqlQueryEnd.Append("@AssociatedVocab,"); parameters.Add(new DaoParameter( "@AssociatedVocab", entity.AssociatedVocab)); } // AssociatedKanji if (!string.IsNullOrWhiteSpace(entity.AssociatedKanji)) { sqlQueryStart.Append(SqlHelper.Field_SrsEntry_AssociatedKanji + ","); sqlQueryEnd.Append("@AssociatedKanji,"); parameters.Add(new DaoParameter( "@AssociatedKanji", entity.AssociatedKanji)); } // MeaningNote if (!string.IsNullOrWhiteSpace(entity.MeaningNote)) { sqlQueryStart.Append(SqlHelper.Field_SrsEntry_MeaningNote + ","); sqlQueryEnd.Append("@MeaningNote,"); parameters.Add(new DaoParameter( "@MeaningNote", entity.MeaningNote)); } // ReadingNote if (!string.IsNullOrWhiteSpace(entity.ReadingNote)) { sqlQueryStart.Append(SqlHelper.Field_SrsEntry_ReadingNote + ","); sqlQueryEnd.Append("@ReadingNote,"); parameters.Add(new DaoParameter( "@ReadingNote", entity.ReadingNote)); } // Tags if (!string.IsNullOrWhiteSpace(entity.Tags)) { sqlQueryStart.Append(SqlHelper.Field_SrsEntry_Tags + ","); sqlQueryEnd.Append("@Tags,"); parameters.Add(new DaoParameter( "@Tags", MultiValueFieldHelper.Trim(entity.Tags))); } // LastUpdateDate sqlQueryStart.Append(SqlHelper.Field_SrsEntry_LastUpdateDate + ","); sqlQueryEnd.Append("@LastUpdateDate,"); parameters.Add(new DaoParameter( "@LastUpdateDate", DateTime.UtcNow.Ticks)); // ServerId if (entity.ServerId.HasValue) { sqlQueryStart.Append(SqlHelper.Field_SrsEntry_ServerId + ","); sqlQueryEnd.Append("@ServerId,"); parameters.Add(new DaoParameter( "@ServerId", entity.ServerId)); } // IsDeleted (because why not?) sqlQueryStart.Append(SqlHelper.Field_SrsEntry_IsDeleted + ","); sqlQueryEnd.Append("@IsDeleted,"); parameters.Add(new DaoParameter("@IsDeleted", entity.IsDeleted)); // We are done with the string builders. // Bring the query pieces together. string finalQuery = sqlQueryStart.ToString().TrimEnd(new char[] { ',' }) + ") " + sqlQueryEnd.ToString().TrimEnd(new char[] { ',' }) + ")"; // Execute the query. if (connection.ExecuteNonQuery(finalQuery, parameters.ToArray()) == 1) { // If the row was inserted, put the insert ID in the entity. entity.ID = connection.GetLastInsertId(); } } finally { if (connection != null) { connection.Dispose(); } } }