/// <summary> /// Function to validate a description string. Checks URLs and Field tags. /// URLs - verifies that there is one and only one TITLE element and that the title and URL are not empty, and that there is an open and closing tag /// Field tag - verifies that there one and only one set of TBL and COL tags, that the tags are not empty, and that there is an open and closing tag /// </summary> /// <param name="currentConnInfo">ConnInfo object containing the current connection info for the description being tested</param> /// <param name="currentMapping">Mapping object containing the current mapping for the description being tested</param> /// <returns>true if a description is valid and false if it is not</returns> public bool isValid(ConnInfo currentConnInfo, Mapping currentMapping) { //validate field tags int startIndex = 0; int endIndex = 0; int lengthOfTag = 0; //if start of field tag is found while (desc.IndexOf("[FIELD]", startIndex, StringComparison.InvariantCultureIgnoreCase) != -1) { //if end of field tag is found, set startindex, else return false if (desc.IndexOf("[/FIELD]", StringComparison.InvariantCultureIgnoreCase) != -1) { //get the index of end of tag and calculate lengthoftag endIndex = desc.IndexOf("[/FIELD]", startIndex, StringComparison.InvariantCultureIgnoreCase); lengthOfTag = endIndex - startIndex; } else { return false; } //if start of table tag found look for end of field tag else return false if (desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) != -1) { //if we find a close tbl tag before the open tbl tag return false if ((desc.IndexOf("[/TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase)) < (desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase))) return false; //if we find another tbl tag, return false else if (desc.IndexOf("[TBL]", desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) != -1) return false; //else if we don't find a close tbl tag, return false else if (desc.IndexOf("[/TBL]", desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) == -1) return false; //else if the length of the table tag is 0, return false else if (desc.IndexOf("[TBL]", startIndex, lengthOfTag) + 5 - desc.IndexOf("[/TBL]", desc.IndexOf("[TBL]") + 4, endIndex - desc.IndexOf("[TBL]", startIndex, lengthOfTag) + 4, StringComparison.InvariantCultureIgnoreCase) == 0) return false; //validate table name int openTbl = desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase); int closeTbl = desc.IndexOf("[/TBL]", desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase); string table = desc.Substring(openTbl+5, closeTbl - openTbl - 5); //if the table isn't the currently mapped table, return false if (!table.Trim().Equals(currentMapping.tableName)) return false; } else { return false; } //if start of column tag is found, look for end of field tag else return false if (desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) != -1) { //if we find a close col tag before the open col tag return false if ((desc.IndexOf("[/COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase)) < (desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase))) return false; //if we find another col tag, return false if (desc.IndexOf("[COL]", desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) != -1) return false; //else if we don't find a close col tag, return false else if (desc.IndexOf("[/COL]", desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) == -1) return false; //else if the length of the column tag is 0, return false else if (desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 5 - desc.IndexOf("[/COL]", desc.IndexOf("[COL]", StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) == 0) return false; //validate column name int openCol = desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase); int closeCol = desc.IndexOf("[/COL]", desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase); string column = desc.Substring(openCol+5, closeCol - openCol - 5); Database db = new Database(currentConnInfo); string query = "SELECT " + column.Trim() + " FROM " + currentMapping.tableName; try { db.executeQueryRemote(query); } catch (ODBC2KMLException) { return false; } } else { return false; } startIndex = endIndex + 8; } //validate url tags startIndex = 0; endIndex = 0; lengthOfTag = 0; //if start of url tag is found while (desc.IndexOf("[URL]", startIndex, StringComparison.InvariantCultureIgnoreCase) != -1) { //if end of url tag is found, set startindex, else return false if (desc.IndexOf("[/URL]", StringComparison.InvariantCultureIgnoreCase) != -1) { //get the index of end of tag and calculate lengthoftag endIndex = desc.IndexOf("[/URL]", startIndex, StringComparison.InvariantCultureIgnoreCase); lengthOfTag = endIndex - startIndex; } else { return false; } //if start of title tag found look for end of title tag else return false if (desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) != -1) { //if we find another title tag, return false if (desc.IndexOf("[TITLE]", desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 7, endIndex - desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) - 7, StringComparison.InvariantCultureIgnoreCase) != -1) return false; //else if we don't find a close title tag, return false else if (desc.IndexOf("[/TITLE]", desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 7, endIndex - desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) - 7, StringComparison.InvariantCultureIgnoreCase) == -1) return false; //else if the length of the title tag is 0, return false else if (desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 8 - desc.IndexOf("[/TITLE]", desc.IndexOf("[TITLE]", StringComparison.InvariantCultureIgnoreCase) + 7, endIndex - desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) - 7, StringComparison.InvariantCultureIgnoreCase) == 0) return false; } else { return false; } //if length of url is 0 return false if (desc.IndexOf("[/TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 8 - desc.IndexOf("[/URL]", desc.IndexOf("[URL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 5, endIndex - desc.IndexOf("[URL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) - 5, StringComparison.InvariantCultureIgnoreCase) == 0) return false; startIndex = endIndex + 6; } return true; }
protected void updateConnection(object sender, CommandEventArgs e) { Button sendBtn = (Button)sender; String args = sendBtn.CommandArgument.ToString(); ConnInfo tempConnInfo = new ConnInfo(); tempConnInfo.setConnectionName(editConnName.Text); tempConnInfo.setDatabaseName(editConnDBName.Text); tempConnInfo.setServerAddress(editConnDBAddr.Text); tempConnInfo.setPortNumber(editConnDBPort.Text); tempConnInfo.setUserName(editConnUser.Text); tempConnInfo.setPassword(editConnPass.Text); tempConnInfo.setDatabaseType((editConnDBType.SelectedIndex)); tempConnInfo.setOracleProtocol(editOracleProtocol.Text); tempConnInfo.setOracleServiceName(editOracleService.Text); tempConnInfo.setOracleSID(editOracleSID.Text); //If the connection information is bad, report the error and cancel the function. This does NOT run against the database. try { if (!tempConnInfo.isValid(Convert.ToInt32(args))) { throw new ODBC2KMLException(""); // Throw any error. The catch is generic. } } catch { String error = "The entered connection information is invalid. Please make sure all fields are filled and that they are in proper format."; if (tempConnInfo.getDatabaseType() == ConnInfo.ORACLE) { error = "The entered connection information is invalid. Please verify that all fields have a value and the value is of proper type." + " Also, make sure that Oracle SID or Oracle Service Name and Oracle Protocol have been entered."; } ErrorHandler eh = new ErrorHandler(error, errorPanel1); this.editConnModalPopUp.Hide(); eh.displayError(); return; } //Create database and test it Database db = new Database(tempConnInfo); //See if you can reach the database. If not, error out and don't save. try { if (tempConnInfo.getDatabaseType() == ConnInfo.MSSQL) { String query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' AND TABLE_NAME != 'sysdiagrams'"; db.executeQueryRemote(query); } else if (tempConnInfo.getDatabaseType() == ConnInfo.MYSQL) { String query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' && TABLE_SCHEMA != 'mysql'"; db.executeQueryRemote(query); } else if (tempConnInfo.getDatabaseType() == ConnInfo.ORACLE) { String query = "select TABLE_NAME from user_tables"; db.executeQueryRemote(query); } } catch { ErrorHandler eh = new ErrorHandler("The database entered could not be connected to. Please verify the information is correct.", errorPanel1); this.editConnModalPopUp.Hide(); eh.displayError(); return; } db.executeQueryLocal("UPDATE Connection SET name='" + tempConnInfo.getConnectionName() + "', dbName='" + tempConnInfo.getDatabaseName() + "', userName='******', password='******', port='" + tempConnInfo.getPortNumber() + "', address='" + tempConnInfo.getServerAddress() + "', type='" + tempConnInfo.getDatabaseType() + "', protocol='" + tempConnInfo.getOracleProtocol() + "', serviceName='" + tempConnInfo.getOracleServiceName() + "', SID='" + tempConnInfo.getOracleSID() + "' WHERE (ID='" + args + "')"); Connection conn = new Connection(Convert.ToInt16(args)); try { conn.populateFields(); //Force the connection into a safe state, if it is not if (!conn.safeStateConnection()) { String error = "Invalid connection information. Please verify all of your fields are filled in correctly." + "If you are using an oracle connection, please make sure you filled out the oracle specific information."; ErrorHandler eh = new ErrorHandler(error, errorPanel1); this.editConnModalPopUp.Hide(); eh.displayError(); return; } } catch (ODBC2KMLException err) { ErrorHandler eh = new ErrorHandler(err.errorText, errorPanel1); this.editConnModalPopUp.Hide(); eh.displayError(); return; } if (e.CommandName.Equals("saveConn")) { this.editConnModalPopUp.Hide(); Response.Redirect("Main.aspx"); } else { this.editConnModalPopUp.Hide(); Response.Redirect("ConnDetails.aspx?ConnID=" + ((Button)sender).CommandArgument.ToString() + "&locked=false"); } }
protected void addConditionToOverlay(object sender, EventArgs e) { Button btn = (Button)sender; string args = btn.CommandArgument.ToString(); TextBox lowerBound = (TextBox)Page.FindControl("addOverlayLowerBound" + args); DropDownList lowerOperator = (DropDownList)Page.FindControl("addOverlayLowerOperator" + args); DropDownList tableName = (DropDownList)Page.FindControl("addOverlayTable" + args); DropDownList fieldName = (DropDownList)Page.FindControl("addOverlayField" + args); DropDownList upperOperator = (DropDownList)Page.FindControl("addOverlayUpperOperator" + args); TextBox upperBound = (TextBox)Page.FindControl("addOverlayUpperBound" + args); UpdatePanel modifyIconConditionInsidePopupPanel = (UpdatePanel)Page.FindControl("modifyOverlayConditionInsidePopupPanel" + args); string overlayId = args; Condition condition = new Condition(); condition.setId(tempId--); condition.setLowerBound(lowerBound.Text.ToString()); condition.setUpperBound(upperBound.Text.ToString()); condition.setTableName(tableName.Text.ToString()); condition.setFieldName(fieldName.Text.ToString()); if (lowerOperator != null) condition.setLowerOperator(lowerOperator.SelectedItem.Text.ToString()); if (upperOperator != null) condition.setUpperOperator(upperOperator.SelectedItem.Text.ToString()); string conditionErrors = condition.getErrorText(); if (conditionErrors != "") { ErrorHandler eh = new ErrorHandler(conditionErrors, (UpdatePanel)Page.FindControl("modifyOverlayConditionInsidePopupPanel" + overlayId), "MPE_OVERLAY_" + overlayId); eh.displayError(); lowerBound.Text = condition.getLowerBound(); lowerOperator.SelectedValue = condition.getLowerOperator(); tableName.SelectedValue = condition.getTableName(); fieldName.SelectedValue = condition.getFieldName(); upperOperator.SelectedValue = condition.getUpperOperator(); upperBound.Text = condition.getUpperBound(); return; } //Verify that the bounds are of the same datatype as the column value if (conditionErrors == "") { //Create database based on the connInfo Database testDB = new Database(conn.connInfo); DataTable testTable = null; try { //Create the datatable to parse through testTable = testDB.executeQueryRemote("SELECT " + condition.getFieldName() + " FROM " + condition.getTableName()); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error connecting to the remote database to verify overlay condition information", errorPanel1); eh.displayError(); return; } //Counter to break out of the loop, this way only a subset of the data is tested int counter = 0; //Check the bounds against the database values foreach (DataRow row in testTable.Rows) { //If both conditions.... if (condition.getLowerBound() != "" && condition.getUpperBound() != "") { //Need variables for Try parse Double lower; Double upper; //See if both conditions evaluate to doubles if (Double.TryParse(condition.getLowerBound(), out lower) && Double.TryParse(condition.getUpperBound(), out upper)) { Double value; //If value is not a double, throw an error if (!Double.TryParse(row[condition.getFieldName()].ToString(), out value) && row[condition.getFieldName()] != null && row[condition.getFieldName()].ToString() != "") { conditionErrors = "The datatype of the entered bounds do not match the datatype of the database values."; ErrorHandler eh = new ErrorHandler(conditionErrors, (UpdatePanel)Page.FindControl("modifyOverlayConditionInsidePopupPanel" + overlayId), "MPE_OVERLAY_" + overlayId); lowerBound.Text = condition.getLowerBound(); lowerOperator.SelectedValue = condition.getLowerOperator(); tableName.SelectedValue = condition.getTableName(); fieldName.SelectedValue = condition.getFieldName(); upperOperator.SelectedValue = condition.getUpperOperator(); upperBound.Text = condition.getUpperBound(); eh.displayError(); return; } //test five elements if (++counter > 5) break; } else //They evaluate to strings { Double value; //If value is a double, throw an error if (Double.TryParse(row[condition.getFieldName()].ToString(), out value) && row[condition.getFieldName()] != null && row[condition.getFieldName()].ToString() != "") { conditionErrors = "The datatype of the entered bounds do not match the datatype of the database values."; ErrorHandler eh = new ErrorHandler(conditionErrors, (UpdatePanel)Page.FindControl("modifyOverlayConditionInsidePopupPanel" + overlayId), "MPE_OVERLAY_" + overlayId); lowerBound.Text = condition.getLowerBound(); lowerOperator.SelectedValue = condition.getLowerOperator(); tableName.SelectedValue = condition.getTableName(); fieldName.SelectedValue = condition.getFieldName(); upperOperator.SelectedValue = condition.getUpperOperator(); upperBound.Text = condition.getUpperBound(); eh.displayError(); return; } else //Decrement counter, this row wasn't a valid row and should be ignored //test five elements if (++counter > 5) break; } } else if (condition.getLowerBound() != "") { //Need variables for Try parse Double lower; //See if the lower conditions evaluate to doubles if (Double.TryParse(condition.getLowerBound(), out lower)) { Double value; //If value is not a double, throw an error if (!Double.TryParse(row[condition.getFieldName()].ToString(), out value) && row[condition.getFieldName()] != null && row[condition.getFieldName()].ToString() != "") { conditionErrors = "The datatype of the entered bounds do not match the datatype of the database values."; ErrorHandler eh = new ErrorHandler(conditionErrors, (UpdatePanel)Page.FindControl("modifyOverlayConditionInsidePopupPanel" + overlayId), "MPE_OVERLAY_" + overlayId); lowerBound.Text = condition.getLowerBound(); lowerOperator.SelectedValue = condition.getLowerOperator(); tableName.SelectedValue = condition.getTableName(); fieldName.SelectedValue = condition.getFieldName(); upperOperator.SelectedValue = condition.getUpperOperator(); upperBound.Text = condition.getUpperBound(); eh.displayError(); return; } //test five elements if (++counter > 5) break; } else //They evaluate to strings { Double value; //If value is a double, throw an error if (Double.TryParse(row[condition.getFieldName()].ToString(), out value) && row[condition.getFieldName()] != null && row[condition.getFieldName()].ToString() != "") { conditionErrors = "The datatype of the entered bounds do not match the datatype of the database values."; ErrorHandler eh = new ErrorHandler(conditionErrors, (UpdatePanel)Page.FindControl("modifyOverlayConditionInsidePopupPanel" + overlayId), "MPE_OVERLAY_" + overlayId); lowerBound.Text = condition.getLowerBound(); lowerOperator.SelectedValue = condition.getLowerOperator(); tableName.SelectedValue = condition.getTableName(); fieldName.SelectedValue = condition.getFieldName(); upperOperator.SelectedValue = condition.getUpperOperator(); upperBound.Text = condition.getUpperBound(); eh.displayError(); return; } //test five elements if (++counter > 5) break; } } else { //Need variables for Try parse Double upper; //See if the lower conditions evaluate to doubles if (Double.TryParse(condition.getUpperBound(), out upper)) { Double value; //If value is not a double, throw an error if (!Double.TryParse(row[condition.getFieldName()].ToString(), out value) && row[condition.getFieldName()] != null && row[condition.getFieldName()].ToString() != "") { conditionErrors = "The datatype of the entered bounds do not match the datatype of the database values."; ErrorHandler eh = new ErrorHandler(conditionErrors, (UpdatePanel)Page.FindControl("modifyOverlayConditionInsidePopupPanel" + overlayId), "MPE_OVERLAY_" + overlayId); lowerBound.Text = condition.getLowerBound(); lowerOperator.SelectedValue = condition.getLowerOperator(); tableName.SelectedValue = condition.getTableName(); fieldName.SelectedValue = condition.getFieldName(); upperOperator.SelectedValue = condition.getUpperOperator(); upperBound.Text = condition.getUpperBound(); eh.displayError(); return; } //test five elements if (++counter > 5) break; } else //They evaluate to strings { Double value; //If value is a double, throw an error if (Double.TryParse(row[condition.getFieldName()].ToString(), out value) && row[condition.getFieldName()] != null && row[condition.getFieldName()].ToString() != "") { conditionErrors = "The datatype of the entered bounds do not match the datatype of the database values."; ErrorHandler eh = new ErrorHandler(conditionErrors, (UpdatePanel)Page.FindControl("modifyOverlayConditionInsidePopupPanel" + overlayId), "MPE_OVERLAY_" + overlayId); lowerBound.Text = condition.getLowerBound(); lowerOperator.SelectedValue = condition.getLowerOperator(); tableName.SelectedValue = condition.getTableName(); fieldName.SelectedValue = condition.getFieldName(); upperOperator.SelectedValue = condition.getUpperOperator(); upperBound.Text = condition.getUpperBound(); eh.displayError(); return; } //test five elements if (++counter > 5) break; } } } } foreach (Overlay overlay in conn.overlays) { if (overlay.getId() == overlayId) { overlay.setConditions(condition); } } try { genOverlayConditionTable(sender, e); } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); eh.displayError(); return; } sessionSave(); }
protected void executeQuery(object sender, EventArgs e) { //try //{ Database db; DataTable dt; Label title = new Label(); if (connectionSelector.SelectedItem.Text == "local") { try { db = new Database(); dt = db.executeQueryLocal(queryString.Text); } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); eh.displayError(); return; } } else { ConnInfo info = new ConnInfo(); try { db = new Database(); string query = "SELECT * FROM Connection WHERE ID=" + connectionSelector.SelectedItem.Value; dt = db.executeQueryLocal(query); if (dt.HasErrors) { throw new ODBC2KMLException("Unknown Database error"); } } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); eh.displayError(); return; } try { info = ConnInfo.getConnInfo(int.Parse(connectionSelector.SelectedItem.Value)); } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); eh.displayError(); return; } db.setConnInfo(info); try { dt = db.executeQueryRemote(queryString.Text); } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); eh.displayError(); return; } } resultsPanel.Visible = true; resultsPanel.Controls.Add(new LiteralControl("<span class=\"connectionStyle\"> Database Query Results</span>")); resultsPanel.Controls.Add(new LiteralControl("<div class=\"mainBoxP\">")); resultsPanel.Controls.Add(new LiteralControl("<table cellpadding=\"5\" cellspacing=\"0\" class=\"mainBox2\">")); resultsPanel.Controls.Add(new LiteralControl("<tr><td>")); resultsPanel.Controls.Add(new LiteralControl("<div class=\"omainBox4\">")); resultsPanel.Controls.Add(new LiteralControl("<table class=\"omainBox5\" cellspacing=\"0\" cellpadding=\"0\">")); resultsPanel.Controls.Add(new LiteralControl("<tr>")); foreach (DataColumn dc in dt.Columns) { resultsPanel.Controls.Add(new LiteralControl("<td><b>" + dc.ColumnName + "<br/></b></td>")); } resultsPanel.Controls.Add(new LiteralControl("</tr><tr><td><br/></td></tr>")); foreach (DataRow dr in dt.Rows) { resultsPanel.Controls.Add(new LiteralControl("<tr>")); foreach (Object data in dr.ItemArray) { resultsPanel.Controls.Add(new LiteralControl("<td>" + data.ToString() + "</td>")); } resultsPanel.Controls.Add(new LiteralControl("</tr>")); } resultsPanel.Controls.Add(new LiteralControl("</table>")); resultsPanel.Controls.Add(new LiteralControl("<div align=\"right\" style=\"padding-top: 20px;\">")); resultsPanel.Controls.Add(new LiteralControl("<input type=\"submit\" ID=\"hideResults\" value=\"Hide Results\" class=\"button\" />")); resultsPanel.Controls.Add(new LiteralControl("</div>")); resultsPanel.Controls.Add(new LiteralControl("</td></tr>")); resultsPanel.Controls.Add(new LiteralControl("</div>")); resultsPanel.Controls.Add(new LiteralControl("</span>")); ModalPopupExtender6.Show(); //} /*catch(Exception exception) { errorPanel1.Visible = true; errorPanel1.Controls.Add(new LiteralControl("<div style=\"color: black\"><p>"+exception.Message+"</p></div>")); errorPanel1.Controls.Add(new LiteralControl("<script type=\"text/javascript\">$(\"#errorPanel1\").dialog('open')</script>")); }*/ }
protected void createConnection(object sender, EventArgs e) { String ConnName = odbcNameE.Text.ToString(); String ConnDBName = odbcDNameE.Text.ToString(); String ConnDBAddress = odbcDatabaseE.Text.ToString(); String ConnPortNum = odbcPNE.Text.ToString(); String ConnUser = odbcUserE.Text.ToString(); String ConnPWD = odbcPWE.Text.ToString(); String ConnDBType = odbcDBType.SelectedValue.ToString(); String oracleProtocol = odbcProtocol.Text.ToString(); String oracleSName = odbcSName.Text.ToString(); String oracleSID = odbcSID.Text.ToString(); String DBTypeNum; odbcDBType.SelectedIndex = 0; if (ConnDBType.Equals("MySQL")){ DBTypeNum = "0"; }else if(ConnDBType.Equals("MSSQL")){ DBTypeNum = "1"; }else{ DBTypeNum = "2"; } if (DBTypeNum.Equals("2")){ if (oracleSName.Equals("") && oracleSID.Equals("")){ ErrorHandler eh = new ErrorHandler("Either Service Name or Service ID must be completed!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } if (oracleProtocol.Equals("")) { ErrorHandler eh = new ErrorHandler("Oracle protocol must be provided!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } } Database dbCheck = new Database(); DataTable dtCheck = null; try { dtCheck = dbCheck.executeQueryLocal("SELECT name FROM Connection WHERE name=\'" + ConnName + "\'"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error getting the Connection's name", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } if (dtCheck.Rows.Count > 0) { ErrorHandler eh = new ErrorHandler("Connection name already in use!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } if (ConnName.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a unique name!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; }else if (ConnDBName.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a database name!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; }else if (ConnDBAddress.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a database address!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; }else if (ConnPortNum.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a port number!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; }else if(ConnUser.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a user name!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; }else if (ConnPWD.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a password!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } ConnInfo testConn = new ConnInfo(); testConn.setConnectionName(ConnName); testConn.setDatabaseName(ConnDBName); testConn.setDatabaseType((int)Convert.ToInt32(DBTypeNum)); testConn.setPassword(ConnPWD); testConn.setPortNumber(ConnPortNum); testConn.setServerAddress(ConnDBAddress); testConn.setUserName(ConnUser); if (DBTypeNum.Equals("2")) { testConn.setOracleProtocol(oracleProtocol); testConn.setOracleServiceName(oracleSName); testConn.setOracleSID(oracleSID); } try { Database dbTest = new Database(testConn); DataTable dtTest; if (DBTypeNum.Equals("0")) { dtTest = dbTest.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"); } else if (DBTypeNum.Equals("1")) { dtTest = dbTest.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"); } else { dtTest = dbTest.executeQueryRemote("SELECT TABLE_NAME FROM user_tables"); } } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } //Call Create DB with the DB Function Database db = new Database(); DataTable dt; try { if (DBTypeNum.Equals("2")) { db.executeQueryLocal("INSERT INTO Connection (name, dbName, userName, password, port, address, type, protocol, serviceName, SID) VALUES ('" + ConnName + "', '" + ConnDBName + "', '" + ConnUser + "', '" + ConnPWD + "', '" + ConnPortNum + "', '" + ConnDBAddress + "', '" + DBTypeNum + "', '" + oracleProtocol + "', '" + oracleSName + "', '" + oracleSID + "')"); } else { db.executeQueryLocal("INSERT INTO Connection (name, dbName, userName, password, port, address, type, protocol, serviceName, SID) VALUES ('" + ConnName + "', '" + ConnDBName + "', '" + ConnUser + "', '" + ConnPWD + "', '" + ConnPortNum + "', '" + ConnDBAddress + "', '" + DBTypeNum + "', '', '', '')"); } } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error saving the connection to the database.", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } this.NewConn1ModalPopUp.Hide(); //Jump to the Modify page try { dt = db.executeQueryLocal("SELECT ID FROM CONNECTION WHERE name='" + ConnName + "' AND dbName='" + ConnDBName + "' AND userName='******' AND port='" + ConnPortNum + "' AND address='" + ConnDBAddress + "' AND type='" + DBTypeNum + "'"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error retreiving the new connection's connID.", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } foreach (DataRow dr in dt.Rows) { string connID = dr["ID"].ToString(); Response.Redirect("ConnDetails.aspx?ConnID=" + connID + "&locked=false"); } }
/// <summary> /// Remove all icon and overlay conditions that are no longer valid. If any conditions are removed, /// purge the description. Returns true if the connection has been validated and false if it failed. /// This is only a temporary purge. If the update is canceled, the connection will be restored to its /// previous state. If something unexpected happen, this function will throw an ODBC2KMLException. /// </summary> /// <param name="descriptionBox">TextBox --> The TextBox that holds the description</param> /// <returns>Boolean --> false if the purge failed, true if it succeeded</returns> public bool validateConnnection(TextBox descriptionBox) { try { //Return false if the conn information is bad if (!this.connInfo.isValid(this.connID)) { return(false); } } catch (ODBC2KMLException ex) { throw ex; } //Database needed to see if values need to be purged Database purgeDB = new Database(this.connInfo); //DataTable to hold all table names and DataTable purgeDT = null; //dataset to hold all column names for each table in the datatable DataSet newTableColumnRelation = new DataSet(); //Verify mapping try { //If there is a mapping format if (this.mapping.getFormat() != 0) { String queryables = ""; //Add columns needed if (this.mapping.getLatFieldName() != "") { queryables += this.mapping.getLatFieldName(); } if (this.mapping.getLongFieldName() != "") { if (queryables == "") { queryables += this.mapping.getLongFieldName(); } else { queryables += ", " + this.mapping.getLongFieldName(); } } if (this.mapping.getPlacemarkFieldName() != "") { if (queryables == "") { queryables += this.mapping.getPlacemarkFieldName(); } else { queryables += ", " + this.mapping.getPlacemarkFieldName(); } } //Test the query String query = "SELECT " + queryables + " FROM " + this.mapping.getTableName(); purgeDB.executeQueryRemote(query); } else //reset the mapping, just to be safe if format = 0 { this.mapping = new Mapping(); } } catch //Clear mapping { this.mapping = new Mapping(); } try { if (this.connInfo.getDatabaseType() == ConnInfo.MSSQL) { //MSSQL specific call purgeDT = purgeDB.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' AND TABLE_NAME != 'sysdiagrams'"); foreach (DataRow row in purgeDT.Rows) { //Retrieve each column name for each table in the purge data table DataTable purgeDC = purgeDB.executeQueryRemote("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + row["TABLE_NAME"] + "')"); //Add the retrieved table to the Dataset purgeDC.TableName = row["TABLE_NAME"].ToString(); newTableColumnRelation.Tables.Add(purgeDC); } } else if (this.connInfo.getDatabaseType() == ConnInfo.MYSQL) { //MySQL specific call purgeDT = purgeDB.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' && TABLE_SCHEMA != 'mysql'"); foreach (DataRow row in purgeDT.Rows) { //Retrieve each column name for each table in the purge data table DataTable purgeDC = purgeDB.executeQueryRemote("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + row["TABLE_NAME"] + "')"); //Add the retrieved table to the Dataset purgeDC.TableName = row["TABLE_NAME"].ToString(); newTableColumnRelation.Tables.Add(purgeDC); } } else if (this.connInfo.getDatabaseType() == ConnInfo.ORACLE) { //Oracle specific call purgeDT = purgeDB.executeQueryRemote("select TABLE_NAME from user_tables"); foreach (DataRow row in purgeDT.Rows) { //Retrieve each column name for each table in the purge data table DataTable purgeDC = purgeDB.executeQueryRemote("SELECT COLUMN_NAME FROM dba_tab_columns WHERE (OWNER IS NOT NULL AND TABLE_NAME = '" + row["TABLE_NAME"] + "')"); //Add the retrieved table to the Dataset purgeDC.TableName = row["TABLE_NAME"].ToString(); newTableColumnRelation.Tables.Add(purgeDC); } } else //Just in case....Bad error { throw new ODBC2KMLException("The update function failed to perform properly, please try again."); } } catch (ODBC2KMLException ex) { ex.errorText = "There was a problem retreiving column names from the remote database"; throw ex; } foreach (Icon i in this.getIcons()) { if (i.purgeInvalidIconConditions(purgeDT, newTableColumnRelation)) { descriptionBox.Text = ""; } } foreach (Overlay o in this.getOverlays()) { if (o.purgeInvalidOverlayConditions(purgeDT, newTableColumnRelation)) { descriptionBox.Text = ""; } } return(true); }
/// <summary> /// This function is called when the information is editted from main. This connection forces all /// invalid connection information to be purged from the database. This is a permanent purge. /// If anything unexpected happens, a ODBC2KMLException will be thrown. /// </summary> /// <returns>Boolean --> False if connection cannot be guaranteed to be in a safe state /// true, if the connection is in a safe state</returns> public Boolean safeStateConnection() { try { //Return false if the conn information is bad if (!this.connInfo.isValid(this.connID)) { return(false); } } catch (ODBC2KMLException ex) { throw ex; } //Database needed to see if values need to be purged Database purgeDB = new Database(this.connInfo); //DataTable to hold all table names and DataTable purgeDT; //dataset to hold all column names for each table in the datatable DataSet newTableColumnRelation = new DataSet(); //Flag to determine if the mapping should be removed Boolean removeMapping = false; //ArrayList for queries ArrayList queries = new ArrayList(); //Verify mapping try { //If there is a mapping format if (this.mapping.getFormat() != 0) { String queryables = ""; //Add columns needed if (this.mapping.getLatFieldName() != "") { queryables += this.mapping.getLatFieldName(); } if (this.mapping.getLongFieldName() != "") { if (queryables == "") { queryables += this.mapping.getLongFieldName(); } else { queryables += ", " + this.mapping.getLongFieldName(); } } if (this.mapping.getPlacemarkFieldName() != "") { if (queryables == "") { queryables += this.mapping.getPlacemarkFieldName(); } else { queryables += ", " + this.mapping.getPlacemarkFieldName(); } } //Test the query String query = "SELECT " + queryables + " FROM " + this.mapping.getTableName(); purgeDB.executeQueryRemote(query); } else //reset the mapping, just to be safe if format = 0 { removeMapping = true; this.mapping = new Mapping(); } } catch //Clear mapping { removeMapping = true; this.mapping = new Mapping(); } try { if (this.connInfo.getDatabaseType() == ConnInfo.MSSQL) { //MSSQL specific call purgeDT = purgeDB.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' AND TABLE_NAME != 'sysdiagrams'"); foreach (DataRow row in purgeDT.Rows) { //Retrieve each column name for each table in the purge data table DataTable purgeDC = purgeDB.executeQueryRemote("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + row["TABLE_NAME"] + "')"); //Add the retrieved table to the Dataset purgeDC.TableName = row["TABLE_NAME"].ToString(); newTableColumnRelation.Tables.Add(purgeDC); } } else if (this.connInfo.getDatabaseType() == ConnInfo.MYSQL) { //MySQL specific call purgeDT = purgeDB.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' && TABLE_SCHEMA != 'mysql'"); foreach (DataRow row in purgeDT.Rows) { //Retrieve each column name for each table in the purge data table DataTable purgeDC = purgeDB.executeQueryRemote("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE (TABLE_NAME = '" + row["TABLE_NAME"] + "')"); //Add the retrieved table to the Dataset purgeDC.TableName = row["TABLE_NAME"].ToString(); newTableColumnRelation.Tables.Add(purgeDC); } } else if (this.connInfo.getDatabaseType() == ConnInfo.ORACLE) { //Oracle specific call purgeDT = purgeDB.executeQueryRemote("select TABLE_NAME from user_tables"); foreach (DataRow row in purgeDT.Rows) { //Retrieve each column name for each table in the purge data table DataTable purgeDC = purgeDB.executeQueryRemote("SELECT COLUMN_NAME FROM dba_tab_columns WHERE (OWNER IS NOT NULL AND TABLE_NAME = '" + row["TABLE_NAME"] + "')"); //Add the retrieved table to the Dataset purgeDC.TableName = row["TABLE_NAME"].ToString(); newTableColumnRelation.Tables.Add(purgeDC); } } else //Just in case....Bad error { throw new ODBC2KMLException("The update function failed to perform properly, please try again."); } } catch (ODBC2KMLException ex) { ex.errorText = "There was a problem retreiving column names from the remote database"; throw ex; } //Flag used to see if the description should be removed Boolean removeDescription = false; try { //For any invalid icon conditions, remove them from the database foreach (Icon i in this.getIcons()) { String query = i.purgeInvalidIconConditionsFromDatabase(purgeDT, newTableColumnRelation, purgeDB); if (query.Length != 0) { removeDescription = true; queries.Add(query); } } } catch (ODBC2KMLException ex) { throw ex; } try { //For any invalid overlay conditions, remove them from the database foreach (Overlay o in this.getOverlays()) { String query = o.purgeInvalidOverlayConditionsFromDatabase(purgeDT, newTableColumnRelation, purgeDB); if (query.Length != 0) { removeDescription = true; queries.Add(query); } } } catch (ODBC2KMLException ex) { throw ex; } //Remove the description from the database if (removeDescription) { String query = "DELETE FROM Description WHERE connID=" + this.connID; queries.Add(query); /*try * { * purgeDB.executeQueryLocal(query); * } * catch (ODBC2KMLException ex) * { * ex.errorText = "There was a problem deleting the decription from the database."; * throw ex; * }*/ } //Remove the mapping if flag is set if (removeMapping) { String query = "DELETE FROM Mapping WHERE connID=" + this.connID; queries.Add(query); /*try * { * purgeDB.executeQueryLocal(query); * } * catch (ODBC2KMLException ex) * { * ex.errorText = "There was a problem deleting the mapping from the database."; * throw ex; * }*/ } purgeDB.executeQueryLocal(queries); return(true); }
/// <summary> /// This function takes a connection object and generates all the associated KML for that connection. /// It works hand-in-hand with KMLGenerationLibrary, Styles, and Placemarks. /// /// There is also a helper class used to prevent duplicate styles (HashStyleComparer). /// </summary> /// <param name="connection">Connection --> connection object for the connection that you wish to generate KML for</param> /// <returns>String --> A string that is the KML</returns> public string generateKMLFromConnection(Connection connection) { //if the mapping is invalid, throw an exception and don't try to continue if (!connection.mapping.isValid(connection.connInfo)) { throw new ODBC2KMLException("There was an exception generating KML. Mapping is invalid."); } //Needed to generate KML, parameter is desired file name within KML file KMLGenerationLibrary kmlGenerator = new KMLGenerationLibrary(this.fileName); try { //Get mappings fromc onnection object Mapping map = connection.getMapping(); //Create array list to hold places ArrayList placemarks = new ArrayList(); //Create hashset to hold unique styles, takes HashStyleComparer which is a helper class HashSet <Style> styles = new HashSet <Style>(new HashStyleComparer()); //Create the Icon array and grabs the icons for the connection ArrayList icons = new ArrayList(); icons = connection.getIcons(); //Create the overlay array and grab the overlays for the connection ArrayList overlays = new ArrayList(); overlays = connection.getOverlays(); //Retrieve description string String descString = connection.getDescription().getDesc(); //Create an array to store new description values ArrayList descArray = new ArrayList(); //Create data table to pass to parser DataTable remote = null; //Create database Database DB = new Database(connection.getConnInfo()); //Grab the tablename out of mapping String tableName = map.getTableName(); try { if (connection.getConnInfo().getDatabaseType() == ConnInfo.MSSQL) { remote = DB.executeQueryRemote("SELECT * FROM " + tableName); } else if (connection.getConnInfo().getDatabaseType() == ConnInfo.MYSQL) { remote = DB.executeQueryRemote("SELECT * FROM " + tableName + ";"); } else if (connection.getConnInfo().getDatabaseType() == ConnInfo.ORACLE) { remote = DB.executeQueryRemote("SELECT * FROM \"" + tableName + "\""); } } catch { throw new ODBC2KMLException("There was an exception generating KML. There was a problem retreiving data from the remote server"); } //Parsed descriptions for rows descArray = Description.parseDesc(remote, descString, tableName); int counter = 0; //For each row in the table!!! foreach (DataRow remoteRow in remote.Rows) { //Set placemark name string placemarkName; try { if (map.getPlacemarkFieldName() == null || map.getPlacemarkFieldName().Trim().Equals("") || map.getPlacemarkFieldName().Trim().Equals("No placemark name mapped")) { placemarkName = ""; } else { placemarkName = (String)remoteRow[map.getPlacemarkFieldName()]; } } catch { throw new ODBC2KMLException("There was an exception generating KML. Error parsing placemark."); } //Foreach row set the description for each row String rowDesc = descArray[counter].ToString(); //Declare the lat and long holders Double rowLat = 0, rowLon = 0; //Check to see how many columns there are try { if (map.getFormat() != Mapping.SEPARATE) { //Select the column value String column = ""; foreach (DataColumn remoteColumn in remote.Columns) { if (remoteColumn.ColumnName == map.getLatFieldName()) { column = remoteRow[remoteColumn].ToString(); } } //Create the array to hold the coordinates double[] coordinates; //Separate the coordinates //Order == Latitude First if (map.getFormat() == Mapping.LATFIRST) { coordinates = map.separate(column, Mapping.LATFIRST); rowLat = coordinates[0]; rowLon = coordinates[1]; } else //Order == Longitude first { coordinates = map.separate(column, Mapping.LONGFIRST); rowLon = coordinates[0]; rowLat = coordinates[1]; } } else//Two separate columns { //Get coordinates foreach (DataColumn remoteColumn in remote.Columns) { if (remoteColumn.ColumnName == map.getLatFieldName()) { rowLat = Double.Parse(remoteRow[remoteColumn].ToString()); } else if (remoteColumn.ColumnName == map.getLongFieldName()) { rowLon = Double.Parse(remoteRow[remoteColumn].ToString()); } } //End for each } //End else } catch { throw new ODBC2KMLException("There was an exception generating KML. Error parsing lat/long rows."); } //Row's icon Icon rowIcon = new Icon(); rowIcon.setLocation(""); //For each icon until the first one found, compare the icons //conditions against the given row Boolean breakLoop = false; foreach (Icon i in icons) { foreach (Condition c in i.getConditions()) { //See if the condition applies to the given row if (c.evaluateCondition(remoteRow, c, tableName)) { //Set temp icon to row icon and tell it to break out rowIcon = new Icon(i); breakLoop = true; } //Grabbed the first icon, break out if (breakLoop) { break; } }//End inner for each //Grabbed the first icon, break out if (breakLoop) { break; } }//End outer for each //Long unsigned int, needed to properly interpret colors UInt64 color = 0; foreach (Overlay o in overlays) { foreach (Condition c in o.getConditions()) { //See if the condition applies if (c.evaluateCondition(remoteRow, c, tableName)) { if (color == 0) { //Set the color to hex value color = 0xFF000000; } //Mix the colors, if multiple colors work color = color | (Convert.ToUInt64(o.getColor(), 16)); } } //End inner for each } //End outer for each //Create Style and placemark for this coordinate set Style rowStyle = new Style(); Placemark rowPlacemark; //if there is an icon, create the name of the style based on the icon name and color if (rowIcon.getLocation() != "") { if (rowIcon.getLocality() == false) { //Create new style with external icon rowStyle = new Style(rowIcon, color, (rowIcon.getLocation() + "_" + color.ToString("X"))); rowIcon.setLocation(""); } else //If the icon is local, append server data { //Create the new style, with local icon rowIcon.setLocation(this.serverPath + rowIcon.getLocation()); rowStyle = new Style(rowIcon, color, (rowIcon.getLocation() + "_" + color.ToString("X"))); rowIcon.setLocation(""); } } else if (rowIcon.getLocation() == "" && color != 0) //Create the style name based on the color { rowStyle = new Style(rowIcon, color, color.ToString("X")); } else //If rowstyle is null, ignore it { rowStyle = null; } //Create placemark and add it to array list rowPlacemark = new Placemark(rowLat, rowLon, rowDesc, placemarkName); placemarks.Add(rowPlacemark); //If there is a row style, add it to the placemark and the array list if (rowStyle != null) { rowPlacemark.setPlacemarkStyleName("#" + rowStyle.getStyleName()); styles.Add(rowStyle); } else { //Default value which won't add a style to this placemark in KML rowPlacemark.setPlacemarkStyleName(""); } //Increment counter for next row (associated with getting the row description) counter++; }//End for each // }//End for each //Add each style to the KML foreach (Style s in styles) { kmlGenerator.addStyle(s); } //Used to check if a look at has been added Boolean addLookAt = false; //Add each placemark to the KML foreach (Placemark p in placemarks) { kmlGenerator.addPlacemark(p); if (!addLookAt) //Add the first placemark as default lookat { kmlGenerator.addLookAt(p); addLookAt = true; } } } catch (ODBC2KMLException e) //If bad things happen pass it up to connection details { throw e; } //Return KML string return(kmlGenerator.finalizeKML()); }//End function
protected void Page_Load(object sender, EventArgs e) { Database db = new Database(); DataTable dt; Label title = new Label(); string connID = Request["con"]; string table = Request["tbl"]; string tblQuery = "SELECT * FROM " + table; ConnInfo info = new ConnInfo(); try { db = new Database(); string query = "SELECT * FROM Connection WHERE ID=" + connID; dt = db.executeQueryLocal(query); if (dt.HasErrors) { throw new ODBC2KMLException("There was a problem getting the connection information from the local database"); } } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.conErrorText, errorPanel1); eh.displayError(); return; } //Cycle through each row and column foreach (DataRow row in dt.Rows) { foreach (DataColumn col in dt.Columns) { //Set all connInfo switch (col.ColumnName) { case "name": info.setConnectionName(row[col].ToString()); break; case "dbName": info.setDatabaseName(row[col].ToString()); break; case "userName": info.setUserName(row[col].ToString()); break; case "password": info.setPassword(row[col].ToString()); break; case "port": info.setPortNumber(row[col].ToString()); break; case "address": info.setServerAddress(row[col].ToString()); break; case "type": info.setDatabaseType(int.Parse(row[col].ToString())); break; case "protocol": info.setOracleProtocol(row[col].ToString()); break; case "serviceName": info.setOracleServiceName(row[col].ToString()); break; case "SID": info.setOracleSID(row[col].ToString()); break; default: break; } } }//End outer loop db.setConnInfo(info); try { dt = db.executeQueryRemote(tblQuery); } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.tblErrorText, errorPanel1); eh.displayError(); return; } //resultsPanel.Visible = true; bool altTables = true; Page.Controls.Add(new LiteralControl("<span style=\"color: white; font-weight:bold; background-color:rgb(26,49,76);\"> " + table + " Database Query Results</span>")); Page.Controls.Add(new LiteralControl("<div>")); Page.Controls.Add(new LiteralControl("<table cellpadding=\"5\" cellspacing=\"2\">")); Page.Controls.Add(new LiteralControl("<tr><td>")); Page.Controls.Add(new LiteralControl("<div>")); Page.Controls.Add(new LiteralControl("<table cellspacing=\"2\" cellpadding=\"2\" rules=\"all\">")); Page.Controls.Add(new LiteralControl("<tr class=\"titleConn\">")); foreach (DataColumn dc in dt.Columns) { Page.Controls.Add(new LiteralControl("<td><b>" + dc.ColumnName + "<br/></b></td>")); } Page.Controls.Add(new LiteralControl("</tr>")); foreach (DataRow dr in dt.Rows) { if (altTables) { Page.Controls.Add(new LiteralControl("<tr class=\"evenConn\">")); foreach (Object data in dr.ItemArray) { Page.Controls.Add(new LiteralControl("<td>" + data.ToString() + "</td>")); } Page.Controls.Add(new LiteralControl("</tr>")); } else { Page.Controls.Add(new LiteralControl("<tr class=\"oddConn\">")); foreach (Object data in dr.ItemArray) { Page.Controls.Add(new LiteralControl("<td>" + data.ToString() + "</td>")); } Page.Controls.Add(new LiteralControl("</tr>")); } altTables = !altTables; } Page.Controls.Add(new LiteralControl("</table>")); Page.Controls.Add(new LiteralControl("</td></tr>")); Page.Controls.Add(new LiteralControl("</div>")); Page.Controls.Add(new LiteralControl("</span>")); }
public Boolean isValid(ConnInfo connInfo) { //If lat and long mappings are set if (this.getLatFieldName() != "" && this.getLongFieldName() != "") { if (this.getTableName() == "") //See if there is a table name { //No table name return(false); } if (this.getFormat() != 1 && this.getFormat() != 2 && this.getFormat() != 3) //See if there is a format { //No format return(false); } double tempDouble; Database db = new Database(connInfo); if (this.format == Mapping.SEPARATE) { DataTable dt = db.executeQueryRemote("SELECT " + this.latFieldName + ", " + this.longFieldName + " FROM " + this.tableName); foreach (DataRow dr in dt.Rows) { //if we can't parse a double from the column, return false if (!Double.TryParse(dr[this.latFieldName].ToString(), out tempDouble) || !Double.TryParse(dr[this.longFieldName].ToString(), out tempDouble)) { return(false); } } } else if (this.format == Mapping.LATFIRST || this.format == Mapping.LONGFIRST) { DataTable dt = db.executeQueryRemote("SELECT " + this.latFieldName + " FROM " + this.tableName); foreach (DataRow dr in dt.Rows) { //try to separate the coordinates from the column, if it fails return false try { double[] coords = separate(dr[this.latFieldName].ToString(), this.format); } catch (ODBC2KMLException ex) { return(false); } } } } else if (this.getTableName() == "") //No lat and long { if (this.getTableName() != "") //See if there is a table name { //Table name return(false); } if (this.getFormat() != Mapping.NONE) //See if format is set to default values { //Not default return(false); } } else { try { if (this.getTableName() != null) // This will throw an exception if this is null { if (this.getLatFieldName() == null) { return(false); } if (this.getFormat() == 0) { return(false); } } } catch // Catch the exception which means this is null, return false because null means not valid { return(false); } } return(true); }
/// <summary> /// This function takes a connection object and generates all the associated KML for that connection. /// It works hand-in-hand with KMLGenerationLibrary, Styles, and Placemarks. /// /// There is also a helper class used to prevent duplicate styles (HashStyleComparer). /// </summary> /// <param name="connection">Connection --> connection object for the connection that you wish to generate KML for</param> /// <returns>String --> A string that is the KML</returns> public string generateKMLFromConnection(Connection connection) { //if the mapping is invalid, throw an exception and don't try to continue if (!connection.mapping.isValid(connection.connInfo)) { throw new ODBC2KMLException("There was an exception generating KML. Mapping is invalid."); } //Needed to generate KML, parameter is desired file name within KML file KMLGenerationLibrary kmlGenerator = new KMLGenerationLibrary(this.fileName); try { //Get mappings fromc onnection object Mapping map = connection.getMapping(); //Create array list to hold places ArrayList placemarks = new ArrayList(); //Create hashset to hold unique styles, takes HashStyleComparer which is a helper class HashSet<Style> styles = new HashSet<Style>(new HashStyleComparer()); //Create the Icon array and grabs the icons for the connection ArrayList icons = new ArrayList(); icons = connection.getIcons(); //Create the overlay array and grab the overlays for the connection ArrayList overlays = new ArrayList(); overlays = connection.getOverlays(); //Retrieve description string String descString = connection.getDescription().getDesc(); //Create an array to store new description values ArrayList descArray = new ArrayList(); //Create data table to pass to parser DataTable remote = null; //Create database Database DB = new Database(connection.getConnInfo()); //Grab the tablename out of mapping String tableName = map.getTableName(); try { if (connection.getConnInfo().getDatabaseType() == ConnInfo.MSSQL) { remote = DB.executeQueryRemote("SELECT * FROM " + tableName); } else if (connection.getConnInfo().getDatabaseType() == ConnInfo.MYSQL) { remote = DB.executeQueryRemote("SELECT * FROM " + tableName + ";"); } else if (connection.getConnInfo().getDatabaseType() == ConnInfo.ORACLE) { remote = DB.executeQueryRemote("SELECT * FROM \"" + tableName + "\""); } } catch { throw new ODBC2KMLException("There was an exception generating KML. There was a problem retreiving data from the remote server"); } //Parsed descriptions for rows descArray = Description.parseDesc(remote, descString, tableName); int counter = 0; //For each row in the table!!! foreach (DataRow remoteRow in remote.Rows) { //Set placemark name string placemarkName; try { if (map.getPlacemarkFieldName() == null || map.getPlacemarkFieldName().Trim().Equals("") || map.getPlacemarkFieldName().Trim().Equals("No placemark name mapped")) { placemarkName = ""; } else { placemarkName = (String)remoteRow[map.getPlacemarkFieldName()]; } } catch { throw new ODBC2KMLException("There was an exception generating KML. Error parsing placemark."); } //Foreach row set the description for each row String rowDesc = descArray[counter].ToString(); //Declare the lat and long holders Double rowLat = 0, rowLon = 0; //Check to see how many columns there are try { if (map.getFormat() != Mapping.SEPARATE) { //Select the column value String column = ""; foreach (DataColumn remoteColumn in remote.Columns) { if (remoteColumn.ColumnName == map.getLatFieldName()) { column = remoteRow[remoteColumn].ToString(); } } //Create the array to hold the coordinates double[] coordinates; //Separate the coordinates //Order == Latitude First if (map.getFormat() == Mapping.LATFIRST) { coordinates = map.separate(column, Mapping.LATFIRST); rowLat = coordinates[0]; rowLon = coordinates[1]; } else //Order == Longitude first { coordinates = map.separate(column, Mapping.LONGFIRST); rowLon = coordinates[0]; rowLat = coordinates[1]; } } else//Two separate columns { //Get coordinates foreach (DataColumn remoteColumn in remote.Columns) { if (remoteColumn.ColumnName == map.getLatFieldName()) { rowLat = Double.Parse(remoteRow[remoteColumn].ToString()); } else if (remoteColumn.ColumnName == map.getLongFieldName()) { rowLon = Double.Parse(remoteRow[remoteColumn].ToString()); } }//End for each }//End else } catch { throw new ODBC2KMLException("There was an exception generating KML. Error parsing lat/long rows."); } //Row's icon Icon rowIcon = new Icon(); rowIcon.setLocation(""); //For each icon until the first one found, compare the icons //conditions against the given row Boolean breakLoop = false; foreach (Icon i in icons) { foreach (Condition c in i.getConditions()) { //See if the condition applies to the given row if (c.evaluateCondition(remoteRow, c, tableName)) { //Set temp icon to row icon and tell it to break out rowIcon = new Icon(i); breakLoop = true; } //Grabbed the first icon, break out if (breakLoop) break; }//End inner for each //Grabbed the first icon, break out if (breakLoop) break; }//End outer for each //Long unsigned int, needed to properly interpret colors UInt64 color = 0; foreach (Overlay o in overlays) { foreach (Condition c in o.getConditions()) { //See if the condition applies if (c.evaluateCondition(remoteRow, c, tableName)) { if (color == 0) { //Set the color to hex value color = 0xFF000000; } //Mix the colors, if multiple colors work color = color | (Convert.ToUInt64(o.getColor(), 16)); } }//End inner for each }//End outer for each //Create Style and placemark for this coordinate set Style rowStyle = new Style(); Placemark rowPlacemark; //if there is an icon, create the name of the style based on the icon name and color if (rowIcon.getLocation() != "") { if (rowIcon.getLocality() == false) { //Create new style with external icon rowStyle = new Style(rowIcon, color, (rowIcon.getLocation() + "_" + color.ToString("X"))); rowIcon.setLocation(""); } else //If the icon is local, append server data { //Create the new style, with local icon rowIcon.setLocation(this.serverPath + rowIcon.getLocation()); rowStyle = new Style(rowIcon, color, (rowIcon.getLocation() + "_" + color.ToString("X"))); rowIcon.setLocation(""); } } else if (rowIcon.getLocation() == "" && color != 0) //Create the style name based on the color { rowStyle = new Style(rowIcon, color, color.ToString("X")); } else //If rowstyle is null, ignore it { rowStyle = null; } //Create placemark and add it to array list rowPlacemark = new Placemark(rowLat, rowLon, rowDesc, placemarkName); placemarks.Add(rowPlacemark); //If there is a row style, add it to the placemark and the array list if (rowStyle != null) { rowPlacemark.setPlacemarkStyleName("#" + rowStyle.getStyleName()); styles.Add(rowStyle); } else { //Default value which won't add a style to this placemark in KML rowPlacemark.setPlacemarkStyleName(""); } //Increment counter for next row (associated with getting the row description) counter++; }//End for each // }//End for each //Add each style to the KML foreach (Style s in styles) { kmlGenerator.addStyle(s); } //Used to check if a look at has been added Boolean addLookAt = false; //Add each placemark to the KML foreach (Placemark p in placemarks) { kmlGenerator.addPlacemark(p); if (!addLookAt) //Add the first placemark as default lookat { kmlGenerator.addLookAt(p); addLookAt = true; } } } catch (ODBC2KMLException e) //If bad things happen pass it up to connection details { throw e; } //Return KML string return kmlGenerator.finalizeKML(); }
protected void createConnection(object sender, EventArgs e) { String ConnName = odbcNameE.Text.ToString(); String ConnDBName = odbcDNameE.Text.ToString(); String ConnDBAddress = odbcDatabaseE.Text.ToString(); String ConnPortNum = odbcPNE.Text.ToString(); String ConnUser = odbcUserE.Text.ToString(); String ConnPWD = odbcPWE.Text.ToString(); String ConnDBType = odbcDBType.SelectedValue.ToString(); String oracleProtocol = odbcProtocol.Text.ToString(); String oracleSName = odbcSName.Text.ToString(); String oracleSID = odbcSID.Text.ToString(); String DBTypeNum; odbcDBType.SelectedIndex = 0; if (ConnDBType.Equals("MySQL")) { DBTypeNum = "0"; } else if (ConnDBType.Equals("MSSQL")) { DBTypeNum = "1"; } else { DBTypeNum = "2"; } if (DBTypeNum.Equals("2")) { if (oracleSName.Equals("") && oracleSID.Equals("")) { ErrorHandler eh = new ErrorHandler("Either Service Name or Service ID must be completed!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } if (oracleProtocol.Equals("")) { ErrorHandler eh = new ErrorHandler("Oracle protocol must be provided!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } } Database dbCheck = new Database(); DataTable dtCheck = null; try { dtCheck = dbCheck.executeQueryLocal("SELECT name FROM Connection WHERE name=\'" + ConnName + "\'"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error getting the Connection's name", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } if (dtCheck.Rows.Count > 0) { ErrorHandler eh = new ErrorHandler("Connection name already in use!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } if (ConnName.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a unique name!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } else if (ConnDBName.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a database name!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } else if (ConnDBAddress.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a database address!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } else if (ConnPortNum.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a port number!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } else if (ConnUser.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a user name!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } else if (ConnPWD.Equals("")) { ErrorHandler eh = new ErrorHandler("The connection must have a password!", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } ConnInfo testConn = new ConnInfo(); testConn.setConnectionName(ConnName); testConn.setDatabaseName(ConnDBName); testConn.setDatabaseType((int)Convert.ToInt32(DBTypeNum)); testConn.setPassword(ConnPWD); testConn.setPortNumber(ConnPortNum); testConn.setServerAddress(ConnDBAddress); testConn.setUserName(ConnUser); if (DBTypeNum.Equals("2")) { testConn.setOracleProtocol(oracleProtocol); testConn.setOracleServiceName(oracleSName); testConn.setOracleSID(oracleSID); } try { Database dbTest = new Database(testConn); DataTable dtTest; if (DBTypeNum.Equals("0")) { dtTest = dbTest.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"); } else if (DBTypeNum.Equals("1")) { dtTest = dbTest.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"); } else { dtTest = dbTest.executeQueryRemote("SELECT TABLE_NAME FROM user_tables"); } } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } //Call Create DB with the DB Function Database db = new Database(); DataTable dt; try { if (DBTypeNum.Equals("2")) { db.executeQueryLocal("INSERT INTO Connection (name, dbName, userName, password, port, address, type, protocol, serviceName, SID) VALUES ('" + ConnName + "', '" + ConnDBName + "', '" + ConnUser + "', '" + ConnPWD + "', '" + ConnPortNum + "', '" + ConnDBAddress + "', '" + DBTypeNum + "', '" + oracleProtocol + "', '" + oracleSName + "', '" + oracleSID + "')"); } else { db.executeQueryLocal("INSERT INTO Connection (name, dbName, userName, password, port, address, type, protocol, serviceName, SID) VALUES ('" + ConnName + "', '" + ConnDBName + "', '" + ConnUser + "', '" + ConnPWD + "', '" + ConnPortNum + "', '" + ConnDBAddress + "', '" + DBTypeNum + "', '', '', '')"); } } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error saving the connection to the database.", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } this.NewConn1ModalPopUp.Hide(); //Jump to the Modify page try { dt = db.executeQueryLocal("SELECT ID FROM CONNECTION WHERE name='" + ConnName + "' AND dbName='" + ConnDBName + "' AND userName='******' AND port='" + ConnPortNum + "' AND address='" + ConnDBAddress + "' AND type='" + DBTypeNum + "'"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error retreiving the new connection's connID.", errorPanel1); this.NewConn1ModalPopUp.Hide(); eh.displayError(); return; } foreach (DataRow dr in dt.Rows) { string connID = dr["ID"].ToString(); Response.Redirect("ConnDetails.aspx?ConnID=" + connID + "&locked=false"); } }
protected void updateConnection(object sender, CommandEventArgs e) { Button sendBtn = (Button)sender; String args = sendBtn.CommandArgument.ToString(); ConnInfo tempConnInfo = new ConnInfo(); tempConnInfo.setConnectionName(editConnName.Text); tempConnInfo.setDatabaseName(editConnDBName.Text); tempConnInfo.setServerAddress(editConnDBAddr.Text); tempConnInfo.setPortNumber(editConnDBPort.Text); tempConnInfo.setUserName(editConnUser.Text); tempConnInfo.setPassword(editConnPass.Text); tempConnInfo.setDatabaseType((editConnDBType.SelectedIndex)); tempConnInfo.setOracleProtocol(editOracleProtocol.Text); tempConnInfo.setOracleServiceName(editOracleService.Text); tempConnInfo.setOracleSID(editOracleSID.Text); //If the connection information is bad, report the error and cancel the function. This does NOT run against the database. try { if (!tempConnInfo.isValid(Convert.ToInt32(args))) { throw new ODBC2KMLException(""); // Throw any error. The catch is generic. } } catch { String error = "The entered connection information is invalid. Please make sure all fields are filled and that they are in proper format."; if (tempConnInfo.getDatabaseType() == ConnInfo.ORACLE) { error = "The entered connection information is invalid. Please verify that all fields have a value and the value is of proper type." + " Also, make sure that Oracle SID or Oracle Service Name and Oracle Protocol have been entered."; } ErrorHandler eh = new ErrorHandler(error, errorPanel1); this.editConnModalPopUp.Hide(); eh.displayError(); return; } //Create database and test it Database db = new Database(tempConnInfo); //See if you can reach the database. If not, error out and don't save. try { if (tempConnInfo.getDatabaseType() == ConnInfo.MSSQL) { String query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' AND TABLE_NAME != 'sysdiagrams'"; db.executeQueryRemote(query); } else if (tempConnInfo.getDatabaseType() == ConnInfo.MYSQL) { String query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' && TABLE_SCHEMA != 'mysql'"; db.executeQueryRemote(query); } else if (tempConnInfo.getDatabaseType() == ConnInfo.ORACLE) { String query = "select TABLE_NAME from user_tables"; db.executeQueryRemote(query); } } catch { ErrorHandler eh = new ErrorHandler("The database entered could not be connected to. Please verify the information is correct.", errorPanel1); this.editConnModalPopUp.Hide(); eh.displayError(); return; } db.executeQueryLocal("UPDATE Connection SET name='" + tempConnInfo.getConnectionName() + "', dbName='" + tempConnInfo.getDatabaseName() + "', userName='******', password='******', port='" + tempConnInfo.getPortNumber() + "', address='" + tempConnInfo.getServerAddress() + "', type='" + tempConnInfo.getDatabaseType() + "', protocol='" + tempConnInfo.getOracleProtocol() + "', serviceName='" + tempConnInfo.getOracleServiceName() + "', SID='" + tempConnInfo.getOracleSID() + "' WHERE (ID='" + args + "')"); Connection conn = new Connection(Convert.ToInt16(args)); try { conn.populateFields(); //Force the connection into a safe state, if it is not if (!conn.safeStateConnection()) { String error = "Invalid connection information. Please verify all of your fields are filled in correctly." + "If you are using an oracle connection, please make sure you filled out the oracle specific information."; ErrorHandler eh = new ErrorHandler(error, errorPanel1); this.editConnModalPopUp.Hide(); eh.displayError(); return; } } catch (ODBC2KMLException err) { ErrorHandler eh = new ErrorHandler(err.errorText, errorPanel1); this.editConnModalPopUp.Hide(); eh.displayError(); return; } if (e.CommandName.Equals("saveConn")) { this.editConnModalPopUp.Hide(); Response.Redirect("Main.aspx"); } else { this.editConnModalPopUp.Hide(); Response.Redirect("ConnDetails.aspx?ConnID=" + ((Button)sender).CommandArgument.ToString() + "&locked=false"); } }
protected void Page_Load(object sender, EventArgs e) { Database db = new Database(); DataTable dt; Label title = new Label(); string connID = Request["con"]; string table = Request["tbl"]; string tblQuery = "SELECT * FROM " + table; ConnInfo info = new ConnInfo(); try { db = new Database(); string query = "SELECT * FROM Connection WHERE ID=" + connID; dt = db.executeQueryLocal(query); if (dt.HasErrors) { throw new ODBC2KMLException("There was a problem getting the connection information from the local database"); } } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.conErrorText, errorPanel1); eh.displayError(); return; } //Cycle through each row and column foreach (DataRow row in dt.Rows) { foreach (DataColumn col in dt.Columns) { //Set all connInfo switch (col.ColumnName) { case "name": info.setConnectionName(row[col].ToString()); break; case "dbName": info.setDatabaseName(row[col].ToString()); break; case "userName": info.setUserName(row[col].ToString()); break; case "password": info.setPassword(row[col].ToString()); break; case "port": info.setPortNumber(row[col].ToString()); break; case "address": info.setServerAddress(row[col].ToString()); break; case "type": info.setDatabaseType(int.Parse(row[col].ToString())); break; case "protocol": info.setOracleProtocol(row[col].ToString()); break; case "serviceName": info.setOracleServiceName(row[col].ToString()); break; case "SID": info.setOracleSID(row[col].ToString()); break; default: break; } } }//End outer loop db.setConnInfo(info); try { dt = db.executeQueryRemote(tblQuery); } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.tblErrorText, errorPanel1); eh.displayError(); return; } //resultsPanel.Visible = true; bool altTables = true; Page.Controls.Add(new LiteralControl("<span style=\"color: white; font-weight:bold; background-color:rgb(26,49,76);\"> " + table + " Database Query Results</span>")); Page.Controls.Add(new LiteralControl("<div>")); Page.Controls.Add(new LiteralControl("<table cellpadding=\"5\" cellspacing=\"2\">")); Page.Controls.Add(new LiteralControl("<tr><td>")); Page.Controls.Add(new LiteralControl("<div>")); Page.Controls.Add(new LiteralControl("<table cellspacing=\"2\" cellpadding=\"2\" rules=\"all\">")); Page.Controls.Add(new LiteralControl("<tr class=\"titleConn\">")); foreach (DataColumn dc in dt.Columns) { Page.Controls.Add(new LiteralControl("<td><b>" + dc.ColumnName + "<br/></b></td>")); } Page.Controls.Add(new LiteralControl("</tr>")); foreach (DataRow dr in dt.Rows) { if (altTables) { Page.Controls.Add(new LiteralControl("<tr class=\"evenConn\">")); foreach (Object data in dr.ItemArray) { Page.Controls.Add(new LiteralControl("<td>" + data.ToString() + "</td>")); } Page.Controls.Add(new LiteralControl("</tr>")); } else { Page.Controls.Add(new LiteralControl("<tr class=\"oddConn\">")); foreach (Object data in dr.ItemArray) { Page.Controls.Add(new LiteralControl("<td>" + data.ToString() + "</td>")); } Page.Controls.Add(new LiteralControl("</tr>")); } altTables = !altTables; } Page.Controls.Add(new LiteralControl("</table>")); Page.Controls.Add(new LiteralControl("</td></tr>")); Page.Controls.Add(new LiteralControl("</div>")); Page.Controls.Add(new LiteralControl("</span>")); }
/// <summary> /// Function to validate a description string. Checks URLs and Field tags. /// URLs - verifies that there is one and only one TITLE element and that the title and URL are not empty, and that there is an open and closing tag /// Field tag - verifies that there one and only one set of TBL and COL tags, that the tags are not empty, and that there is an open and closing tag /// </summary> /// <param name="currentConnInfo">ConnInfo object containing the current connection info for the description being tested</param> /// <param name="currentMapping">Mapping object containing the current mapping for the description being tested</param> /// <returns>true if a description is valid and false if it is not</returns> public bool isValid(ConnInfo currentConnInfo, Mapping currentMapping) { //validate field tags int startIndex = 0; int endIndex = 0; int lengthOfTag = 0; //if start of field tag is found while (desc.IndexOf("[FIELD]", startIndex, StringComparison.InvariantCultureIgnoreCase) != -1) { //if end of field tag is found, set startindex, else return false if (desc.IndexOf("[/FIELD]", StringComparison.InvariantCultureIgnoreCase) != -1) { //get the index of end of tag and calculate lengthoftag endIndex = desc.IndexOf("[/FIELD]", startIndex, StringComparison.InvariantCultureIgnoreCase); lengthOfTag = endIndex - startIndex; } else { return(false); } //if start of table tag found look for end of field tag else return false if (desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) != -1) { //if we find a close tbl tag before the open tbl tag return false if ((desc.IndexOf("[/TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase)) < (desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase))) { return(false); } //if we find another tbl tag, return false else if (desc.IndexOf("[TBL]", desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) != -1) { return(false); } //else if we don't find a close tbl tag, return false else if (desc.IndexOf("[/TBL]", desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) == -1) { return(false); } //else if the length of the table tag is 0, return false else if (desc.IndexOf("[TBL]", startIndex, lengthOfTag) + 5 - desc.IndexOf("[/TBL]", desc.IndexOf("[TBL]") + 4, endIndex - desc.IndexOf("[TBL]", startIndex, lengthOfTag) + 4, StringComparison.InvariantCultureIgnoreCase) == 0) { return(false); } //validate table name int openTbl = desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase); int closeTbl = desc.IndexOf("[/TBL]", desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[TBL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase); string table = desc.Substring(openTbl + 5, closeTbl - openTbl - 5); //if the table isn't the currently mapped table, return false if (!table.Trim().Equals(currentMapping.tableName)) { return(false); } } else { return(false); } //if start of column tag is found, look for end of field tag else return false if (desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) != -1) { //if we find a close col tag before the open col tag return false if ((desc.IndexOf("[/COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase)) < (desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase))) { return(false); } //if we find another col tag, return false if (desc.IndexOf("[COL]", desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) != -1) { return(false); } //else if we don't find a close col tag, return false else if (desc.IndexOf("[/COL]", desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) == -1) { return(false); } //else if the length of the column tag is 0, return false else if (desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 5 - desc.IndexOf("[/COL]", desc.IndexOf("[COL]", StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase) == 0) { return(false); } //validate column name int openCol = desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase); int closeCol = desc.IndexOf("[/COL]", desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, endIndex - desc.IndexOf("[COL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 4, StringComparison.InvariantCultureIgnoreCase); string column = desc.Substring(openCol + 5, closeCol - openCol - 5); Database db = new Database(currentConnInfo); string query = "SELECT " + column.Trim() + " FROM " + currentMapping.tableName; try { db.executeQueryRemote(query); } catch (ODBC2KMLException) { return(false); } } else { return(false); } startIndex = endIndex + 8; } //validate url tags startIndex = 0; endIndex = 0; lengthOfTag = 0; //if start of url tag is found while (desc.IndexOf("[URL]", startIndex, StringComparison.InvariantCultureIgnoreCase) != -1) { //if end of url tag is found, set startindex, else return false if (desc.IndexOf("[/URL]", StringComparison.InvariantCultureIgnoreCase) != -1) { //get the index of end of tag and calculate lengthoftag endIndex = desc.IndexOf("[/URL]", startIndex, StringComparison.InvariantCultureIgnoreCase); lengthOfTag = endIndex - startIndex; } else { return(false); } //if start of title tag found look for end of title tag else return false if (desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) != -1) { //if we find another title tag, return false if (desc.IndexOf("[TITLE]", desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 7, endIndex - desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) - 7, StringComparison.InvariantCultureIgnoreCase) != -1) { return(false); } //else if we don't find a close title tag, return false else if (desc.IndexOf("[/TITLE]", desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 7, endIndex - desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) - 7, StringComparison.InvariantCultureIgnoreCase) == -1) { return(false); } //else if the length of the title tag is 0, return false else if (desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 8 - desc.IndexOf("[/TITLE]", desc.IndexOf("[TITLE]", StringComparison.InvariantCultureIgnoreCase) + 7, endIndex - desc.IndexOf("[TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) - 7, StringComparison.InvariantCultureIgnoreCase) == 0) { return(false); } } else { return(false); } //if length of url is 0 return false if (desc.IndexOf("[/TITLE]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 8 - desc.IndexOf("[/URL]", desc.IndexOf("[URL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) + 5, endIndex - desc.IndexOf("[URL]", startIndex, lengthOfTag, StringComparison.InvariantCultureIgnoreCase) - 5, StringComparison.InvariantCultureIgnoreCase) == 0) { return(false); } startIndex = endIndex + 6; } return(true); }
protected void executeQuery(object sender, EventArgs e) { //try //{ Database db; DataTable dt; Label title = new Label(); if (connectionSelector.SelectedItem.Text == "local") { try { db = new Database(); dt = db.executeQueryLocal(queryString.Text); } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); eh.displayError(); return; } } else { ConnInfo info = new ConnInfo(); try { db = new Database(); string query = "SELECT * FROM Connection WHERE ID=" + connectionSelector.SelectedItem.Value; dt = db.executeQueryLocal(query); if (dt.HasErrors) { throw new ODBC2KMLException("Unknown Database error"); } } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); eh.displayError(); return; } try { info = ConnInfo.getConnInfo(int.Parse(connectionSelector.SelectedItem.Value)); } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); eh.displayError(); return; } db.setConnInfo(info); try { dt = db.executeQueryRemote(queryString.Text); } catch (ODBC2KMLException ex) { ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1); eh.displayError(); return; } } resultsPanel.Visible = true; resultsPanel.Controls.Add(new LiteralControl("<span class=\"connectionStyle\"> Database Query Results</span>")); resultsPanel.Controls.Add(new LiteralControl("<div class=\"mainBoxP\">")); resultsPanel.Controls.Add(new LiteralControl("<table cellpadding=\"5\" cellspacing=\"0\" class=\"mainBox2\">")); resultsPanel.Controls.Add(new LiteralControl("<tr><td>")); resultsPanel.Controls.Add(new LiteralControl("<div class=\"omainBox4\">")); resultsPanel.Controls.Add(new LiteralControl("<table class=\"omainBox5\" cellspacing=\"0\" cellpadding=\"0\">")); resultsPanel.Controls.Add(new LiteralControl("<tr>")); foreach (DataColumn dc in dt.Columns) { resultsPanel.Controls.Add(new LiteralControl("<td><b>" + dc.ColumnName + "<br/></b></td>")); } resultsPanel.Controls.Add(new LiteralControl("</tr><tr><td><br/></td></tr>")); foreach (DataRow dr in dt.Rows) { resultsPanel.Controls.Add(new LiteralControl("<tr>")); foreach (Object data in dr.ItemArray) { resultsPanel.Controls.Add(new LiteralControl("<td>" + data.ToString() + "</td>")); } resultsPanel.Controls.Add(new LiteralControl("</tr>")); } resultsPanel.Controls.Add(new LiteralControl("</table>")); resultsPanel.Controls.Add(new LiteralControl("<div align=\"right\" style=\"padding-top: 20px;\">")); resultsPanel.Controls.Add(new LiteralControl("<input type=\"submit\" ID=\"hideResults\" value=\"Hide Results\" class=\"button\" />")); resultsPanel.Controls.Add(new LiteralControl("</div>")); resultsPanel.Controls.Add(new LiteralControl("</td></tr>")); resultsPanel.Controls.Add(new LiteralControl("</div>")); resultsPanel.Controls.Add(new LiteralControl("</span>")); ModalPopupExtender6.Show(); //} /*catch(Exception exception) * { * errorPanel1.Visible = true; * errorPanel1.Controls.Add(new LiteralControl("<div style=\"color: black\"><p>"+exception.Message+"</p></div>")); * errorPanel1.Controls.Add(new LiteralControl("<script type=\"text/javascript\">$(\"#errorPanel1\").dialog('open')</script>")); * }*/ }