public model.Researcher getResearcherById(int researcherId)
{
model.Researcher researcher = new model.Researcher();
ConnectionManager connMgr = new ConnectionManager();
MySqlConnection connection = connMgr.getConnection();
connection.Open();
string query = "select r.*,u.name from researcher r, university u where r.researcherid = " + researcherId + " and u.universityid = r.university_universityid";
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
researcher.ResearcherID = reader.GetInt32(0);
researcher.FirstName = reader.GetString(1);
researcher.LastName = reader.GetString(2);
researcher.Email = reader.GetString(3);
researcher.ContactNumber = reader.GetString(4);
researcher.Type = reader.GetString(5);
researcher.State = reader.GetString(6);
researcher.City = reader.GetString(7);
researcher.Major = reader.GetString(8);
researcher.LatestDegree = reader.GetString(9);
researcher.TotalRators = reader.GetInt32(10);
researcher.TotalRatings = reader.GetInt32(11);
researcher.University = new model.University();
researcher.University.Name = reader.GetString(13);
}
}
connection.Close();
return(researcher);
}
public List <model.Researcher> getResearcher(string lastName, String area)
{
List <model.Researcher> researcherList = new List <model.Researcher>();
ConnectionManager connMgr = new ConnectionManager();
MySqlConnection connection = connMgr.getConnection();
connection.Open();
string query = null;
if (lastName != null && lastName != String.Empty && (area == string.Empty || area == null))
{
query = "select r.*,u.name from researcher r, university u where lastname like '%" + lastName + "%' and u.universityid = r.university_universityid ";
}
else if ((lastName == null || lastName == String.Empty) && area != null && area != string.Empty)
{
query = "select r.*, u.name from researcher r, area a, researchareaassociation ra, university u where a.description like '%" + area + "%' and ra.area_areaid = a.areaid and r.researcherid = ra.researcher_researcherid and u.universityid = r.university_universityid";
}
else if (lastName != null && lastName != string.Empty && (area != null && area == string.Empty))
{
query = "select r.*, u.name from area a, researcher r, researchareaassociation ra, university u where a.description like '%" + area + "%' and ra.area_areaid = a.areaid and r.researcherid = ra.researcher_researcherid and r. lastName like '%" + lastName + "%' and u.universityid = r.university_universityid";
}
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
model.Researcher researcher = new model.Researcher();
researcher.ResearcherID = reader.GetInt32(0);
researcher.FirstName = reader.GetString(1);
researcher.LastName = reader.GetString(2);
researcher.Email = reader.GetString(3);
researcher.ContactNumber = reader.GetString(4);
researcher.Type = reader.GetString(5);
researcher.State = reader.GetString(6);
researcher.City = reader.GetString(7);
researcher.Major = reader.GetString(8);
researcher.LatestDegree = reader.GetString(9);
researcher.TotalRators = reader.GetInt32(10);
researcher.TotalRatings = reader.GetInt32(11);
researcher.University = new model.University();
researcher.University.Name = reader.GetString(13);
researcherList.Add(researcher);
}
}
connection.Close();
return(researcherList);
}
public void updateResearcher(model.Researcher researcher)
{
try
{
ConnectionManager connMgr = new ConnectionManager();
MySqlConnection connection = connMgr.getConnection();
connection.Open();
string query = "update scifusion.researcher set FirstName = '" + researcher.FirstName + "', LastName ='" + researcher.LastName + "', Email ='" + researcher.Email + "', ContactNumber ='" + researcher.ContactNumber + "', Type ='" + researcher.Type + "', State = '" + researcher.State + "', City = '" + researcher.City + "', Major='" + researcher.Major + "', LatestDegree ='" + researcher.LatestDegree + "', TotalRators='" + researcher.TotalRators + "', TotalRatings='" + researcher.TotalRatings + "' where researcherid = " + researcher.ResearcherID + "";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
connMgr.closeConnection();
}
catch (Exception e)
{
throw e;
}
}
public void saveResearcher(model.Researcher researcher)
{
try
{
MySqlConnection connection = new MySqlConnection(DBConstants.connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
string query = "Insert into scifusion.researcher ( FirstName, LastName, Email, ContactNumber, Type, State, City, Major, LatestDegree, TotalRators, TotalRatings, University_UniversityID) values ('" + researcher.FirstName + "', '" + researcher.LastName + "','" + researcher.Email + "','" + researcher.ContactNumber + "','" + researcher.Type + "','" + researcher.State + "','" + researcher.City + "','" + researcher.Major + "','" + researcher.LatestDegree + "','" + researcher.TotalRators + "','" + researcher.TotalRatings + "','" + researcher.University_universityID + "')";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception e)
{
throw e;
}
}