public IEnumerable <VocabEntity> GetVocabByReadings(string kanjiReading, string kanaReading)
{
DaoConnection connection = null;
try
{
//connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
connection = _connection;
VocabBuilder builder = new VocabBuilder();
if (kanjiReading == kanaReading)
{
IEnumerable <NameValueCollection> vocabs = connection.Query(
string.Format("SELECT * FROM {0} WHERE {1}=@kanaWriting",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_KanaWriting),
new DaoParameter("@kanaWriting", kanaReading));
if (vocabs.Count() == 1)
{
var result = builder.BuildEntity(vocabs.First(), null);
IncludeMeanings(connection, result);
yield return(result);
yield break;
}
}
IEnumerable <NameValueCollection> fullMatch = connection.Query(
string.Format("SELECT * FROM {0} WHERE {1}=@kanaWriting AND {2}=@kanjiWriting",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_KanaWriting,
SqlHelper.Field_Vocab_KanjiWriting),
new DaoParameter("@kanaWriting", kanaReading), new DaoParameter("@kanjiWriting", kanjiReading));
foreach (NameValueCollection match in fullMatch)
{
var result = builder.BuildEntity(match, null);
IncludeMeanings(connection, result);
yield return(result);
}
}
finally
{
//if (connection != null)
//{
// connection.Dispose();
//}
}
}
/// <summary>
/// Retrieves and returns the collection of vocab matching the
/// given filters.
/// </summary>
/// <param name="kanji">Kanji filter. Only vocab containing this
/// kanji will be filtered in.</param>
/// <param name="readingFilter">Reading filter. Only vocab containing
/// this string in their kana or kanji reading will be filtered in.</param>
/// <param name="meaningFilter">Meaning filter. Only vocab containing
/// this string as part of at least one of their meaning entries will
/// be filtered in.</param>
/// <param name="categoryFilter">If not null, this category is used as the filter.</param>
/// <param name="jlptLevel">The JLPT level to filter
/// (1-5, where a lower value means it is not covered on the JLPT
/// and a higher value means that this filter will be ignored).</param>
/// <param name="wkLevel">The WaniKani level to filter
/// (1-60, where a higher value means it is not taught by WaniKani
/// and a lower value means that this filter will be ignored).</param>
/// <param name="isCommonFirst">Indicates if common vocab should be
/// presented first. If false, results are sorted only by the length
/// of their writing (asc or desc depending on the parameter)</param>
/// <param name="isShortWritingFirst">Indicates if results should
/// be sorted by ascending or descending writing length.
/// If True, short readings come first. If False, long readings
/// come first.</param>
/// <returns>Vocab entities matching the filters.</returns>
public IEnumerable <VocabEntity> GetFilteredVocab(KanjiEntity kanji,
string readingFilter, string meaningFilter, VocabCategory categoryFilter,
int jlptLevel, int wkLevel,
bool isCommonFirst, bool isShortWritingFirst)
{
List <DaoParameter> parameters = new List <DaoParameter>();
string sqlFilterClauses = BuildVocabFilterClauses(parameters, kanji,
readingFilter, meaningFilter, categoryFilter, jlptLevel, wkLevel);
string sortClause = "ORDER BY ";
if (isCommonFirst)
{
sortClause += string.Format("v.{0} DESC,", SqlHelper.Field_Vocab_IsCommon);
}
sortClause += string.Format("length(v.{0}) {1}",
SqlHelper.Field_Vocab_KanaWriting,
(isShortWritingFirst ? "ASC" : "DESC"));
DaoConnection connection = null;
DaoConnection srsConnection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase);
srsConnection.OpenAsync();
IEnumerable <NameValueCollection> vocabs = connection.Query(
string.Format("SELECT DISTINCT v.* FROM {0} v {1}{2}",
SqlHelper.Table_Vocab,
sqlFilterClauses,
sortClause),
parameters.ToArray());
VocabBuilder vocabBuilder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in vocabs)
{
VocabEntity vocab = vocabBuilder.BuildEntity(nvcVocab, null);
IncludeCategories(connection, vocab);
IncludeMeanings(connection, vocab);
IncludeKanji(connection, srsConnection, vocab);
IncludeSrsEntries(srsConnection, vocab);
IncludeVariants(connection, vocab);
yield return(vocab);
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Gets the first vocab that exactly matches the given reading.
/// </summary>
/// <param name="reading">Reading to match.</param>
/// <returns>First matching vocab, or null if not found.</returns>
public IEnumerable <VocabEntity> GetMatchingVocab(string reading)
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable <NameValueCollection> vocabs = connection.Query(
string.Format("SELECT v.* FROM {0} v WHERE v.{1}=@v ORDER BY v.{2} DESC",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_KanjiWriting,
SqlHelper.Field_Vocab_IsCommon),
new DaoParameter("@v", reading));
if (vocabs.Any())
{
VocabBuilder builder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in vocabs)
{
VocabEntity result = builder.BuildEntity(nvcVocab, null);
IncludeMeanings(connection, result);
yield return(result);
}
}
else
{
vocabs = connection.Query(
string.Format("SELECT v.* FROM {0} v WHERE v.{1}=@v ORDER BY v.{2} DESC",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_KanaWriting,
SqlHelper.Field_Vocab_IsCommon),
new DaoParameter("@v", reading));
VocabBuilder builder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in vocabs)
{
VocabEntity result = builder.BuildEntity(nvcVocab, null);
IncludeMeanings(connection, result);
yield return(result);
}
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Includes the vocab variants in the entity.
/// </summary>
private void IncludeVariants(DaoConnection connection, VocabEntity vocab)
{
IEnumerable <NameValueCollection> results = connection.Query(
string.Format("SELECT * FROM {0} WHERE {1}=@gid AND {2}!=@id",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_GroupId,
SqlHelper.Field_Vocab_Id),
new DaoParameter("@gid", vocab.GroupId),
new DaoParameter("@id", vocab.ID));
VocabBuilder builder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in results)
{
vocab.Variants.Add(builder.BuildEntity(nvcVocab, null));
}
}
/// <summary>
/// Retrieves and returns the complete VocabEntity matching the given ID.
/// </summary>
/// <param name="id">Id to search.</param>
/// <returns>The VocabEntity that matches the given ID, or null if not found.</returns>
public VocabEntity GetVocabById(long id)
{
VocabEntity result = null;
DaoConnection connection = null;
DaoConnection srsConnection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase);
srsConnection.OpenAsync();
IEnumerable <NameValueCollection> vocabs = connection.Query(
string.Format("SELECT * FROM {0} WHERE {1}=@id",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_Id),
new DaoParameter("@id", id));
if (vocabs.Any())
{
VocabBuilder builder = new VocabBuilder();
VocabEntity vocab = builder.BuildEntity(vocabs.First(), null);
IncludeCategories(connection, vocab);
IncludeMeanings(connection, vocab);
IncludeKanji(connection, srsConnection, vocab);
IncludeSrsEntries(srsConnection, vocab);
IncludeVariants(connection, vocab);
result = vocab;
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
public IEnumerable<VocabEntity> GetAllVocab()
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable<NameValueCollection> vocabs = connection.Query(
"SELECT * FROM " + SqlHelper.Table_Vocab);
VocabBuilder builder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in vocabs)
{
yield return builder.BuildEntity(nvcVocab, null);
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
public VocabEntity GetSingleVocabByKanaReading(string kanaReading)
{
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)
{
IEnumerable <NameValueCollection> vocabs = connection.Query(
string.Format("SELECT * FROM {0} WHERE {1}=@kanaWriting",
SqlHelper.Table_Vocab,
SqlHelper.Field_Vocab_KanaWriting),
new DaoParameter("@kanaWriting", kanaReading));
VocabBuilder builder = new VocabBuilder();
return(builder.BuildEntity(vocabs.First(), null));
}
}
finally
{
//if (connection != null)
//{
// connection.Dispose();
//}
}
return(null);
}
public IEnumerable <VocabEntity> GetAllVocab()
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable <NameValueCollection> vocabs = connection.Query(
string.Format("SELECT * FROM {0}", SqlHelper.Table_Vocab));
VocabBuilder builder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in vocabs)
{
yield return(builder.BuildEntity(nvcVocab, null));
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
public IEnumerable<VocabEntity> GetVocabByReadings(string kanjiReading, string kanaReading)
{
DaoConnection connection = null;
try
{
//connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
connection = _connection;
string requestString = string.Empty;
VocabBuilder builder = new VocabBuilder();
if (kanjiReading == kanaReading)
{
IEnumerable<NameValueCollection> vocabs = connection.Query(
"SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_KanaWriting + "=@kanaWriting",
new DaoParameter("@kanaWriting", kanaReading));
if (vocabs.Count() == 1)
{
yield return builder.BuildEntity(vocabs.First(), null);
yield break;
}
}
IEnumerable<NameValueCollection> fullMatch = connection.Query(
"SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_KanaWriting + "=@kanaWriting"
+ " AND " + SqlHelper.Field_Vocab_KanjiWriting + "=@kanjiWriting",
new DaoParameter("@kanaWriting", kanaReading), new DaoParameter("@kanjiWriting", kanjiReading));
foreach (NameValueCollection match in fullMatch)
{
yield return builder.BuildEntity(match, null);
}
}
finally
{
//if (connection != null)
//{
// connection.Dispose();
//}
}
}
/// <summary>
/// Includes the vocab variants in the entity.
/// </summary>
private void IncludeVariants(DaoConnection connection, VocabEntity vocab)
{
IEnumerable<NameValueCollection> results = connection.Query(
"SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE "
+ SqlHelper.Field_Vocab_GroupId + "=@gid AND "
+ SqlHelper.Field_Vocab_Id + "!=@id",
new DaoParameter("@gid", vocab.GroupId),
new DaoParameter("@id", vocab.ID));
VocabBuilder builder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in results)
{
vocab.Variants.Add(builder.BuildEntity(nvcVocab, null));
}
}
/// <summary>
/// Retrieves and returns the collection of vocab matching the
/// given filters.
/// </summary>
/// <param name="kanji">Kanji filter. Only vocab containing this
/// kanji will be filtered in.</param>
/// <param name="readingFilter">Reading filter. Only vocab containing
/// this string in their kana or kanji reading will be filtered in.</param>
/// <param name="meaningFilter">Meaning filter. Only vocab containing
/// this string as part of at least one of their meaning entries will
/// be filtered in.</param>
/// <param name="isCommonFirst">Indicates if common vocab should be
/// presented first. If false, results are sorted only by the length
/// of their writing (asc or desc depending on the parameter)</param>
/// <param name="isShortWritingFirst">Indicates if results should
/// be sorted by ascending or descending writing length.
/// If True, short readings come first. If False, long readings
/// come first.</param>
/// <returns>Vocab entities matching the filters.</returns>
public IEnumerable<VocabEntity> GetFilteredVocab(KanjiEntity kanji,
string readingFilter, string meaningFilter, bool isCommonFirst,
bool isShortWritingFirst)
{
List<DaoParameter> parameters = new List<DaoParameter>();
string sqlFilterClauses = BuildVocabFilterClauses(parameters, kanji,
readingFilter, meaningFilter);
string sortClause = "ORDER BY ";
if (isCommonFirst)
{
sortClause += "v." + SqlHelper.Field_Vocab_IsCommon + " DESC,";
}
sortClause += "length(v." + SqlHelper.Field_Vocab_KanaWriting + ") "
+ (isShortWritingFirst ? "ASC" : "DESC");
DaoConnection connection = null;
DaoConnection srsConnection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase);
srsConnection.OpenAsync();
IEnumerable<NameValueCollection> vocabs = connection.Query(
"SELECT DISTINCT v.* FROM " + SqlHelper.Table_Vocab + " v "
+ sqlFilterClauses
+ sortClause,
parameters.ToArray());
VocabBuilder vocabBuilder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in vocabs)
{
VocabEntity vocab = vocabBuilder.BuildEntity(nvcVocab, null);
IncludeCategories(connection, vocab);
IncludeMeanings(connection, vocab);
IncludeKanji(connection, srsConnection, vocab);
IncludeSrsEntries(srsConnection, vocab);
IncludeVariants(connection, vocab);
yield return vocab;
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Retrieves and returns the complete VocabEntity matching the given ID.
/// </summary>
/// <param name="id">Id to search.</param>
/// <returns>The VocabEntity that matches the given ID, or null if not found.</returns>
public VocabEntity GetVocabById(long id)
{
VocabEntity result = null;
DaoConnection connection = null;
DaoConnection srsConnection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase);
srsConnection.OpenAsync();
IEnumerable<NameValueCollection> vocabs = connection.Query(
"SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE "
+ SqlHelper.Field_Vocab_Id + "=@id",
new DaoParameter("@id", id));
if (vocabs.Any())
{
VocabBuilder builder = new VocabBuilder();
VocabEntity vocab = builder.BuildEntity(vocabs.First(), null);
IncludeCategories(connection, vocab);
IncludeMeanings(connection, vocab);
IncludeKanji(connection, srsConnection, vocab);
IncludeSrsEntries(srsConnection, vocab);
IncludeVariants(connection, vocab);
result = vocab;
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return result;
}
/// <summary>
/// Gets the first vocab that exactly matches the given reading.
/// </summary>
/// <param name="reading">Reading to match.</param>
/// <returns>First matching vocab, or null if not found.</returns>
public IEnumerable<VocabEntity> GetMatchingVocab(string reading)
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable<NameValueCollection> vocabs = connection.Query(
"SELECT v.* FROM " + SqlHelper.Table_Vocab + " v "
+ "WHERE v." + SqlHelper.Field_Vocab_KanjiWriting + "=@v "
+ "ORDER BY v." + SqlHelper.Field_Vocab_IsCommon + " DESC",
new DaoParameter("@v", reading));
if (vocabs.Any())
{
VocabBuilder builder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in vocabs)
{
VocabEntity result = builder.BuildEntity(nvcVocab, null);
IncludeMeanings(connection, result);
yield return result;
}
}
else
{
vocabs = connection.Query(
"SELECT v.* FROM " + SqlHelper.Table_Vocab + " v "
+ "WHERE v." + SqlHelper.Field_Vocab_KanaWriting + "=@v "
+ "ORDER BY v." + SqlHelper.Field_Vocab_IsCommon + " DESC",
new DaoParameter("@v", reading));
VocabBuilder builder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in vocabs)
{
VocabEntity result = builder.BuildEntity(nvcVocab, null);
IncludeMeanings(connection, result);
yield return result;
}
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
public VocabEntity GetSingleVocabByKanaReading(string kanaReading)
{
DaoConnection connection = null;
try
{
//connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
connection = _connection;
string requestString = string.Empty;
long count = (long)connection.QueryScalar(
"SELECT COUNT(1) FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_KanaWriting + "=@kanaWriting",
new DaoParameter("@kanaWriting", kanaReading));
if (count == 1)
{
IEnumerable<NameValueCollection> vocabs = connection.Query(
"SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_KanaWriting + "=@kanaWriting",
new DaoParameter("@kanaWriting", kanaReading));
VocabBuilder builder = new VocabBuilder();
return builder.BuildEntity(vocabs.First(), null);
}
}
finally
{
//if (connection != null)
//{
// connection.Dispose();
//}
}
return null;
}