public bool AddTutorRelatives(TutorRelatives rel, string userId)
{
Boolean retVal = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlCommand cmd;
string query = "INSERT INTO `tutorrelatives`(`userId`, `relType`, `firstName`, `lastName`, `otherName`, `mobile`, `tel`, `email`, `postAddress`, `nextOfKin`, `dateCreated`, `lastModified`) VALUES (@userId,@relType,@firstName,@lastName,@otherName,@mobile,@tel,@email,@postAddress,@nextOfKin,@dateCreated,@lastModified)";
try
{
con.Open();
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@userId", rel.UserId);
cmd.Parameters.AddWithValue("@relType", rel.RelType);
cmd.Parameters.AddWithValue("@firstName", rel.FirstName);
cmd.Parameters.AddWithValue("@lastName", rel.LastName);
cmd.Parameters.AddWithValue("@otherName", rel.OtherName);
cmd.Parameters.AddWithValue("@mobile", rel.Mobile);
cmd.Parameters.AddWithValue("@tel", rel.Tel);
cmd.Parameters.AddWithValue("@email", rel.Email);
cmd.Parameters.AddWithValue("@postAddress", rel.PostAddress);
cmd.Parameters.AddWithValue("@nextOfKin", rel.NextOfKin);
cmd.Parameters.AddWithValue("@dateCreated", rel.DateCreated);
cmd.Parameters.AddWithValue("@lastModified", rel.LastModified);
int afRow = cmd.ExecuteNonQuery();
new AuditLogService().AddAuditLog("ADDING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
if (afRow > 0)
{
retVal = true;
}
}
catch (MySqlException ex)
{
new AuditLogService().AddAuditLog("ERROR ADDING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
catch (Exception ex)
{
new AuditLogService().AddAuditLog("ERROR ADDING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
finally
{
con.Close();
}
return(retVal);
}
public bool UpdateTutorRelatives(TutorRelatives rel, string userId)
{
Boolean retVal = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlCommand cmd;
string query = "UPDATE `tutorrelatives` SET `userId`=@userId,`relType`=@relType,`firstName`=@firstName,`lastName`=@lastName,`otherName`=@otherName,`mobile`=@mobile,`tel`=@tel,`email`=@email,`postAddress`=@postAddress,`nextOfKin`=@nextOfKin,`lastModified`=@lastModified WHERE id=@id";
try
{
con.Open();
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@userId", rel.UserId);
cmd.Parameters.AddWithValue("@relType", rel.RelType);
cmd.Parameters.AddWithValue("@firstName", rel.FirstName);
cmd.Parameters.AddWithValue("@lastName", rel.LastName);
cmd.Parameters.AddWithValue("@otherName", rel.OtherName);
cmd.Parameters.AddWithValue("@mobile", rel.Mobile);
cmd.Parameters.AddWithValue("@tel", rel.Tel);
cmd.Parameters.AddWithValue("@email", rel.Email);
cmd.Parameters.AddWithValue("@postAddress", rel.PostAddress);
cmd.Parameters.AddWithValue("@nextOfKin", rel.NextOfKin);
cmd.Parameters.AddWithValue("@lastModified", rel.LastModified);
cmd.Parameters.AddWithValue("@id", rel.Id);
int affecRow = cmd.ExecuteNonQuery();
new AuditLogService().AddAuditLog("UPDATING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
if (affecRow > 0)
{
retVal = true;
}
}
catch (MySqlException ex)
{
new AuditLogService().AddAuditLog("ERROR UPDATING RELATIVE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
catch (Exception ex)
{
new AuditLogService().AddAuditLog("ERROR UPDATING RELATIVE ", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
finally
{ con.Close(); }
return(retVal);
}
public List <TutorRelatives> GetAllTutorRelativesByType(string type, string userId)
{
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlCommand cmd;
MySqlDataReader dr = null;
string query = "SELECT `id`, `userId`, `relType`, `firstName`, `lastName`, `otherName`, `mobile`, `tel`, `email`, `postAddress`, `nextOfKin`, `active`, `dateCreated`, `lastModified` FROM `tutorrelatives` WHERE relType=@relType";
List <TutorRelatives> relList = new List <TutorRelatives>();
TutorRelatives rel;
try
{
con.Open();
cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@relType", type);
dr = cmd.ExecuteReader();
new AuditLogService().AddAuditLog("LOADING RELATIVES BY TYPE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
if (dr.HasRows)
{
while (dr.Read())
{
rel = new TutorRelatives();
rel.Id = dr.GetInt32(0);
rel.UserId = dr.GetString(1);
rel.RelType = dr.GetString(2);
rel.FirstName = dr.GetString(3);
rel.LastName = dr.GetString(4);
rel.OtherName = dr.GetString(5);
rel.Mobile = dr.GetString(6);
rel.Tel = dr.GetString(7);
rel.Email = dr.GetString(8);
rel.PostAddress = dr.GetString(9);
rel.NextOfKin = dr.GetString(10);
rel.Active = dr.GetInt32(11);
rel.DateCreated = dr.GetDateTime(12);
rel.LastModified = dr.GetDateTime(13);
rel.xFullName = dr.GetString(3) + " " + dr.GetString(5) + " " + dr.GetString(4);
relList.Add(rel);
}
}
}
catch (MySqlException ex)
{
new AuditLogService().AddAuditLog("ERROR LOADING RELATIVE BY TYPE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
catch (Exception ex)
{
new AuditLogService().AddAuditLog("ERROR LOADING RELATIVE BY TYPE", userId, new UserService().GetUserName(userId), query, DateTime.Now);
string errorString = ex.Message;
}
finally
{
dr.Close();
con.Close();
}
return(relList);
}