78 lines
2.4 KiB
C#
78 lines
2.4 KiB
C#
|
using System.Xml.Serialization;
|
|||
|
|
using Microsoft.Data.Sqlite;
|
|||
|
|
Console.WriteLine("Willkommen zu Aufgabe13");
|
|||
|
|
|
|||
|
|
|
|||
|
|
List<Vertreter> vertreterListe;
|
|||
|
|
XmlSerializer serializer = new XmlSerializer(typeof(List<Vertreter>));
|
|||
|
|
using (var stream = new FileStream(@".\vertreter.xml", FileMode.Open))
|
|||
|
|
{
|
|||
|
|
vertreterListe = (List<Vertreter>)serializer.Deserialize(stream);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using (var connection = new SqliteConnection("Data Source=sales.db3"))
|
|||
|
|
{
|
|||
|
|
connection.Open();
|
|||
|
|
|
|||
|
|
InsertDataIfNotExists(connection, vertreterListe);
|
|||
|
|
|
|||
|
|
PrintAllData(connection);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
void InsertDataIfNotExists(SqliteConnection connection, List<Vertreter> vertreterList)
|
|||
|
|
{
|
|||
|
|
string insertQuery = @"
|
|||
|
|
INSERT OR IGNORE INTO Vertreter (Vorname, Nachname, Firma, Gebiet, Umsatz)
|
|||
|
|
VALUES (@Vorname, @Nachname, @Firma, @Gebiet, @Umsatz)";
|
|||
|
|
|
|||
|
|
using (var transaction = connection.BeginTransaction())
|
|||
|
|
{
|
|||
|
|
foreach (var vertreter in vertreterList)
|
|||
|
|
{
|
|||
|
|
using (var command = new SqliteCommand(insertQuery, connection))
|
|||
|
|
{
|
|||
|
|
command.Parameters.AddWithValue("@Vorname", vertreter.Vorname);
|
|||
|
|
command.Parameters.AddWithValue("@Nachname", vertreter.Nachname);
|
|||
|
|
command.Parameters.AddWithValue("@Firma", vertreter.Firma);
|
|||
|
|
command.Parameters.AddWithValue("@Gebiet", vertreter.Gebiet);
|
|||
|
|
command.Parameters.AddWithValue("@Umsatz", vertreter.Umsatz);
|
|||
|
|
|
|||
|
|
command.ExecuteNonQuery();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
transaction.Commit();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
void PrintAllData(SqliteConnection connection)
|
|||
|
|
{
|
|||
|
|
string selectQuery = "SELECT Vorname, Nachname, Firma, Gebiet, Umsatz FROM Vertreter";
|
|||
|
|
|
|||
|
|
using (var command = new SqliteCommand(selectQuery, connection))
|
|||
|
|
using (var reader = command.ExecuteReader())
|
|||
|
|
{
|
|||
|
|
while (reader.Read())
|
|||
|
|
{
|
|||
|
|
string vorname = reader.GetString(0);
|
|||
|
|
string nachname = reader.GetString(1);
|
|||
|
|
string firma = reader.GetString(2);
|
|||
|
|
string gebiet = reader.GetString(3);
|
|||
|
|
decimal umsatz = reader.GetDecimal(4);
|
|||
|
|
|
|||
|
|
Console.WriteLine($"{vorname} {nachname}, {firma}, {gebiet}: {umsatz}");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
[Serializable]
|
|||
|
|
public class Vertreter
|
|||
|
|
{
|
|||
|
|
public string Vorname { get; set; }
|
|||
|
|
public string Nachname { get; set; }
|
|||
|
|
public string Firma { get; set; }
|
|||
|
|
public string Gebiet { get; set; }
|
|||
|
|
public decimal Umsatz { get; set; }
|
|||
|
|
}
|