public void VoegKlasToe(Klas k)
{
SqlConnection connection = getConnection();
string queryK = "INSERT INTO dbo.klasSQL (Id, klasnaam) VALUES(@id, @Klasnaam)";
using (SqlCommand command = connection.CreateCommand())
{
connection.Open();
try
{
command.Parameters.Add(new SqlParameter("@Klasnaam", SqlDbType.NVarChar));
command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
command.CommandText = queryK;
command.Parameters["@Klasnaam"].Value = k.KlasNaam;
command.Parameters["@id"].Value = k.Id; //Hier werk ik met eigen id
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
}
public Klas GeefKlas(int klasId)
{
SqlConnection connection = getConnection();
string querySt = "Select * FROM dbo.klasSQL WHERE id=@Id";
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = querySt;
SqlParameter paramId = new SqlParameter();
paramId.ParameterName = "@Id";
paramId.DbType = DbType.Int32;
paramId.Value = klasId;
command.Parameters.Add(paramId);
connection.Open();
try
{
SqlDataReader reader = command.ExecuteReader();
reader.Read();
Klas klas = new Klas((int)reader["Id"], (string)reader["klasnaam"]);
reader.Close();
return(klas);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(null);
}
finally
{
connection.Close();
}
}
}
public Student GeefStudent(int id)
{
SqlConnection connection = getConnection();
string queryS = "SELECT * FROM dbo.studentSQL WHERE id = @id";
string querySC = "SELECT * FROM [adresBeheer].[dbo].[cursusSQL] t1,[adresBeheer].[dbo].[student_cursusSQL] t2"
+ "WHERE t1.Id = t2.cursusID and t2.studentid = @id";
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = queryS;
SqlParameter paramId = new SqlParameter();
paramId.ParameterName = "@id";
paramId.DbType = DbType.Int32;
paramId.Value = id;
command.Parameters.Add(paramId);
connection.Open();
try
{
SqlDataReader reader = command.ExecuteReader();
reader.Read();
int studentId = (int)reader["Id"];
string studentnaam = (string)reader["naam"];
int klasId = (int)reader["klasId"];
reader.Close();
Klas klas = GeefKlas(klasId);
Student student = new Student(studentId, studentnaam, klas);
command.CommandText = querySC;
reader = command.ExecuteReader();
while (reader.Read())
{
Cursus cursus = new Cursus(reader.GetInt32(0), reader.GetString(1));
student.voegCursusToe(cursus);
}
reader.Close();
return(student);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(null);
}
finally
{
connection.Close();
}
}
}
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
DataBeheer db = new DataBeheer(@"Data Source=DESKTOP-HT91N8R\SQLEXPRESS;Initial Catalog=TestDatabank;Integrated Security=True");
//-----------------------Cursussen toevoegen----------------------//
Cursus kaas = new Cursus(1, "kaasCursus");
Cursus poes = new Cursus(2, "poesCursus");
//db.VoegCursusToe(kaas);
//db.VoegCursusToe(poes);
//------------------------Klassen toevoegen-----------------------//
Klas klas1 = new Klas(1, "klas1");
Klas klas2 = new Klas(2, "klas2");
//db.VoegKlasToe(klas1);
//db.VoegKlasToe(klas2);
Student st1 = new Student("st1", klas1);
Student st2 = new Student("st2", klas2);
Student st3 = new Student("st3", klas2);
Student st4 = new Student("st4", klas2);
//db.VoegStudentToe(st1);
//db.VoegStudentToe(st2);
//db.VoegStudentToe(st3);
//db.VoegStudentToe(st4);
//----------------------Cursus updaten-------------------------//
//Cursus curs = new Cursus(1, "curs");
//db.UpdateCursus(curs);
//--------------Testen VoegStudentMetCursussenToe--------------------//
//db.VoegStudentMetCursussenToe(st1);
//db.VoegStudentMetCursussenToe(st2);
//db.VoegStudentMetCursussenToe(st3);
//db.VoegStudentMetCursussenToe(st4);
}
public Student(int id, string naam, Klas klas)
{
StudentId = id;
Klas = klas;
Naam = naam;
}
public Student(string naam, Klas klas)
{
Klas = klas;
Naam = naam;
}