public void VoegBestellingToe(Bestelling bestelling) { if (bestelling.GeefProducten().Count() != 0) { SqlConnection connection = getConnection(); string query = "INSERT INTO dbo.bestelling (betaald, prijsBetaald, tijdstip, klantID) OUTPUT INSERTED.bestellingID VALUES (@betaald,@prijsBetaald,@tijdstip,@klantID)"; string query2 = "INSERT INTO dbo.bestellingDetails(productID,bestellingID,aantal) VALUES (@productID,@bestellingID,@aantal)"; using (SqlCommand command1 = connection.CreateCommand()) using (SqlCommand command2 = connection.CreateCommand()) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); command1.Transaction = transaction; command2.Transaction = transaction; try { //bestelling toevoegen command1.Parameters.Add("@betaald", SqlDbType.Bit).Value = bestelling.Betaald; command1.Parameters.Add("@prijsBetaald", SqlDbType.Float).Value = bestelling.Kostprijs(); command1.Parameters.Add("@tijdstip", SqlDbType.DateTime).Value = bestelling.Tijdstip; if (bestelling.Klant != null) { command1.Parameters.Add("@klantID", SqlDbType.Int).Value = bestelling.Klant.KlantId; } command1.CommandText = query; int newID = (int)command1.ExecuteScalar(); //producten toevoegen command2.Parameters.Add(new SqlParameter("@productID", SqlDbType.Int)); command2.Parameters.Add(new SqlParameter("@aantal", SqlDbType.Int)); command2.Parameters.Add("@bestellingID", SqlDbType.Int).Value = newID; command2.CommandText = query2; foreach (KeyValuePair <Product, int> kvp in bestelling.GeefProducten()) { command2.Parameters["@productID"].Value = kvp.Key.ProductId; command2.Parameters["@aantal"].Value = kvp.Value; command2.ExecuteNonQuery(); } transaction.Commit(); } catch (Exception ex) { Console.WriteLine(ex); } finally { connection.Close(); } } } }
public void UpdateBestelling(Bestelling bestelling) { SqlConnection connection = getConnection(); string query = "SELECT * FROM dbo.bestelling b WHERE b.bestellingID = @bestellingID"; string query2 = "DELETE FROM dbo.bestellingDetails WHERE bestellingID = @bestellingID"; string query3 = "INSERT INTO dbo.bestellingDetails(productID,bestellingID,aantal) VALUES (@productID,@bestellingID,@aantal)"; using (SqlDataAdapter adapter = new SqlDataAdapter()) using (SqlCommand command = connection.CreateCommand()) { connection.Open(); try { //verwijder alles uit bestellingDetails met overeenkomend bestellingID command.Parameters.Add("@bestellingID", SqlDbType.Int).Value = bestelling.BestellingId; command.CommandText = query2; command.ExecuteNonQuery(); //voeg nieuwe zaken toe aan bestellingDetails command.Parameters.Add(new SqlParameter("@productID", SqlDbType.Int)); command.Parameters.Add(new SqlParameter("@aantal", SqlDbType.Int)); command.CommandText = query3; foreach (KeyValuePair <Product, int> kvp in bestelling.GeefProducten()) { command.Parameters["@productID"].Value = kvp.Key.ProductId; command.Parameters["@aantal"].Value = kvp.Value; command.ExecuteNonQuery(); } if (bestelling.Betaald) { bestelling.ZetBetaald(); } bestelling.Kostprijs(); //update bestelling in de database SqlParameter paramId = new SqlParameter(); paramId.ParameterName = "@bestellingID"; paramId.DbType = DbType.Int32; paramId.Value = bestelling.BestellingId; 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]["betaald"] = bestelling.Betaald; table.Rows[0]["prijsBetaald"] = bestelling.Kostprijs(); table.Rows[0]["tijdstip"] = bestelling.Tijdstip; table.Rows[0]["klantID"] = bestelling.Klant.KlantId; adapter.Update(table); } catch (Exception ex) { Console.WriteLine(ex); } finally { connection.Close(); } } }