public MemberPreferencesDTO getMemberPreferences()
{
DataTable dt = new DataTable();
string sqlSentence = "";
try
{
sqlSentence += "SELECT members.id, members.realName, members.displayName, members.email FROM members ";
sqlSentence += "WHERE members.id = @memberId ";
List <SqlParameter> sqlParameters = new List <SqlParameter>();
sqlParameters.Add(new SqlParameter("@memberId", memberInfo.id));
dt = dbWrapper.FillDataTable(sqlSentence, sqlParameters);
}
catch (Exception ex)
{
throw ex;
}
return((MemberPreferencesDTO)getConvertedDatatable(dt, typeof(MemberPreferencesDTO)));
}
public Member getMember(int memberId)
{
DataTable dt = new DataTable();
string sqlSentence = "";
try
{
sqlSentence += "SELECT * FROM members ";
sqlSentence += "WHERE members.id = @memberId ";
List <SqlParameter> sqlParameters = new List <SqlParameter>();
sqlParameters.Add(new SqlParameter("@memberId", memberId));
dt = dbWrapper.FillDataTable(sqlSentence, sqlParameters);
}
catch (Exception ex)
{
throw ex;
}
return((Member)getConvertedDatatable(dt, typeof(Member)));
}
public List <Contact> getContacts(SearchContactDTO searchOptions = null)
{
DataTable dt = new DataTable();
List <SqlParameter> sqlParameters = new List <SqlParameter>();
string sqlSentence = "";
try
{
sqlSentence += "SELECT * FROM contacts ";
sqlSentence += "WHERE contacts.memberId = @memberId ";
if (searchOptions != null)
{
if (searchOptions.firstName != string.Empty)
{
sqlSentence += "AND contacts.firstName LIKE '%" + searchOptions.firstName + "%' ";
}
if (searchOptions.lastName != string.Empty)
{
sqlSentence += "AND contacts.lastName LIKE '%" + searchOptions.lastName + "%' ";
}
if (searchOptions.genre != string.Empty)
{
if (searchOptions.genre.ToUpper() == "F" || searchOptions.genre.ToUpper() == "M")
{
sqlSentence += "AND contacts.genre = @genre ";
sqlParameters.Add(new SqlParameter("@genre", searchOptions.genre));
}
}
if (searchOptions.email != string.Empty)
{
sqlSentence += "AND contacts.email LIKE '%" + searchOptions.email + "%' ";
}
if (searchOptions.mobileNumber != string.Empty)
{
sqlSentence += "AND contacts.mobileNumber LIKE '%" + searchOptions.mobileNumber + "%' ";
}
if (searchOptions.landlineNumber != string.Empty)
{
sqlSentence += "AND contacts.landlineNumber LIKE '%" + searchOptions.landlineNumber + "%' ";
}
if (searchOptions.onlyActive && !searchOptions.onlyInactive)
{
sqlSentence += "AND contacts.isActive = @active ";
sqlParameters.Add(new SqlParameter("@active", true));
}
if (searchOptions.onlyInactive && !searchOptions.onlyActive)
{
sqlSentence += "AND contacts.isActive = @active ";
sqlParameters.Add(new SqlParameter("@active", false));
}
if (searchOptions.onlyWithPicture && !searchOptions.onlyWithoutPicture)
{
sqlSentence += "AND contacts.picExtension IS NOT NULL ";
}
if (searchOptions.onlyWithoutPicture && !searchOptions.onlyWithPicture)
{
sqlSentence += "AND contacts.picExtension IS NULL ";
}
if (searchOptions.comments != string.Empty)
{
sqlSentence += "AND contacts.comments LIKE '%" + searchOptions.comments + "%' ";
}
if (searchOptions.createdSince != null && searchOptions.createdUntil != null)
{
if (searchOptions.createdSince <= searchOptions.createdUntil)
{
sqlSentence += "AND (contacts.createdAt BETWEEN @dateSince AND @dateUntil) ";
SqlParameter dateSince = new SqlParameter("@dateSince", searchOptions.createdSince);
SqlParameter dateUntil = new SqlParameter("@dateUntil", searchOptions.createdUntil);
dateSince.DbType = DbType.DateTime;
dateUntil.DbType = DbType.DateTime;
sqlParameters.Add(dateSince);
sqlParameters.Add(dateUntil);
}
}
}
sqlSentence += "ORDER BY contacts.firstName, contacts.lastName ";
sqlParameters.Add(new SqlParameter("@memberId", memberInfo.id));
dt = dbWrapper.FillDataTable(sqlSentence, sqlParameters);
}
catch (Exception ex)
{
throw ex;
}
return((List <Contact>)getConvertedDatatable(dt, typeof(List <Contact>)));
}
