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