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 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)
{
// ...
}
}
// history
public Task <List <HistoryItem> > GetHistoryAsync(string typeFilter = null, string actionFilter = null, string reminderFilter = null, byte responceFilter = 3)
{
AsyncTableQuery <HistoryItem> query = _database.Table <HistoryItem>();
if (typeFilter != null)
{
query = query.Where(h => h.TrackedType.Equals(typeFilter));
}
if (actionFilter != null)
{
query = query.Where(h => h.Action.Equals(actionFilter));
}
if (reminderFilter != null)
{
query = query.Where(h => h.Reminder.Equals(reminderFilter));
}
if (responceFilter != 3)
{
query = query.Where(h => h.Responce == responceFilter);
}
return(query.OrderByDescending(h => h.ID).ToListAsync());
}
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 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 <T> Select(Guid identifier)
{
AsyncTableQuery <T> tb = database.Table <T>();
var item = tb.Where(x => x.Id == identifier);
return(await item.FirstAsync());
}
public void TestAsyncTableElementAtAsync()
{
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);
Task <Customer> task = query.ElementAtAsync(7);
task.Wait();
Customer loaded = task.Result;
// check...
Assert.AreEqual("7", loaded.FirstName);
}
public async Task <bool> AddUser(User user)
{
try
{
await Init();
AsyncTableQuery <User> users = DbConection.Table <User>();
User resultUser = await DbConection.Table <User>().Where(x => x.login == user.login || x.email == user.email).FirstOrDefaultAsync();
if (resultUser == null)
{
int resultReg = await DbConection.InsertAsync(user);
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(false);
}
}
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);
}
}
protected override AsyncTableQuery <Currency> ApplyFilters(AsyncTableQuery <Currency> query, CurrencyFilter filter)
{
if (!string.IsNullOrEmpty(filter.Code))
{
query = query.Where(x => x.Code.Contains(filter.Code));
}
return(base.ApplyFilters(query, filter));
}
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 TestAsyncTableQueryToFirstAsyncMissing()
{
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync <Customer>();
// create...
Customer customer = CreateCustomer();
await conn.InsertAsync(customer);
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>().Where(v => v.Id == -1);
Assert.That(async() => await query.FirstAsync(), Throws.TypeOf <InvalidOperationException>());
}
protected override AsyncTableQuery <Transaction> ApplyFilters(AsyncTableQuery <Transaction> query, TransactionFilter filter)
{
if (filter.DateSince.HasValue)
{
query = query.Where(x => x.Date >= filter.DateSince.Value);
}
if (filter.IsCashPayment)
{
query = query.Where(x => x.BankId == null);
}
return(base.ApplyFilters(query, filter));
}
public async Task <List <Currency> > ReadChoosen(int length)
{
List <ChoosenCurrenciesTable> choosenCurrencies = await database.Table <ChoosenCurrenciesTable>().ToListAsync();
AsyncTableQuery <Currency> query = database.Table <Currency>();
List <Currency> currencies = new List <Currency>();
for (int i = 0; i < length; i++)
{
int index = choosenCurrencies[i].CurrencyId - 1;
currencies.Add(await query.ElementAtAsync(index));
}
return(currencies);
}
protected override AsyncTableQuery <Wallet> ApplyFilters(AsyncTableQuery <Wallet> query, WalletFilter filter)
{
if (!string.IsNullOrEmpty(filter.Name))
{
query = query.Where(x => x.Name.Contains(filter.Name));
}
if (filter.ImageId.HasValue)
{
query = query.Where(x => x.ImageId == filter.ImageId.Value);
}
return(base.ApplyFilters(query, filter));
}
public async Task TestAsyncTableQueryToFirstOrDefaultAsyncMissing()
{
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync <Customer>();
// create...
Customer customer = CreateCustomer();
await conn.InsertAsync(customer);
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>().Where(v => v.Id == -1);
Customer loaded = await query.FirstOrDefaultAsync();
// check...
Assert.IsNull(loaded);
}
public async Task TestAsyncTableQueryToFirstOrDefaultAsyncFound()
{
SQLiteAsyncConnection conn = GetAsyncConnection();
await conn.CreateTableAsync <Customer>();
// create...
Customer customer = CreateCustomer();
await conn.InsertAsync(customer);
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>().Where(v => v.Id == customer.Id);
Customer loaded = await query.FirstOrDefaultAsync();
// check...
Assert.AreEqual(customer.Email, loaded.Email);
}
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 void TestAsyncTableQueryToFirstAsyncMissing()
{
SQLiteAsyncConnection conn = GetConnection();
conn.CreateTableAsync <Customer>().Wait();
// create...
Customer customer = CreateCustomer();
conn.InsertAsync(customer).Wait();
// query...
AsyncTableQuery <Customer> query = conn.Table <Customer>().Where(v => v.Id == -1);
Task <Customer> task = query.FirstAsync();
ExceptionAssert.Throws <AggregateException>(() => task.Wait());
}
private async Task <int> CountReady(Expression <Func <T, bool> > filter = null,
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);
}
return(await tableQuery.CountAsync());
}
public static async Task <bool> ExistWithNameAsync(this AsyncTableQuery <ProjectData> query, string projectName, Guid clientId)
{
List <ProjectData> existingProjects;
if (clientId != Guid.Empty)
{
existingProjects = await query
.Where(r => r.Name == projectName && r.ClientId == clientId)
.ToListAsync().ConfigureAwait(false);
}
else
{
existingProjects = await query
.Where(r => r.Name == projectName && r.ClientId == null)
.ToListAsync().ConfigureAwait(false);
}
return(existingProjects.Count != 0);
}
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);
}
protected virtual AsyncTableQuery <T1> ApplyFilters(AsyncTableQuery <T1> query, T2 filter)
{
if (filter.Ids != null && filter.Ids.Any())
{
query = query.Where(x => filter.Ids.Contains(x.Id));
}
if (filter.Skip > 0)
{
query = query.Skip(filter.Skip.Value);
}
if (filter.Take > 0)
{
query = query.Take(filter.Take.Value);
}
return(query);
}
protected override AsyncTableQuery <Rule> ApplyFilters(AsyncTableQuery <Rule> query, RuleFilter filter)
{
if (!string.IsNullOrEmpty(filter.Name))
{
query = query.Where(x => x.Name.Contains(filter.Name));
}
if (!string.IsNullOrEmpty(filter.Pattern))
{
query = query.Where(x => x.Pattern.Contains(filter.Pattern));
}
if (filter.CategoryId.HasValue)
{
query = query.Where(x => x.CategoryId == filter.CategoryId.Value);
}
return(base.ApplyFilters(query, filter));
}
protected override AsyncTableQuery <WalletCategory> ApplyFilters(AsyncTableQuery <WalletCategory> query, WalletCategoryFilter filter)
{
if (filter.WalletId.HasValue && filter.CategoryId.HasValue)
{
query = query.Where(x => x.WalletId.Equals(filter.WalletId) && x.CategoryId.Equals(filter.CategoryId));
}
else if (filter.WalletId.HasValue)
{
query = query.Where(x => x.WalletId.Equals(filter.WalletId));
}
else if (filter.CategoryId.HasValue)
{
query = query.Where(x => x.CategoryId.Equals(filter.CategoryId));
}
return(base.ApplyFilters(query, filter));
}
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));
}
/// <summary>
/// 分页查询，调用此方法前必须进行排序
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="source"></param>
/// <param name="current"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static async Task <PagedModel <TEntity> > PagingAsync <TEntity>(
this AsyncTableQuery <TEntity> source,
int current = IPagedModel.DefaultCurrent,
int pageSize = IPagedModel.DefaultPageSize) where TEntity : new()
{
var skipCount = (current - 1) * pageSize;
var total = await source.CountAsync();
var dataSource = await source.Skip(skipCount).Take(pageSize).ToListAsync();
var pagedModel = new PagedModel <TEntity>
{
Current = current,
PageSize = pageSize,
Total = total,
DataSource = dataSource,
};
return(pagedModel);
}
public async Task TestAsyncTableQueryCountAsync()
{
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());
}
// load...
AsyncTableQuery <Customer> query = conn.Table <Customer>();
var result = await query.CountAsync();
// check...
Assert.AreEqual(10, result);
}
