//default constructor public LibrarianListItem(Task task, LibrarianWindow parent, SQLiteDatabase database) { InitializeComponent(); this.taskObject = task; this.db = database; this.parentWindow = parent; SetItemVisualization(task); }
static void Main(string[] args) { SQLiteDatabase database = new SQLiteDatabase("MediaLibrary.sqlite"); QueryManager queriesManager = new QueryManager(); queriesManager.db = database; //set a new requets queriesManager.Request = new QueryRequest("q", GroupByType.ByTitleStartingLetter); //List<Book> books = dbObjects.DbBook.Select("",database); Dictionary<string, List<Book>> querieResults = queriesManager.Response; }
public static List<Book> Select(string condition, SQLiteDatabase db) { try { List<Book> books = new List<Book>(); DataTable resultSet = db.GetDataTable(String.Format("SELECT * FROM 'Books' {0}", condition)); foreach (DataRow row in resultSet.Rows) { Book book = new Book(); book.ID = row["id"].ToString(); book.Title = row["Title"].ToString(); book.Isbn = row["ISBN"].ToString(); book.Authors = GetAuthorsFromString(row["Authors"].ToString()); book.Genres = GetGenresFromString(row["Genres"].ToString()); book.Description = row["Description"].ToString(); book.PathName = row["FilePath"].ToString(); book.Publisher = row["Publisher"].ToString(); int siteRating = 0; bool succParsingSite = int.TryParse(row["SiteRating"].ToString(), out siteRating); book.SiteRating = siteRating; int userRating = 0; bool succParsingUser = int.TryParse(row["UserRating"].ToString(), out userRating); book.UserRating = userRating; DateTime publicationDate = DateTime.MinValue; bool succParsingDateTime = DateTime.TryParse(row["PublicationDate"].ToString(), out publicationDate); book.PublicationDate = publicationDate; ulong numOfPages = 0; bool succParsingNumOfPages = ulong.TryParse(row["Pages"].ToString(), out numOfPages); book.NumberOfPages = numOfPages; book.Cover = row["CoverImage"].ToString(); /*string taskStatus = row["taskStatus"].ToString(); switch (int.Parse(taskStatus)) { case 1: tsk.CurrentState = State.InProgress; break; case 2: tsk.CurrentState = State.Failed; break; case 3: tsk.CurrentState = State.Completed; break; default: tsk.CurrentState = State.Waiting; break; }*/ books.Add(book); } return books; } catch (Exception fail) { throw new FSSQLiteException("An error occurred when trying to select a book."); } }
public static void DeleteUnusedTasks(SQLiteDatabase db) { try { db.Delete("Tasks", "taskStatus=3"); } catch (Exception fail) { throw new FSSQLiteException("An error occurred when trying to delete a task."); } }
public static void RefreshTasks(SQLiteDatabase db) { try { //UPDATE Tasks SET taskStatus=1 WHERE id=(SELECT id FROM Tasks WHERE taskStatus=0 LIMIT 1) db.ExecuteNonQuery("UPDATE Tasks SET taskStatus='3' WHERE id IN (SELECT id FROM Tasks WHERE taskStatus=-7)"); } catch (Exception fail) { throw new FSSQLiteException("An error occurred when trying to update a task."); } }
public static void Insert(string taskString, SQLiteDatabase db) { try { Dictionary<string, string> data = new Dictionary<string, string>(); data.Add("taskString", SQLiteDatabase.EscapeSpecialChars(taskString)); data.Add("taskStatus", "0"); db.Insert("Tasks", data); } catch (Exception fail) { throw new FSSQLiteException("An error occurred when trying to insert a new task."); } }
public static void Delete(string id, SQLiteDatabase db) { try { bool succ = db.Delete("Books", String.Format("id={0}", id)); if (!succ) { throw new Exception(); } } catch { throw new FSSQLiteException("An unexpected error occured when trying to delete a book."); } }
public static void Insert(Book bookToInsert, SQLiteDatabase db) { try { Dictionary<string, string> data = BookToFieldsDictionary(bookToInsert); bool succ = db.Insert("Books", data); if (!succ) { return; //throw new Exception(); } } catch { throw new FSSQLiteException("An unexpected error occured when trying to insert a new book."); } }
/// <summary> /// Insert a book in DB /// </summary> /// <param name="bookToInsert"></param> /// <param name="db"></param> public static void InsertBook(Book bookToInsert, SQLiteDatabase db) { try { Dictionary<string, string> data = new Dictionary<string, string>(); data.Add("title", SQLiteDatabase.EscapeSpecialChars(bookToInsert.Title)); data.Add("isbn", SQLiteDatabase.EscapeSpecialChars(bookToInsert.Isbn)); data.Add("publisher", SQLiteDatabase.EscapeSpecialChars(bookToInsert.Publisher)); data.Add("userRating", bookToInsert.UserRating.ToString()); data.Add("LibraryThingRating", bookToInsert.SiteRating.ToString()); data.Add("datePublication", bookToInsert.PublicationDate.ToString()); string genresString = ""; foreach (Genre genre in bookToInsert.Genres) { genresString+=genre.ToString()+""; } data.Add("genres", genresString); data.Add("description", SQLiteDatabase.EscapeSpecialChars(bookToInsert.Description)); data.Add("pages", bookToInsert.NumberOfPages.ToString()); db.Insert("Books", data); /*foreach (var genre in bookToInsert.Genres) { DataTable genreIds = db.GetIdByItem("Genres", genre.Name); foreach (DataRow g in genreIds.Rows) { data.Add("genres", g["id"].ToString()); } } foreach (var author in bookToInsert.Authors) { DataTable authorIds = db.GetIdByItem("Genres", author.FullName); foreach (DataRow a in authorIds.Rows) { data.Add("authors", a["id"].ToString()); } }*/ } catch (Exception fail) { throw new FSSQLiteException("An error occurred when trying to insert a new book."); } }
public static void Insert(string bookTitle, SQLiteDatabase db) { try { Dictionary<string, string> data = new Dictionary<string, string>(); string[] splitedFileName=bookTitle.Split(new string[]{"\\"},StringSplitOptions.RemoveEmptyEntries); data.Add("Title",splitedFileName[splitedFileName.Length-1]); data.Add("FilePath", bookTitle); bool succ = db.Insert("Books", data); if (!succ) { return; //throw new Exception(); } } catch { throw new FSSQLiteException("An unexpected error occured when trying to insert a new book."); } }
public static Task[] SelectAllTasks(SQLiteDatabase db) { try { List<Task> tasks = new List<Task>(); DataTable resultSet = db.GetDataTable("SELECT * FROM 'Tasks' WHERE taskStatus>=0 ORDER BY taskStatus DESC"); foreach (DataRow row in resultSet.Rows) { Task tsk = new Task(); tsk.PathName = row["taskString"].ToString(); tsk.ID = row["id"].ToString(); tsk.ISBN = row["ISBN"].ToString(); string taskStatus = row["taskStatus"].ToString(); switch (int.Parse(taskStatus)) { case 1: tsk.CurrentState = State.InProgress; break; case 2: tsk.CurrentState = State.Failed; break; case 3: tsk.CurrentState = State.Completed; break; default: tsk.CurrentState = State.Waiting; break; } tasks.Add(tsk); } return tasks.ToArray(); } catch (Exception fail) { throw new FSSQLiteException("An error occurred when trying to select a task."); } }
public static void Update(string id, Book newData, SQLiteDatabase db) { try { Dictionary<string, string> data = BookToFieldsDictionary(newData); bool succ = db.Update("Books", data, String.Format("id={0}", id)); if (!succ) { throw new Exception(); } } catch { throw new FSSQLiteException("An unexpected error occured when trying to update a book."); } }
/// <summary> /// Get all books from DB and insert them in a List /// </summary> public override void CreateMediaLibrary() { SQLiteDatabase database = new SQLiteDatabase(); string query = "SELECT * FROM Books"; DataTable books = database.GetDataTable(query); foreach (DataRow b in books.Rows) { Book book = new Book(); book.Isbn = b["isbn"].ToString(); book.Title = b["title"].ToString(); book.Publisher = b["publisher"].ToString(); //book.SiteRating = (decimal?)b["rating"]??0; book.SiteRating = 0; //book.UserRating = (decimal?)b["userRating"]; book.UserRating = 0; //book.PublicationDate = (DateTime?)b["datePublication"] ?? DateTime.MinValue; int genreId = int.Parse(b["genres"].ToString()); DataTable genresTable = database.GetItemById("Genres", genreId); foreach (DataRow genre in genresTable.Rows) { book.Genres.Add(new Genre { Name = genre.ToString() }); } //book.NumberOfPages = ulong.Parse(b["pages"].ToString()); book.NumberOfPages = 0; book.Description = b["description"].ToString(); this.MediaList.Add(book); } //throw new NotImplementedException(); }
public MainWindow() { InitializeComponent(); this.database= new SQLiteDatabase("MediaLibrary.sqlite"); }
public static void SetToCompleted(string id, SQLiteDatabase db) { ChangeState(id, 3, db); }
public static void Update(string id, string columnId, string newValue, SQLiteDatabase db) { try { Dictionary<string, string> data = new Dictionary<string, string>(); data.Add(columnId, newValue); bool succ = db.Update("Books", data, String.Format("id={0}", id)); if (!succ) { throw new Exception(); } } catch { throw new FSSQLiteException("An unexpected error occured when trying to update a book."); } }
public static void SetInProgress(string id, SQLiteDatabase db) { ChangeState(id, 1, db); }
public static void SetToFailed(string id, SQLiteDatabase db) { ChangeState(id, 2, db); }
private void ExecuteQuery_Button_Click(object sender, RoutedEventArgs e) { if (this.QueryString_Combo.Text.ToUpper() == "SOURCE") { string loadedFilename = ""; OpenFileDialog dlg = new OpenFileDialog(); dlg.FileName = ""; // Default file name dlg.DefaultExt = ".sqlite"; // Default file extension dlg.Filter = "SQLite databases (.sqlite)|*.sqlite"; // Filter files by extension // Show open file dialog box Nullable<bool> result = dlg.ShowDialog(); // Process open file dialog box results if (result == true) { // Open document loadedFilename = dlg.FileName; } if (!String.IsNullOrEmpty(loadedFilename)) { this.database = new SQLiteDatabase(loadedFilename); } } else { ExecuteQuery(this.QueryString_Combo.Text); ComboBoxItem newQuery = new ComboBoxItem(); newQuery.Content = this.QueryString_Combo.Text; if (!this.QueryString_Combo.Items.Contains(newQuery)) { this.QueryString_Combo.Items.Add(newQuery); } } }
public static void SetToJustAdded(string id, SQLiteDatabase db) { ChangeState(id, -7, db); }
/// <summary> /// Insert a book only with a title /// </summary> /// <param name="title"></param> /// <param name="db"></param> public static void InsertBook(string title, SQLiteDatabase db) { try { Dictionary<string, string> data = new Dictionary<string, string>(); data.Add("title", SQLiteDatabase.EscapeSpecialChars(title)); db.Insert("Books", data); } catch (Exception fail) { throw new FSSQLiteException("An error occurred when trying to insert a new book."); } }
public static void UpdateISBN(string id, string ISBN, SQLiteDatabase db) { try { //UPDATE Tasks SET taskStatus=1 WHERE id=(SELECT id FROM Tasks WHERE taskStatus=0 LIMIT 1) db.ExecuteNonQuery(String.Format("UPDATE Tasks SET taskStatus='0', ISBN='{1}' WHERE id={0}", id, SQLiteDatabase.EscapeSpecialChars(ISBN))); } catch (Exception fail) { throw new FSSQLiteException("An error occurred when trying to update a task."); } }
private static void ChangeState(string id, int stateCode, SQLiteDatabase db) { try { int res = db.ExecuteNonQuery(String.Format("UPDATE Tasks SET taskStatus={1} WHERE id={0}", id, stateCode)); } catch (Exception fail) { throw new FSSQLiteException("An error occurred when trying to update a task."); } }
private void InitDb(string datasource) { this.Data = new SQLiteDatabase(datasource); }