private void Form1_Load(object sender, EventArgs e) { /** * Add load in CDs from the database [X] * Fill in the genre and artist combo boxes based on details from CDs [X] * Set user level to guess [x] * Set Display to match user level [x] * Clean up */ //newUsrLbl.Text = "HEYYYYYY"; //newUsrLbl.Visible = true; this.Size = new Size(550, 450); foreach (Control myPnl in this.Controls) { var temp = myPnl.GetType(); if (myPnl.Name == "cdManagemns") //Not a panel { continue; } myPnl.Location = new Point(0, 10); myPnl.Size = new Size(700, 550); } loginPnl.Enabled = false; loginPnl.Visible = false; AdminPnl.Enabled = false; AdminPnl.Visible = false; addCdPnl.Enabled = false; addCdPnl.Visible = false; //ResultsList.View = View.Details; //ResultsList.GridLines = true; ResultsList.SelectionMode = DataGridViewSelectionMode.FullRowSelect; // SongListView.View = View.Details; //SongListView.GridLines = true; //SongListView.FullRowSelect = true; //Default Menu Strip View [Guess View] currentUserTsr.Visible = false; SwitchToAddCdPanleTsr.Visible = false; using (OleDbConnection conn = new OleDbConnection(pathStr)) { string sqlQuery = "SELECT * FROM CDdtb"; OleDbCommand cmd = new OleDbCommand(sqlQuery, conn); try { conn.Open(); try { OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int temp = (int)reader["AlbumID"]; Cd newCd = new Cd(); newCd.setAlbum((string)reader["Album"]); newCd.setArtist((string)reader["Artist"]); newCd.setGenre((string)reader["Genre"]); newCd.setDate((DateTime)reader["DateStr"]); newCd.setId(temp.ToString()); CdList.AddLast(newCd); if (temp > nextID) { nextID = temp; } } nextID++; } // string nextAlbumID = CdList.L catch (Exception ex) { Debug.WriteLine("Error reading from database\n" + ex.StackTrace.ToString()); } foreach (Cd x in CdList) { if (x.getGenre().Trim() != "") { if (!GenreComboBx.Items.Contains(x.getGenre())) { GenreComboBx.Items.Add(x.getGenre()); } } if (x.getArtist().Trim() != "") { if (!ArtistComboBx.Items.Contains(x.getArtist())) { ArtistComboBx.Items.Add(x.getArtist()); } } } AutoCompleteSearchTB(); } catch (Exception ex) { Debug.WriteLine(ex.StackTrace); Debug.WriteLine(ex.Message); } finally { conn.Close(); } } using (OleDbConnection songConn = new OleDbConnection(songPathStr)) { foreach (Cd x in CdList) { string sqlQuery = @"SELECT * FROM SongListDTB where Album like @album and Artist like @artist"; OleDbCommand cmd = new OleDbCommand(sqlQuery, songConn); cmd.Parameters.AddWithValue("@album", x.getAlbum()); cmd.Parameters.AddWithValue("@artist", x.getArtist()); try { songConn.Open(); try { OleDbDataReader reader = cmd.ExecuteReader(); List <string> tmpLst = new List <string>(); string temp = ""; while (reader.Read()) { temp = (string)reader["Song_Name"]; temp = temp.Trim(); tmpLst.Add(temp); } x.setSongName(tmpLst); } catch { } } catch { } finally { songConn.Close(); } } } }
private void applyChangesToolStripMenuItem_Click(object sender, EventArgs e) { LinkedList <Cd> EditCdList = new LinkedList <Cd>(); foreach (DataGridViewRow x in ResultsList.Rows) { string tempAlbum = (x.Cells["AlbumCol"].Value.ToString()); string tempArtist = (x.Cells["ArtistCol"].Value.ToString()); string tempGenre = (x.Cells["GenreCol"].Value.ToString()); string tempId = (x.Cells["IdCol"].Value.ToString()); StandardizeText(ref tempAlbum); StandardizeText(ref tempArtist); StandardizeText(ref tempGenre); Cd tempCd = new Cd(); tempCd.setAlbum(tempAlbum); tempCd.setArtist(tempArtist); tempCd.setGenre(tempGenre); tempCd.setId(tempId); if (!CdList.Contains(tempCd)) { EditCdList.AddLast(tempCd); } } string UpdCmdStr = @"UPDATE CDdtb SET Album = @Album, Artist = @Artist, Genre = @Genre, DateStr = @DateStr Where AlbumID = @AlbumID"; string SongListUpdCmdStr = "UPDATE SongListDTB SET Album = @Album, Artist = @Artist Where AlbumID = @AlbumID"; using (OleDbConnection conn = new OleDbConnection(pathStr)) { using (OleDbCommand UpdCmd = new OleDbCommand(UpdCmdStr, conn)) { conn.Open(); foreach (Cd x in EditCdList) { UpdCmd.Parameters.Clear(); UpdCmd.Parameters.AddWithValue("@Album", x.getAlbum()); UpdCmd.Parameters.AddWithValue("@Artist", x.getArtist()); UpdCmd.Parameters.AddWithValue("@Genre", x.getGenre()); UpdCmd.Parameters.AddWithValue("@DateStr", x.getDate()); UpdCmd.Parameters.AddWithValue("@AlbumID", x.getID()); UpdCmd.ExecuteNonQuery(); foreach (Cd y in CdList) { if (y.getID() == x.getID()) { y.setAlbum(x.getAlbum()); y.setArtist(x.getArtist()); y.setGenre(x.getGenre()); break; } } } } conn.Close(); //List<string> tmpLst = new List<string>(); using (OleDbConnection SongListConn = new OleDbConnection(songPathStr)) { using (OleDbCommand SongUpdCmd = new OleDbCommand(SongListUpdCmdStr, SongListConn)) { SongListConn.Open(); foreach (Cd x in EditCdList) { SongUpdCmd.Parameters.Clear(); SongUpdCmd.Parameters.AddWithValue("@Album", x.getAlbum()); SongUpdCmd.Parameters.AddWithValue("@Artist", x.getArtist()); SongUpdCmd.ExecuteNonQuery(); } SongListConn.Close(); } } } }
private void AddCDBtn_Click(object sender, EventArgs e) { //TODO Add in a confirmation button bool canAdd = true; string sqlQuery = @"INSERT INTO CDdtb (`Album`,`Artist`,`Genre`,`DateStr`) values (?,?,?,?)"; string addSongs = @"INSERT INTO SongListDTB ( `Album`, `Artist`,`Song_Name`, `AlbumID`) values (?,?,?,?)"; string newAlbum = addAlbumBx.Text.Trim(); StandardizeText(ref newAlbum); string newArtist = addArtistBx.Text.Trim(); StandardizeText(ref newArtist); string newGenre = addGenreBx.Text.Trim(); StandardizeText(ref newGenre); using (OleDbConnection conn = new OleDbConnection(pathStr)) { using (OleDbCommand cmd = new OleDbCommand(sqlQuery, conn)) { foreach (Cd x in CdList) { if (newAlbum == x.getAlbum() && newArtist == x.getArtist()) { MessageBox.Show("CD already exists."); canAdd = false; break; } } if (canAdd) { try { conn.Open(); cmd.Parameters.AddWithValue("@Album", newAlbum); cmd.Parameters.AddWithValue("@Artist", newArtist); cmd.Parameters.AddWithValue("@Genre", newGenre); cmd.Parameters.AddWithValue("@DateStr", this.dateTimePicker1.Value.Date); cmd.ExecuteNonQuery(); cmd.Dispose(); conn.Close(); Cd x = new Cd(); x.setAlbum(newAlbum); x.setArtist(newArtist); x.setDate(this.dateTimePicker1.Value.Date); x.setGenre(newGenre); CdList.AddLast(x); if (newGenre != "") { if (!GenreComboBx.Items.Contains(newGenre)) { GenreComboBx.Items.Add(newGenre); } } if (newArtist != "") { if (!ArtistComboBx.Items.Contains(newArtist)) { ArtistComboBx.Items.Add(newArtist); } } using (OleDbConnection songConn = new OleDbConnection(songPathStr)) { using (OleDbCommand songCmd = new OleDbCommand(addSongs, songConn)) { try { songConn.Open(); songCmd.Parameters.AddWithValue("@Album", x.getAlbum()); songCmd.Parameters.AddWithValue("@Artist", x.getArtist()); List <string> tmpSngLst = new List <string>(); string z = ""; songCmd.Parameters.AddWithValue("@SongName", z); foreach (string y in AddSongBx.Lines) { z = y.Trim(); StandardizeText(ref z); songCmd.Parameters["@SongName"].Value = z; songCmd.Parameters.AddWithValue("@AlbumID", nextID); songCmd.ExecuteNonQuery(); tmpSngLst.Add(y); } nextID++; x.setSongName(tmpSngLst); //foreach(string x in AddSongBx.Text) } catch (Exception ex) { Debug.WriteLine(ex.StackTrace); } finally { songConn.Close(); } } } } catch (Exception ex) { Debug.WriteLine("Error opening file" + ex.StackTrace); } finally { conn.Close(); } } } } }