//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);
}
