public void addListToDatabase(Records recs, string pcname, string username, string password)
{
connection = new SqlConnection(@"Data Source=" + pcname + ";Initial Catalog=Timetable;Persist Security Info=True;User ID=" + username + ";Password="******"");
try
{
connection.Open();
int minutes = recs.StartUur % 100;
int hours = recs.StartUur / 100;
TimeSpan startTijd = new TimeSpan(hours, minutes, 0);
int olodId = checkOlod(recs.Deelopleidingsonderdeel.Replace('\'', ' '), recs.Opleidingsonderdeel.Replace('\'', ' '), recs.Studiepunten);
Docent[] docenten = new Docent[recs.Docenten.Length];
int count = 0;
if (recs.Docenten.Length != 0)
{
foreach (string s in recs.Docenten)
{
if (s.Contains(' '))
{
string[] naamvoornaam = s.Split(' ');
if (naamvoornaam.Length == 2)
{
docenten[count] = new Docent(naamvoornaam[0].Replace('\'', ' '), naamvoornaam[1].Replace('\'', ' '));
}
else
{
string achternaam = naamvoornaam[0];
for (int i = 1; i < naamvoornaam.Length-1; i++)
{
achternaam += " "+naamvoornaam[i];
}
docenten[count] = new Docent(achternaam.Replace('\'', ' '), naamvoornaam[naamvoornaam.Length - 1].Replace('\'', ' '));
}
}
else
{
docenten[count] = new Docent(s, string.Empty);
}
count++;
}
}
else
{
docenten[count] = new Docent("info", "artesis");
}
int[] docentId = checkDocent(docenten);
checkOlodDocentRelation(docentId, olodId);
int[] klasid = checkKlas(recs.Klas);
checkOlodKlasRelation(klasid, olodId);
int duur = (recs.Duration%100)+((recs.Duration/100)*60);
string room = null;
foreach (string s in recs.Lokaal)
{
room += s + " ";
}
int dag = recs.Dag;
CultureInfo ciCurr = CultureInfo.CurrentCulture;
int week = ciCurr.Calendar.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
SqlCommand addLes = connection.CreateCommand();
addLes.CommandText = "INSERT INTO Les (tijd, olod_id, duur_in_minuten,lokaal, dag, week) VALUES ('" + startTijd + "'," + olodId + ", " +
+duur + ",'"+room+"'," + dag + "," + week + ")";
addLes.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
finally
{
connection.Close();
}
}
private int[] checkDocent(Docent[] docenten)
{
int[] id = new int[docenten.Length];
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Docent";
SqlDataReader thisReader = command.ExecuteReader();
List<string> email = new List<string>();
while (thisReader.Read())
{
email.Add(thisReader.GetString(3));
}
thisReader.Close();
int count = 0;
Boolean found = false;
foreach (Docent temp in docenten)
{
foreach (string a in email)
{
if (a == temp.Email)
found = true;
}
if (!found || email.Count == 0)
{
addDocentToDatabase(temp.Name, temp.Lastname, temp.Email);
}
found = false;
}
SqlCommand getDocentId = connection.CreateCommand();
foreach (Docent a in docenten)
{
getDocentId.CommandText = "select docent_id from Docent where email = '" + a.Email + "'";
SqlDataReader idReader = getDocentId.ExecuteReader();
while (idReader.Read())
{
id[count] = idReader.GetInt32(0);
}
count++;
idReader.Close();
}
return id;
}