/// <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);
}
}
