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