private async Task SearchQuestionByTitleAsync(string title)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(dbName);
AsyncTableQuery <QuestionsWithAnswers> query = conn.Table <QuestionsWithAnswers>().Where(x => x.Question.Contains(title));
List <QuestionsWithAnswers> result = await query.ToListAsync();
foreach (var question in result)
{
// ...
}
var allQuestions = await conn.QueryAsync <QuestionsWithAnswers>("SELECT * FROM Articles");
foreach (var question in allQuestions)
{
// ...
}
var otherQuestions = await conn.QueryAsync <QuestionsWithAnswers>(
"SELECT Content FROM Articles WHERE Title = ?", new object[] { "Hackers, Creed" });
foreach (var question in otherQuestions)
{
// ...
}
}
public void TestAsyncTableQueryTake()
{
SQLiteAsyncConnection conn = GetConnection();
conn.CreateTableAsync <Customer>().Wait();
conn.ExecuteAsync("delete from customer").Wait();
// create...
for (int index = 0; index < 10; index++)
{
Customer customer = CreateCustomer();
customer.FirstName = index.ToString();
conn.InsertAsync(customer).Wait();
}
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>().OrderBy(v => v.FirstName).Take(1);
Task <List <Customer> > task = query.ToListAsync();
task.Wait();
List <Customer> items = task.Result;
// check...
Assert.AreEqual(1, items.Count);
Assert.AreEqual("0", items[0].FirstName);
}
public async Task updateAsync(string num)
{
if (!File.Exists(dbPath))
{
createDatabase(dbPath);
}
SQLiteAsyncConnection con = new SQLiteAsyncConnection(dbPath);
var Table = new SQLiteConnection(dbPath).GetTableInfo(TableName);
if (Table.Count == 0)
{
sqliteConn.CreateTable <InfoTable>();
}
AsyncTableQuery <InfoTable> Infos = con.Table <InfoTable>();
List <InfoTable> list = await Infos.ToListAsync();
foreach (var q in list)
{
if (q.EmsNum == num && q.state == "未同步")
{
q.state = "已同步";
}
}
await con.UpdateAsync(list);
}
private async Task SearchArticleByTitleAsync(string title)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(dbName);
AsyncTableQuery <Article> query = conn.Table <Article>().Where(x => x.Title.Contains(title));
List <Article> result = await query.ToListAsync();
foreach (var article in result)
{
// ...
}
var allArticles = await conn.QueryAsync <Article>("SELECT * FROM Articles");
foreach (var article in allArticles)
{
// ...
}
var otherArticles = await conn.QueryAsync <Article>(
"SELECT Content FROM Articles WHERE Title = ?", new object[] { "Hackers, Creed" });
foreach (var article in otherArticles)
{
// ...
}
}
public async Task TestAsyncTableThenByDescending()
{
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync <Customer>();
await conn.ExecuteAsync("delete from customer");
// create...
for (int index = 0; index < 10; index++)
{
await conn.InsertAsync(CreateCustomer());
}
var preceedingFirstNameCustomer = CreateCustomer();
preceedingFirstNameCustomer.FirstName = "a" + preceedingFirstNameCustomer.FirstName;
await conn.InsertAsync(preceedingFirstNameCustomer);
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>().OrderBy(v => v.FirstName).ThenByDescending(v => v.Email);
var items = await query.ToListAsync();
// check...
var list = (await conn.Table <Customer>().ToListAsync()).OrderBy(v => v.FirstName).ThenByDescending(v => v.Email).ToList();
for (var i = 0; i < list.Count; i++)
{
Assert.AreEqual(list[i].Email, items[i].Email);
}
}
public async Task Init()
{
db = new DB(DB.GetDBPath());
await db.Init();
AsyncTableQuery <User> atqu = db.Table <User>();
List <User> lUsers = await atqu.ToListAsync();
ocUsers = new ObservableCollection <User>(lUsers);
}
public async Task <IEnumerable <Mushroom> > GetMushroomsAsync(IEnumerable <int> ids)
{
AsyncTableQuery <Mushroom> result = _database.Table <Mushroom>();
if (ids != null && ids.Any())
{
result = result.Where(m => ids.Contains(m.Id));
}
return(await result.ToListAsync());
}
/// <summary>
/// RetrieveLog wrapper with a return length limit
/// </summary>
/// <param name="lim">Limit number of returned rows</param>
/// <returns>Returns AppEvents up to the specified limit, or no limit if <paramref name="lim"/> is <see langword="null"/></returns>
public static async Task <List <AppEvent> > RetrieveLogAsync(int?lim)
{
if (conn == null)
{
conn = new SQLiteAsyncConnection(path);
}
await conn.CreateTableAsync <AppEvent>();
AsyncTableQuery <AppEvent> query = (lim == null ? conn.Table <AppEvent>() : conn.Table <AppEvent>().Where(a => a.Id < lim));
return(await query.ToListAsync());
}
public async Task <List <T> > Get <TValue>(Expression <Func <T, bool> > predicate = null, Expression <Func <T, TValue> > orderBy = null)
{
AsyncTableQuery <T> query = db.Table <T>();
if (predicate != null)
{
query = query.Where(predicate);
}
if (orderBy != null)
{
query = query.OrderBy <TValue>(orderBy);
}
return(await query.ToListAsync());
}
public async Task TestAsyncTableQueryWhereOperation()
{
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync <Customer>();
// create...
Customer customer = CreateCustomer();
await conn.InsertAsync(customer);
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>();
var items = await query.ToListAsync();
// check...
Customer loaded = items.Where(v => v.Id == customer.Id).First();
Assert.AreEqual(customer.Email, loaded.Email);
}
public async Task TestAsyncTableOrderByDescending()
{
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync <Customer>();
await conn.ExecuteAsync("delete from customer");
// create...
for (int index = 0; index < 10; index++)
{
await conn.InsertAsync(CreateCustomer());
}
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>().OrderByDescending(v => v.Email);
var items = await query.ToListAsync();
// check...
Assert.AreEqual(1, string.Compare(items[0].Email, items[9].Email));
}
private async Task <IList <T> > GetManyReady(Expression <Func <T, bool> > filter = null,
Func <AsyncTableQuery <T>, AsyncTableQuery <T> > order = null,
int skip = 0,
int take = 0,
bool includeDeleted = false)
{
AsyncTableQuery <T> tableQuery = _mainContext.GetCurrentContext().Table <T>();
if (!includeDeleted)
{
tableQuery = tableQuery.Where(q => q.IsDeleted.Equals(includeDeleted));
}
if (filter != null)
{
tableQuery = tableQuery.Where(filter);
}
if (order != null)
{
tableQuery = order(tableQuery);
}
if (order == null)
{
tableQuery = tableQuery.OrderByDescending(b => b.CreatedDate);
}
if (skip > 0)
{
tableQuery = tableQuery.Skip(skip);
}
if (take > 0)
{
tableQuery = tableQuery.Take(take);
}
return(await tableQuery.ToListAsync());
}
public void TestAsyncTableQueryWhereOperation()
{
SQLiteAsyncConnection conn = GetConnection();
conn.CreateTableAsync <Customer>().Wait();
// create...
Customer customer = CreateCustomer();
conn.InsertAsync(customer).Wait();
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>();
Task <List <Customer> > task = query.ToListAsync();
task.Wait();
List <Customer> items = task.Result;
// check...
Customer loaded = items.Where(v => v.Id == customer.Id).First();
Assert.AreEqual(customer.Email, loaded.Email);
}
public void TestAsyncTableOrderByDescending()
{
SQLiteAsyncConnection conn = GetConnection();
conn.CreateTableAsync <Customer>().Wait();
conn.ExecuteAsync("delete from customer").Wait();
// create...
for (int index = 0; index < 10; index++)
{
conn.InsertAsync(CreateCustomer()).Wait();
}
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>().OrderByDescending(v => v.Email);
Task <List <Customer> > task = query.ToListAsync();
task.Wait();
List <Customer> items = task.Result;
// check...
Assert.AreEqual(1, string.Compare(items[0].Email, items[9].Email));
}
public async Task TestAsyncTableQueryTake()
{
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync <Customer>();
await conn.ExecuteAsync("delete from customer");
// create...
for (int index = 0; index < 10; index++)
{
Customer customer = CreateCustomer();
customer.FirstName = index.ToString();
await conn.InsertAsync(customer);
}
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>().OrderBy(v => v.FirstName).Take(1);
var items = await query.ToListAsync();
// check...
Assert.AreEqual(1, items.Count);
Assert.AreEqual("0", items[0].FirstName);
}
public void TestTableAsync()
{
// connect...
SQLiteAsyncConnection conn = GetConnection();
conn.CreateTableAsync <Customer>().Wait();
conn.ExecuteAsync("delete from customer").Wait();
// insert some...
var customers = new List <Customer>();
for (int index = 0; index < 5; index++)
{
var customer = new Customer();
customer.FirstName = "foo";
customer.LastName = "bar";
customer.Email = Guid.NewGuid().ToString();
// insert...
conn.InsertAsync(customer).Wait();
// add...
customers.Add(customer);
}
// run the table operation...
AsyncTableQuery <Customer> query = conn.Table <Customer>();
List <Customer> loaded = query.ToListAsync().Result;
// check that we got them all back...
Assert.AreEqual(5, loaded.Count);
Assert.IsNotNull(loaded.Where(v => v.Id == customers[0].Id));
Assert.IsNotNull(loaded.Where(v => v.Id == customers[1].Id));
Assert.IsNotNull(loaded.Where(v => v.Id == customers[2].Id));
Assert.IsNotNull(loaded.Where(v => v.Id == customers[3].Id));
Assert.IsNotNull(loaded.Where(v => v.Id == customers[4].Id));
}
private static async Task <GroupContainer> FromQuery(AsyncTableQuery <TimeEntryData> query)
{
var groups = new GroupContainer();
try {
var entries = await query.ToListAsync().ConfigureAwait(false);
// Find unique time entries and add them to the list
foreach (var entry in entries)
{
if (groups.Contains(entry))
{
continue;
}
groups.Add(entry);
}
groups.Sort();
} catch (Exception exc) {
var log = ServiceContainer.Resolve <ILogger> ();
log.Error(Tag, exc, "Failed to compose recent time entries");
}
return(groups);
}
public async Task <List <T> > Select()
{
AsyncTableQuery <T> tb = database.Table <T>();
return(await tb.ToListAsync());
}
public async Task <List <TEntity> > GetAll() => await _entities.ToListAsync();
public Task <List <T> > GetItemsAsync()
{
AsyncTableQuery <T> AsQueryable = database.Table <T>();
return(AsQueryable.ToListAsync());
}
public async Task <bool> UpdateHistoryList(bool Clean)
{
bool ret = false;
try
{
Stopwatch sw = Stopwatch.StartNew();
if (!this.IsSQLiteDBConnected())
{
await this.CreateConnectionAsync();
}
AsyncTableQuery <History> query = sqlite_conn.Table <History>();
List <History> CurList = await query.ToListAsync();
int added = 0;
int removed = 0;
bool isnew = (this.HistoryDic.Count == 0);
//Dont block UI, but wait for the thread to finish
await Task.Run(async() =>
{
Dictionary <string, String> tmpdic = new Dictionary <string, String>();
//lets try to keep the original objects in memory and not create them new
foreach (History hist in CurList)
{
if (!isnew)
{
//add missing
if (this.HistoryDic.TryAdd(hist.Filename.ToLower(), hist))
{
added++;
}
tmpdic.Add(hist.Filename.ToLower(), hist.Filename);
}
else
{
this.HistoryDic.TryAdd(hist.Filename.ToLower(), hist);
added++;
}
}
if (!isnew)
{
//subtract
foreach (History hist in this.HistoryDic.Values)
{
if (!tmpdic.ContainsKey(hist.Filename.ToLower()))
{
History th;
this.HistoryDic.TryRemove(hist.Filename.ToLower(), out th);
removed++;
}
}
}
if (Clean)
{
await this.CleanHistoryList();
}
});
ret = (added > 0 || removed > 0);
if (ret)
{
//this.DeletedCount.AtomicAddAndGet(removed);
//this.AddedCount.AtomicAddAndGet(added);
this.LastUpdateTime = DateTime.Now;
this.HasChanged.WriteFullFence(true);
}
this.HasChanged.WriteFullFence(false);
Global.Log($"Update History Database: Added={added}, removed={removed}, total={this.HistoryDic.Count}, StackDepth={new StackTrace().FrameCount}, TID={Thread.CurrentThread.ManagedThreadId}, TCNT={Process.GetCurrentProcess().Threads.Count} in {sw.ElapsedMilliseconds}ms");
}
catch (Exception ex)
{
Global.Log("Error: " + Global.ExMsg(ex));
}
return(ret);
}
public async Task <List <TModel> > GetAll <TModel>() where TModel : IPersistModel, new()
{
AsyncTableQuery <TModel> query = db.Table <TModel>(); // TODO how to return queriable
return(await query.ToListAsync());
}
private async Task GetSampleDataAsync()
{
if (this._groups.Count != 0)
{
return;
}
Database db = new Database(Database.GetDBPath());
await db.Init();
List <Category> lCategory = new List <Category>();
List <Recipe> lRecipe = new List <Recipe>();
//get collection of all categories
AsyncTableQuery <Category> category = db.Table <Category>();
List <Category> lCategories = await category.ToListAsync();
for (int i = 0; i < lCategories.Count; i++)
{
Category c = lCategories[i];
SampleDataGroup group = new SampleDataGroup(c.Id, c.Name);
//get collection of all recipes for this particular category
AsyncTableQuery <Recipe> recipe = db.Table <Recipe>().Where(a => a.Category_Id == c.Id);
List <Recipe> lRecipes = await recipe.ToListAsync();
lRecipes = lRecipes.OrderByDescending(a => a.Rating).ToList();
//iterate through each recipe in this category
for (int k = 0; k < lRecipes.Count; k++)
{
Recipe r = lRecipes[k];
group.Items.Add(new SampleDataItem(r.Id, r.Category_Id, r.Name, r.Description, r.Ingredients, r.Method, r.Image, r.Rating, r.PreparationTime));
}
this.Groups.Add(group);
}
/*
* Uri dataUri = new Uri("ms-appx:///DataModel/SampleData.json");
*
* StorageFile file = await StorageFile.GetFileFromApplicationUriAsync(dataUri);
* string jsonText = await FileIO.ReadTextAsync(file);
* JsonObject jsonObject = JsonObject.Parse(jsonText);
* JsonArray jsonArray = jsonObject["Groups"].GetArray();
*
* foreach (JsonValue groupValue in jsonArray)
* {
* JsonObject groupObject = groupValue.GetObject();
* SampleDataGroup group = new SampleDataGroup(groupObject["UniqueId"].GetString(),
* groupObject["Title"].GetString(),
* groupObject["Subtitle"].GetString(),
* groupObject["ImagePath"].GetString(),
* groupObject["Description"].GetString());
*
* foreach (JsonValue itemValue in groupObject["Items"].GetArray())
* {
* JsonObject itemObject = itemValue.GetObject();
* group.Items.Add(new SampleDataItem(itemObject["UniqueId"].GetString(),
* itemObject["Title"].GetString(),
* itemObject["Subtitle"].GetString(),
* itemObject["ImagePath"].GetString(),
* itemObject["Description"].GetString(),
* itemObject["Content"].GetString()));
* }
* this.Groups.Add(group);
* }*/
}
public async Task <List <Contact> > LoadDataAsync()
{
return((await _contactEntityTable.ToListAsync())
.Select(Contact.From).ToList());
}
public static Task <IEnumerable <T> > ToEnumerableAsync <T>(this AsyncTableQuery <T> tableQuery)
where T : new()
{
return(tableQuery.ToListAsync().Map(t => (IEnumerable <T>)t));
}
// TODO update
public T FindAsync <T>(int id) where T : BaseModel, new()
{
AsyncTableQuery <T> query = this.conn.Table <T>().Where(x => x.Id == id);
return(query.ToListAsync().Result.FirstOrDefault());
}
