/// <summary> /// Connects to the SQL Database and searches for the SearchTerm st and returns a list of results /// </summary> /// <param name="st">An instance of SearchTerm containing the different search parameters</param> /// <returns>A list containing the results of the search</returns> public List <SearchResult> retrieveData(SearchTerm st) { string connectionString = "Server=mysql.cs.ksu.edu;Database=kabsu; User ID = kabsu; Password = insecurepassword; Integrated Security=true"; try { using (var connection = new MySqlConnection(connectionString)) { using (var command = new MySqlCommand("kabsu.RetrieveData", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Clear(); command.Parameters.AddWithValue("@Owner", st.Owner); command.Parameters.AddWithValue("@Breed", st.Breed); command.Parameters.AddWithValue("@AnimalName", st.AnimalName); command.Parameters.AddWithValue("@Code", st.Code); command.Parameters.Add("@CanNum", MySqlDbType.VarChar, 32); command.Parameters["@CanNum"].Value = (st.CanNum); command.Parameters.AddWithValue("@Town", st.Town); command.Parameters.AddWithValue("@State", st.State); connection.Open(); var reader = command.ExecuteReader(); var resultList = new List <SearchResult>(); while (reader.Read()) { searchResult = new SearchResult( reader.GetString(reader.GetOrdinal("CanNum")), reader.GetString(reader.GetOrdinal("AnimalID")), reader.GetString(reader.GetOrdinal("CollDate")), reader.GetInt32(reader.GetOrdinal("NumUnits")), reader.GetString(reader.GetOrdinal("AnimalName")), reader.GetString(reader.GetOrdinal("Breed")), reader.GetString(reader.GetOrdinal("RegNum")), reader.GetString(reader.GetOrdinal("PersonName")), reader.GetString(reader.GetOrdinal("Town")), reader.GetString(reader.GetOrdinal("State")), reader.GetString(reader.GetOrdinal("Country")), reader.GetString(reader.GetOrdinal("Species"))); resultList.Add(searchResult); } connection.Close(); return(resultList); } } } catch (Exception ex) { MessageBox.Show("Unable to connect to database."); return(new List <SearchResult>()); } }
/// <summary> /// Connects to the SQL Database and searches for the SearchTerm st and returns a list of results /// </summary> /// <param name="st">An instance of SearchTerm containing the different search parameters</param> /// <returns>A list containing the results of the search</returns> public List <SearchResult> retrieveData(SearchTerm st) { string connectionString = "Server=mysql.cs.ksu.edu;Database=kabsu; User ID = kabsu; Password = insecurepassword; Integrated Security=true"; //The connection string of the current database location try { using (var connection = new MySqlConnection(connectionString)) { using (var command = new MySqlCommand("kabsu.RetrieveData", connection)) //Initializes command to the RetrieveData stored procedure { command.CommandType = CommandType.StoredProcedure; //Add variables from the search term as inputs for the procedure. command.Parameters.AddWithValue("@Owner", st.Owner); command.Parameters.AddWithValue("@Breed", st.Breed); command.Parameters.AddWithValue("@AnimalName", st.AnimalName); command.Parameters.AddWithValue("@Code", st.Code); command.Parameters.AddWithValue("@CanNum", st.CanNum); command.Parameters.AddWithValue("@Town", st.Town); command.Parameters.AddWithValue("@State", st.State); connection.Open(); var reader = command.ExecuteReader(); //Executes a procedure with a reader returning the result rows var resultList = new List <SearchResult>(); while (reader.Read()) //While there are still rows to return { searchResult = new SearchResult( //Create a search result of the current row reader.GetString(reader.GetOrdinal("Valid")), reader.GetString(reader.GetOrdinal("CanNum")), reader.GetString(reader.GetOrdinal("AnimalID")), reader.GetString(reader.GetOrdinal("CollDate")), reader.GetString(reader.GetOrdinal("NumUnits")), reader.GetString(reader.GetOrdinal("AnimalName")), reader.GetString(reader.GetOrdinal("Breed")), reader.GetString(reader.GetOrdinal("RegNum")), reader.GetString(reader.GetOrdinal("PersonName")), reader.GetString(reader.GetOrdinal("City")), reader.GetString(reader.GetOrdinal("State")), reader.GetString(reader.GetOrdinal("Country")), reader.GetString(reader.GetOrdinal("Species"))); resultList.Add(searchResult); //Add the result to the result list } return(resultList); //return the populated list of results } } } catch (Exception) //Catches any SQL Exceptions and aborts the procedure while sending an error message. { MessageBox.Show("Unable to connect to database."); return(new List <SearchResult>()); //return empty list } }
/// <summary> /// method to create a new search term and retrieve its data /// </summary> /// <returns></returns> public List <SearchResult> CalculateResultList() { SetAllTerms(); searchTerm = new SearchTerm(canNum, code, animalName, breed, owner, town, state); searchResults = new SearchResults(); List <SearchResult> results = searchResults.retrieveData(searchTerm); return(results); }
/// <summary> /// method to create a new search term and retrieve its data by a list of strings /// </summary> /// <returns></returns> public List <string> CalculateInventoryList() { SetAllTerms(); searchTerm = new SearchTerm(canNum, code, animalName, breed, owner, town, state); searchResults = new SearchResults(); List <SearchResult> results = searchResults.retrieveData(searchTerm); List <string> description = new List <string>(); foreach (SearchResult s in results) { description.Add(s.ToString()); } return(description); }
/// <summary> /// Sorts the terms in the text boxes by the SearchContents text, and creates a Search Term to /// find the search results with. Returns the results of the search in SearchResults /// </summary> /// <returns>A list of search results</returns> private List <SearchResult> CalculateResultList() { //Sort the unspecified search terms SetTerm(uxSearchTerm1.Text, uxSearchContents1.Text); SetTerm(uxSearchTerm2.Text, uxSearchContents2.Text); SetTerm(uxSearchTerm3.Text, uxSearchContents3.Text); SetTerm(uxSearchTerm4.Text, uxSearchContents4.Text); searchTerm = new SearchTerm(canNum, code, animalName, breed, owner, town, state); searchTerm = new SearchTerm(canNum, code, animalName, breed, owner, town, state); searchResults = new SearchResults(); List <SearchResult> results = searchResults.retrieveData(searchTerm); //Executes database search procedure, returns SearchResult List return(results); }
private static string CONNECTION_STRING = "Server=mysql.cs.ksu.edu;Database=kabsu; User ID = kabsu; Password = insecurepassword; Integrated Security=true"; //The connection string of the current database location /// <summary> /// Constructor for the window, which initializes initial column width and sets the Grid /// item source to be a list of search results. /// </summary> /// <param name="results">A list of database search results</param> /// <param name="searchTerm">A collection of search terms to search with</param> public SearchWindowResults(List <SearchResult> results, SearchTerm searchTerm) { //Uncomment Line below to use one-time database excel file insertion. Triggers on search, takes A LONG TIME, close and re-comment directly after search. //DataLoad.DatabaseLoad(); this.searchTerm = searchTerm; InitializeComponent(); uxSearchResults.ItemsSource = results; //initializes the data grid's context to be the search results ValidColumn.Width = 40; CanNumColumn.Width = 50; CodeColumn.Width = 110; CollDateColumn.Width = 90; UnitsColumn.Width = 40; AnimalNameColumn.Width = 225; BreedColumn.Width = 80; RegNumColumn.Width = 80; OwnerColumn.Width = 100; TownColumn.Width = 100; StateColumn.Width = 42; Activated += Window_Activated; }
/// <summary> /// this finds the capacity of the cans in question /// if they are entered with ',' seperating them, then if will take every can # entered. /// if no can numbers were entered, it returns an empty list /// Note that this function is very slow. /// </summary> /// <returns></returns> public List <SearchResult> RetrieveCanList(out int numOfCans) { SearchResults searchResults = new SearchResults(); List <SearchResult> results = new List <SearchResult>(); //Case: no can numbers were entered. if (canNum.Equals("*")) { SearchTerm searchTerm = new SearchTerm(canNum, code, animalName, breed, owner, town, state); results = searchResults.retrieveData(searchTerm); numOfCans = results.GroupBy(x => x.CanNum).Count(); return(results); } //Check that query is valid if (IsValidQuery(canNum)) { //split the query by ',' string[] canNumbers = canNum.Split(','); string[] canRange = new string[(canNumbers.Length * 2)]; int j = 0; //split each section by ':' for (int i = 0; i < canNumbers.Length; i++) { string[] temp = canNumbers[i].Split(':'); if (temp.Length > 1) { canRange[j] = temp[0]; canRange[j + 1] = temp[1]; j += 2; //if there is a colon, delete that query from canNumbers canNumbers[i] = ""; } } //get all cans from canNumbers foreach (string number in canNumbers) { if (number != "") { SearchTerm searchTerm = new SearchTerm(number.Trim().ToString(), code, animalName, breed, owner, town, state); results.AddRange(searchResults.retrieveData(searchTerm)); } } for (int i = 0; i < canRange.Length; i += 2) { if (canRange[i] != null) { int begin = Int32.Parse(canRange[i]); int end = Int32.Parse(canRange[i + 1]); if (begin > end) { int temp = begin; begin = end; end = temp; } while (begin <= end) { SearchTerm searchTerm = new SearchTerm(begin.ToString(), code, animalName, breed, owner, town, state); results.AddRange(searchResults.retrieveData(searchTerm)); begin++; } } } numOfCans = results.GroupBy(x => x.CanNum).Count(); return(results); } else { //TODO: tell user the input was invalid numOfCans = 0; return(results); } }