/// <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();
}
}
}