public List <AlternativeAddress> GetPersonAlternativeAddresses(ref Person person) { var sqlcmd = @"SELECT TOP 1 * FROM [AlternativeAddress] WHERE ([Person] = @PID)"; using (var cmd = new SqlCommand(sqlcmd, OpenConnection)) { cmd.Parameters.AddWithValue("@PID", person.PersonID); SqlDataReader rdr = null; rdr = cmd.ExecuteReader(); List <AlternativeAddress> listPerAltAddr = new List <AlternativeAddress>(); while (rdr.Read()) { var altAddr = new AlternativeAddress { AAID = (int)rdr["AddrId"], AAType = (string)rdr["AltAType"], // type = work,summerhouse,etc. //Address = (Address)rdr["Address"], //Persons = (Person)rdr["PersonsResiding"] }; listPerAltAddr.Add(altAddr); } return(listPerAltAddr); } }
public async Task <IHttpActionResult> PutAlternativeAddress(int id, AlternativeAddress alternativeAddress) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } if (id != alternativeAddress.Id) { return(BadRequest()); } _unitOfWork.AltAddressRepository.Add(alternativeAddress); try { _unitOfWork.Save(); } catch (DbUpdateConcurrencyException) { if (!AlternativeAddressExists(id)) { return(NotFound()); } else { throw; } } return(StatusCode(HttpStatusCode.NoContent)); }
/**************** Alternative Address CRUD ****************/ public void AddAlternativeAddressPerson(ref AlternativeAddress mAAddress) { // prepare command string using paramters in string and returning the given identity foreach (var a in mAAddress.attachedPerson) { string insertStringParam = @"INSERT INTO [AlternativeAddressPersons] (Person_personId, AlternativeAddress_AAId) VALUES (@personId, @AAId)"; using (SqlCommand cmd = new SqlCommand(insertStringParam, OpenConnection)) { // Get your parameters ready cmd.Parameters.AddWithValue("@personId", a.personId); cmd.Parameters.AddWithValue("@AAId", mAAddress.AAId); var id = (int)cmd.ExecuteNonQuery(); //Returns the identity of the new tuple/record } } }
public async Task <IHttpActionResult> GetAlternativeAddress(int id) { AlternativeAddress alternativeAddress = _unitOfWork.AltAddressRepository.GetById(id); if (alternativeAddress == null) { return(NotFound()); } return(Ok(alternativeAddress)); }
public async Task <IHttpActionResult> PostAlternativeAddress(AlternativeAddress alternativeAddress) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } _unitOfWork.AltAddressRepository.Add(alternativeAddress); _unitOfWork.Save(); return(CreatedAtRoute("DefaultApi", new { id = alternativeAddress.Id }, alternativeAddress)); }
public void DeleteAlternativeAddressDB(ref AlternativeAddress AA) { string DeleteALtAddress = @"DELETE FROM AlternativeAddress WHERE (AAID = @AAId)"; using (SqlCommand cmd = new SqlCommand(DeleteALtAddress, OpenConnection)) { cmd.Parameters.AddWithValue("@AAId", AA.AAID); var AAid = (int)cmd.ExecuteNonQuery(); //Returns the identity of the new tuple/record AA = null; } }
public void DeleteAlternativeAddress(ref AlternativeAddress mAAddress) { string deleteString = @"DELETE FROM AlternativeAddress WHERE (AAId = @AAId)"; using (SqlCommand cmd = new SqlCommand(deleteString, OpenConnection)) { cmd.Parameters.AddWithValue("@AAId", mAAddress.AAId); var id = (int)cmd.ExecuteNonQuery(); } mAAddress = null; }
// CRUD operation on an Alternative Address #region Create, Update, Delete an Alternative Address #region Create Alternative Address public void CreateAlternativeAddressDB(ref AlternativeAddress AA) { string CreateAltAddress = @"INSERT INTO [AlternativeAddress] (PersonID, AddressID, AAType) OUTPUT INSERTED.AAID VALUES (@AltAType, @PersonId, @AddressId)"; using (SqlCommand cmd = new SqlCommand(CreateAltAddress, OpenConnection)) { cmd.Parameters.AddWithValue("@PersonId", AA.PersonID); cmd.Parameters.AddWithValue("@AddressId", AA.AddressID); cmd.Parameters.AddWithValue("@AltAType", AA.AAType); AA.AAID = (int)cmd.ExecuteNonQuery(); //Returns the identity of the new tuple/record } }
public void UpdateAlternativeAddressDB(ref AlternativeAddress AA) { string UpdateAltAddr = @"UPDATE AA SET AAType = @AltAType, PersonID = @PersonId, AddressID = @AddressId WHERE AAID = @AltAddressId"; using (SqlCommand cmd = new SqlCommand(UpdateAltAddr, OpenConnection)) { // Get your parameters ready cmd.Parameters.AddWithValue("@AltAType", AA.AAType); cmd.Parameters.AddWithValue("@PersonId", AA.PersonID); cmd.Parameters.AddWithValue("@AddressId", AA.AddressID); var Pid = (int)cmd.ExecuteNonQuery(); //Returns the identity of the new tuple/record } }
public async Task <IHttpActionResult> DeleteAlternativeAddress(int id) { // AlternativeAddress alternativeAddress = await db.AlternativeAddresses.FindAsync(id); AlternativeAddress alternativeAddress = _unitOfWork.AltAddressRepository.GetById(id); if (alternativeAddress == null) { return(NotFound()); } //db.AlternativeAddresses.Remove(alternativeAddress); //await db.SaveChangesAsync(); _unitOfWork.AltAddressRepository.Delete(alternativeAddress); _unitOfWork.Save(); return(Ok(alternativeAddress)); }
public void UpdateAlternativeAddress(ref AlternativeAddress mAAddress) { // prepare command string string updateString = @"UPDATE AlternativeAddress SET attachedAddress_addressId = @addressId, addressType = @addressType WHERE AAId = @AAId"; using (SqlCommand cmd = new SqlCommand(updateString, OpenConnection)) { // Get your parameters ready cmd.Parameters.AddWithValue("@addressId", mAAddress.attachedAddress.addressId); cmd.Parameters.AddWithValue("@addressType", mAAddress.addressType); cmd.Parameters.AddWithValue("@AAId", mAAddress.AAId); var id = (int)cmd.ExecuteNonQuery(); } foreach (var a in mAAddress.attachedPerson) { string updateString2 = @"UPDATE AlternativeAddressPersons SET Person_personId = @personId WHERE AlternativeAddress_AAId = @AAId"; using (SqlCommand cmd = new SqlCommand(updateString2, OpenConnection)) { // Get your parameters ready cmd.Parameters.AddWithValue("@personId", a.personId); cmd.Parameters.AddWithValue("@AAId", mAAddress.AAId); var id = (int)cmd.ExecuteNonQuery(); } } }
public void AddAlternativeAddress(ref AlternativeAddress mAAddress) { string sqlCmd = @"INSERT INTO [AlternativeAddress] (attachedAddress_addressId, addressType) OUTPUT INSERTED.AAId VALUES (@addressId, @addressType)"; using (SqlCommand cmd = new SqlCommand(sqlCmd, OpenConnection)) { // Get your parameters ready cmd.Parameters.AddWithValue("@addressId", mAAddress.attachedAddress.addressId); cmd.Parameters.AddWithValue("@addressType", mAAddress.addressType); mAAddress.AAId = (int)cmd.ExecuteScalar(); //Returns the identity of the new tuple/record } foreach (var a in mAAddress.attachedPerson) { string insertStringParam = @"INSERT INTO [AlternativeAddressPersons] (Person_personId, AlternativeAddress_AAId) VALUES (@personId, @AAId)"; using (SqlCommand cmd = new SqlCommand(insertStringParam, OpenConnection)) { // Get your parameters ready cmd.Parameters.AddWithValue("@personId", a.personId); cmd.Parameters.AddWithValue("@AAId", mAAddress.AAId); var id = (int)cmd.ExecuteNonQuery(); //Returns the identity of the new tuple/record } } }
public void GetFullContactPersonTreeDB(ref Person p) { string fullPersonkartotek = @"SELECT Address.AddressID, Address.StreetName, Address.HouseNumber, City.CityID, City.CityName, City.PostNumber, City.Country, Person.PersonID, Person.FirstName, Person.MiddleName, Person.LastName, Telefon.TelefonID, Telefon.Number, Telefon.TelefonType, Email.EmailID, Email.EmailAddress, Notes.NotesID, Notes.NotesText FROM Address INNER JOIN City ON City.CityID = Address.CityID INNER JOIN Person ON Address.AddressID = Person.AddressID INNER JOIN Telefon ON Telefon.PersonID = Person.PersonID INNER JOIN Email ON Email.PersonID = Person.PersonID INNER JOIN Notes ON Notes.PersonID = Person.PersonID WHERE Person.PersonID = @PersonID"; using (SqlCommand cmd = new SqlCommand(fullPersonkartotek, OpenConnection)) { cmd.Parameters.AddWithValue("@PersonID", p.PersonID); SqlDataReader rdr = null; rdr = cmd.ExecuteReader(); int personCount = 0; int addressCount = 0; int altAddressCount = 0; long personid = 0; long addid = 0; Person person = new Person(); Address address = null; person.PrimaryAddress = new Address { }; person.AlternativeAddresses = new List <AlternativeAddress>(); while (rdr.Read()) { long pid = (long)rdr["PersonId"]; if (personid != pid) { personCount++; person.PersonID = pid; personid = person.PersonID; person.FirstName = (string)rdr["FirstName"]; person.LastName = (string)rdr["LastName"]; //person.Emails = (string)rdr["EmailAddress"]; //person.Notes = (string)rdr["Notes"]; } if (!rdr.IsDBNull(5)) { addressCount++; long addressid = (long)rdr["AddressID"]; if (addid != addressid) { address = new Address { PersonsPrimary = new List <Person> { }, AddressID = addressid }; person.PrimaryAddress = address; } if (address != null) { addid = address.AddressID; address.StreetName = (string)rdr["StreetName"]; address.HouseNumber = (string)rdr["HouseNumber"]; address.Town = (City)rdr["Town"]; } } if (!rdr.IsDBNull(12)) { altAddressCount++; AlternativeAddress aa = new AlternativeAddress() { AAID = (long)rdr["AlternativeAddressID"], AAType = (string)rdr["AltAddressType"], //Persons = (Person)rdr["Persons"], //Address = (Address)rdr["Address"] }; address.AlternativePerson.Add(aa); } } } }
public void ProgramApp() { PersonkartotekDBUtil personUtil = new PersonkartotekDBUtil(); City city = new City() // city { CityID = 1, CityName = "Aarhus", Country = "DK", PostNumber = "8000" }; personUtil.CreateCityDB(ref city); Address primaryAddr = new Address() // primary address { AddressID = 1, StreetName = "Finlandsgade", HouseNumber = "99", Town = city, // CityID = city.CityID, AlternativePerson = new List <AlternativeAddress>(), PersonsPrimary = new List <Person>() }; Email email = new Email() // email { EmailID = 5, EmailAddress = "*****@*****.**", PersonID = 11 }; Notes notes = new Notes() // notes { NotesID = 7, NotesText = "All the notes about this person comes soon...", PersonID = 11 }; Provider provider = new Provider() // telefon provider { ProviderID = 2, ProviderName = "Telia" }; Telefon tlf = new Telefon() // Telefon { TelefonID = 26, Number = 22663355, TelefonType = "Private", ProviderID = 2, PersonID = 11, TelefonProvider = provider }; // Create section personUtil.CreateAddressDB(ref primaryAddr); personUtil.CreateEmailDB(ref email); personUtil.CreateNotesDB(ref notes); personUtil.CreateTelefonDB(ref tlf); Person Father = new Person() { PersonID = 0, FirstName = "Martin", MiddleName = "", LastName = "Moore", AddressID = 1, PrimaryAddress = primaryAddr, Emails = email, Notes = notes }; personUtil.CreatePersonDB(ref Father); Person Son = new Person() { PersonID = 2, FirstName = "Michael", MiddleName = "", LastName = "Rasmussen", AddressID = 2, TelefonNumbers = null, Notes = null, Emails = null, }; AlternativeAddress altAdr = new AlternativeAddress() // alternavtive address /address 2 reference to alternative AAType { AAID = 0, AAType = "Bolig", AddressID = 1, PersonID = 0, Persons = Son, Address = primaryAddr }; primaryAddr.PersonsPrimary.Add(Son); // reference from address to Person Son.PrimaryAddress = primaryAddr; // reference from person to primary addresss Son.AlternativeAddresses.Add(altAdr); // reference from person to ALT address Son.PrimaryAddress = primaryAddr; personUtil.CreateAlternativeAddressDB(ref altAdr); // Update section personUtil.UpdateAddressDB(ref primaryAddr); personUtil.UpdateEmailDB(ref email); personUtil.UpdateNotesDB(ref notes); personUtil.UpdateTelefonDB(ref tlf); personUtil.UpdatePersonDB(ref Father); primaryAddr.PersonsPrimary.Add(Father); // reference from Alt address to Person // Delete section personUtil.DeletePersonDB(ref Father); personUtil.DeleteTelefonDB(ref tlf); personUtil.DeleteNotesDB(ref notes); personUtil.DeleteEmailDB(ref email); personUtil.DeleteAddressDB(ref primaryAddr); return; }