public void CreateContact(FullContactModel contact)
{
//save basic contact
string sql = "INSERT INTO dbo.Contacts (FirstName, LastName) VALUES (@FirstName, @LastName);";
db.SaveData(sql, new { FirstName = contact.BasicInfo.FirstName, LastName = contact.BasicInfo.LastName }, connString);
//get id of the contact
sql = "SELECT Id FROM dbo.Contacts WHERE FirstName = @FirstName AND LastName = @LastName;";
int contactId = db.LoadData <IdLookupModel, dynamic>(sql, new { FirstName = contact.BasicInfo.FirstName, LastName = contact.BasicInfo.LastName }, connString).First().Id;
foreach (var i in contact.PhoneNumbers)
{
if (i.Id == 0)
{
sql = "INSERT INTO dbo.PhoneNumbers (PhoneNumber) VALUES (@PhoneNumber);";
db.SaveData(sql, new { i.PhoneNumber }, connString);
sql = "SELECT ID FROM dbo.PhoneNumbers WHERE PhoneNumber = @PhoneNumber;";
i.Id = db.LoadData <IdLookupModel, dynamic>(sql, new { i.PhoneNumber }, connString).First().Id;
}
sql = "INSERT INTO dbo.ContactPhoneNumbers (ContactId, PhoneNumberId) VALUES (@ContactId, @PhoneNumberId)";
db.SaveData(sql, new { ContactId = contactId, PhoneNumberId = i.Id }, connString);
}
foreach (var i in contact.EmailAddresses)
{
if (i.Id == 0)
{
sql = "INSERT INTO dbo.EmailAddresses (EmailAddress) VALUES (@EmailAddress);";
db.SaveData(sql, new { i.EmailAddress }, connString);
sql = "SELECT ID FROM dbo.EmailAddresses WHERE EmailAddress = @EmailAddress;";
i.Id = db.LoadData <IdLookupModel, dynamic>(sql, new { i.EmailAddress }, connString).First().Id;
}
sql = "INSERT INTO dbo.ContactEmail (ContactId, EmailAddressId) VALUES (@ContactId, @EmailAddressId)";
db.SaveData(sql, new { ContactId = contactId, EmailAddressId = i.Id }, connString);
}
}
public void CreateFullContact(FullContactModel contact)
{
//insert into table People values
string sql = "Insert into dbo.People (FirstName, LastName) values (@FirstName, @LastName);";
db.SaveData(sql, new { FirstName = contact.BasicInfo.FirstName, LastName = contact.BasicInfo.LastName }, connectionString);
//get the id of inserted contact
sql = "Select Id from dbo.People where FirstName = @FirstName and LastName = @LastName;";
int contactId = db.LoadData <IdLookUpModel, dynamic>(sql,
new { FirstName = contact.BasicInfo.FirstName, LastName = contact.BasicInfo.LastName },
connectionString).First().Id;
//verify all contact adresses in order to see if they already exist in the adresses table, if not add them to adresses table
foreach (var adress in contact.adresses)
{
if (adress.Id == 0)
{
//add adress to adresses table
sql = "Insert into dbo.Adresses (Adress) values (@Adress);";
db.SaveData(sql, new { Adress = adress.Adress }, connectionString);
// get the id of inserted adress
sql = "Select Id from dbo.Adresses where Adress = @Adress;";
adress.Id = db.LoadData <IdLookUpModel, dynamic>(sql,
new { Adress = adress.Adress },
connectionString).First().Id;
}
//add link in the PeopleAdresses table
sql = "Insert into dbo.PeopleAdresses (PeopleId, AdressesId) values (@PeopleId, @AdressesId);";
db.SaveData(sql, new { PeopleId = contactId, AdressesId = adress.Id }, connectionString);
}
}
