public Cursus GeefCursus(int id)
{
SqlConnection connection = getConnection();
string query = "SELECT * FROM dbo.cursusSQL WHERE id=@id";
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = query;
SqlParameter paramID = new SqlParameter();
paramID.ParameterName = "@Id";
paramID.DbType = System.Data.DbType.Int32;
paramID.Value = id;
command.Parameters.Add(paramID);
connection.Open();
try
{
SqlDataReader reader = command.ExecuteReader();
reader.Read();
Cursus cursus = new Cursus((int)reader["id"], (string)reader["cursusnaam"]);
reader.Close();
return(cursus);
}
catch (Exception ex)
{
Console.WriteLine(ex);
return(null);
}
finally
{
connection.Close();
}
}
}
public void VoegCursusToe(Cursus c)
{
SqlConnection connection = getConnection();
string query = "INSERT INTO dbo.cursusSQL (cursusnaam) VALUES(@cursusnaam)"; // werkt altijd met parameters , niet veilig wegens sql injection
using (SqlCommand command = connection.CreateCommand())
{
connection.Open();
try
{
command.Parameters.Add(new SqlParameter("@cursusnaam", SqlDbType.NVarChar));
command.CommandText = query;
command.Parameters["@cursusnaam"].Value = c.cursusnaam;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
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();
}
}
}
public void UpdateCursus(Cursus c)
{
SqlConnection connection = getConnection();
Cursus cursusDB = GeefCursus(c.id);
string query = "SELECT * FROM dbo.cursusSQL WHERE Id=@id";
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
try
{
SqlParameter paramId = new SqlParameter();
paramId.ParameterName = "@Id";
paramId.DbType = DbType.Int32;
paramId.Value = c.id;
SqlCommandBuilder builder = new SqlCommandBuilder();
builder.DataAdapter = adapter;
adapter.SelectCommand = new SqlCommand();
adapter.SelectCommand.CommandText = query;
adapter.SelectCommand.Connection = connection;
adapter.SelectCommand.Parameters.Add(paramId);
adapter.UpdateCommand = builder.GetUpdateCommand();
DataTable table = new DataTable();
adapter.Fill(table);
table.Rows[0]["cursusnaam"] = c.cursusnaam;
adapter.Update(table);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
connection.Close();
}
}
}