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 = 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); //Hier werk ik met gegenereerde id! } 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)"; using (SqlCommand command = connection.CreateCommand()) { connection.Open(); try { command.Parameters.Add(new SqlParameter("@Cursusnaam", System.Data.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(); } } }
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 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(); } } }
public void voegCursusToe(Cursus cursus) { Cursussen.Add(cursus); }