//#region "Initialize/close"
/// <summary>
/// Initializes the database connection
/// </summary>
public async Task InitializeDatabaseAsync()
{
//Create table schema if doesn't exist.
//Note that is non-destructive and should not drop tables if they are already there
await AsyncConnection.CreateTableAsync <DatabaseObjects.BankAccount>();
await AsyncConnection.CreateTableAsync <DatabaseObjects.BankAccountTransaction>();
int bankAccountCount = await AsyncConnection.Table <DatabaseObjects.BankAccount>().CountAsync();
Log.ConsoleInfo(string.Format("seconomy: {0} clean - {1} accounts", _path, bankAccountCount));
_ready = true;
/*
* return AsyncConnection.CreateTableAsync<DatabaseObjects.BankAccount>().ContinueWith((i) => {
* AsyncConnection.CreateTableAsync<DatabaseObjects.BankAccountTransaction>().ContinueWith((x) => {
* AsyncConnection.Table<DatabaseObjects.BankAccount>().CountAsync().ContinueWith((count) => {
* int bankAccountCount = count.Result;
* Log.ConsoleInfo(string.Format("seconomy: {0} clean - {1} accounts", _path, bankAccountCount));
*
* _ready = true;
* });
* });
* });
*/
}
public async Task TaskGenerateAsync(int count)
{
var connect = new SQLiteAsyncConnection(DataBaseFile);
var taskList = await connect.Table<TaskTable>().Take(count).ToListAsync();
taskList.Shuffle ();
var categoryList = await connect.Table<CategoryTable>().ToListAsync();
_tasks = new TaskList(taskList, categoryList);
}
private static Task CreateDatabase (SQLiteAsyncConnection connection, CancellationToken cancellationToken)
{
return Task.Factory.StartNew(() =>
{
//Create the tables
var createTask = connection.CreateTablesAsync (tableTypes);
createTask.Wait();
//Count number of assignments
var countTask = connection.Table<Assignment>().CountAsync();
countTask.Wait();
//If no assignments exist, insert our initial data
if (countTask.Result == 0)
{
var insertTask = connection.InsertAllAsync(TestData.All);
//Wait for inserts
insertTask.Wait();
//Mark database created
initialized = true;
}
});
}
private async void Button_Click_3(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(ApplicationData.Current.LocalFolder.Path + "\\people.db");
var query = conn.Table<Person>();//.Where(x => x.Name == "张三");
var result = await query.ToListAsync();
listbox.ItemsSource = result;
}
/// <summary>
/// For Displaying search result from database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void acbDrugSearch_TextChanged(object sender, RoutedEventArgs e)
{
var folder = ApplicationData.Current.LocalFolder.GetFolderAsync("DataFolder");
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "DataFile.sqlite"), true);
string pillname = acbDrugSearch.Text;
if (!string.IsNullOrEmpty(pillname) && !string.IsNullOrWhiteSpace(pillname) && pillname.Length > 3)
{
var query = conn.Table<drugs>().Where(x => x.drugname.Contains(pillname));
var result = await query.ToListAsync();
if (result == null)
{
MessageBox.Show("Pill not found.");
}
else
{
List<string> drugnamelist = new List<string>();
foreach (var item in result)
{
dataReadName =item.drugname;
dataReadMilligram = item.strenght;
dataReadNameMilligram = dataReadName + dataReadMilligram;
drugnamelist.Add(dataReadNameMilligram);
}
lstDrugSearch.ItemsSource = drugnamelist;
}
}
}
public async Task<AlbumItem> LoadAlbumViaName(int artistId, string albumName)
{
var connection = new SQLiteAsyncConnection(DbPath);
var query = connection.Table<AlbumItem>().Where(x => x.Name.Equals(albumName)).Where(x => x.ArtistId == artistId);
var result = await query.ToListAsync();
return result.FirstOrDefault();
}
/// <summary>
/// Invoked when this page is about to be displayed in a Frame.
/// </summary>
/// <param name="e">Event data that describes how this page was reached.
/// This parameter is typically used to configure the page.</param>
protected async override void OnNavigatedTo(NavigationEventArgs e)
{
bool dbExist = await CheckDbAsync("Tips.db3");
if (!dbExist)
{
await CreateDatabaseAsync();
}
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("Tips.db3");
var query = conn.Table<Tips>();
tips = await query.ToListAsync();
string title1res = tips[tips.Count - 1].title1;
string title2res = tips[tips.Count - 1].title2;
string title3res = tips[tips.Count - 1].title3;
string desc1res = tips[tips.Count - 1].desc1;
string desc2res = tips[tips.Count - 1].desc2;
string desc3res = tips[tips.Count - 1].desc3;
Tips l = new Tips { title1 = title1res, title2 = title2res, title3 = title3res, desc1 = desc1res, desc2 = desc2res, desc3 = desc3res };
t.Add(l);
TipsList.ItemsSource = t;
}
private async void LoadChartContents()
{
bool dbExist = await CheckDbAsync("ouzen.db3");
if (!dbExist)
{
await CreateDatabaseAsync();
}
// Get personnes
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("ouzen.db3");
var query = conn.Table<ouzen>();
ouzen = await query.ToListAsync();
for (int i = 0; i < ouzen.Count; i++) Debug.WriteLine(ouzen[i].date1);
// items.Add(new ouzen { Name =q , Value = _random.Next(35, 150) });
((LineSeries)this.LineChart.Series[0]).ItemsSource = ouzen;
((LineSeries)this.LineChart.Series[0]).DependentRangeAxis =
new LinearAxis
{
Minimum = 35,
Maximum = 150,
Orientation = AxisOrientation.Y,
Interval = 5,
ShowGridLines = true
};
((LineSeries)this.LineChart.Series[0]).Refresh();
}
public async Task<TrackItem> LoadTrack(int trackId)
{
var connection = new SQLiteAsyncConnection(DbPath);
var query = connection.Table<TrackItem>().Where(x => x.Id.Equals(trackId));
var result = await query.ToListAsync();
return result.FirstOrDefault();
}
public async Task<VideoItem> GetFromPath(String path)
{
var conn = new SQLiteAsyncConnection(DbPath);
var req = conn.Table<VideoItem>().Where(x => x.Path == path);
var res = await req.ToListAsync().ConfigureAwait(false);
return res.FirstOrDefault();
}
public async static Task<int> CounterSubscribed()
{
SQLiteAsyncConnection sqlConnection = new SQLiteAsyncConnection(DbHelper.DB_PATH);
int counters = await sqlConnection.Table<Counter>().CountAsync();
AppStore.AddValue(Constants.CountersSubscribed, counters.ToString());
return counters;
}
public async Task<string> ReadMessage(int id)
{
var connection = new SQLiteAsyncConnection(_databasePath);
var message = await connection.Table<Message> ().Where(m => m.Id == id).FirstOrDefaultAsync ();
return message?.MessageString ?? "No message stored";
}
public async Task<string> GetFirstTrackPathByAlbumId(int albumId)
{
var connection = new SQLiteAsyncConnection(DbPath);
var query = connection.Table<TrackItem>().Where(x => x.AlbumId == albumId);
var result = await query.FirstOrDefaultAsync();
return result != null ? result.Path : null;
}
private static async Task SetValueAsyncInternal(string name, string value, SQLiteAsyncConnection conn)
{
// if we don't have a connection, assume the system one...
if(conn == null)
conn = StreetFooRuntime.GetSystemDatabase();
// load an existing value...
var setting = await conn.Table<SettingItem>().Where(v => v.Name == name).FirstOrDefaultAsync();
if (setting != null)
{
// change and update...
setting.Value = value;
await conn.UpdateAsync(setting);
}
else
{
setting = new SettingItem()
{
Name = name,
Value = value
};
// save...
await conn.InsertAsync(setting);
}
}
async Task<MusicLibraryViewModel.ArtistItem> LoadViaArtistName(string artistName)
{
var connection = new SQLiteAsyncConnection(_dbPath);
var query = connection.Table<MusicLibraryViewModel.ArtistItem>().Where(x => x.Name.Equals(artistName));
var result = await query.ToListAsync();
return result.FirstOrDefault();
}
async Task<MusicLibraryViewModel.TrackItem> LoadTrack(int artistId, int albumId, string trackName)
{
var connection = new SQLiteAsyncConnection(_dbPath);
var query = connection.Table<MusicLibraryViewModel.TrackItem>().Where(x => x.Name.Equals(trackName)).Where(x => x.ArtistId == artistId).Where(x => x.AlbumId == albumId);
var result = await query.ToListAsync();
return result.FirstOrDefault();
}
public async Task<bool> DoesTrackExist(string path)
{
bool b = false;
var connection = new SQLiteAsyncConnection(DbPath);
var query = connection.Table<TrackItem>().Where(x => x.Path == path);
b = await query.CountAsync() != 0;
return b;
}
public async static Task<Menu> GetMenu(int counterId)
{
string currentDay = DateTime.Today.DayOfWeek.ToString();
TimeSpan currentTime = DateTime.Now.TimeOfDay;
SQLiteAsyncConnection sqlConnection = new SQLiteAsyncConnection(DbHelper.DB_PATH);
var collectionItem = await sqlConnection.Table<Menu>().Where(x => x.CounterId == counterId).FirstOrDefaultAsync();
return collectionItem;
}
async Task<ObservableCollection<MusicLibraryViewModel.TrackItem>> LoadTracksByAlbumId(int albumId)
{
var connection = new SQLiteAsyncConnection(_dbPath);
var query = connection.Table<MusicLibraryViewModel.TrackItem>().Where(x => x.AlbumId == albumId);
var result = await query.ToListAsync();
return new ObservableCollection<MusicLibraryViewModel.TrackItem>(result);
}
public async static Task InsertOrganization(Organization organization)
{
SQLiteAsyncConnection sqlConnection = new SQLiteAsyncConnection(DbHelper.DB_PATH);
var collectionItem = await sqlConnection.Table<Organization>().Where(x => x.Id == organization.Id).FirstOrDefaultAsync();
if (collectionItem == null)
{
await sqlConnection.InsertAsync(organization);
}
}
public async static Task DeleteItems(int menuId)
{
SQLiteAsyncConnection sqlConnection = new SQLiteAsyncConnection(DbHelper.DB_PATH);
var collectionItem = await sqlConnection.Table<Item>().Where(x => x.MenuId == menuId).ToListAsync();
foreach(Item item in collectionItem)
{
await sqlConnection.DeleteAsync(item);
}
}
public async static Task DeleteCounter(int counterId)
{
SQLiteAsyncConnection sqlConnection = new SQLiteAsyncConnection(DbHelper.DB_PATH);
var collectionItem = await sqlConnection.Table<Counter>().Where(x => x.Id == counterId).FirstOrDefaultAsync();
if (collectionItem != null)
{
await sqlConnection.DeleteAsync(collectionItem);
}
await CounterSubscribed();
}
public async static Task<bool> CounterExists(int counterId)
{
SQLiteAsyncConnection sqlConnection = new SQLiteAsyncConnection(DbHelper.DB_PATH);
var collectionItem = await sqlConnection.Table<Counter>().Where(x => x.Id == counterId).FirstOrDefaultAsync();
if (collectionItem != null)
{
return true;
}
return false;
}
private async void Button_Click_4(object sender, RoutedEventArgs e)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(ApplicationData.Current.LocalFolder.Path + "\\people.db");
var query = conn.Table<Person>().Where(x => x.Name == "张三");
var result = await query.ToListAsync();
foreach (var item in result)
{
await conn.DeleteAsync(item);
}
}
public async Task<TrackItem> LoadTrackByPath(string path)
{
var connection = new SQLiteAsyncConnection(DbPath);
var query = connection.Table<TrackItem>().Where(x => x.Path == path);
if (await query.CountAsync() > 0)
{
return await query.FirstOrDefaultAsync();
}
return null;
}
public static async Task<List<Exam>> getExamsAsync()
{
if (exams.Count != 0)
{
return exams;
}
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Windows.Storage.ApplicationData.Current.LocalFolder.Path + "\\Data.db");
await conn.CreateTableAsync<Exam>();
exams = await conn.Table<Exam>().OrderBy(exam => exam.ExamTime).ToListAsync();
return exams;
}
async void GetRemarkName(string fakeid)
{
try
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(ApplicationData.Current.LocalFolder.Path + "\\" + LoginInfo.UserName + "\\NewMessage.db");
var query = conn.Table<PersonalInfoTable>().Where(x => x.Username == LoginInfo.UserName).Where(x => x.Fakeid == fakeid);
var result = await query.ToListAsync();
var items = new ObservableCollection<PersonalInfoTable>(result);
Name.Text = items[0].Remarkname == "" ? items[0].Nickname : items[0].Remarkname;
}
catch { }
}
public static async Task<List<Product>> getAllProducts()
{
List<Product> products = null;
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(PRODUCTS_TABLE);
products = await conn.Table<Product>().Where(x => true).ToListAsync();
return products;
}
private void initExternalDB()
{
DBVersion dbVersion = null;
mDatabase.CreateTableAsync <DBVersion>().Wait();
var taskR = mDatabase.Table <DBVersion>().FirstOrDefaultAsync();
dbVersion = taskR.Result;
if (dbVersion == null || dbVersion.Version < 1)
{
dbVersion = new DBVersion();
initExternalDBStruct_v1(dbVersion);
}
if (dbVersion.Version < 2)
{
updateExternalDBStruct_v2(dbVersion);
}
}
private static async Task<string> GetValueAsyncInternal(string name, SQLiteAsyncConnection conn)
{
if(conn == null)
conn = StreetFooRuntime.GetSystemDatabase();
// load any existing value...
var setting = (await conn.Table<SettingItem>().Where(v => v.Name == name).ToListAsync()).FirstOrDefault();
if (setting != null)
return setting.Value;
else
return null;
}
/// <summary>
/// Invoked when this page is about to be displayed in a Frame.
/// </summary>
/// <param name="e">Event data that describes how this page was reached.
/// This parameter is typically used to configure the page.</param>
protected async override void OnNavigatedTo(NavigationEventArgs e)
{
bool dbExist = await CheckDbAsync("Prog.db3");
if (!dbExist)
{
await CreateDatabaseAsync();
}
// Get personnes
SQLiteAsyncConnection conn = new SQLiteAsyncConnection("Prog.db3");
var query = conn.Table<Prog>();
p = await query.ToListAsync();
if (p.Count == 0)
{
grid2.Visibility = Visibility.Collapsed;
grid1.Visibility = Visibility.Visible;
}
if (p.Count != 0)
{
grid1.Visibility = Visibility.Collapsed;
grid2.Visibility = Visibility.Visible;
}
bool dbExist1 = await CheckDbAsync("calories.db3");
if (!dbExist1)
{
await CreateDatabaseAsync1();
}
// Get personnes
SQLiteAsyncConnection conn1 = new SQLiteAsyncConnection("calories.db3");
var query1 = conn1.Table<calories>();
cal = await query1.ToListAsync();
if (cal.Count == 0)
{
messagediet.Message = "you logged 0 from 2500 Calories";
}
if (cal.Count != 0)
{
messagediet.Message = "you logged " + cal[cal.Count-1].totalcalorie +" from 2500 Calories";
}
}
public static async Task<List<Product>> filterProducts(string filter)
{
List<Product> products = null;
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(PRODUCTS_TABLE);
products = await conn.Table<Product>().
Where(x => x.ProductCode.
Contains(filter) ||
x.ProductDesription.Contains(filter)).ToListAsync();
return products;
}
public Task <List <Entities.Station> > GetStationAsync()
{
return(Database.Table <Entities.Station>().ToListAsync());
}
public async Task <ICollection <BudgetCategory> > GetAllCategoriesAsync()
{
var categories = await connection.Table <BudgetCategory>().ToListAsync();
return(categories as ICollection <BudgetCategory>);
}
public Task <List <Entities.Emergency> > GetEmergencyAsync()
{
return(Database.Table <Entities.Emergency>().ToListAsync());
}
public Task <List <Product> > GetProducts()
{
return(_database.Table <Product>().ToListAsync());
}
public List <Entities.UserInfo> GetUserInfoAsync()
{
return(Database.Table <Entities.UserInfo>().ToListAsync().Result);
}
public async Task <IEnumerable <T> > GetAll <T>() where T : BaseModel, new()
{
return(await connection.Table <T>().ToListAsync());
}
