public List <ResearchWork> getResearchWorkByNameArea(string researchName, string workIDescription) { try { List <ResearchWork> researchList = new List <ResearchWork>(); ConnectionManager connectionManager = new ConnectionManager(); MySqlConnection connection = connectionManager.getConnection(); connection.Open(); string query = null; if ((researchName != null || researchName != string.Empty) && (workIDescription == null || workIDescription == string.Empty)) { query = "select rw.name, rw.links, r.FirstName, r.LastName, a.Description from researchwork rw, researcher r, workareaassociation wa, researchworkassociation rwa, area a where rw.name like '%" + researchName + "%' and wa.ResearchWork_WorkID = rw.WorkID and a.areaid = wa.Area_AreaID and rwa.ResearchWork_WorkID = rw.WorkID and r.ResearcherID = rwa.Researcher_ResearcherID;"; } else if ((researchName != null || researchName != string.Empty) && (workIDescription != null || workIDescription != string.Empty)) { query = "select rw.name, rw.links, r.FirstName, r.LastName, a.description from researchwork rw, researcher r, workareaassociation wa, researchworkassociation rwa, area a where a.Description like '%" + workIDescription + "%' and rw.name like '%" + researchName + "%' and a.areaid = wa.Area_AreaID and wa.ResearchWork_WorkID = rw.WorkID and rwa.ResearchWork_WorkID = rw.WorkID and r.ResearcherID = rwa.Researcher_ResearcherID;"; } else if ((researchName == null || researchName == string.Empty) && (workIDescription != null || workIDescription != string.Empty)) { query = " select rw.name, rw.links, r.FirstName, r.LastName, a.Description from researchwork rw, researcher r, workareaassociation wa, researchworkassociation rwa, area a where a.Description like '%" + workIDescription + "%' and a.areaid = wa.Area_AreaID and wa.ResearchWork_WorkID = rw.WorkID and rwa.ResearchWork_WorkID = rw.WorkID and r.ResearcherID = rwa.Researcher_ResearcherID;"; } //command.CommandText = "select rw.*, u.name, a.description from researchwork rw, "+"\n"+ // "area a, workareaassociation wa, university u" + "\n" + // "where rw.name like '%"+researchName+"%' and rw.workid = wa.researchwork_workid" + "\n" + // "and wa.area_areaid = a.areaid and a.description like '%"+workIDescription+"%';"; MySqlCommand command = new MySqlCommand(query, connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ResearchWork researchWork = new ResearchWork(); Researcher researcher = new Researcher(); researchWork.Links = reader.GetString(1); researchWork.Name = reader.GetString(0); researcher.FirstName = reader.GetString(2); researcher.LastName = reader.GetString(3); researchWork.Researcher = researcher; researchList.Add(researchWork); } reader.Close(); connectionManager.closeConnection(); return(researchList); } catch (Exception e) { throw e; } }
public List <ResearchWork> getResearchWorkById(int researchWorkId) { try { List <ResearchWork> researchList = new List <ResearchWork>(); ConnectionManager connectionManager = new ConnectionManager(); MySqlConnection connection = connectionManager.getConnection(); connection.Open(); MySqlCommand command = connection.CreateCommand(); command.CommandText = "select rw.* ,r.firstname , r.LastName,r.ResearcherId" + "\n" + "from researchwork rw" + "\n" + "left outer join researchworkassociation rwa" + "\n" + "on rwa.ResearchWork_WorkID =rw.WorkID" + "\n" + " and rw.workId = " + researchWorkId + " " + "\n" + "left outer join researcher r" + "\n" + "on r.ResearcherID = rwa.Researcher_ResearcherID" + "\n" + " where rw.workId = " + researchWorkId + " " + "\n" + " order by rw.WorkID asc;"; MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ResearchWork researchWork = new ResearchWork(); Researcher researcher = new Researcher(); researchWork.WorkId = int.Parse(reader["WorkId"].ToString()); researchWork.University_universityId = int.Parse(reader["University_UniversityID"].ToString()); researchWork.Links = (string)reader["links"].ToString(); researchWork.Name = reader["Name"].ToString(); researcher.ResearcherID = int.Parse(reader["ResearcherId"].ToString()); researcher.FirstName = reader["FirstName"].ToString(); researcher.LastName = reader["LastName"].ToString(); researchWork.Researcher = researcher; researchList.Add(researchWork); } reader.Close(); connectionManager.closeConnection(); return(researchList); } catch (Exception e) { throw e; } }
public void saveResearchWork(ResearchWork researchWork) { try { ConnectionManager connectionManager = new ConnectionManager(); MySqlConnection connection = connectionManager.getConnection(); Researcher researcher = new Researcher(); researcher = researchWork.Researcher; connection.Open(); MySqlCommand command2 = connection.CreateCommand(); command2.CommandText = "Select u.universityid from university u where u.name = " + researchWork.University.Name; var universityId = command2.ExecuteNonQuery(); MySqlCommand command = connection.CreateCommand(); command.CommandText = "INSERT INTO researchwork(Links,university_universityId,Name)" + "\n" + "VALUES ('" + researchWork.Links + "','" + universityId + "' , '" + researchWork.Name + "')"; int success = command.ExecuteNonQuery(); command.CommandText = "SELECT LAST_INSERT_ID();"; MySqlDataReader reader = command.ExecuteReader(); reader.Read(); int workId = int.Parse(reader[0].ToString()); connection.Close(); //new connection for new insert connection = connectionManager.getConnection(); connection.Open(); command = connection.CreateCommand(); if (success != null) { command.CommandText = "INSERT INTO researchworkassociation(ResearchWork_WorkId,Researcher_ResearcherId)" + "\n" + "VALUES ('" + workId + "','" + researcher.ResearcherID + "');"; command.ExecuteNonQuery(); connection.Close(); } } catch (Exception e) { throw e; } }
public void updateResearchWork(ResearchWork reseachWork, Boolean updateRWA) { try { ConnectionManager connectionManager = new ConnectionManager(); MySqlConnection connection = connectionManager.getConnection(); Researcher researcher = new Researcher(); researcher = reseachWork.Researcher; connection.Open(); MySqlCommand command = connection.CreateCommand(); reseachWork.University_universityId = reseachWork.University_universityId + 1; command.CommandText = "UPDATE researchwork" + "\n" + "SET researchwork.Links = '" + reseachWork.Links + "'" + "\n" + ",researchwork.university_universityId= '" + reseachWork.University_universityId + "' " + "\n" + ",researchwork.name ='" + reseachWork.Name + "' " + "\n" + "where researchwork.workId = '" + reseachWork.WorkId + "'"; int success = command.ExecuteNonQuery(); connection.Close(); if (success != null && updateRWA) { //new connection for new update connection = connectionManager.getConnection(); connection.Open(); command = connection.CreateCommand(); command.CommandText = "UPDATE researchworkassociation" + "\n" + "SET ResearchWork_WorkId = '" + reseachWork.WorkId + "'" + "\n" + ",Researcher_ResearcherId= '" + researcher.ResearcherID + "'"; command.ExecuteNonQuery(); connection.Close(); } } catch (Exception e) { throw e; } }
public List <ResearchWork> getResearchWorkByUnivId(int universityId) { try { List <ResearchWork> researchList = new List <ResearchWork>(); ConnectionManager connectionManager = new ConnectionManager(); MySqlConnection connection = connectionManager.getConnection(); connection.Open(); MySqlCommand command = connection.CreateCommand(); command.CommandText = "select * from researchwork rw ,university u where rw.University_UniversityID = u.universityId and u.universityId = '" + universityId + "';"; MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { ResearchWork researchWork = new ResearchWork(); University university = new University(); researchWork.WorkId = int.Parse(reader["WorkId"].ToString()); researchWork.University_universityId = int.Parse(reader["University_UniversityID"].ToString()); researchWork.Links = (string)reader["links"].ToString(); researchWork.Name = reader["Name"].ToString(); university.UniversityId = researchWork.University_universityId; university.Name = reader["Name"].ToString(); university.City = reader["City"].ToString(); university.State = reader["State"].ToString(); university.Country = reader["Country"].ToString(); researchWork.University = university; researchList.Add(researchWork); } reader.Close(); connectionManager.closeConnection(); return(researchList); } catch (Exception e) { throw e; } }