void Button2Click(object sender, System.EventArgs e) { int myIndex = comboBox1.SelectedIndex; string selectedIndex = ""; string selectedName = ""; dbResult localResult = new dbResult(); selectedIndex = localIndex[myIndex]; selectedName = localName[myIndex]; localResult.queryDatabase("WHERE ((MyNumber='" + selectedIndex + "')", ")", localResult, conn2); localResult.ClearIndex(); localResult.ClearName(); for (int i = 0; i < localIndex.Count; i++) { localResult.SetIndex(localIndex[i]); localResult.SetName(localName[i]); } //delete the entry we're going to edit localResult.deleteFromDatabase(localIndex[myIndex], conn2); //set up the extraction form and show ExtractionForm editForm = new ExtractionForm(myDBPath, localResult); editForm.Show(); }
void ComboBox1SelectedIndexChanged(object sender, EventArgs e) { //take the new index of the combobox and look up the index of the local list. //use the local list value to search the database user IDs. //then repopulate the form. //access connections have already been set up and everything. //TODO:after quert, point editResult to the localResult for use when button2 is pressed string selectedIndex = ""; string selectedName = ""; int myIndex = comboBox1.SelectedIndex; comboIndex = myIndex; dbResult localResult = new dbResult(); selectedIndex = localIndex[myIndex]; selectedName = localName[myIndex]; localResult.queryDatabase("WHERE ((MyNumber='" + selectedIndex + "')", ")", localResult, conn2); localResult.ClearIndex(); localResult.ClearName(); for (int i = 0; i < localIndex.Count; i++) { localResult.SetIndex(localIndex[i]); localResult.SetName(localName[i]); } //editResult = localResult; clearForm(); populateForm(localResult, myIndex); //localResult = null; }
void populateForm(dbResult findResult, int nameBoxSel) { textBox1.Text = findResult.GetLastName(); textBox2.Text = findResult.GetFirstName(); textBox3.Text = findResult.GetMiddleName(); textBox5.Text = findResult.GetPassport(); textBox6.Text = findResult.GetCountry(); textBox9.Text = findResult.GetHaircolor(); textBox10.Text = findResult.GetEyecolor(); textBox8.Text = findResult.GetHeight(); textBox7.Text = findResult.GetWeight(); textBox4.Text = findResult.GetEthnicity(); richTextBox1.Text = findResult.GetNotes(); textBox12.Text = findResult.GetDOB(); textBox11.Text = findResult.GetPOB(); pictureBox1.ImageLocation = findResult.GetPics(); for (int i = 0; i < findResult.GetNameCount(); i++) { comboBox1.Items.Add(findResult.GetName(i)); } comboBox1.SelectedIndex = nameBoxSel; for (int i = 0; i < findResult.GetEntriesCount(); i++) { comboBox2.Items.Add(findResult.GetEntries(i)); } comboBox2.SelectedIndex = findResult.GetEntriesCount() - 1; for (int i = 0; i < findResult.GetAddressCount(); i++) { comboBox3.Items.Add(findResult.GetAddress(i)); } comboBox3.SelectedIndex = findResult.GetAddressCount() - 1; for (int i = 0; i < findResult.GetTechnologyCount(); i++) { comboBox6.Items.Add(findResult.GetTechnology(i)); } comboBox6.SelectedIndex = findResult.GetTechnologyCount() - 1; for (int i = 0; i < findResult.GetBusinessesCount(); i++) { comboBox5.Items.Add(findResult.GetBusinesses(i)); } comboBox5.SelectedIndex = findResult.GetBusinessesCount() - 1; for (int i = 0; i < findResult.GetAssociatesCount(); i++) { comboBox7.Items.Add(findResult.GetAssociates(i)); } comboBox7.SelectedIndex = findResult.GetAssociatesCount() - 1; for (int i = 0; i < findResult.GetLinksCount(); i++) { comboBox8.Items.Add(findResult.GetLinks(i)); } comboBox8.SelectedIndex = findResult.GetLinksCount() - 1; for (int i = 0; i < findResult.GetEducationCount(); i++) { comboBox4.Items.Add(findResult.GetEducation(i)); } comboBox4.SelectedIndex = findResult.GetEducationCount() - 1; }
void Button1Click(object sender, EventArgs e) { //create new database object dbResult resumeFilter = new dbResult(); //send object to filter to fill in values filterResume(resumeFilter); //show add person form ExtractionForm extractForm = new ExtractionForm(localDataBasePath, resumeFilter); extractForm.Show(); }
void FindButtonClick(object sender, System.EventArgs e) { //create the form to show results, create teh SQL string, run the command and then show results on form dbResult findResult = new dbResult(); string queryCriteria = ""; string queryEnd = ""; if (!isConnected) { MessageBox.Show("Please connect to database prior to searching."); } else { //last name must be entered prior to search. entering * is a wildcard, and entering nothing actually searches for a blank criteria if (lastName.Text != "") { if (lastName.Text != "*") { queryEnd = ")"; queryCriteria = "WHERE ((LastName='" + lastName.Text.ToString() + "')"; if (firstName.Text != "") { queryCriteria = queryCriteria + "AND " + "(FirstName='" + firstName.Text.ToString() + "')"; } if (middleName.Text != "") { queryCriteria = queryCriteria + "AND " + "(middleName='" + middleName.Text.ToString() + "')"; } } findResult.queryDatabase(queryCriteria, queryEnd, findResult, conn); //set up the form and show dbForm findForm = new dbForm(findResult, dataBasePath); findForm.Show(); } } }
public dbForm(dbResult findResult, string dataBasePath) { // // The InitializeComponent() call is required for Windows Forms designer support. // InitializeComponent(); // // TODO: Add constructor code after the InitializeComponent() call. // this.localIndex.Clear(); this.localName.Clear(); for (int i = 0; i < findResult.GetIndexCount(); i++) { this.localIndex.Add(findResult.GetIndex(i)); this.localName.Add(findResult.GetName(i)); } myDBPath = dataBasePath; myResult = findResult; ConnectToAccess2(dataBasePath); populateForm(findResult, findResult.GetNameCount() - 1); }
void Button1Click(object sender, EventArgs e) { string fromRichTextBox; dbResult localResult = new dbResult(); //connect to database ConnectToAccess3(localDataBasePath); //find the last number added string sqlString = "SELECT MyNumber, ID FROM tblPeeps WHERE (ID=(SELECT MAX(ID) FROM tblPeeps))"; string myNumber = "33"; OleDbCommand Com = new OleDbCommand(); Com.CommandText = sqlString; Com.Connection = conn3; OleDbDataReader objDataReader = null; objDataReader = Com.ExecuteReader(); if (objDataReader == null) { return; } while (objDataReader.Read()) { myNumber = objDataReader["MyNumber"].ToString(); int num = int.Parse(myNumber); myNumber = (++num).ToString(); } objDataReader.Close(); //fill query object with values from form localResult.SetName(textBox13.Text); localResult.SetLastName(textBox1.Text); localResult.SetFirstName(textBox2.Text); localResult.SetMiddleName(textBox3.Text); localResult.SetCountry(textBox6.Text); localResult.SetPassport(textBox5.Text); localResult.SetPics(textBox14.Text); localResult.SetEyecolor(textBox10.Text); localResult.SetHaircolor(textBox9.Text); localResult.SetHeight(textBox8.Text); localResult.SetWeight(textBox7.Text); localResult.SetEthnicity(textBox4.Text); localResult.SetNotes(richTextBox1.Text); localResult.SetPOB(textBox11.Text); localResult.SetDOB(textBox12.Text); //for the second table, take each row and add to localResult separating by : fromRichTextBox = richTextBox8.Text; string[] words = fromRichTextBox.Split('\n'); foreach (string word in words) { //if (word.Contains() localResult.SetEntries(word); } fromRichTextBox = richTextBox2.Text; words = fromRichTextBox.Split('\n'); string[] words2 = richTextBox12.Text.Split('\n'); int i = 0; foreach (string word in words) { //if (!word.Contains("")) localResult.SetAddress(word + ":" + words2[i++]); } fromRichTextBox = richTextBox3.Text; words = fromRichTextBox.Split('\n'); words2 = richTextBox9.Text.Split('\n'); string[] words3 = richTextBox10.Text.Split('\n'); i = 0; foreach (string word in words) { //if (!word.Contains("")) localResult.SetEducation(word + ":" + words2[i] + ":" + words3[i++]); } fromRichTextBox = richTextBox4.Text; words = fromRichTextBox.Split('\n'); words2 = richTextBox11.Text.Split('\n'); i = 0; foreach (string word in words) { //if (!word.Contains("")) localResult.SetBusinesses(word + ":" + words2[i++]); } fromRichTextBox = richTextBox5.Text; words = fromRichTextBox.Split('\n'); foreach (string word in words) { //if (!word.Contains("")) localResult.SetTechnology(word); } fromRichTextBox = richTextBox6.Text; words = fromRichTextBox.Split('\n'); foreach (string word in words) { //if (!word.Contains("")) localResult.SetAssociates(word); } fromRichTextBox = richTextBox7.Text; words = fromRichTextBox.Split('\n'); foreach (string word in words) { //if (word != "") localResult.SetLinks(word); } //insert object values into database insertDatabase(localResult, myNumber, conn3); MessageBox.Show("Successfully added to database."); }
public ExtractionForm(string dataBasePath, dbResult initWindow) { // // The InitializeComponent() call is required for Windows Forms designer support. // InitializeComponent(); // // TODO: Add constructor code after the InitializeComponent() call. // localDataBasePath = dataBasePath; //fill in textbox values from the initWindow object // textBox13.Text = initWindow.GetName(0); //never uncomment this again. it breaks the form. textBox1.Text = initWindow.GetLastName(); textBox2.Text = initWindow.GetFirstName(); textBox3.Text = initWindow.GetMiddleName(); textBox14.Text = initWindow.GetPics(); textBox6.Text = initWindow.GetCountry(); textBox5.Text = initWindow.GetPassport(); richTextBox1.Text = initWindow.GetNotes(); textBox10.Text = initWindow.GetEyecolor(); textBox9.Text = initWindow.GetHaircolor(); textBox8.Text = initWindow.GetHeight(); textBox7.Text = initWindow.GetWeight(); textBox4.Text = initWindow.GetEthnicity(); textBox12.Text = initWindow.GetDOB(); textBox11.Text = initWindow.GetPOB(); string entriesString = ""; for (int i = 0; i < initWindow.GetEntriesCount(); i++) { entriesString = entriesString + initWindow.GetEntries(i) + "\n"; } richTextBox8.Text = entriesString; string linksString = ""; for (int i = 0; i < initWindow.GetLinksCount(); i++) { linksString = linksString + initWindow.GetLinks(i) + "\n"; } richTextBox7.Text = linksString; string tempString = ""; for (int i = 0; i < initWindow.GetAddressCount(); i++) { tempString = tempString + initWindow.GetAddress(i) + "\n"; } richTextBox2.Text = tempString; tempString = ""; for (int i = 0; i < initWindow.GetTechnologyCount(); i++) { tempString = tempString + initWindow.GetTechnology(i) + "\n"; } richTextBox5.Text = tempString; string[] educString = new String[3]; string substring = ""; for (int i = 0; i < initWindow.GetEducationCount(); i++) { //since adding and editing a person's info creates different delimiters in the object, replace //with common delimiter before working string substring = initWindow.GetEducation(i).Replace("\t\t", ":"); string[] temp = substring.Split(':'); for (int j = 0; j < 3; j++) { educString[j] = educString[j] + temp[j] + "\n"; } } richTextBox3.Text = educString[0]; richTextBox9.Text = educString[1]; richTextBox10.Text = educString[2]; string[] busString = new String[2]; for (int i = 0; i < initWindow.GetBusinessesCount(); i++) { //since adding and editing a person's info creates different delimiters in the object, replace //with common delimiter before working string substring = initWindow.GetBusinesses(i).Replace("\t\t", ":"); string[] temp = substring.Split(':'); for (int j = 0; j < 2; j++) { busString[j] = busString[j] + temp[j] + "\n"; } } richTextBox4.Text = busString[0]; richTextBox11.Text = busString[1]; string[] addyString = new String[2]; for (int i = 0; i < initWindow.GetAddressCount(); i++) { //since adding and editing a person's info creates different delimiters in the object, replace //with common delimiter before working string substring = initWindow.GetAddress(i).Replace("\t\t", ":"); string[] temp = substring.Split(':'); for (int j = 0; j < 2; j++) { addyString[j] = addyString[j] + temp[j] + "\n"; } } richTextBox2.Text = addyString[0]; richTextBox12.Text = addyString[1]; string assocString = ""; for (int i = 0; i < initWindow.GetAssociatesCount(); i++) { assocString = assocString + initWindow.GetAssociates(i) + "\n"; } richTextBox6.Text = assocString; string techString = ""; for (int i = 0; i < initWindow.GetTechnologyCount(); i++) { techString = techString + initWindow.GetTechnology(i) + "\n"; } richTextBox5.Text = techString; }
public void insertDatabase(dbResult findResult, string myIndex, System.Data.OleDb.OleDbConnection conn) { //create INSERT command string insertCols1 = "PersonName, LastName, FirstName, MiddleName, Country, Photo, PlaceOfBirth, DateOfBirth, Passport, Notes, EyeColor, HairColor, Height, Weight, Ethnicity, MyNumber"; string sqlString = "INSERT INTO tblPeeps (" + insertCols1 + ") VALUES ("; string insertVals1 = "'" + findResult.GetName(0) + "', '" + findResult.GetLastName() + "', '" + findResult.GetFirstName() + "', '" + findResult.GetMiddleName() + "', '" + findResult.GetCountry() + "', '" + findResult.GetPics() + "', '" + findResult.GetPOB() + "', '" + findResult.GetDOB() + "', '" + findResult.GetPassport() + "', '" + findResult.GetNotes() + "', '" + findResult.GetEyecolor() + "', '" + findResult.GetHaircolor() + "', '" + findResult.GetHeight() + "', '" + findResult.GetWeight() + "', '" + findResult.GetEthnicity() + "', '" + myIndex + "'"; sqlString = sqlString + insertVals1 + ")"; //set up connection and run command for 1st table OleDbCommand Com = new OleDbCommand(); Com.CommandText = sqlString; Com.Connection = conn; OleDbDataReader objDataReader = null; objDataReader = Com.ExecuteReader(); objDataReader.Close(); //set up sql strings for each insertion to the second table int maxLines = findResult.GetEntriesCount(); if (findResult.GetAddressCount() > maxLines) { maxLines = findResult.GetAddressCount(); } if (findResult.GetAssociatesCount() > maxLines) { maxLines = findResult.GetAssociatesCount(); } if (findResult.GetBusinessesCount() > maxLines) { maxLines = findResult.GetBusinessesCount(); } if (findResult.GetEducationCount() > maxLines) { maxLines = findResult.GetEducationCount(); } if (findResult.GetEntriesCount() > maxLines) { maxLines = findResult.GetEntriesCount(); } if (findResult.GetLinksCount() > maxLines) { maxLines = findResult.GetLinksCount(); } if (findResult.GetTechnologyCount() > maxLines) { maxLines = findResult.GetTechnologyCount(); } //set up sql command, connection and run command for second table OleDbCommand Com2 = new OleDbCommand(); Com2.Connection = conn; OleDbDataReader objDataReader2 = null; string insertCols2 = "Entries, Addresses, DateOfAddresses, Technologies, " + "Associates, Universities, Degrees, FieldOfStudies, PlaceOfBiz, RoleInBiz, Links, PersonID"; string valueString = ""; //create individual rows and send them one at a time to DB for (int i = 0; i < maxLines; i++) { //create strings using values from form valueString = ""; if (i < findResult.GetEntriesCount()) { valueString = valueString + "'" + findResult.GetEntries(i) + "', "; } else { valueString = valueString + "'', "; } string[] words = findResult.GetAddress(0).Split(':'); if (i < findResult.GetAddressCount()) { words = findResult.GetAddress(i).Split(':'); valueString = valueString + "'" + words[0] + "', '" + words[1] + "', "; } else { valueString = valueString + "'', '', "; } if (i < findResult.GetTechnologyCount()) { valueString = valueString + "'" + findResult.GetTechnology(i) + "', "; } else { valueString = valueString + "'', "; } if (i < findResult.GetAssociatesCount()) { valueString = valueString + "'" + findResult.GetAssociates(i) + "', "; } else { valueString = valueString + "'', "; } if (i < findResult.GetEducationCount()) { words = findResult.GetEducation(i).Split(':'); valueString = valueString + "'" + words[0] + "', '" + words[1] + "', '" + words[2] + "', "; } else { valueString = valueString + "'', '', '', "; } if (i < findResult.GetBusinessesCount()) { words = findResult.GetBusinesses(i).Split(':'); valueString = valueString + "'" + words[0] + "', '" + words[1] + "', "; } else { valueString = valueString + "'', '', "; } if (i < findResult.GetLinksCount()) { valueString = valueString + "'" + findResult.GetLinks(i) + "', "; } else { valueString = valueString + "'', "; } valueString = valueString + "'" + myIndex + "'"; //put together SQL string and send to database sqlString = "INSERT INTO tblOtherInfo (" + insertCols2 + ") VALUES (" + valueString + ")"; Com2.CommandText = sqlString; objDataReader2 = Com2.ExecuteReader(); objDataReader2.Close(); objDataReader2 = null; } }
public void queryDatabase(string whereClause, string endClause, dbResult findResult, System.Data.OleDb.OleDbConnection conn) { bool foundData = false; string sqlString = "SELECT LastName, FirstName, MiddleName, PersonName, StreetAddress, " + "Country, PlaceOfBirth, DateOfBirth, Passport, Notes, EyeColor, " + "HairColor, Height, Weight, Ethnicity, MyNumber, Photo " + "FROM tblPeeps " + whereClause + endClause; OleDbCommand Com = new OleDbCommand(); Com.CommandText = sqlString; Com.Connection = conn; OleDbDataReader objDataReader = null; objDataReader = Com.ExecuteReader(); if (objDataReader == null) { return; } while (objDataReader.Read()) { findResult.SetLastName(objDataReader["LastName"].ToString()); findResult.SetFirstName(objDataReader["FirstName"].ToString()); findResult.SetMiddleName(objDataReader["MiddleName"].ToString()); findResult.SetCountry(objDataReader["Country"].ToString()); findResult.SetPOB(objDataReader["PlaceOfBirth"].ToString()); findResult.SetDOB(objDataReader["DateOfBirth"].ToString()); findResult.SetPassport(objDataReader["Passport"].ToString()); findResult.SetNotes(objDataReader["Notes"].ToString()); findResult.SetEyecolor(objDataReader["EyeColor"].ToString()); findResult.SetHaircolor(objDataReader["HairColor"].ToString()); findResult.SetHeight(objDataReader["Height"].ToString()); findResult.SetWeight(objDataReader["Weight"].ToString()); findResult.SetEthnicity(objDataReader["Ethnicity"].ToString()); findResult.SetPics(objDataReader["Photo"].ToString()); findResult.SetName(objDataReader["PersonName"].ToString()); findResult.SetIndex(objDataReader["MyNumber"].ToString()); foundData = true; } objDataReader.Close(); if (foundData) { sqlString = "SELECT Entries, Addresses, Technologies, DateOfAddresses, " + "Associates, Universities, Degrees, FieldOfStudies, PlaceOfBiz, RoleInBiz, Links " + "FROM tblOtherInfo " + "WHERE (PersonID='" + findResult.GetIndex(findResult.GetIndexCount() - 1) + "')"; OleDbCommand Com2 = new OleDbCommand(); Com2.CommandText = sqlString; Com2.Connection = conn; OleDbDataReader objDataReader2 = null; objDataReader2 = Com2.ExecuteReader(); if (objDataReader2 == null) { return; } while (objDataReader2.Read()) { findResult.SetEntries(objDataReader2["Entries"].ToString()); findResult.SetAddress(objDataReader2["Addresses"].ToString() + "\t\t" + objDataReader2["DateOfAddresses"].ToString()); findResult.SetEducation(objDataReader2["Universities"].ToString() + "\t\t" + objDataReader2["Degrees"].ToString() + "\t\t" + objDataReader2["FieldOfStudies"].ToString()); findResult.SetTechnology(objDataReader2["Technologies"].ToString()); findResult.SetBusinesses(objDataReader2["PlaceOfBiz"].ToString() + "\t\t" + objDataReader2["RoleInBiz"].ToString()); findResult.SetAssociates(objDataReader2["Associates"].ToString()); findResult.SetLinks(objDataReader2["Links"].ToString()); } objDataReader2.Close(); } else { MessageBox.Show("Search Returned 0 matches.\nEnter * in LastName field to see all entries."); } }
void Button1Click(object sender, EventArgs e) { //TODO: creat afunction; need a new structure to get results into and pass to display sindow string sqlSimpleQuery = ""; string sqlJoinQuery = ""; string whereClause = ""; string totalWheres = ""; bool needAnd = false; customQuery analyze = new customQuery(); dbResult test = new dbResult(); //access connection has already been made //look over checkboxes and use combobox selections to create SQL statement if (checkBox1.Checked) { string[] comboArray = comboBox4.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries); whereClause = "(Universities='" + comboArray[0] + "')"; totalWheres = totalWheres + whereClause + "\n"; sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause; needAnd = true; sqlJoinQuery = "SELECT PersonID, Universities FROM tblOtherInfo WHERE " + whereClause; } if (checkBox2.Checked) { string[] comboArray = comboBox4.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries); whereClause = "(Degrees='" + comboArray[1] + "')"; totalWheres = totalWheres + whereClause + "\n"; sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause; if (needAnd == false) { needAnd = true; sqlJoinQuery = "SELECT PersonID, Degrees FROM tblOtherInfo WHERE " + whereClause; } else { sqlJoinQuery = "SELECT DegreesJoinPrev.PersonID FROM (" + sqlJoinQuery + ")DegreesJoinPrev INNER JOIN (" + sqlSimpleQuery + ")DegreesJoin ON DegreesJoinPrev.PersonID=DegreesJoin.PersonID"; } } if (checkBox3.Checked) { string[] comboArray = comboBox4.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries); whereClause = "(FieldOfStudies='" + comboArray[2] + "')"; totalWheres = totalWheres + whereClause + "\n"; sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause; if (needAnd == false) { needAnd = true; sqlJoinQuery = "SELECT PersonID, FieldOfStudies FROM tblOtherInfo WHERE " + whereClause; } else { sqlJoinQuery = "SELECT FOSJoinPrev.PersonID FROM (" + sqlJoinQuery + ")FOSJoinPrev INNER JOIN (" + sqlSimpleQuery + ")FOSJoin ON FOSJoinPrev.PersonID=FOSJoin.PersonID"; } } if (checkBox4.Checked) { string[] comboArray = comboBox5.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries); whereClause = "(PlaceOfBiz='" + comboArray[0] + "')"; totalWheres = totalWheres + whereClause + "\n"; sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause; if (needAnd == false) { needAnd = true; sqlJoinQuery = "SELECT PersonID, PlaceOfBiz FROM tblOtherInfo WHERE " + whereClause; } else { sqlJoinQuery = "SELECT POBJoinPrev.PersonID FROM (" + sqlJoinQuery + ")POBJoinPrev INNER JOIN (" + sqlSimpleQuery + ")POBJoin ON POBJoinPrev.PersonID=POBJoin.PersonID"; } } if (checkBox5.Checked) { string[] comboArray = comboBox5.Text.ToString().Split(new string[] { "\t" }, StringSplitOptions.RemoveEmptyEntries); whereClause = "(RoleInBiz='" + comboArray[1] + "')"; totalWheres = totalWheres + whereClause + "\n"; sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause; if (needAnd == false) { needAnd = true; sqlJoinQuery = "SELECT PersonID, RoleInBiz FROM tblOtherInfo WHERE " + whereClause; } else { sqlJoinQuery = "SELECT RIBJoinPrev.PersonID FROM (" + sqlJoinQuery + ")RIBJoinPrev INNER JOIN (" + sqlSimpleQuery + ")RIBJoin ON RIBJoinPrev.PersonID=RIBJoin.PersonID"; } } if (checkBox6.Checked) { whereClause = "(Technologies='" + comboBox6.Text.ToString() + "')"; totalWheres = totalWheres + whereClause + "\n"; sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause; if (needAnd == false) { needAnd = true; sqlJoinQuery = "SELECT PersonID, Technologies FROM tblOtherInfo WHERE " + whereClause; } else { sqlJoinQuery = "SELECT TechJoinPrev.PersonID FROM (" + sqlJoinQuery + ")TechJoinPrev INNER JOIN (" + sqlSimpleQuery + ")TechJoin ON TechJoinPrev.PersonID=TechJoin.PersonID"; } } if (checkBox7.Checked) { whereClause = "(Associates='" + comboBox7.Text.ToString() + "')"; totalWheres = totalWheres + whereClause + "\n"; sqlSimpleQuery = "SELECT DISTINCT PersonID FROM tblOtherInfo WHERE " + whereClause; if (needAnd == false) { needAnd = true; sqlJoinQuery = "SELECT PersonID, Associates FROM tblOtherInfo WHERE " + whereClause; } else { sqlJoinQuery = "SELECT AssJoinPrev.PersonID FROM (" + sqlJoinQuery + ")AssJoinPrev INNER JOIN (" + sqlSimpleQuery + ")AssJoin ON AssJoinPrev.PersonID=AssJoin.PersonID"; } } //end query //send statement to database and get results analyze.queryDatabase(sqlJoinQuery, analyze, conn2); //set up the form and show with results displayForm analyzeDisplay = new displayForm(analyze, totalWheres); analyzeDisplay.Show(); }
void filterResume(dbResult filterResults) { //get the name and address and remove newlines string temp = richTextBox1.Text.Replace('\n', ' '); temp = temp.Replace('.', ' '); temp = temp.Replace(',', ' '); //use regex to check for address, get index of start of address and then assume name precedes it //looking for format 5503 West Seminary Rd #1897 falls church VA 22041 Regex exp = new Regex(@"\d+\s+\w+(\s+\w+)?\s+[a-z]+(\s+\D+\d+)?\s+[a-z]+(\s+[a-z]+)?\s+[a-z]+\s+\d\d\d\d\d", RegexOptions.IgnoreCase); MatchCollection MatchList = exp.Matches(temp); if (MatchList.Count == 1) { Match FirstMatch = MatchList[0]; filterResults.SetAddress(FirstMatch.Value + ":"); string nameTemp = temp.Substring(0, temp.IndexOf(FirstMatch.Value) - 1); string[] nameArray = nameTemp.Split(' '); int count = 0; //there are better ways to do this, but I needed an easy way to get rid of hidden spaces foreach (string name in nameArray) { if (name != "") { count++; } } if (count >= 3) { filterResults.SetFirstName(nameArray[0]); filterResults.SetMiddleName(nameArray[1]); filterResults.SetLastName(nameArray[2]); } else if (count >= 2) { filterResults.SetFirstName(nameArray[0]); filterResults.SetLastName(nameArray[1]); } } //now work on education portion of form string educText = richTextBox2.Text; //find degrees, then colleges, then fields of study //first two can be found by using regex to filter keywords Regex educExp = new Regex(@"((((bachelor(s)?(,|\s+of)\s+)|(b\.?))|((master(s)?(,|\s+of)\s+)|(m\.?))|((doctorate(,|\s+of)\s+)|(d\.?)))(((s\.?(\s+|,))|(science(,|\s+)))|((e\.?(\s+|,))|(engineering(,|\s+)))|((a\.?(\s+|,))|(art(,|\s+)))|((t\.?(\s+|,))|(tech[a-z]+(,|\s+)))))|((doctorate\s+of\s+philosophy(,|\s+))|(p\.?h\.?d\.?(,|\s+)))", RegexOptions.IgnoreCase); MatchCollection MatchListEduc = educExp.Matches(educText); Regex univExp = new Regex(@"((University\s+of\s+([A-Z][a-z]+(\s+|,))+(\s+|,)?)|(([A-Z][a-z]+\s+)+University(\s+|,)?))|((College\s+of\s+([A-Z][a-z]+(\s+|,))+(\s+|,)?)|(([A-Z][a-z]+\s+)+College(\s+|,)?))|((Institute\s+of\s+([A-Z][a-z]+(\s+|,))+(\s+|,)?)|(([A-Z][a-z]+\s+)+Institute(\s+|,)?))"); MatchCollection MatchListUniv = univExp.Matches(educText); //need to use degree data to split into substrings //then use regex to find field of study in substring for each degree //Regex fosExp = new Regex(@"", // RegexOptions.IgnoreCase); //MatchCollection MatchListFos = fosExp.Matches(educText); //foreach (Match FirstMatchEduc in MatchListUniv) // filterResults.SetEducation(FirstMatchEduc.Value+":"+"university"+":"+"fos"); //format <university>:<degree>:<field of study> sent to object to get passed to extranctionform for (int i = 0; i < MatchListEduc.Count; i++) { filterResults.SetEducation(MatchListUniv[i].Value + ":" + MatchListEduc[i].Value + ":" + "fos"); } //now get associates info from teh box; go for the low hanging fruit first. :) string[] assArray = richTextBox4.Text.Split('\n'); foreach (string assoc in assArray) { filterResults.SetAssociates(assoc); } //now get technologies from education and emplyment history; once again, low hanging fruit. :) //open keyword file and pull keywords inot array; then look through education and employment text for keywords string textIn = File.ReadAllText("techWords.cfg"); string [] textArray = textIn.Split(new string [] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries); foreach (string keyword in textArray) { if (richTextBox3.Text.Contains(keyword) || richTextBox2.Text.Contains(keyword)) { filterResults.SetTechnology(keyword); } } }