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