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();
}
}
}
