public List <FamilyInfoDBModel> SelectAllFamilyInfo() { List <FamilyInfoDBModel> contacts = new List <FamilyInfoDBModel>(); using (SqlConnection connection = new SqlConnection(_connectionstring)) { connection.Open(); var fields = "FamilyContactGuid, FamilyContactId, FirstName, Lastname, Gender, Email, HomePhone, MobilePhone, Address, City, State, ZipCode, Kovil, KovilPirivu, NativePlace,MaritalStatus,FamilyPicFileName"; var spouseFields = "SpouseFirstName,SpouseLastName,SpouseEmail,SpouseMobilePhone,SpouseKovil,SpouseKovilPirivu,SpouseNativePlace"; string query = "SELECT " + fields + "," + spouseFields + ", CreatedOn, LastModifiedOn FROM FamilyContact order by FirstName, LastName"; using (SqlCommand cmd = new SqlCommand(query, connection)) { using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { FamilyInfoDBModel familyDBModel = new FamilyInfoDBModel(); LoadFamilyInfoDBModel(familyDBModel, reader); contacts.Add(familyDBModel); } } } } var kidsField = "FamilyContactGuid,KidsInfoGuid,FirstName,Age,Gender"; string kidsInfoQuery = "SELECT " + kidsField + " FROM KidsInfo order by FamilyContactGuid, Age desc"; using (SqlCommand cmd = new SqlCommand(kidsInfoQuery, connection)) { using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { var kidsInfo = new KidsInfoDBModel(); LoadKidsInfoDBModel(kidsInfo, reader); var familyDBModel = contacts.FirstOrDefault(m => m.FamilyContactGuid == kidsInfo.FamilyContactGuid); if (familyDBModel != null) { if (familyDBModel.Kids == null) { familyDBModel.Kids = new List <KidsInfoDBModel>(); } familyDBModel.Kids.Add(kidsInfo); } } } reader.Close(); } } connection.Close(); } return(contacts); }
private static void LoadKidsInfoDBModel(KidsInfoDBModel kidsInfoDBModel, SqlDataReader reader) { kidsInfoDBModel.FamilyContactGuid = reader.GetString(0); kidsInfoDBModel.KidsInfoGuid = reader.GetString(1); kidsInfoDBModel.FirstName = reader.IsDBNull(2) ? null : reader.GetString(2); kidsInfoDBModel.Age = reader.GetInt16(3); kidsInfoDBModel.Gender = reader.IsDBNull(4) ? null : reader.GetString(4); }
public FamilyInfoDBModel SelectWithKidsInfo(string id) { FamilyInfoDBModel familyDBModel = new FamilyInfoDBModel(); using (SqlConnection connection = new SqlConnection(_connectionstring)) { connection.Open(); var fields = "FamilyContactGuid, FamilyContactId, FirstName, Lastname, Gender, Email, HomePhone, MobilePhone, Address, City, State, ZipCode, Kovil, KovilPirivu, NativePlace,MaritalStatus,FamilyPicFileName"; var spouseFields = "SpouseFirstName,SpouseLastName,SpouseEmail,SpouseMobilePhone,SpouseKovil,SpouseKovilPirivu,SpouseNativePlace"; string query = "SELECT " + fields + "," + spouseFields + ", CreatedOn, LastModifiedOn FROM FamilyContact WHERE FamilyContactGuid = @FamilyContactGuid"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.Add("@FamilyContactGuid", SqlDbType.VarChar, 128).Value = id; using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { LoadFamilyInfoDBModel(familyDBModel, reader); } } reader.Close(); } } var kidsField = "FamilyContactGuid,KidsInfoGuid,FirstName,Age,Gender"; string kidsInfoQuery = "SELECT " + kidsField + " FROM KidsInfo WHERE FamilyContactGuid = @FamilyContactGuid Order by Age desc"; using (SqlCommand cmd = new SqlCommand(kidsInfoQuery, connection)) { cmd.Parameters.Add("@FamilyContactGuid", SqlDbType.VarChar, 128).Value = id; using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { familyDBModel.Kids = new List <KidsInfoDBModel>(); while (reader.Read()) { var kidsInfoDBModel = new KidsInfoDBModel(); LoadKidsInfoDBModel(kidsInfoDBModel, reader); familyDBModel.Kids.Add(kidsInfoDBModel); } } reader.Close(); } } connection.Close(); } return(familyDBModel); }
private FamilyInfoDBModel ProcessViewModeltoDBModel(ContactInfoViewModel model) { var familyContact = new FamilyInfoDBModel(); var isNewFamilyContact = string.IsNullOrWhiteSpace(model.FamilyContactGuid); familyContact.FamilyContactGuid = isNewFamilyContact ? Guid.NewGuid().ToString("N") : model.FamilyContactGuid; familyContact.FirstName = string.IsNullOrWhiteSpace(model.FirstName) ? string.Empty : model.FirstName; familyContact.LastName = string.IsNullOrWhiteSpace(model.LastName) ? string.Empty : model.LastName; familyContact.Gender = string.IsNullOrWhiteSpace(model.Gender) ? string.Empty : model.Gender; familyContact.MaritalStatus = string.IsNullOrWhiteSpace(model.MaritalStatus) ? string.Empty : model.MaritalStatus; familyContact.Email = string.IsNullOrWhiteSpace(model.Email) ? string.Empty : model.Email; familyContact.HomePhone = string.IsNullOrWhiteSpace(model.HomePhone) ? string.Empty : model.HomePhone; familyContact.MobilePhone = string.IsNullOrWhiteSpace(model.MobilePhone) ? null : model.MobilePhone; //Nullable familyContact.Address = string.IsNullOrWhiteSpace(model.Address) ? null : model.Address; //Nullable familyContact.City = string.IsNullOrWhiteSpace(model.City) ? string.Empty : model.City; familyContact.State = string.IsNullOrWhiteSpace(model.State) ? string.Empty : model.State; familyContact.ZipCode = string.IsNullOrWhiteSpace(model.ZipCode) ? null : model.ZipCode; //Nullable familyContact.Kovil = string.IsNullOrWhiteSpace(model.Kovil) ? string.Empty : model.Kovil; familyContact.KovilPirivu = string.IsNullOrWhiteSpace(model.KovilPirivu) ? string.Empty : model.KovilPirivu; familyContact.NativePlace = string.IsNullOrWhiteSpace(model.NativePlace) ? string.Empty : model.NativePlace; familyContact.FamilyPicFileName = string.IsNullOrEmpty(model.FamilyPicFileExtn)? null: string.Concat(familyContact.FamilyContactGuid, model.FamilyPicFileExtn); if (familyContact.FamilyPicFileName == null && model.FamilyPicFilePath != null) { familyContact.FamilyPicFileName = model.FamilyPicFilePath.Substring(model.FamilyPicFilePath.LastIndexOf('/') + 1); } //Attach Spouse if (familyContact.MaritalStatus == "M") { var spouseInfo = new SpouseInfoDBModel { FirstName = string.IsNullOrWhiteSpace(model.SpouseFirstName) ? string.Empty : model.SpouseFirstName, LastName = string.IsNullOrWhiteSpace(model.SpouseLastName) ? string.Empty : model.SpouseLastName, Email = string.IsNullOrWhiteSpace(model.SpouseEmail) ? null : model.SpouseEmail, MobilePhone = string.IsNullOrWhiteSpace(model.SpouseMobilePhone) ? null : model.SpouseMobilePhone, Kovil = string.IsNullOrWhiteSpace(model.SpouseKovil) ? string.Empty : model.SpouseKovil, KovilPirivu = string.IsNullOrWhiteSpace(model.SpouseKovilPirivu) ? string.Empty : model.SpouseKovilPirivu, NativePlace = string.IsNullOrWhiteSpace(model.SpouseNativePlace) ? string.Empty : model.SpouseNativePlace }; familyContact.Spouse = spouseInfo; } familyContact.Kids = new List <KidsInfoDBModel>(); foreach (KidsViewModel kidsInfo in model.Kids) { if (!string.IsNullOrWhiteSpace(kidsInfo.FirstName) || kidsInfo.Age.HasValue || !string.IsNullOrWhiteSpace(kidsInfo.Gender)) { var kid = new KidsInfoDBModel(); var isNewKidContact = string.IsNullOrWhiteSpace(kidsInfo.KidsInfoGuid); kid.KidsInfoGuid = isNewKidContact ? Guid.NewGuid().ToString("N") : kidsInfo.KidsInfoGuid; kid.FamilyContactGuid = familyContact.FamilyContactGuid; kid.FirstName = string.IsNullOrWhiteSpace(kidsInfo.FirstName) ? string.Empty : kidsInfo.FirstName; kid.Age = kidsInfo.Age.HasValue ? kidsInfo.Age.Value : 0; kid.Gender = string.IsNullOrWhiteSpace(kidsInfo.Gender) ? string.Empty : kidsInfo.Gender; familyContact.Kids.Add(kid); } } return(familyContact); }