/** * This function replaces the URL result to the new one(and it's fathers). * NOTE:the new data stored in the data base is the data of the given newResult * so make sure that all the needed data there is valid(except from the resultID). */ public void replaceURLResult(String taskId, Result oldResult, Result newResult) { SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); try { conn.Open(); SqlCommand cmd = new SqlCommand("DELETE FROM Results WHERE Url = \'" + oldResult.getUrl() + "\' AND TaskID = \'" + taskId + "\'", conn); cmd.ExecuteNonQuery(); addURLResult(taskId, newResult); } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (conn != null) { conn.Close(); } } }
/** * This function returns a list of urls which suits a specific category, every url will be described * as object which contains data about: url, categoryId, rank, trustMeter. */ public List <Result> getURLsFromCategory(String taskId, String categoryId, Order order, int from, int to) { SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); SqlDataReader rdr = null; List <Result> resultUrls = new List <Result>(); try { conn.Open(); SqlCommand cmd = null; string orderStr = getOrderString(order); if (categoryId == null) { cmd = new SqlCommand("SELECT ResultID,Url,rank,TrustMeter,CategoryID From Results WHERE " + "TaskID = \'" + taskId + "\'" + orderStr, conn); } else { cmd = new SqlCommand("SELECT ResultID,Url,rank,TrustMeter,CategoryID From Results WHERE " + "TaskID = \'" + taskId + "\' AND CategoryID = \'" + categoryId + "\'" + orderStr, conn); } rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { int rank = Convert.ToInt32(rdr["rank"].ToString().Trim()); int trustMeter = Convert.ToInt32(rdr["TrustMeter"].ToString().Trim()); Result resultItem = new Result(rdr["ResultID"].ToString().Trim(), rdr["Url"].ToString().Trim(), rdr["CategoryID"].ToString().Trim(), rank, trustMeter); resultUrls.Add(resultItem); } } //SqlCommand cmnd = new SqlCommand("SELECT CategoryID From Category WHERE ParentCategory = \'" + // categoryId + "\'",conn); // rdr = cmnd.ExecuteReader(); } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } return(rangeOfResults(resultUrls, from, to)); }
/** * removes the predifined property fields in the specified task * it will return the number of rows which has been removed due to this remove */ public int removeProperty(String taskId, String property) { SqlConnection conn = null; int rowsRemoved = 0; try { conn = new SqlConnection(SettingsReader.getConnectionString()); conn.Open(); SqlCommand cmd = new SqlCommand("DELETE FROM TaskProperties WHERE TaskID=\'" + taskId + "\' AND Property=\'" + property + "\'", conn); rowsRemoved = cmd.ExecuteNonQuery(); } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (conn != null) { conn.Close(); } } return(rowsRemoved); }
/** * inserts a new property in the database with the specified value * note: it will add another property entry if you have used this property before. */ public int insertProperty(String taskId, String property, String value) { SqlConnection conn = null; int rowsRemoved = 0; try { conn = new SqlConnection(SettingsReader.getConnectionString()); conn.Open(); SqlCommand cmd = new SqlCommand("INSERT INTO TaskProperties (TaskID,Property,Value)" + " Values (\'" + taskId + "\',\'" + property + "\',\'" + value + "\')", conn); cmd.ExecuteNonQuery(); } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (conn != null) { conn.Close(); } } return(rowsRemoved); }
public void changeWorkDetails(TaskStatus status) { SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); String myStatus = TaskStatus.convertToStatusString(status.getTaskStatus()); try { conn.Open(); String cmdtxt = "UPDATE Task SET TaskName=\'" + status.getTaskName() + "\' ,Status =\'" + myStatus + "\' ,ElapsedTime =\'" + status.getTaskElapsedTime() + "\' WHERE TaskID = \'" + status.getTaskID() + "\'"; SqlCommand cmd = new SqlCommand(cmdtxt, conn); cmd.CommandText = cmdtxt; cmd.ExecuteNonQuery(); } finally { if (conn != null) { conn.Close(); } } }
/** * sets the seeds for the specified task */ public void setSeedList(String taskId, List <String> seeds) { SqlConnection conn = null; try { removeProperty(taskId, "SEED"); conn = new SqlConnection(SettingsReader.getConnectionString()); conn.Open(); foreach (String seed in seeds) { SqlCommand cmd = new SqlCommand("INSERT INTO TaskProperties (TaskID,Property,Value)" + " Values (\'" + taskId + "\',\'" + "SEED" + "\',\'" + seed + "\')", conn); cmd.ExecuteNonQuery(); } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (conn != null) { conn.Close(); } } }
/** * sets the categories for the specified task */ public void setCategories(String taskId, List <Category> categoryList) { resetCategories(taskId); SqlConnection conn = null; try { conn = new SqlConnection(SettingsReader.getConnectionString()); conn.Open(); foreach (Category category in categoryList) { String keywords = ""; bool flag = true; foreach (string token in category.getKeywordList()) { if (flag == true) { keywords = token; flag = false; } else { keywords = keywords + ";" + token; } } SqlCommand cmd = null; Console.WriteLine(category.getParentName()); if ((category.getParentName() != null) && (category.getParentName() != "")) { cmd = new SqlCommand("INSERT INTO Category (TaskID,CategoryName,Keywords,ParentCategory,ConfidenceLevel)" + " Values (\'" + taskId + "\',\'" + category.getCatrgoryName() + "\',\'" + keywords + "\',\'" + category.getParentName() + "\'," + category.getConfidenceLevel().ToString() + ")", conn); } else { cmd = new SqlCommand("INSERT INTO Category (TaskID,CategoryName,Keywords,ConfidenceLevel)" + " Values (\'" + taskId + "\',\'" + category.getCatrgoryName() + "\',\'" + keywords + "\'," + category.getConfidenceLevel().ToString() + ")", conn); } cmd.ExecuteNonQuery(); } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (conn != null) { conn.Close(); } } }
/** * returns the category list of the specified task */ public List <Category> getCategories(String taskId) { List <Category> categoryList = new List <Category>(); SqlConnection conn = null; SqlDataReader rdr = null; try { conn = new SqlConnection(SettingsReader.getConnectionString()); conn.Open(); SqlCommand cmd = new SqlCommand("SELECT CategoryID,CategoryName,Keywords,ParentCategory,ConfidenceLevel" + " FROM Category WHERE TaskID=\'" + taskId + "\'", conn); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { int confidenceLevel = Convert.ToInt32(rdr["ConfidenceLevel"]); List <String> dbkeywords = null, keywords = new List <string>(); if (!rdr["Keywords"].Equals(System.DBNull.Value)) { dbkeywords = new List <string>(((String)rdr["Keywords"]).Split(';')); } foreach (string key in dbkeywords) { if (key.Length != 0) { keywords.Add(key); } } Category category = new Category(rdr["CategoryID"].ToString(), rdr["ParentCategory"].ToString(), rdr["CategoryName"].ToString().Trim(), keywords, confidenceLevel); categoryList.Add(category); } } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } return(categoryList); }
/** * This function gets results of a task, the results will be represented as list of Result object: * categoryId, rank, trustMeter. */ public List <Result> getURLResults(String taskId, String url) { // 1. Instantiate the connection SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); SqlDataReader rdr = null; List <Result> resultsCrawled = new List <Result>(); try { conn.Open(); SqlCommand cmd = new SqlCommand("SELECT ResultID,Url,CategoryID,rank,TrustMeter" + " FROM Results " + "WHERE TaskID=\'" + taskId + "\' AND Url = \'" + url + "\'", conn); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { int rank = Convert.ToInt32(rdr["rank"].ToString().Trim()); int trustMeter = Convert.ToInt32(rdr["TrustMeter"].ToString().Trim()); Result resultItem = new Result(rdr["ResultID"].ToString(), rdr["Url"].ToString().Trim(), rdr["CategoryID"].ToString(), rank, trustMeter); resultsCrawled.Add(resultItem); } } else { resultsCrawled = null; } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } return(resultsCrawled); }
/** * sets a new restrictions for the specified task */ public void setRestrictions(String taskId, Constraints constrains) { SqlConnection conn = null; SqlDataReader rdr = null; try { conn = new SqlConnection(SettingsReader.getConnectionString()); conn.Open(); SqlCommand cmd = new SqlCommand("UPDATE Task" + " SET LinkDepth=\'" + constrains.getAllowedDepth() + "\', AllowUrlParam=\'" + constrains.isParametrizationAllowed() + "\' WHERE TaskID=\'" + taskId + "\'", conn); cmd.ExecuteNonQuery(); removeProperty(taskId, "ALLOW"); foreach (String crawl in constrains.getCrawlList()) { cmd = new SqlCommand("INSERT INTO TaskProperties (TaskID,Property,Value)" + " Values (\'" + taskId + "\',\'" + "ALLOW" + "\',\'" + crawl + "\')", conn); cmd.ExecuteNonQuery(); } removeProperty(taskId, "RESTRICT"); foreach (String crawl in constrains.getRestrictionList()) { cmd = new SqlCommand("INSERT INTO TaskProperties (TaskID,Property,Value)" + " Values (\'" + taskId + "\',\'" + "RESTRICT" + "\',\'" + crawl + "\')", conn); cmd.ExecuteNonQuery(); } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } }
/** * This function returns the number of the urls which already has been crawled that belongs to the * given taskId. */ public ulong getTotalURLs(String taskId, String categoryId) { SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); SqlDataReader rdr = null; ulong totalUrls = 0; try { conn.Open(); SqlCommand cmd = null; if (categoryId == null) { cmd = new SqlCommand("SELECT COUNT(TaskID) AS TotalUrls FROM Results " + "WHERE TaskID = \'" + taskId + "\'", conn); } else { cmd = new SqlCommand("SELECT COUNT(TaskID) AS TotalUrls FROM Results " + "WHERE TaskID = \'" + taskId + "\' AND CategoryID = \'" + categoryId + "\'", conn); } rdr = cmd.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); totalUrls = Convert.ToUInt32(rdr["TotalUrls"].ToString().Trim()); } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } return(totalUrls); }
/** * returns the seeds list for the specified task */ public List <String> getSeedList(String taskId) { List <String> seedsList = new List <string>(); SqlConnection conn = null; SqlDataReader rdr = null; try { conn = new SqlConnection(SettingsReader.getConnectionString()); conn.Open(); SqlCommand cmd = new SqlCommand("SELECT Value" + " FROM TaskProperties WHERE TaskID=\'" + taskId + "\' AND Property=\'SEED\'", conn); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { seedsList.Add(rdr["Value"].ToString()); } } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); seedsList = null; } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } return(seedsList); }
/** * returns the property value; null in case property not found */ public String getProperty(String taskId, String property) { SqlConnection conn = null; SqlDataReader rdr = null; try { conn = new SqlConnection(SettingsReader.getConnectionString()); conn.Open(); SqlCommand cmd = new SqlCommand("SELECT Value" + " FROM TaskProperties WHERE TaskID=\'" + taskId + "\' AND Property=\'" + property + "\'", conn); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { if (rdr.Read()) { return(rdr["Value"].ToString()); } } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } return(null); }
public void releaseWorkResources(String taskId) { SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); try { conn.Open(); String cmdtxt = "DELETE FROM TaskProperties WHERE TaskID = \'" + taskId + "\'"; SqlCommand cmd = new SqlCommand(cmdtxt, conn); cmd.CommandText = cmdtxt; cmd.ExecuteNonQuery(); cmdtxt = "DELETE FROM Results WHERE TaskID = \'" + taskId + "\'"; cmd.CommandText = cmdtxt; cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE FROM Category WHERE TaskID = \'" + taskId + "\'"; cmd.ExecuteNonQuery(); cmd.CommandText = "DELETE FROM Task WHERE TaskID = \'" + taskId + "\'"; cmd.ExecuteNonQuery(); } finally { if (conn != null) { conn.Close(); } } }
/** * This method sets the given categoryID(of the parent) to be a parent * of the given other categoryID(of the son). */ public void setParentToSon(String parentID, String sonID) { SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); try { conn.Open(); String cmdtxt = "UPDATE Category SET ParentCategory=\'" + parentID + "\' WHERE CategoryID = \'" + sonID + "\'"; if (parentID == "" || parentID == null) { cmdtxt = "UPDATE Category SET ParentCategory=NULL WHERE CategoryID = \'" + sonID + "\'"; } SqlCommand cmd = new SqlCommand(cmdtxt, conn); cmd.ExecuteNonQuery(); } finally { if (conn != null) { conn.Close(); } } }
/** * This function removes all entries for specific task, which specified in the given argument. */ public void removeAllResults(String taskID) { SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString()); try { conn.Open(); SqlCommand cmd = new SqlCommand("DELETE FROM Results WHERE TaskID = \'" + taskID + "\'", conn); cmd.ExecuteNonQuery(); } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); } finally { if (conn != null) { conn.Close(); } } }
/** * returns the saved constrains for the specified task */ public Constraints getRestrictions(String taskId) { Constraints constrains; SqlConnection conn = null; SqlDataReader rdr = null; try { bool taskExist = false; string restrict = "", crawl = ""; int linkDepth = 1; bool parametersAllowed = false; conn = new SqlConnection(SettingsReader.getConnectionString()); conn.Open(); SqlCommand cmd = new SqlCommand("SELECT LinkDepth,AllowUrlParam" + " FROM Task WHERE TaskID=\'" + taskId + "\'", conn); rdr = cmd.ExecuteReader(); if (rdr.HasRows) { if (rdr.Read()) { taskExist = true; linkDepth = Convert.ToInt32(rdr["LinkDepth"]); int allowParameters = Convert.ToInt32(rdr["AllowUrlParam"]); parametersAllowed = (allowParameters != 0); } else { throw new Exception(); } } cmd = new SqlCommand("SELECT Value" + " FROM TaskProperties WHERE TaskID=\'" + taskId + "\' AND Property=\'RESTRICT\'", conn); if (rdr != null) { rdr.Close(); } rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { restrict = restrict + rdr["Value"] + ' '; } if (restrict.Length != 0) { restrict = restrict.TrimEnd(new char[] { ' ' }); } } cmd = new SqlCommand("SELECT Value" + " FROM TaskProperties WHERE TaskID=\'" + taskId + "\' AND Property=\'ALLOW\'", conn); if (rdr != null) { rdr.Close(); } rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { crawl = crawl + rdr["Value"] + ' '; } if (crawl.Length != 0) { crawl = crawl.TrimEnd(new char[] { ' ' }); } } if (taskExist) { constrains = new Constraints((uint)linkDepth, parametersAllowed, restrict, crawl); } else { constrains = null; } } catch (Exception e) { System.Console.WriteLine("Exception Caught: " + e.Message); constrains = null; } finally { if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } } return(constrains); }
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 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); }
/** * 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(); } } }