public displayForm(customQuery results, string queryRoot)
{
//
// The InitializeComponent() call is required for Windows Forms designer support.
//
InitializeComponent();
//
// TODO: Add constructor code after the InitializeComponent() call.
//
localQuery = results;
root = queryRoot;
}
public void queryDatabase(string sqlString, customQuery findResult, System.Data.OleDb.OleDbConnection conn)
{
OleDbCommand Com = new OleDbCommand();
Com.CommandText = sqlString;
Com.Connection = conn;
OleDbDataReader objDataReader = null;
objDataReader = Com.ExecuteReader();
if (objDataReader == null)
{
return;
}
while (objDataReader.Read())
{
findResult.SetIndex(objDataReader["PersonID"].ToString());
}
objDataReader.Close();
string whereClause = "(MyNumber='" + findResult.GetIndex(0) + "') ";
for (int i = 1; i < findResult.GetIndexCount(); i++)
{
whereClause = whereClause + "OR (MyNumber='" + findResult.GetIndex(i) + "') ";
}
string localsqlString = "SELECT PersonName FROM tblPeeps WHERE (" + whereClause + ")";
OleDbCommand Com2 = new OleDbCommand();
Com2.CommandText = localsqlString;
Com2.Connection = conn;
OleDbDataReader objDataReader2 = null;
objDataReader2 = Com2.ExecuteReader();
if (objDataReader2 == null)
{
return;
}
while (objDataReader2.Read())
{
findResult.SetName(objDataReader2["PersonName"].ToString());
}
objDataReader2.Close();
}
void OpenToolStripMenuItemClick(object sender, EventArgs e)
{
customQuery newQuery = new customQuery();
string newRoot = "";
//open the dialog box so user can choose path
openFileDialog1.ShowDialog();
string path = openFileDialog1.FileName;
//get root and branches from file
string textIn = File.ReadAllText(path);
string [] textArray = textIn.Split(new string [] { "###" }, StringSplitOptions.RemoveEmptyEntries);
string [] contentsArray = textArray[3].Split(new string [] { "<EOL>" }, StringSplitOptions.RemoveEmptyEntries);
string [] newRootArray = new string[2];
string [] newBranchArray = new String[2];
//check that an actual connectMap file has been selected
if (textArray[0].Contains("connectMap"))
{
//grab teh root file
newRootArray = contentsArray[0].Split(new string [] { ">" }, StringSplitOptions.RemoveEmptyEntries);
newRoot = newRootArray[1];
//get the branches and set up the localQuery object
for (int i = 1; i < contentsArray.Length; i++)
{
newBranchArray = contentsArray[i].Split(new string [] { ">" }, StringSplitOptions.RemoveEmptyEntries);
newQuery.SetName(newBranchArray[1]);
}
}
//create new display and pass it the root and query objects
displayForm analyzeDisplay = new displayForm(newQuery, newRoot);
analyzeDisplay.Show();
}
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();
}
