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