static void Main(string[] args)
{
IDFactory idF = new IDFactory(0, 100, 5000); //klant,bestelling,product
var kM = new /*Db*/ KlantManager();
var pM = new ProductManager();
var bM = new BestellingManager();
pM.VoegProductToe(ProductFactory.MaakProduct("product 1", 10.0, idF));
pM.VoegProductToe(ProductFactory.MaakProduct("product 2", 12.0, idF));
pM.VoegProductToe(ProductFactory.MaakProduct("product 3", 13.0, idF));
foreach (var x in pM.GeefProducten())
{
Console.WriteLine(x);
}
kM.VoegKlantToe(KlantFactory.MaakKlant("klant 1", "adres 1", idF));
kM.VoegKlantToe(KlantFactory.MaakKlant("klant 2", "adres 2", idF));
foreach (var x in kM.GeefKlanten()) //Console.WriteLine(x);
{
x.Show();
}
bM.VoegBestellingToe(BestellingFactory.MaakBestelling(idF));
bM.VoegBestellingToe(BestellingFactory.MaakBestelling(kM.GeefKlant(1), idF));
Bestelling b = bM.GeefBestelling(101);
b.VoegProductToe(pM.GeefProduct("product 2"), 8);
b.VoegProductToe(pM.GeefProduct("product 1"), 7);
Console.WriteLine($"Prijs:{b.Kostprijs()}, {b.PrijsBetaald}");
b.ZetBetaald();
Console.WriteLine($"Prijs:{b.Kostprijs()}, {b.PrijsBetaald}");
foreach (var x in bM.GeefBestellingen()) //Console.WriteLine(x);
{
x.Show();
}
foreach (var x in kM.GeefKlanten()) //Console.WriteLine(x);
{
x.Show();
}
Console.WriteLine("-----------------");
Klant k1 = kM.GeefKlant(1);
k1.Show();
k1.VoegToeBestelling(b);
k1.Show();
}
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();
}
}
}
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();
}
}
}
}