/// <summary> /// Pettern creater /// </summary> /// <param name="petternTitle"></param> /// <param name="pettern"></param> /// <param name="groupId"></param> /// <returns></returns> public static bool CreatePettern(string petternTitle, string pettern, long groupId) { var rows = 0; using (var conn = new SQLiteConnection(Connection.ConnString)) { conn.Open(); using (var comm = new SQLiteCommand(conn)) { comm.CommandText = "SELECT * FROM clipboard_petterns WHERE pettern=?"; comm.Parameters.AddWithValue("@0", pettern); using (var reader = comm.ExecuteReader()) { if (reader.HasRows) { return(false); } } } using (var commin = new SQLiteCommand(conn)) { commin.CommandText = "INSERT INTO clipboard_petterns (title, pettern, date, groupid) values(?,?,?,?)"; commin.Parameters.AddWithValue("@0", petternTitle); commin.Parameters.AddWithValue("@1", pettern); commin.Parameters.AddWithValue("@2", DateTime.Now.ToString("dd-MM-yyyy HH':'mm':'ss")); commin.Parameters.AddWithValue("@3", groupId); rows = commin.ExecuteNonQuery(); } foreach (var item in ClipboardPetternGroupL) { if (item.RowId == groupId) { var newpettern = new ClipboardPettern() { RowId = conn.LastInsertRowId, Order = item.ClipboardPetterns.Count + 1, Title = petternTitle, Pettern = pettern, Date = DateTime.Now.ToString("dd-MM-yyyy HH':'mm':'ss"), GroupId = groupId }; item.ClipboardPetterns.Add(newpettern); } } conn.Close(); return(rows > 0 ? true : false); } }
/// <summary> /// Get pettern groups list /// </summary> private static void GetPetternGroups() { if (ClipboardPetternGroupL.Count > 0) { ClipboardPetternGroupL.Clear(); } using (var conn = new SQLiteConnection(Connection.ConnString)) { conn.Open(); using (var comm = new SQLiteCommand(conn)) { comm.CommandText = "SELECT rowid,* FROM clipboard_petterns_groups"; using (var reader = comm.ExecuteReader()) { if (reader.HasRows) { int i = 1; while (reader.Read()) { // group var petternGroup = new ClipboardPetternGroup() { RowId = Convert.ToInt64(reader["rowid"].ToString()), Order = i++, Title = reader["title"].ToString(), Date = reader["date"].ToString(), Status = reader["status"].ToString() == "1" ? true : false }; // get group contains using (var commpt = new SQLiteCommand(conn)) { commpt.CommandText = "SELECT rowid, * FROM clipboard_petterns WHERE groupid=?"; commpt.Parameters.AddWithValue("@0", reader["rowid"]); using (var readerpt = commpt.ExecuteReader()) { if (readerpt.HasRows) { int o = 1; // create petterns group petternGroup.ClipboardPetterns = new ObservableCollection <ClipboardPettern>(); while (readerpt.Read()) { var pettern = new ClipboardPettern() { RowId = Convert.ToInt64(readerpt["rowid"].ToString()), Order = o++, Title = readerpt["title"].ToString(), Pettern = readerpt["pettern"].ToString(), Date = readerpt["date"].ToString(), GroupId = Convert.ToInt64(readerpt["rowid"].ToString()) }; petternGroup.ClipboardPetterns.Add(pettern); } } } } ClipboardPetternGroupL.Add(petternGroup); } } } } conn.Close(); } }