private void CreatePerson(Person person) { using (var connection = new SqlConnection(ConnectionString)) { connection.Open(); var command = new SqlCommand( string.Format( "INSERT INTO person (vorname, nachname, strasse, plz, ort) OUTPUT INSERTED.IDENTITYCOL VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')", person.Vorname, person.Nachname, person.Strasse, person.Plz, person.Ort), connection); person.PersonId = (int)command.ExecuteScalar(); foreach (var runnerSponsor in person.Sponsors) { if (this.DoesRunnerSponsorExists(runnerSponsor)) { this.UpdateRunnerSponsor(runnerSponsor); } else { this.CreateRunnerSponsor(runnerSponsor); } } } }
public void SavePerson(Person person) { if (DoesPersonExists(person.PersonId)) { this.UpdatePerson(person); return; } this.CreatePerson(person); }
private Person GetPersonWithoutSponsors(int personId) { var person = new Person(); var connection = new SqlConnection(ConnectionString); connection.Open(); var command = new SqlCommand( "SELECT personid, vorname, nachname, strasse, plz, ort FROM person WHERE personid = " + personId, connection); var reader = command.ExecuteReader(); while (reader.Read()) { person.PersonId = Convert.ToInt32(reader[0]); person.Vorname = reader[1].ToString(); person.Nachname = reader[2].ToString(); person.Strasse = reader[3].ToString(); person.Plz = reader[4].ToString(); person.Ort = reader[5].ToString(); } reader.Close(); connection.Close(); return person; }
private void UpdatePerson(Person person) { var connection = new SqlConnection(ConnectionString); connection.Open(); var command = new SqlCommand( string.Format( "UPDATE person SET vorname = '{0}', nachname = '{1}', strasse = '{2}', plz = '{3}', ort = '{4}' WHERE personid = {5}", person.Vorname, person.Nachname, person.Strasse, person.Plz, person.Ort, person.PersonId), connection); command.ExecuteNonQuery(); var sponsorIds = this.GetAllSponsorIdsInDatabase(person.PersonId); foreach (var sponsorId in sponsorIds.Where(sponsorId => !person.Sponsors.Select(sponsor => sponsor.SponsorId).Contains(sponsorId))) { this.DeleteSponsor(sponsorId, person.PersonId); } foreach (var runnerSponsor in person.Sponsors) { if (DoesRunnerSponsorExists(runnerSponsor)) { this.UpdateRunnerSponsor(runnerSponsor); } else { if (!this.DoesPersonExists(runnerSponsor.SponsorId)) { this.CreatePerson(runnerSponsor.Sponsor); } runnerSponsor.SponsorId = runnerSponsor.Sponsor.PersonId; this.CreateRunnerSponsor(runnerSponsor); } } connection.Close(); }
private void FillSponsors(Person person) { var connection = new SqlConnection(ConnectionString); connection.Open(); var command = new SqlCommand( "SELECT sponsorid, betrag FROM runnersponsor WHERE runnerid =" + person.PersonId, connection); var reader = command.ExecuteReader(); while (reader.Read()) { person.Sponsors.Add(new RunnerSponsor { RunnerId = person.PersonId, SponsorId = Convert.ToInt32(reader[0]), Runner = person, Betrag = Convert.ToInt32(reader[1]) }); } reader.Close(); foreach (var runnerSponsor in person.Sponsors) { runnerSponsor.Sponsor = this.GetPersonWithoutSponsors(runnerSponsor.SponsorId); } connection.Close(); }