public static ObservableCollection <Diary> LoadFromDatabase(string DBName, string TableName)
{
ObservableCollection <Diary> diaries = new ObservableCollection <Diary>();
string path = ApplicationData.Current.LocalFolder.Path + "\\" + DBName;
if (File.Exists(path))
{
SqliteCommand selectCommand = new SqliteCommand("SELECT CSY_DATE,CSY_WEATHER,CSY_CONTENT FROM " + TableName, db);
SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
Diary diary = new Diary();
diary.Date = query.GetString(0);
diary.Weather = query.GetString(1);
diary.Content = query.GetString(2);
diary.FixContent = diary.Content;
diaries.Add(diary);
}
}
return(diaries);
}
public async static Task AndDatabaseAsync()
{
string conn = "Filename=" + ApplicationData.Current.LocalFolder.Path + "\\YourDiary1.db3";
using (SqliteConnection db1 = new SqliteConnection(conn))
{
db1.Open();
string sql = "";
//SqliteCommand comm1 = new SqliteCommand(sql, db1);
//SqliteDataReader dataReader1=await comm1.ExecuteReaderAsync();
sql = "select * from CSY_DIARY";
SqliteCommand comm = new SqliteCommand(sql, db1);
SqliteDataReader dataReader = await comm.ExecuteReaderAsync();
while (dataReader.Read())
{
sql = "select * from CSY_DIARY where CSY_DATE='" +
dataReader.GetString(1) + "'";
SqliteCommand comm1 = new SqliteCommand(sql, SqliteDatabase.db);
SqliteDataReader dataReader1 = await comm1.ExecuteReaderAsync();
if (!dataReader1.HasRows)
{
Diary diary = new Diary()
{
Date = dataReader.GetString(1),
Weather = dataReader.GetString(2),
Content = dataReader.GetString(3)
};
SqliteDatabase.InsertData(diary,
SqliteDatabase.db, DiaryTableName);
}
}
//dataReader.Close();
sql = "select * from CSY_REMIND";
SqliteCommand comm2 = new SqliteCommand(sql, db1);
SqliteDataReader dataReader2 = await comm2.ExecuteReaderAsync();
while (dataReader2.Read())
{
sql = "select * from CSY_REMIND where CSY_DATE='" +
dataReader2.GetString(1) + "'";
SqliteCommand comm1 = new SqliteCommand(sql, SqliteDatabase.db);
SqliteDataReader dataReader1 = await comm1.ExecuteReaderAsync();
if (!dataReader1.HasRows)
{
Remind remind = new Remind()
{
Date = dataReader2.GetString(1),
Content = dataReader2.GetString(2)
};
SqliteDatabase.InsertData(remind,
SqliteDatabase.db, RemindTableName);
}
}
//dataReader2.Close();
db1.Close();
}
}