public HigherEducationDTO find(string userName, string major)
{
HigherEducationDTO info = new HigherEducationDTO();
SqlConnection oConn = new SqlConnection();
SqlCommand sqlCmd = null;
try
{
oConn.ConnectionString = ConfigurationManager.AppSettings["conn"];
oConn.Open();
sqlCmd = oConn.CreateCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "select * from HigherEducation where userName = '******' AND major = '" + major + "'";
SqlDataReader rdr = sqlCmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
info.userName = rdr["userName"].ToString();
info.institution = rdr["institution"].ToString();
info.town = rdr["town"].ToString();
info.province = rdr["province"].ToString();
info.country = rdr["country"].ToString();
info.educationType = rdr["educationType"].ToString();
info.major = rdr["major"].ToString();
info.industry = rdr["industry"].ToString();
info.length = rdr["length"].ToString();
info.nqf = rdr["nqf"].ToString();
}
}
}
catch
{ }
finally
{
if (sqlCmd != null)
{
sqlCmd = null;
}
if (oConn != null)
{
if (oConn.State.Equals(ConnectionState.Open))
{
oConn.Close();
}
oConn = null;
}
}
return info;
}
public void HigherEducation_Test()
{
/*Context*/
HigherEducationDAO higher_context = new HigherEducationDAO();
AccountDAO account_context = new AccountDAO();
/*Insert*/
AccountDTO account = new AccountDTO();
account.userName = "******";
account.status = "active";
account.password = "******";
account.accountType = "admin";
account_context.presist(account);
HigherEducationDTO higherEdu = new HigherEducationDTO();
higherEdu.userName = "******";
higherEdu.country = "SA";
higherEdu.educationType = "BTECH";
higherEdu.industry = "Information Technology";
higherEdu.institution = "CPUT";
higherEdu.length = "four years";
higherEdu.major = "Technical Programming";
higherEdu.nqf = "7";
higherEdu.province = "WP";
higherEdu.town = "Cape Town";
higher_context.presist(higherEdu);
bool expected = true;
bool actual;
actual = higher_context.isFound("griddy", "Technical Programming");
Assert.AreEqual(expected, actual);
/*Update*/
higherEdu.institution = "UWC";
higher_context.merge(higherEdu);
string expectedUpdate = "UWC";
HigherEducationDTO contUpd = higher_context.find("griddy", "Technical Programming");
Assert.AreEqual(expectedUpdate, contUpd.institution);
/*Delete*/
higher_context.removeByUserId("griddy", "Technical Programming");
bool expectedDelete = false;
bool actualDelete = higher_context.isFound("griddy", "Technical Programming");
Assert.AreEqual(expectedDelete, actualDelete);
account_context.removeByUserId("griddy");
}
public HigherEducationDTO find(string userName, string major)
{
var obj = (from p in ctx.HigherEducations
where p.userName == @userName && p.major == @major
select p).Single();
HigherEducationDTO add = new HigherEducationDTO();
add.userName = obj.userName;
add.institution = obj.institution;
add.town = obj.town;
add.province = obj.province;
add.country = obj.country;
add.educationType = obj.educationType;
add.major = obj.major;
add.industry = obj.industry;
add.length = obj.length;
add.nqf = obj.nqf;
return add;
}
public List<HigherEducationDTO> findAll()
{
var objs = (from p in ctx.HigherEducations
select p);
HigherEducationDTO add = null;
List<HigherEducationDTO> addList = new List<HigherEducationDTO>();
foreach (HigherEducation obj in objs)
{
add = new HigherEducationDTO();
add.userName = obj.userName;
add.institution = obj.institution;
add.town = obj.town;
add.province = obj.province;
add.country = obj.country;
add.educationType = obj.educationType;
add.major = obj.major;
add.industry = obj.industry;
add.length = obj.length;
add.nqf = obj.nqf;
addList.Add(add);
}
return addList;
}
public void setHigherEducationDto(HigherEducationDTO higherEducationDto)
{
view.setCountry(higherEducationDto.country);
view.setEducationType(higherEducationDto.educationType);
view.setIndustry(higherEducationDto.industry);
view.setInstitution(higherEducationDto.institution);
view.setLength(higherEducationDto.length);
view.setMajor(higherEducationDto.major);
view.setNqf(higherEducationDto.nqf);
view.setProvince(higherEducationDto.province);
view.setTown(higherEducationDto.town);
//view.setUsername(higherEducationInformationDto.userName);
}
public HigherEducationDTO getHigherEducationDto()
{
HigherEducationDTO higherEducationDto = new HigherEducationDTO();
higherEducationDto.userName = view.getUsername();
higherEducationDto.country = view.getCountry();
higherEducationDto.educationType = view.getEducationType();
higherEducationDto.industry = view.getIndustry();
higherEducationDto.institution = view.getInstitution();
higherEducationDto.length = view.getLength();
higherEducationDto.major = view.getMajor();
higherEducationDto.nqf = view.getNqf();
higherEducationDto.province = view.getProvince();
higherEducationDto.town = view.getTown();
return higherEducationDto;
}
public bool merge(HigherEducationDTO entity)
{
try
{
var addObj = (from p in ctx.HigherEducations
where p.userName == @entity.userName && p.major == @entity.major
select p).Single();
model.HigherEducation obj = (HigherEducation)addObj;
/*Update*/
obj.userName = entity.userName;
obj.major = entity.major;
obj.institution = entity.institution;
obj.town = entity.town;
obj.province = entity.province;
obj.country = entity.country;
obj.educationType = entity.educationType;
obj.major = entity.major;
obj.industry = entity.industry;
obj.length = entity.length;
obj.nqf = entity.nqf;
ctx.SubmitChanges();
return true;
}
catch (Exception e)
{
model.Log log = new Log();
log.message = "HigherEducation Merge: " + " ["+entity.userName+" , "+entity.major+"] " + e.Message;
ctx.SubmitChanges();
ctx.Dispose();
ctx = new ModelDataContext();
return false;
}
}
public bool remove(HigherEducationDTO entity)
{
return this.removeByUserId(entity.userName,entity.major);
}
public bool presist(HigherEducationDTO entity)
{
try
{
model.HigherEducation obj = new HigherEducation();
obj.userName = entity.userName;
obj.major = entity.major;
obj.institution = entity.institution;
obj.town = entity.town;
obj.province = entity.province;
obj.country = entity.country;
obj.educationType = entity.educationType;
obj.major = entity.major;
obj.industry = entity.industry;
obj.length = entity.length;
obj.nqf = entity.nqf;
ctx.HigherEducations.InsertOnSubmit(obj);
ctx.SubmitChanges();
return true;
}
catch (Exception)
{
ctx.Dispose();
ctx = new ModelDataContext();
return false;
}
}
public bool presist(HigherEducationDTO entity)
{
bool success = false;
SqlConnection oConn = new SqlConnection();
SqlCommand sqlCmd = null;
try
{
oConn.ConnectionString = ConfigurationManager.AppSettings["conn"];
oConn.Open();
sqlCmd = oConn.CreateCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "insertHigherEducation";
sqlCmd.Parameters.Add(new SqlParameter("userName", entity.userName));
sqlCmd.Parameters.Add(new SqlParameter("institution", entity.institution));
sqlCmd.Parameters.Add(new SqlParameter("town", entity.town));
sqlCmd.Parameters.Add(new SqlParameter("province", entity.province));
sqlCmd.Parameters.Add(new SqlParameter("country", entity.country));
sqlCmd.Parameters.Add(new SqlParameter("educationType", entity.educationType));
sqlCmd.Parameters.Add(new SqlParameter("major", entity.major));
sqlCmd.Parameters.Add(new SqlParameter("industry", entity.industry));
sqlCmd.Parameters.Add(new SqlParameter("length", entity.length));
sqlCmd.Parameters.Add(new SqlParameter("nqf", entity.nqf));
SqlDataReader rdr = sqlCmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{ } //Read all
}
rdr.Close();
if (rdr.RecordsAffected > 0)
success = true;
}
catch { }
finally
{
if (sqlCmd != null)
{
sqlCmd = null;
}
if (oConn != null)
{
if (oConn.State.Equals(ConnectionState.Open))
{
oConn.Close();
}
oConn = null;
}
}
return success;
}
