public List <ExtraNote> Get() { List <ExtraNote> listOfExtraNotes = new List <ExtraNote>(); try { using (var connection = new MySqlConnection(connectionString)) { connection.Open(); using (var cmd = new MySqlCommand()) { cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "Select * from extranotes"; cmd.Connection = connection; MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ExtraNote extraNote = new ExtraNote(); extraNote.id = Convert.ToInt32(reader.GetValue(0)); extraNote.intervieweeid = Convert.ToInt32(reader.GetValue(1)); extraNote.columnname = reader.GetValue(2).ToString(); extraNote.note = reader.GetValue(3).ToString(); listOfExtraNotes.Add(extraNote); } reader.Close(); } } } catch (MySqlException ex) { Console.WriteLine(ex.Message); } return(listOfExtraNotes); }
public List <Interviewee> GetInterviewees() { List <Interviewee> listOfInterviewee = new List <Interviewee>(); Debug.WriteLine("get interviewee"); try { using (var connection = new MySqlConnection(connectionString)) { connection.Open(); Debug.WriteLine("connection is opened"); using (var cmd = new MySqlCommand()) { cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "Select * from interviewee"; cmd.Connection = connection; MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Interviewee interviewee = new Interviewee(); interviewee.extranotes = new List <ExtraNote>(); interviewee.id = Convert.ToInt32(reader.GetValue(0)); interviewee.firstname = reader.GetValue(1).ToString(); interviewee.lastname = reader.GetValue(2).ToString(); interviewee.email = reader.GetValue(3).ToString(); interviewee.university = reader.GetValue(4).ToString(); interviewee.githublink = reader.GetValue(5).ToString(); interviewee.bamboolink = reader.GetValue(6).ToString(); interviewee.backendnote = Convert.ToInt32(reader.GetValue(7)); interviewee.frontend = Convert.ToInt32(reader.GetValue(8)); interviewee.algorithms = Convert.ToInt32(reader.GetValue(9)); interviewee.specialnote = reader.GetValue(10).ToString(); // the notes for this interviewee using (var connectionNote = new MySqlConnection(connectionString)) { connectionNote.Open(); using (var cmdNote = new MySqlCommand()) { cmdNote.CommandType = System.Data.CommandType.Text; cmdNote.CommandText = "Select * from extranotes"; cmdNote.Connection = connectionNote; MySqlDataReader readerNote = cmdNote.ExecuteReader(); while (readerNote.Read()) { ExtraNote extraNote = new ExtraNote(); extraNote.id = Convert.ToInt32(readerNote.GetValue(0)); extraNote.intervieweeid = Convert.ToInt32(readerNote.GetValue(1)); extraNote.columnname = readerNote.GetValue(2).ToString(); extraNote.note = readerNote.GetValue(3).ToString(); // here we check whether the extra note is for this interviewee or not if (interviewee.id == extraNote.intervieweeid) { interviewee.extranotes.Add(extraNote); } } readerNote.Close(); } } Debug.WriteLine(interviewee.firstname + " is added"); listOfInterviewee.Add(interviewee); } reader.Close(); } } } catch (MySqlException ex) { Debug.WriteLine(ex); } return(listOfInterviewee); }
public void EditExtraNote(List <ExtraNote> extraNotes) { List <int> listOfIds = new List <int>(); try { using (var connection = new MySqlConnection(connectionString)) { connection.Open(); // getting the extra columns if (extraNotes.Count > 0) { using (var cmd = new MySqlCommand()) { cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "Select * from extranotes where intervieweeid = " + extraNotes[0].intervieweeid; cmd.Connection = connection; MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ExtraNote extraNote = new ExtraNote(); extraNote.id = Convert.ToInt32(reader.GetValue(0)); extraNote.intervieweeid = Convert.ToInt32(reader.GetValue(1)); extraNote.columnname = reader.GetValue(2).ToString(); extraNote.note = reader.GetValue(3).ToString(); listOfIds.Add(extraNote.id); } reader.Close(); } } // so the extra column for this interviewee is new // if interviewee is created without this extra column we will create // extrar columns for this interviewee with placeholder values if (listOfIds.Count() < extraNotes.Count()) { int difference = extraNotes.Count() - listOfIds.Count(); // how many extra column the interviewee lack for (int i = 0; i < difference; ++i) { // Here I need to add a new extra notes to this particular interviewee of which is the object to be edited using (var connectionExtraNote = new MySqlConnection(connectionString)) { connectionExtraNote.Open(); using (var cmdExtraNote = new MySqlCommand()) { cmdExtraNote.CommandType = System.Data.CommandType.Text; cmdExtraNote.CommandText = "insert into extranotes (intervieweeid, columnname, note) " + "values (@intervieweeid, @columnname, @note)"; cmdExtraNote.Connection = connection; int intervieweeId = extraNotes[0].intervieweeid; string columnname = extraNotes[0].columnname; cmdExtraNote.Parameters.AddWithValue("@intervieweeid", intervieweeId); cmdExtraNote.Parameters.AddWithValue("@columnname", columnname); cmdExtraNote.Parameters.AddWithValue("@note", "--"); // placeholder value untill it is edited Debug.WriteLine("placeholder added for interviewee witd id of " + intervieweeId); cmdExtraNote.ExecuteNonQuery(); } } } // now we have as much extra columns as needed to go further into this operation // we need to fill the list of ids again, and before doing this, we msut empty the lsit first listOfIds.Clear(); using (var cmd = new MySqlCommand()) { cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "Select * from extranotes where intervieweeid = " + extraNotes[0].intervieweeid; cmd.Connection = connection; MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ExtraNote extraNote = new ExtraNote(); extraNote.id = Convert.ToInt32(reader.GetValue(0)); extraNote.intervieweeid = Convert.ToInt32(reader.GetValue(1)); extraNote.columnname = reader.GetValue(2).ToString(); extraNote.note = reader.GetValue(3).ToString(); listOfIds.Add(extraNote.id); } cmd.Parameters.Clear(); reader.Close(); } } // now we have the listOfIds // editing using (var cmd = new MySqlCommand()) { int index = 0; foreach (var extraNote in extraNotes) { int noteIndex = listOfIds.ElementAt(index); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "update extranotes set " + "columnname = @columnname ," + "note = @note " + "where id = @id"; cmd.Connection = connection; cmd.Parameters.AddWithValue("@columnname", extraNote.columnname); cmd.Parameters.AddWithValue("@note", extraNote.note); cmd.Parameters.AddWithValue("@id", noteIndex); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); index++; } } } } catch (MySqlException ex) { Debug.WriteLine(ex.Message); } }