/** * This function gets UserID and QueryOption and returns the TaskID,TaskName, * Status,ElapsedTime of all the tasks that have the given UserID and that they are * in the state of the given QueryOpiton. */ public List <TaskStatus> getWorkDetails(String userID, QueryOption option) { // 1. Instantiate the connection SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); SqlDataReader rdr = null; List <TaskStatus> taskDetailsList = new List <TaskStatus>(); try { // 2. Open the connection conn.Open(); SqlCommand cmd; String statusString = ""; switch (option) { case QueryOption.ActiveTasks: statusString = "ACTIVE"; break; case QueryOption.IdleTasks: statusString = "IDLE"; break; case QueryOption.WaitingTasks: statusString = "WAITING"; break; default: statusString = ""; break; } if (option == QueryOption.AllTasks) { cmd = new SqlCommand("SELECT TaskID,TaskName,Status,ElapsedTime from Task WHERE UserID=\'" + userID + "\'", conn); } else { cmd = new SqlCommand("SELECT TaskID,TaskName,Status,ElapsedTime from Task" + " WHERE UserID=\'" + userID + "\' AND Status=\'" + statusString + "\'", conn); } // get query results rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { TaskStatus taskDetails = new TaskStatus(rdr["TaskID"].ToString()); taskDetails.setTaskElapsedTime((long)rdr["ElapsedTime"]); taskDetails.setTaskName((String)rdr["TaskName"]); Status statusOfTask = TaskStatus.convertToStatusObj((String)rdr["Status"]); taskDetails.setTaskStatus(statusOfTask); taskDetailsList.Add(taskDetails); } } } finally { // close the reader if (rdr != null) { rdr.Close(); } // 5. Close the connection if (conn != null) { conn.Close(); } } return(taskDetailsList); }
public String createWorkResources(String userID, String taskName) { SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); String taskid = null; SqlDataReader rdr = null; try { conn.Open(); //insert new row to Task String cmdtxt = "SELECT TaskName FROM Task WHERE UserID = \'" + userID + "\'"; SqlCommand cmd = new SqlCommand(cmdtxt, conn); //Execute command rdr = cmd.ExecuteReader(); //check if the inserted userid has a task named taskName if (rdr.HasRows) { while (rdr.Read()) { //Console.WriteLine(rdr["TaskName"]); string nameExtacted = (string)rdr["TaskName"]; nameExtacted = nameExtacted.TrimEnd(' '); if (nameExtacted == taskName) { throw new Exception("TaskName for the user allready exists"); } } } if (rdr != null) { rdr.Close(); } //if the taskName does not exist in the table for the inserted userid //then insert it into the table. cmdtxt = "INSERT INTO Task (UserID,TaskName,Status,ElapsedTime,LinkDepth,AllowUrlParam) " + "VALUES (\'" + userID + "\',\'" + taskName + "\',\'IDLE\',0,4,\'false\')"; cmd.CommandText = cmdtxt; cmd.ExecuteNonQuery(); //return the taskID of the new row created cmdtxt = "SELECT TaskID FROM Task WHERE UserID=\'" + userID + "\' AND TaskName=\'" + taskName + "\'"; cmd.CommandText = cmdtxt; rdr = cmd.ExecuteReader(); while (rdr.Read()) { taskid = rdr["TaskID"].ToString(); } } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } return(taskid); }
/** * This function adds the URL result to the given categories (and it's fathers). */ public void addURLResult(String taskId, Result result) { SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); SqlDataReader rdr = null; String categoryID = result.getCategoryID(); try { conn.Open(); SqlCommand cmdcheck = new SqlCommand("SELECT TaskName FROM Task WHERE TaskID = \'" + taskId + "\'", conn); rdr = cmdcheck.ExecuteReader(); if (rdr.HasRows) { if (rdr != null) { rdr.Close(); } if ((categoryID == "") || (categoryID == "0") || (categoryID == null)) { //System.Console.WriteLine("1> Cat ID: " + categoryID); SqlCommand cmd = new SqlCommand("INSERT INTO Results (TaskID,Url,rank,TrustMeter) " + "Values(\'" + taskId + "\',\'" + result.getUrl() + "\',\'" + result.getRank() + "\',\'" + result.getTrustMeter() + "\')", conn); cmd.ExecuteNonQuery(); } else { while ((categoryID != null) && (categoryID != "")) { //System.Console.WriteLine("2> Cat ID: " + categoryID); SqlCommand cmd = new SqlCommand("INSERT INTO Results (TaskID,Url,CategoryID,rank,TrustMeter) " + "Values(\'" + taskId + "\',\'" + result.getUrl() + "\',\'" + categoryID + "\',\'" + result.getRank() + "\',\'" + result.getTrustMeter() + "\')", conn); cmd.ExecuteNonQuery(); SqlCommand cmnd = new SqlCommand("SELECT ParentCategory From Category WHERE CategoryID = \'" + categoryID + "\'", conn); rdr = cmnd.ExecuteReader(); if (rdr.HasRows) { if (rdr.Read()) { categoryID = rdr["ParentCategory"].ToString(); } else { categoryID = null; } } if (rdr != null) { rdr.Close(); } } } } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } }