public static void InsertData(Remind remind, string DBName, string TableName)
{
SqliteCommand insertCommand = new SqliteCommand();
insertCommand.Connection = db;
//diary.Date = DateTime.ParseExact(diary.Date.ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
insertCommand.CommandText = "INSERT INTO " + TableName +
"(CSY_DATE,CSY_CONTENT) VALUES ('" + remind.Date + "','" +
remind.Content + "')";
insertCommand.ExecuteReader();
}
public static ObservableCollection <Remind> LoadFromDatabase2(string DBName, string TableName)
{
ObservableCollection <Remind> reminds = new ObservableCollection <Remind>();
string path = ApplicationData.Current.LocalFolder.Path + "\\" + DBName;
if (File.Exists(path))
{
SqliteCommand selectCommand = new SqliteCommand("SELECT CSY_DATE,CSY_CONTENT FROM " + TableName, db);
SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
Remind remind = new Remind();
remind.Date = query.GetString(0);
remind.Content = query.GetString(1);
remind.FixContent = remind.Content;
reminds.Add(remind);
}
}
return(reminds);
}
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();
}
}