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