public override string ToString() { return(LastName.ToString() + " " + SName.ToString() + ", " + Course.ToString() + " ะบััั"); }
public HttpResponseMessage GetSearchResult(object data) { var db = DbUtils.GetDBConnection(); db.Connection.Open(); IEnumerable <IDictionary <string, object> > response = null; var jsonData = JsonConvert.SerializeObject(data); var dictJson = JsonConvert.DeserializeObject <Dictionary <string, object> >(jsonData); object actionObject; dictJson.TryGetValue("actionName", out actionObject); string Action = actionObject.ToString(); string input_queryapi = ""; object userName; dictJson.TryGetValue("queried_by_username", out userName); if (Action.Equals("GetTableSchema")) { object tableName; dictJson.TryGetValue("TableName", out tableName); input_queryapi = tableName.ToString(); response = db.Query("INFORMATION_SCHEMA.COLUMNS").Select("COLUMN_NAME").WhereRaw("TABLE_NAME = ?", tableName).Get().Cast <IDictionary <string, object> >(); return(Request.CreateResponse(HttpStatusCode.OK, response)); } if (Action.Equals("GetInstructorByName")) { object empName; dictJson.TryGetValue("EmpName", out empName); input_queryapi = empName.ToString(); response = db.Query("Employee").WhereRaw("lower(EmpName) = ?", empName).Get().Cast <IDictionary <string, object> >(); //response = db.Query("Employee").WhereRaw("lower(EmpName) = ?", "dr. abdul aziz").Get().Cast<IDictionary<string, object>>(); } else if (Action.Equals("GetInstructorByEmail")) { object email; dictJson.TryGetValue("Email", out email); input_queryapi = email.ToString(); response = db.Query("Employee").WhereRaw("lower(Email) = ?", email).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetInstructorByRank")) { object designationTitle; dictJson.TryGetValue("DesignationTitle", out designationTitle); input_queryapi = designationTitle.ToString(); response = db.Query("Employee").Join("Designation", "Employee.DesignationID", "Designation.DesignationID").Where("DesignationTitle", designationTitle).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } else if (Action.Equals("GetInstructorByDepartment")) { object departmentID; dictJson.TryGetValue("DepartmentID", out departmentID); input_queryapi = departmentID.ToString(); response = db.Query("Employee").Join("Department", "Employee.DepartmentID", "Department.DepartmentID").Where("Department.DepartmentID", departmentID).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } else if (Action.Equals("GetInstructorByNameStartsWith")) { object startNamePrefix; dictJson.TryGetValue("EmpName", out startNamePrefix); string prefixString = startNamePrefix.ToString() + "%"; input_queryapi = startNamePrefix.ToString(); response = db.Query("Employee").WhereLike("EmpName", prefixString).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } else if (Action.Equals("GetInstructorByNameContains")) { object startNamePrefix; dictJson.TryGetValue("EmpName", out startNamePrefix); string prefixString = "%" + startNamePrefix.ToString() + "%"; input_queryapi = startNamePrefix.ToString(); response = db.Query("Employee").WhereLike("EmpName", prefixString).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } else if (Action.Equals("GetInstructorByCourseName")) { object courseName; dictJson.TryGetValue("CourseName", out courseName); input_queryapi = courseName.ToString(); response = db.Query("Employee").Join("CourseFaculty", "Employee.EmpID", "CourseFaculty.EmpID").Join("CourseOffered", "CourseOffered.CourseOfferedID", "CourseFaculty.CourseOfferedID").Join("Course", "CourseOffered.CourseID", "Course.CourseID").Where("CourseName", courseName).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } else if (Action.Equals("GetInstructorByCourseCode")) { object courseCode; dictJson.TryGetValue("CourseCode", out courseCode); input_queryapi = courseCode.ToString(); response = db.Query("Employee").Join("CourseFaculty", "Employee.EmpID", "CourseFaculty.EmpID").Join("CourseOffered", "CourseOffered.CourseOfferedID", "CourseFaculty.CourseOfferedID").Join("Course", "CourseOffered.CourseID", "Course.CourseID").Where("CourseCode", courseCode).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } else if (Action.Equals("GetCoursesTaughtByASpecificInstructor")) { object EmpName; dictJson.TryGetValue("EmpName", out EmpName); input_queryapi = EmpName.ToString(); response = db.Query("Semester").Select("CourseName").Join("CourseOffered", "Semester.SemesterID", "CourseOffered.SemesterID").Join("Course", "Course.CourseID", "CourseOffered.CourseID") .Join("CourseFaculty", "CourseFaculty.CourseOfferedID", "CourseOffered.CourseOfferedID") .Join("Employee", "Employee.EmpID", "CourseFaculty.EmpID") .Where("EmpName", EmpName).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } else if (Action.Equals("GetCoursesTaughtByAInstructorInAParticularSemester")) { object semesterName; object empName; dictJson.TryGetValue("EmpName", out empName); dictJson.TryGetValue("SemesterName", out semesterName); input_queryapi = empName.ToString() + " " + semesterName.ToString(); response = db.Query("Semester").Join("CourseOffered", "Semester.SemesterID", "CourseOffered.SemesterID").Join("Course", "Course.CourseID", "CourseOffered.CourseID") .Join("CourseFaculty", "CourseFaculty.CourseOfferedID", "CourseOffered.CourseOfferedID") .Join("Employee", "Employee.EmpID", "CourseFaculty.EmpID") .Where("SemesterName", semesterName) .Where("EmpName", empName).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } // STUDENT QUERIES else if (Action.Equals("GetStudentByName")) { object SName; dictJson.TryGetValue("SName", out SName); input_queryapi = SName.ToString(); response = db.Query("Student").WhereRaw("lower(SName) = ?", SName).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentByStudentID")) { object RollNumber; dictJson.TryGetValue("RollNumber", out RollNumber); input_queryapi = RollNumber.ToString(); response = db.Query("Student").WhereRaw("lower(RollNumber) = ?", RollNumber).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentByBatchID")) { object BatchID; dictJson.TryGetValue("BatchID", out BatchID); input_queryapi = BatchID.ToString(); response = db.Query("Student").WhereRaw("BatchID = ?", BatchID).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentsByDepartment")) { object DepartmentName; dictJson.TryGetValue("DepartmentName", out DepartmentName); input_queryapi = DepartmentName.ToString(); response = db.Query("Student").Join("Programme", "Programme.ProgrammeID", "Student.ProgrammeID").Join("Department", "Department.DepartmentID", "Programme.DepartmentID") .Where("DepartmentName", DepartmentName).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentsBySection")) { object SectionName; dictJson.TryGetValue("SectionName", out SectionName); input_queryapi = SectionName.ToString(); response = db.Query("Student").Join("Section", "Section.BatchID", "Student.BatchID") .Where("SectionName", SectionName).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentByNUEmail")) { object Email; dictJson.TryGetValue("Email", out Email); input_queryapi = Email.ToString(); response = db.Query("Student").Join("CandidateStudent", "CandidateStudent.CandidateID", "Student.CandidateID") .Where("Student.Email", Email).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentByPrimaryEmail")) { object Email; dictJson.TryGetValue("Email", out Email); input_queryapi = Email.ToString(); response = db.Query("Student").Join("CandidateStudent", "CandidateStudent.CandidateID", "Student.CandidateID") .Where("CandidateStudent.Email", Email).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentsByNameStartsWith")) { object startNamePrefix; dictJson.TryGetValue("Sname", out startNamePrefix); input_queryapi = startNamePrefix.ToString(); string prefixString = startNamePrefix.ToString() + "%"; response = db.Query("Student").WhereLike("Sname", prefixString).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } else if (Action.Equals("GetStudentsByNameContains")) { object startNamePrefix; dictJson.TryGetValue("EmpName", out startNamePrefix); input_queryapi = startNamePrefix.ToString(); string prefixString = "%" + startNamePrefix.ToString() + "%"; response = db.Query("Student").WhereLike("Sname", prefixString).Get().Cast <IDictionary <string, object> >(); //get product by id=1 } else if (Action.Equals("GetStudentsByCourseName")) { object CourseName; dictJson.TryGetValue("CourseName", out CourseName); input_queryapi = CourseName.ToString(); response = db.Query("Student").Join("CourseEnrollment", "CourseEnrollment.StudentID", "Student.StudentID").Join("Course", "Course.CourseID", "CourseEnrollment.CourseID") .Where("CourseName", CourseName).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentsByCourseCode")) { object CourseCode; dictJson.TryGetValue("CourseCode", out CourseCode); input_queryapi = CourseCode.ToString(); response = db.Query("Student").Join("CourseEnrollment", "CourseEnrollment.StudentID", "Student.StudentID").Join("Course", "Course.CourseID", "CourseEnrollment.CourseID") .Where("CourseCode", CourseCode).Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentsBySkill")) { response = db.Query("StudentSkills").Join("Skill", "Skill.SkillID", "StudentSkills.SkillID").Join("Student", "StudentSkills.StudentID", "Student.StudentID") .Get().Cast <IDictionary <string, object> >(); } else if (Action.Equals("GetStudentsByDomain")) { response = db.Query("Domain").Join("Skill", "Skill.DomainID", "Domain.DomainID").Join("StudentSkills", "StudentSkills.SkillID", "Skill.SkillID").Join("Student", "Student.StudentID", "StudentSkills.StudentID") .Get().Cast <IDictionary <string, object> >(); } //var db1 = DbUtils.GetDBConnection(); //db1.Connection.Open(); SqlConnection dbConnection = new SqlConnection(ConfigurationManager.AppSettings["SqlDBConn"].ToString()); try { /** * db1.Query("dbo.SearchLog").AsInsert(new * { * input_query = input_queryapi, * actionName = Action, * queried_by_username = userName.ToString() * }); **/ string query = "INSERT INTO dbo.SearchLog(input_query, actionName, queried_by_username) VALUES(@input_query,@actionName,@queried_by_username)"; using (SqlCommand command = new SqlCommand(query, dbConnection)) { command.Parameters.AddWithValue("@input_query", input_queryapi); command.Parameters.AddWithValue("@actionName", Action); command.Parameters.AddWithValue("@queried_by_username", userName.ToString()); dbConnection.Open(); int result = command.ExecuteNonQuery(); // Check Error if (result < 0) { Console.WriteLine("Error inserting data into Database!"); } } } catch (Exception e) { } return(Request.CreateResponse(HttpStatusCode.OK, response)); }