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