public void setIDfromDBoverlay(int connID, int overlayID) { Database DB = new Database(); DataTable id = new DataTable(); try { id = DB.executeQueryLocal("SELECT ID FROM OverlayCondition WHERE connID=" + connID + " and overlayID=" + overlayID + " " + "and lowerBound=\'" + this.lowerBound + "\' " + "and upperBound=\'" + this.upperBound + "\' " + "and lowerOperator=\'" + this.lowerOperator + "\' " + "and upperOperator=\'" + this.upperOperator + "\' " + "and fieldName=\'" + this.fieldName + "\' " + "and tableName=\'" + this.tableName + "\'"); } catch (ODBC2KMLException ex) { throw new ODBC2KMLException(ex.errorText); } foreach (DataRow row in id.Rows) { this.id = (int)row[0]; } }
public static Description getDescription(int connID) { //Basic Constructs Description description = new Description(); Database localDatabase = new Database(); DataTable table = null; description.setDesc(""); //Create description query and populate table string query = "SELECT * FROM Description WHERE connID=" + connID; try { table = localDatabase.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "Error retreiving description from the local database"; throw ex; } foreach (DataRow row in table.Rows) { description.setDesc(row["description"].ToString()); }//End outer loop return description; }
/// <summary> /// Get all of the mapping information associated with a given connection. /// </summary> /// <param name="connID">int --> connection ID</param> /// <returns>Mapping --> Populated Map Object</returns> public static Mapping getMapping(int connID) { Database localDatabase = new Database(); DataTable table = null; //Create mapping query and populate table string query = "SELECT * FROM Mapping WHERE connID=" + connID; try { table = localDatabase.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "Error selecting the mapping from the database"; throw ex; } //Mapping to return Mapping map = new Mapping(); foreach (DataRow row in table.Rows) { //Set mapping map.setTableName(row["tableName"].ToString()); map.setLatFieldName(row["latFieldName"].ToString()); map.setLongFieldName(row["longFieldName"].ToString()); map.setPlacemarkFieldName(row["placemarkFieldName"].ToString()); map.setFormat((int)row["format"]); }//End outer loop //Return populated map object return(map); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Database db = new Database(); DataTable dt; try { dt = db.executeQueryLocal("SELECT id,name FROM CONNECTION"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error getting the list of connections", errorPanel1); eh.displayError(); return; } connectionSelector.DataSource = dt; connectionSelector.DataTextField = "name"; connectionSelector.DataValueField = "id"; connectionSelector.DataBind(); connectionSelector.Items.Add("local"); } if (Request.QueryString.Get("locked") == "1") { ChangeControlStatus(false); } }
public static Description getDescription(int connID) { //Basic Constructs Description description = new Description(); Database localDatabase = new Database(); DataTable table = null; description.setDesc(""); //Create description query and populate table string query = "SELECT * FROM Description WHERE connID=" + connID; try { table = localDatabase.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "Error retreiving description from the local database"; throw ex; } foreach (DataRow row in table.Rows) { description.setDesc(row["description"].ToString()); }//End outer loop return(description); }
/// <summary> /// Validates the connection name. Only use is to ensure the name is not /// an empty string. /// </summary> /// <param name="name">String --> Connection string to validate</param> /// <returns>Boolean --> true -> Valid, False -> Invalid</returns> public static Boolean validConnName(String name, int connID) { //Empty connection name if (name.Equals("")) { return(false); } //Create database and execute query Database DB = new Database(); String query = "SELECT * FROM Connection WHERE name='" + name + "' AND ID!=" + connID; DataTable DT = null; try { DT = DB.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "Error getting connection information"; throw ex; } //If the row number isn't 0, return false if (DT.Rows.Count > 0) { return(false); } //Valid name return(true); }
/// <summary> /// This function purges all of the invalid conditions for the given Database information from the local database. /// </summary> /// <param name="purgeDT">DataTable --> List of tablename</param> /// <param name="columnToTableRelation">DataSet --> List of columns for each table name</param> /// <param name="temp">Database --> A temp database used to remove the icon conditions from local database</param> /// <returns>Boolean --> True if purge, false if no purge</returns> public String purgeInvalidIconConditionsFromDatabase(DataTable purgeDT, DataSet columnToTableRelation, Database temp) { String query = ""; //Get the icon's conditions for (int count = 0; count < this.getConditions().Count; count++) { //If the condition is invalid, remove it from the database and connection object if (!(((Condition)this.getConditions()[count]).isValid(purgeDT, columnToTableRelation))) { query = "DELETE FROM IconCondition WHERE ID=" + ((Condition)this.getConditions()[count]).getId(); try { temp.executeQueryLocal(query); this.removeCondition(count); count--; } catch (ODBC2KMLException ex) { ex.errorText = "There was an error deleting an icon condition"; throw ex; } } } return(query); }
protected void confirmEdit(object sender, EventArgs e) { ImageButton sendBtn = (ImageButton)sender; String args = sendBtn.CommandArgument.ToString(); Database dbCheck = new Database(); DataTable dtCheck; try { dtCheck = dbCheck.executeQueryLocal("SELECT name,dbName,userName,password,port,address,type,protocol,serviceName,SID FROM Connection WHERE ID=\'" + args + "\'"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error retreiving connection information for connection " + args + ".", errorPanel1); eh.displayError(); return; } foreach (DataRow dr in dtCheck.Rows) { editConnName.Text = dr[0].ToString(); editConnDBName.Text = dr[1].ToString(); editConnUser.Text = dr[2].ToString(); editConnPass.Attributes.Add("value", dr[3].ToString()); editConnDBPort.Text = dr[4].ToString(); editConnDBAddr.Text = dr[5].ToString(); if (Convert.ToInt32(dr[6].ToString()) == ConnInfo.MYSQL) { editConnDBType.SelectedValue = "MySQL"; } else if (Convert.ToInt32(dr[6].ToString()) == ConnInfo.MSSQL) { editConnDBType.SelectedValue = "MSSQL"; } else { editConnDBType.SelectedValue = "Oracle"; } editConnDBType.Text = dr[6].ToString(); editOracleProtocol.Text = dr[7].ToString(); editOracleService.Text = dr[8].ToString(); editOracleSID.Text = dr[9].ToString(); } saveAndEditConn.CommandArgument = args; saveEditConn.CommandArgument = args; this.editConnModalPopUp.Show(); }
protected void viewIconLibFunc(object sender, EventArgs e) { Database db = new Database(); DataTable dt; dt = db.executeQueryLocal("SELECT ID, location FROM IconLibrary"); int sizeOfBox = 6; int currentBoxCount = 0; iconLibPanel.Controls.Clear(); iconLibPanel.Controls.Add(new LiteralControl("<table class=\"boxPopupStyle2\" cellpadding=\"5\">\n")); if (dt.Rows.Count == 0) { iconLibPanel.Controls.Add(new LiteralControl("<tr><td class=\"tableTD\">All icons in the icon library are currently being used in the connection.</td></tr>\n")); } else { foreach (DataRow dr in dt.Rows) { if (currentBoxCount == sizeOfBox) { iconLibPanel.Controls.Add(new LiteralControl("</tr>\n")); currentBoxCount = 0; } if (currentBoxCount == 0) { iconLibPanel.Controls.Add(new LiteralControl("<tr>\n")); } iconLibPanel.Controls.Add(new LiteralControl("<td>")); Image img = new Image(); img.ID = "imgLib_" + dr["ID"].ToString(); img.ImageUrl = dr["location"].ToString(); img.AlternateText = "Icon Cannot be Displayed"; img.ToolTip = dr["location"].ToString(); img.Height = 64; img.Width = 64; iconLibPanel.Controls.Add(img); iconLibPanel.Controls.Add(new LiteralControl("</td>")); currentBoxCount += 1; } } iconLibPanel.Controls.Add(new LiteralControl("</table>\n")); this.IconLibModalPopup.Show(); //iconLibPanel }
protected void editAndSaveConnectionInformation(object sender, CommandEventArgs e) { Button sendBtn = (Button)sender; String args = sendBtn.CommandArgument.ToString(); Database dbCheck = new Database(); DataTable dtCheck; DataRow dr; try { dtCheck = dbCheck.executeQueryLocal("SELECT name,dbName,userName,password,port,address,type,protocol,serviceName,SID FROM Connection WHERE ID=\'" + args + "\'"); dr = dtCheck.Rows[0]; } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error retreiving connection information for connection " + args + ".", errorPanel1); eh.displayError(); return; } 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 (editConnName.Text.Equals(dr["name"].ToString()) && editConnDBName.Text.Equals(dr["dbName"].ToString()) && editConnDBAddr.Text.Equals(dr["address"].ToString()) && editConnDBPort.Text.Equals(dr["port"].ToString()) && editConnUser.Text.Equals(dr["userName"].ToString()) && editConnPass.Text.Equals(dr["password"].ToString()) && editConnDBType.SelectedIndex.ToString().Equals(dr["type"].ToString()) && editOracleProtocol.Text.Equals(dr["protocol"].ToString()) && editOracleService.Text.Equals(dr["serviceName"].ToString()) && editOracleSID.Text.Equals(dr["SID"].ToString())) { updateConnection(sender, e); } else { this.editConnModalPopUp.Hide(); this.warningModal.Show(); continueUpdate.CommandName = e.CommandName.ToString(); continueUpdate.CommandArgument = args; } }
protected void confirmEdit(object sender, EventArgs e) { ImageButton sendBtn = (ImageButton)sender; String args = sendBtn.CommandArgument.ToString(); Database dbCheck = new Database(); DataTable dtCheck; try { dtCheck = dbCheck.executeQueryLocal("SELECT name,dbName,userName,password,port,address,type,protocol,serviceName,SID FROM Connection WHERE ID=\'" + args + "\'"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error retreiving connection information for connection " + args + ".", errorPanel1); eh.displayError(); return; } foreach (DataRow dr in dtCheck.Rows) { editConnName.Text = dr[0].ToString(); editConnDBName.Text = dr[1].ToString(); editConnUser.Text = dr[2].ToString(); editConnPass.Attributes.Add("value", dr[3].ToString()); editConnDBPort.Text = dr[4].ToString(); editConnDBAddr.Text = dr[5].ToString(); if (Convert.ToInt32(dr[6].ToString()) == ConnInfo.MYSQL) { editConnDBType.SelectedValue = "MySQL"; } else if (Convert.ToInt32(dr[6].ToString()) == ConnInfo.MSSQL) { editConnDBType.SelectedValue = "MSSQL"; } else { editConnDBType.SelectedValue = "Oracle"; } editConnDBType.Text = dr[6].ToString(); editOracleProtocol.Text = dr[7].ToString(); editOracleService.Text = dr[8].ToString(); editOracleSID.Text = dr[9].ToString(); } saveAndEditConn.CommandArgument = args; saveEditConn.CommandArgument = args; this.editConnModalPopUp.Show(); }
protected void deleteConnFunction(object sender, EventArgs e) { //Delete the connection Button sendBtn = (Button)sender; String args = sendBtn.CommandArgument.ToString(); Database db = new Database(); try { db.executeQueryLocal("DELETE FROM CONNECTION WHERE ID=" + args); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error deleting the connection", errorPanel1); eh.displayError(); return; } this.deletePopupExtender.Hide(); Response.Redirect("Main.aspx"); }
/// <summary> /// used for uploading icons from local computer /// </summary> /// <param name="sender"></param> /// <param name="e"></param> public static String uploadClick(String fileSaveLoc, String relativeFileSaveLoc, FileUpload fileUpEx) { ArrayList validTypes = BuildTypeList(); Boolean valid = false; //checks to make sure there is an uploaded file if ((fileUpEx.HasFile) && (!fileUpEx.FileName.Equals(""))) { //checks for valid filetype foreach (String type in validTypes) { if (fileUpEx.PostedFile.ContentType.Equals(type)) { valid = true; } } //checks for valid dimensions if (valid && ValidateFileDimensions(fileUpEx.PostedFile.InputStream)) { String filepath = fileUpEx.PostedFile.FileName; String file_ext = System.IO.Path.GetExtension(filepath); String filename = System.IO.Path.GetFileNameWithoutExtension(filepath); String suffix = GetRandomString(); String file = filename + suffix + file_ext; String relativeName = relativeFileSaveLoc + file; //save the file to the server try { fileUpEx.PostedFile.SaveAs(fileSaveLoc + file); } catch (DirectoryNotFoundException) { throw new ODBC2KMLException("Error saving file, please ensure " + fileSaveLoc + " exists on this machine"); } Database DB = new Database(); try { DB.executeQueryLocal("INSERT INTO IconLibrary (location, isLocal) VALUES (\'" + relativeName + "\', 1)"); } catch (ODBC2KMLException ex) { throw new ODBC2KMLException(ex.errorText); } return relativeName; } else if (!valid) { String errorText = "Current File type = " + fileUpEx.PostedFile.ContentType + " File type not appropriate (only jpg, gif, tiff, png, bmp accepted)"; throw new ODBC2KMLException(errorText); } else { throw new ODBC2KMLException("File dimensions too large (max 128 x 128)"); } } else { throw new ODBC2KMLException("Please select a file to upload."); } }
/// <summary> /// used for uploading icons from remote sources /// if fetch is checked this function downloads the linked icon and saves its info to the db and saves the icon /// if fetch is not checked it just saves the linked icon's info to the db /// </summary> /// <param name="sender"></param> /// <param name="e"></param> public static String URLsubmitClick(bool fetch, String URL, String fileSaveLoc, String relativeFileSaveLoc) { ArrayList validTypes = BuildTypeList2(); Database DB = new Database(); WebClient Client = new WebClient(); //Create a request for the URL. if (URL.Equals("")) throw new ODBC2KMLException("Please enter a URL."); DataTable dt = DB.executeQueryLocal("SELECT * FROM IconLibrary WHERE location='" + URL + "'"); if (dt.Rows.Count > 0) throw new ODBC2KMLException("URL Path already exists in Icon Library."); WebRequest request = WebRequest.Create(URL); request.Proxy = null; try { //Get the response. HttpWebResponse response = (HttpWebResponse)request.GetResponse(); } catch (Exception) { throw new ODBC2KMLException("Cannot connect to the URL you entered"); } //below lines get information to check validity of icon and saves the icon temporarily String fileName = System.IO.Path.GetFileNameWithoutExtension(URL); String ext = System.IO.Path.GetExtension(URL); String suffix = GetRandomString(); String name = fileSaveLoc + fileName + suffix + ext; //checks to see if fileType of icon is valid bool valid = false; foreach (String type in validTypes) { if (ext.Equals(type)) { valid = true; break; } } if (valid) { try { Client.DownloadFile(URL, name); } catch (WebException) { throw new ODBC2KMLException("Error with temporary download, please ensure " + fileSaveLoc + " exists on this machine"); } /*catch (ArgumentException) { throw new ODBC2KMLException("No URL entered"); }*/ FileStream fs = File.OpenRead(name); bool validDim = ValidateFileDimensions(fs); fs.Close(); if (fetch) { String relativeName = relativeFileSaveLoc + fileName + suffix + ext; //checks if icon has valid dimensions if (validDim) { try { DB.executeQueryLocal("INSERT INTO IconLibrary (location, isLocal) VALUES (\'" + relativeName + "\', 1)"); } catch (ODBC2KMLException ex) { throw new ODBC2KMLException(ex.errorText); } } else { File.Delete(name); throw new ODBC2KMLException("The file you linked to was to large (max 128 x 128)"); } return relativeName; } else { //checks if icon has valid dimensions File.Delete(name); if (validDim) { try { DB.executeQueryLocal("INSERT INTO IconLibrary (location, isLocal) VALUES (\'" + URL + "\', 0)"); } catch (ODBC2KMLException ex) { throw new ODBC2KMLException(ex.errorText); } } else { throw new ODBC2KMLException("The file you linked to was to large (max 128 x 128)"); } return URL; } } else if (!valid) { throw new ODBC2KMLException("You linked to an invalid file type"); } return ""; }
/// <summary> /// used for uploading icons from local computer /// </summary> /// <param name="sender"></param> /// <param name="e"></param> public static String uploadClick(String fileSaveLoc, String relativeFileSaveLoc, FileUpload fileUpEx) { ArrayList validTypes = BuildTypeList(); Boolean valid = false; //checks to make sure there is an uploaded file if ((fileUpEx.HasFile) && (!fileUpEx.FileName.Equals(""))) { //checks for valid filetype foreach (String type in validTypes) { if (fileUpEx.PostedFile.ContentType.Equals(type)) { valid = true; } } //checks for valid dimensions if (valid && ValidateFileDimensions(fileUpEx.PostedFile.InputStream)) { String filepath = fileUpEx.PostedFile.FileName; String file_ext = System.IO.Path.GetExtension(filepath); String filename = System.IO.Path.GetFileNameWithoutExtension(filepath); String suffix = GetRandomString(); String file = filename + suffix + file_ext; String relativeName = relativeFileSaveLoc + file; //save the file to the server try { fileUpEx.PostedFile.SaveAs(fileSaveLoc + file); } catch (DirectoryNotFoundException) { throw new ODBC2KMLException("Error saving file, please ensure " + fileSaveLoc + " exists on this machine"); } Database DB = new Database(); try { DB.executeQueryLocal("INSERT INTO IconLibrary (location, isLocal) VALUES (\'" + relativeName + "\', 1)"); } catch (ODBC2KMLException ex) { throw new ODBC2KMLException(ex.errorText); } return(relativeName); } else if (!valid) { String errorText = "Current File type = " + fileUpEx.PostedFile.ContentType + " File type not appropriate (only jpg, gif, tiff, png, bmp accepted)"; throw new ODBC2KMLException(errorText); } else { throw new ODBC2KMLException("File dimensions too large (max 128 x 128)"); } } else { throw new ODBC2KMLException("Please select a file to upload."); } }
/// <summary> /// used for uploading icons from remote sources /// if fetch is checked this function downloads the linked icon and saves its info to the db and saves the icon /// if fetch is not checked it just saves the linked icon's info to the db /// </summary> /// <param name="sender"></param> /// <param name="e"></param> public static String URLsubmitClick(bool fetch, String URL, String fileSaveLoc, String relativeFileSaveLoc) { ArrayList validTypes = BuildTypeList2(); Database DB = new Database(); WebClient Client = new WebClient(); //Create a request for the URL. if (URL.Equals("")) { throw new ODBC2KMLException("Please enter a URL."); } DataTable dt = DB.executeQueryLocal("SELECT * FROM IconLibrary WHERE location='" + URL + "'"); if (dt.Rows.Count > 0) { throw new ODBC2KMLException("URL Path already exists in Icon Library."); } WebRequest request = WebRequest.Create(URL); request.Proxy = null; try { //Get the response. HttpWebResponse response = (HttpWebResponse)request.GetResponse(); } catch (Exception) { throw new ODBC2KMLException("Cannot connect to the URL you entered"); } //below lines get information to check validity of icon and saves the icon temporarily String fileName = System.IO.Path.GetFileNameWithoutExtension(URL); String ext = System.IO.Path.GetExtension(URL); String suffix = GetRandomString(); String name = fileSaveLoc + fileName + suffix + ext; //checks to see if fileType of icon is valid bool valid = false; foreach (String type in validTypes) { if (ext.Equals(type)) { valid = true; break; } } if (valid) { try { Client.DownloadFile(URL, name); } catch (WebException) { throw new ODBC2KMLException("Error with temporary download, please ensure " + fileSaveLoc + " exists on this machine"); } /*catch (ArgumentException) * { * throw new ODBC2KMLException("No URL entered"); * }*/ FileStream fs = File.OpenRead(name); bool validDim = ValidateFileDimensions(fs); fs.Close(); if (fetch) { String relativeName = relativeFileSaveLoc + fileName + suffix + ext; //checks if icon has valid dimensions if (validDim) { try { DB.executeQueryLocal("INSERT INTO IconLibrary (location, isLocal) VALUES (\'" + relativeName + "\', 1)"); } catch (ODBC2KMLException ex) { throw new ODBC2KMLException(ex.errorText); } } else { File.Delete(name); throw new ODBC2KMLException("The file you linked to was to large (max 128 x 128)"); } return(relativeName); } else { //checks if icon has valid dimensions File.Delete(name); if (validDim) { try { DB.executeQueryLocal("INSERT INTO IconLibrary (location, isLocal) VALUES (\'" + URL + "\', 0)"); } catch (ODBC2KMLException ex) { throw new ODBC2KMLException(ex.errorText); } } else { throw new ODBC2KMLException("The file you linked to was to large (max 128 x 128)"); } return(URL); } } else if (!valid) { throw new ODBC2KMLException("You linked to an invalid file type"); } return(""); }
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 editAndSaveConnectionInformation(object sender, CommandEventArgs e) { Button sendBtn = (Button)sender; String args = sendBtn.CommandArgument.ToString(); Database dbCheck = new Database(); DataTable dtCheck; DataRow dr; try { dtCheck = dbCheck.executeQueryLocal("SELECT name,dbName,userName,password,port,address,type,protocol,serviceName,SID FROM Connection WHERE ID=\'" + args + "\'"); dr = dtCheck.Rows[0]; } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error retreiving connection information for connection " + args + ".", errorPanel1); eh.displayError(); return; } 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 (editConnName.Text.Equals(dr["name"].ToString()) && editConnDBName.Text.Equals(dr["dbName"].ToString()) && editConnDBAddr.Text.Equals(dr["address"].ToString()) && editConnDBPort.Text.Equals(dr["port"].ToString()) && editConnUser.Text.Equals(dr["userName"].ToString()) && editConnPass.Text.Equals(dr["password"].ToString()) && editConnDBType.SelectedIndex.ToString().Equals(dr["type"].ToString()) && editOracleProtocol.Text.Equals(dr["protocol"].ToString()) && editOracleService.Text.Equals(dr["serviceName"].ToString()) && editOracleSID.Text.Equals(dr["SID"].ToString())) { updateConnection(sender, e); }else{ this.editConnModalPopUp.Hide(); this.warningModal.Show(); continueUpdate.CommandName = e.CommandName.ToString(); continueUpdate.CommandArgument = args; } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Database db = new Database(); DataTable dt; try { dt = db.executeQueryLocal("SELECT id,name FROM CONNECTION"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error getting the list of connections", errorPanel1); eh.displayError(); return; } connectionSelector.DataSource = dt; connectionSelector.DataTextField = "name"; connectionSelector.DataValueField = "id"; connectionSelector.DataBind(); connectionSelector.Items.Add("local"); } if (Request.QueryString.Get("locked") == "1") { ChangeControlStatus(false); } }
/// <summary> /// Validates the connection name. Only use is to ensure the name is not /// an empty string. /// </summary> /// <param name="name">String --> Connection string to validate</param> /// <returns>Boolean --> true -> Valid, False -> Invalid</returns> public static Boolean validConnName(String name, int connID) { //Empty connection name if(name.Equals("")) { return false; } //Create database and execute query Database DB = new Database(); String query = "SELECT * FROM Connection WHERE name='" + name + "' AND ID!=" + connID; DataTable DT = null; try { DT = DB.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "Error getting connection information"; throw ex; } //If the row number isn't 0, return false if (DT.Rows.Count > 0) return false; //Valid name return true; }
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"); } }
public static ArrayList getIcons(int connID) { ArrayList icons = new ArrayList(); Database localDatabase = new Database(); //Create icon query and populate table string query = "SELECT * FROM Icon WHERE connID=" + connID + " ORDER BY ID"; DataTable table = null; try { table = localDatabase.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "There was an error getting icons for the connection"; throw ex; } foreach (DataRow row in table.Rows) { //Create a new icon Icon newIcon = new Icon(); //Create a new table to perform subqueries on DataTable newTable = new DataTable(); //IconLibrary query string locQuery = "SELECT * FROM IconLibrary WHERE ID=" + ((int)row["iconLibraryID"]) + " ORDER BY ID"; try { newTable = localDatabase.executeQueryLocal(locQuery); } catch (ODBC2KMLException ex) { ex.errorText = "There was an error populating the Icon Library"; throw ex; } foreach (DataRow nRow in newTable.Rows) { //Set the location of the icon newIcon.setLocation(nRow["location"].ToString()); newIcon.setId(nRow["ID"].ToString()); if ((Boolean)nRow["isLocal"] == false) { newIcon.setLocality(false); } else { newIcon.setLocality(true); } }//End outer loop newTable.Clear(); //IconCondition query string conQuery = "SELECT * FROM IconCondition WHERE iconID=" + ((int)row["ID"]) + " AND connID=" + connID; try { newTable = localDatabase.executeQueryLocal(conQuery); } catch (ODBC2KMLException ex) { ex.errorText = "There was a problem selecting icon conditions for icon " + (int)row["iconLibraryID"]; throw ex; } //Cycle through each condition foreach (DataRow nRow in newTable.Rows) { //Create the condition and add its values Condition condition = new Condition(); if (nRow["lowerBound"] != null) { condition.setLowerBound(nRow["lowerBound"].ToString()); } else { condition.setLowerBound(""); } if (nRow["upperBound"] != null) { condition.setUpperBound(nRow["upperBound"].ToString()); } else { condition.setUpperBound(""); } condition.setLowerOperator((int)nRow["lowerOperator"]); condition.setUpperOperator((int)nRow["upperOperator"]); condition.setTableName(nRow["tableName"].ToString()); condition.setFieldName(nRow["fieldName"].ToString()); condition.setId(Convert.ToInt16(nRow["ID"].ToString())); //Add the condition to the icon array newIcon.setConditions(condition); //Free up condition memory condition = null; }//End outer loop //Free up table memory newTable = null; icons.Add(newIcon); //Free up icon memory newIcon = null; }//End outer loop return(icons); }
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 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> /// This function purges all of the invalid conditions for the given Database information from the local database. /// </summary> /// <param name="purgeDT">DataTable --> List of tablename</param> /// <param name="columnToTableRelation">DataSet --> List of columns for each table name</param> /// <param name="temp">Database --> A temp database used to remove the icon conditions from local database</param> /// <returns>Boolean --> True if purge, false if no purge</returns> public String purgeInvalidIconConditionsFromDatabase(DataTable purgeDT, DataSet columnToTableRelation, Database temp) { String query = ""; //Get the icon's conditions for (int count = 0; count < this.getConditions().Count; count++) { //If the condition is invalid, remove it from the database and connection object if (!(((Condition)this.getConditions()[count]).isValid(purgeDT, columnToTableRelation))) { query = "DELETE FROM IconCondition WHERE ID=" + ((Condition)this.getConditions()[count]).getId(); try { temp.executeQueryLocal(query); this.removeCondition(count); count--; } catch (ODBC2KMLException ex) { ex.errorText = "There was an error deleting an icon condition"; throw ex; } } } return query; }
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"); } }
public static ArrayList getIcons(int connID) { ArrayList icons = new ArrayList(); Database localDatabase = new Database(); //Create icon query and populate table string query = "SELECT * FROM Icon WHERE connID=" + connID + " ORDER BY ID"; DataTable table = null; try { table = localDatabase.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "There was an error getting icons for the connection"; throw ex; } foreach (DataRow row in table.Rows) { //Create a new icon Icon newIcon = new Icon(); //Create a new table to perform subqueries on DataTable newTable = new DataTable(); //IconLibrary query string locQuery = "SELECT * FROM IconLibrary WHERE ID=" + ((int)row["iconLibraryID"]) + " ORDER BY ID"; try { newTable = localDatabase.executeQueryLocal(locQuery); } catch (ODBC2KMLException ex) { ex.errorText = "There was an error populating the Icon Library"; throw ex; } foreach (DataRow nRow in newTable.Rows) { //Set the location of the icon newIcon.setLocation(nRow["location"].ToString()); newIcon.setId(nRow["ID"].ToString()); if ((Boolean)nRow["isLocal"] == false) { newIcon.setLocality(false); } else { newIcon.setLocality(true); } }//End outer loop newTable.Clear(); //IconCondition query string conQuery = "SELECT * FROM IconCondition WHERE iconID=" + ((int)row["ID"]) + " AND connID=" + connID; try { newTable = localDatabase.executeQueryLocal(conQuery); } catch (ODBC2KMLException ex) { ex.errorText = "There was a problem selecting icon conditions for icon " + (int)row["iconLibraryID"]; throw ex; } //Cycle through each condition foreach (DataRow nRow in newTable.Rows) { //Create the condition and add its values Condition condition = new Condition(); if (nRow["lowerBound"] != null) { condition.setLowerBound(nRow["lowerBound"].ToString()); } else { condition.setLowerBound(""); } if (nRow["upperBound"] != null) { condition.setUpperBound(nRow["upperBound"].ToString()); } else { condition.setUpperBound(""); } condition.setLowerOperator((int)nRow["lowerOperator"]); condition.setUpperOperator((int)nRow["upperOperator"]); condition.setTableName(nRow["tableName"].ToString()); condition.setFieldName(nRow["fieldName"].ToString()); condition.setId(Convert.ToInt16(nRow["ID"].ToString())); //Add the condition to the icon array newIcon.setConditions(condition); //Free up condition memory condition = null; }//End outer loop //Free up table memory newTable = null; icons.Add(newIcon); //Free up icon memory newIcon = null; }//End outer loop return icons; }
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>")); }
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 Page_Load(object sender, EventArgs e) { fileSaveLoc = Server.MapPath("/icons/"); //Get the DB stuff from here Database db = new Database(); DataTable dt; try { dt = db.executeQueryLocal("SELECT id,name FROM CONNECTION"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error getting the current connections", errorPanel1); eh.displayError(); return; } int i = 0; if (dt.Rows.Count == 0) { ConnectionsAvailable.Controls.Add(new LiteralControl("<tr><td class=\"tableTD\">No connections currently exist.</td></tr>\n")); } else { foreach (DataRow dr in dt.Rows) { string dbID = dr.ItemArray.ElementAt(0).ToString(); string odbcName = dr.ItemArray.ElementAt(1).ToString(); //Defines buttons ImageButton openConn = new ImageButton(); openConn.CssClass = "openIcon"; openConn.ImageUrl = "graphics/connIcon.gif"; openConn.AlternateText = "Open Connection"; openConn.ToolTip = "Open Connection"; openConn.PostBackUrl = "ConnDetails.aspx?ConnID=" + dbID + "&locked=true"; ImageButton editConn = new ImageButton(); editConn.CssClass = "editIcon"; editConn.ImageUrl = "graphics/connIcon.gif"; editConn.AlternateText = "Edit Connection"; editConn.ToolTip = "Edit Connection"; editConn.Click += new ImageClickEventHandler(confirmEdit); editConn.CommandArgument = dbID; ImageButton deleteConn = new ImageButton(); deleteConn.ID = "dc" + Convert.ToString(i); deleteConn.CssClass = "deleteIcon"; deleteConn.ImageUrl = "graphics/connIcon.gif"; deleteConn.AlternateText = "Delete Connection"; deleteConn.ToolTip = "Delete Connection"; deleteConn.Click += new ImageClickEventHandler(confirmDelete); deleteConn.CommandArgument = dbID; deleteConn.CommandArgument += "#" + odbcName; ImageButton genKML = new ImageButton(); genKML.CssClass = "kmlIcon"; genKML.ImageUrl = "graphics/connIcon.gif"; genKML.AlternateText = "Generate KML File"; genKML.ToolTip = "Generate KML File"; genKML.Click += new ImageClickEventHandler(genKMLFunction); genKML.CommandArgument = dbID; //End button definition if (i % 2.00 == 0) { ConnectionsAvailable.Controls.Add(new LiteralControl("<tr class=\"oddConn\">\n")); } else { ConnectionsAvailable.Controls.Add(new LiteralControl("<tr class=\"evenConn\">\n")); } ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<span id=\"Conn" + dbID + "\">" + odbcName + "</span>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<a href=\"#\" title=\"Open Connection\"></a>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td class=\"connIcons\">\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<table>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<tr>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(openConn); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(editConn); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(deleteConn); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(genKML); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</tr>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</table>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</tr>\n")); i += 1; } } }
public static ConnInfo getConnInfo(int connID) { ConnInfo connInfo = new ConnInfo(); Database localDatabase = new Database(); //Construct the connInfo query and retrieve the DataTable string query = "SELECT * FROM Connection WHERE ID=" + connID + " ORDER BY ID"; DataTable table = null; try { table = localDatabase.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "Error getting connection information for the connection"; throw ex; } //Cycle through each row and column foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) { //Set all connInfo switch (col.ColumnName) { case "name": connInfo.setConnectionName(row[col].ToString()); break; case "dbName": connInfo.setDatabaseName(row[col].ToString()); break; case "userName": connInfo.setUserName(row[col].ToString()); break; case "password": connInfo.setPassword(row[col].ToString()); break; case "port": connInfo.setPortNumber(row[col].ToString()); break; case "address": connInfo.setServerAddress(row[col].ToString()); break; case "type": connInfo.setDatabaseType((int)row[col]); break; case "protocol": connInfo.setOracleProtocol(row[col].ToString()); break; case "serviceName": connInfo.setOracleServiceName(row[col].ToString()); break; case "SID": connInfo.setOracleSID(row[col].ToString()); break; default: break; } } }//End outer loop return connInfo; }
public void setIDfromDBoverlay(int connID, int overlayID) { Database DB = new Database(); DataTable id = new DataTable(); try { id = DB.executeQueryLocal("SELECT ID FROM OverlayCondition WHERE connID=" + connID + " and overlayID=" + overlayID + " " + "and lowerBound=\'" + this.lowerBound + "\' " + "and upperBound=\'" + this.upperBound + "\' " + "and lowerOperator=\'" + this.lowerOperator + "\' " + "and upperOperator=\'" + this.upperOperator + "\' " + "and fieldName=\'" + this.fieldName + "\' " + "and tableName=\'" + this.tableName + "\'"); } catch (ODBC2KMLException ex) { throw new ODBC2KMLException(ex.errorText); } foreach (DataRow row in id.Rows) { this.id = (int)row[0]; } }
protected void deleteConnFunction(object sender, EventArgs e) { //Delete the connection Button sendBtn = (Button)sender; String args = sendBtn.CommandArgument.ToString(); Database db = new Database(); try { db.executeQueryLocal("DELETE FROM CONNECTION WHERE ID=" + args); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error deleting the connection", errorPanel1); eh.displayError(); return; } this.deletePopupExtender.Hide(); Response.Redirect("Main.aspx"); }
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>")); }
protected void Page_Load(object sender, EventArgs e) { fileSaveLoc = Server.MapPath("/icons/"); //Get the DB stuff from here Database db = new Database(); DataTable dt; try { dt = db.executeQueryLocal("SELECT id,name FROM CONNECTION"); } catch (ODBC2KMLException) { ErrorHandler eh = new ErrorHandler("There was an error getting the current connections", errorPanel1); eh.displayError(); return; } int i = 0; if (dt.Rows.Count == 0) { ConnectionsAvailable.Controls.Add(new LiteralControl("<tr><td class=\"tableTD\">No connections currently exist.</td></tr>\n")); } else { foreach (DataRow dr in dt.Rows) { string dbID = dr.ItemArray.ElementAt(0).ToString(); string odbcName = dr.ItemArray.ElementAt(1).ToString(); //Defines buttons ImageButton openConn = new ImageButton(); openConn.CssClass = "openIcon"; openConn.ImageUrl = "graphics/connIcon.gif"; openConn.AlternateText = "Open Connection"; openConn.ToolTip = "Open Connection"; openConn.PostBackUrl = "ConnDetails.aspx?ConnID=" + dbID + "&locked=true"; ImageButton editConn = new ImageButton(); editConn.CssClass = "editIcon"; editConn.ImageUrl = "graphics/connIcon.gif"; editConn.AlternateText = "Edit Connection"; editConn.ToolTip = "Edit Connection"; editConn.Click += new ImageClickEventHandler(confirmEdit); editConn.CommandArgument = dbID; ImageButton deleteConn = new ImageButton(); deleteConn.ID = "dc" + Convert.ToString(i); deleteConn.CssClass = "deleteIcon"; deleteConn.ImageUrl = "graphics/connIcon.gif"; deleteConn.AlternateText = "Delete Connection"; deleteConn.ToolTip = "Delete Connection"; deleteConn.Click += new ImageClickEventHandler(confirmDelete); deleteConn.CommandArgument = dbID; deleteConn.CommandArgument += "#" + odbcName; ImageButton genKML = new ImageButton(); genKML.CssClass = "kmlIcon"; genKML.ImageUrl = "graphics/connIcon.gif"; genKML.AlternateText = "Generate KML File"; genKML.ToolTip = "Generate KML File"; genKML.Click += new ImageClickEventHandler(genKMLFunction); genKML.CommandArgument = dbID; //End button definition if (i % 2.00 == 0) { ConnectionsAvailable.Controls.Add(new LiteralControl("<tr class=\"oddConn\">\n")); } else { ConnectionsAvailable.Controls.Add(new LiteralControl("<tr class=\"evenConn\">\n")); } ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<span id=\"Conn" + dbID + "\">" + odbcName + "</span>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<a href=\"#\" title=\"Open Connection\"></a>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td class=\"connIcons\">\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<table>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<tr>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(openConn); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(editConn); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(deleteConn); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("<td>\n")); ConnectionsAvailable.Controls.Add(genKML); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</tr>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</table>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</td>\n")); ConnectionsAvailable.Controls.Add(new LiteralControl("</tr>\n")); i += 1; } } }
protected void viewIconLibFunc(object sender, EventArgs e) { Database db = new Database(); DataTable dt; dt = db.executeQueryLocal("SELECT ID, location FROM IconLibrary"); int sizeOfBox = 6; int currentBoxCount = 0; iconLibPanel.Controls.Clear(); iconLibPanel.Controls.Add(new LiteralControl("<table class=\"boxPopupStyle2\" cellpadding=\"5\">\n")); if (dt.Rows.Count == 0) { iconLibPanel.Controls.Add(new LiteralControl("<tr><td class=\"tableTD\">All icons in the icon library are currently being used in the connection.</td></tr>\n")); } else { foreach (DataRow dr in dt.Rows) { if (currentBoxCount == sizeOfBox) { iconLibPanel.Controls.Add(new LiteralControl("</tr>\n")); currentBoxCount = 0; } if (currentBoxCount == 0) { iconLibPanel.Controls.Add(new LiteralControl("<tr>\n")); } iconLibPanel.Controls.Add(new LiteralControl("<td>")); Image img = new Image(); img.ID = "imgLib_" + dr["ID"].ToString(); img.ImageUrl = dr["location"].ToString(); img.AlternateText = "Icon Cannot be Displayed"; img.ToolTip = dr["location"].ToString(); img.Height = 64; img.Width = 64; iconLibPanel.Controls.Add(img); iconLibPanel.Controls.Add(new LiteralControl("</td>")); currentBoxCount += 1; } } iconLibPanel.Controls.Add(new LiteralControl("</table>\n")); this.IconLibModalPopup.Show(); //iconLibPanel }
protected void addSingleIconToLib(String path) { Database db = new Database(); DataTable dt; try { dt = db.executeQueryLocal("SELECT ID, location, isLocal FROM IconLibrary WHERE location=\'" + path + "\'"); } catch (ODBC2KMLException ex) { throw ex; } foreach (DataRow dr in dt.Rows) { Icon icon = new Icon(); icon.setId(dr["ID"].ToString()); icon.setLocation(dr["location"].ToString()); icon.setLocality((bool)dr["isLocal"]); iconListAvailableToAdd.Add(icon); } fillIconLibraryPopup(); sessionSave(); }
/// <summary> /// Get all overlays based on a specific connection ID. /// </summary> /// <param name="connID">Int --> connection ID</param> /// <returns>ArrayList --> overlays</returns> public static ArrayList getOverlays(int connID) { Database localDatabase = new Database(); ArrayList overlays = new ArrayList(); DataTable table = null; //Create overlay query and populate table string query = "SELECT * FROM Overlay WHERE connID=" + connID + " ORDER BY ID"; try { table = localDatabase.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "There was an error retreiving overlays"; throw ex; } foreach (DataRow row in table.Rows) { //Create overlay and set basic information Overlay newOverlay = new Overlay(); newOverlay.setColor(row["color"].ToString()); newOverlay.setId(row["id"].ToString()); //Create the new table for another query DataTable newTable = new DataTable(); //Query string and query string conQuery = "SELECT * FROM OverlayCondition WHERE overlayID=" + (Convert.ToInt16(newOverlay.getId())) + " AND connID=" + connID + " ORDER BY ID"; try { newTable = localDatabase.executeQueryLocal(conQuery); } catch (ODBC2KMLException ex) { ex.errorText = "There was a problem selecting overlay conditions"; throw ex; } //Cycle through each condition foreach (DataRow nRow in newTable.Rows) { //Create the condition and add its values Condition condition = new Condition(); condition.setFieldName(nRow["fieldName"].ToString()); if (nRow["lowerBound"] != null) { condition.setLowerBound(nRow["lowerBound"].ToString()); } else { condition.setLowerBound(""); } if (nRow["upperBound"] != null) { condition.setUpperBound(nRow["upperBound"].ToString()); } else { condition.setUpperBound(""); } condition.setLowerOperator((int)nRow["lowerOperator"]); condition.setUpperOperator((int)nRow["upperOperator"]); condition.setTableName(nRow["tableName"].ToString()); condition.setId(Convert.ToInt16(nRow["ID"].ToString())); //Add the condition to the overlay array newOverlay.setConditions(condition); //Free up condition memory condition = null; }//End loop //Add the overlay to the list of overlays overlays.Add(newOverlay); newOverlay = null; newTable = null; } return(overlays); }
protected void fillIconLibraryLists() { iconListAvailableToAdd.Clear(); iconListAvailableToRemove.Clear(); string conId = Request.QueryString.Get("ConnID"); Database db = new Database(); DataTable dt; try { dt = db.executeQueryLocal("SELECT ID, location, isLocal FROM IconLibrary AS IL WHERE (NOT EXISTS (SELECT ID, connID FROM Icon AS IC WHERE (connID = " + conId + " ) AND (iconLibraryID = IL.ID)))"); } catch (ODBC2KMLException ex) { throw ex; } foreach (DataRow dr in dt.Rows) { Icon icon = new Icon(); icon.setId(dr["ID"].ToString()); icon.setLocation(dr["location"].ToString()); icon.setLocality((bool)dr["isLocal"]); iconListAvailableToAdd.Add(icon); } Database db2 = new Database(); DataTable dt2; try { dt2 = db2.executeQueryLocal("SELECT IconLibrary.ID, IconLibrary.location, IconLibrary.isLocal FROM IconLibrary,Icon Where IconLibrary.ID=Icon.iconLibraryID AND Icon.ConnID=" + conId); } catch (ODBC2KMLException ex) { throw ex; } foreach (DataRow dr2 in dt2.Rows) { string iconId = dr2["ID"].ToString(); string iconLoc = dr2["location"].ToString(); Icon icon = new Icon(); icon.setId(iconId); icon.setLocation(iconLoc); icon.setLocality((bool)dr2["isLocal"]); iconListAvailableToRemove.Add(icon); } }
/// <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); }
//Additional /// <summary> /// Verifies all connection information and proceeds to attempt to save the connection. /// Returns 1 for INVALIDCONNINFO. /// Returns 2 for INVALIDMAPPING. /// Returns 3 for INVALIDSTATE. /// Returns 4 for INVALIDDESCRIPTION. /// Returns 5 for INVALIDFLAGS. /// Returns 0 for CONNECTIONSAVED. /// </summary> /// <returns>Integer --> Value based on what happens</returns> public int saveConn() { Boolean validConnInfo = false; Boolean validMapping = false; Boolean validDescription = false; Boolean validIcons = false; Boolean validOverlays = false; ArrayList transactionQuery = new ArrayList(); Database db = new Database(); try { //Validate the Connection information if (!this.connInfo.isValid(this.connID)) { return(1); } else { validConnInfo = true; } //Validate the mapping if (!this.mapping.isValid(this.connInfo)) //TODO: resolve this { return(2); } else { validMapping = true; } //Remove bad icon and overlay conditions if (!this.safeStateConnection()) { return(3); } else { validOverlays = true; validIcons = true; } //Validate description if (!this.description.isValid(this.connInfo, this.mapping)) { return(4); } else { validDescription = true; } //ALL THINGS ARE VALID, ATTEMPT A SAVE if (validConnInfo && validDescription && validIcons && validMapping && validOverlays) { //Database needed to make all of the queries Database database = new Database(); String query = "UPDATE Connection SET name=\'" + this.connInfo.connectionName + "\', dbName=\'" + this.connInfo.databaseName + "\', userName=\'" + this.connInfo.userName + "\', password=\'" + this.connInfo.password + "\', port=\'" + this.connInfo.portNumber + "\', address=\'" + this.connInfo.serverAddress + "\', protocol=\'" + this.connInfo.oracleProtocol + "\', SID=\'" + this.connInfo.oracleSID + "\', serviceName=\'" + this.connInfo.oracleServiceName + "\', type=\'" + this.connInfo.databaseType + "\' WHERE ID=" + this.connID; //Update the connection information transactionQuery.Add(query); //If the latfieldname is empty, and it passed the validation function, then remove the mapping if (this.mapping == null) { //Remove the mapping query = "DELETE FROM Mapping WHERE connID=" + this.connID; transactionQuery.Add(query); } else { //See if there is a mapping query = "SELECT * FROM Mapping WHERE connID=" + this.connID; DataTable checkTable = database.executeQueryLocal(query); //Is there currently a mapping? if (checkTable.Rows.Count > 0) { query = "UPDATE Mapping SET format=" + this.mapping.getFormat() + ", latFieldName='" + this.mapping.getLatFieldName() + "'" + ", longFieldName='" + this.mapping.getLongFieldName() + "'" + ", tableName='" + this.mapping.getTableName() + "'" + ", placemarkFieldName='" + this.mapping.getPlacemarkFieldName() + "'" + " WHERE connID=" + this.connID; } else //Insert mapping { query = "INSERT INTO Mapping (format, latFieldName, longFieldName, tableName, placemarkFieldName, connID) " + "VALUES(" + this.mapping.getFormat() + ", '" + this.mapping.getLatFieldName() + "', '" + this.mapping.getLongFieldName() + "', '" + this.mapping.getTableName() + "', '" + this.mapping.getPlacemarkFieldName() + "', " + this.connID + ")"; } //Update mapping transactionQuery.Add(query); } if (this.description.getDesc() == "") //No description { //Remove the description query = "DELETE FROM Description WHERE connID=" + this.connID; transactionQuery.Add(query); } else //Description { //See if there is a description query = "SELECT * FROM Description WHERE connID=" + this.connID; DataTable checkTable = database.executeQueryLocal(query); //Update the description if (checkTable.Rows.Count > 0) { query = "UPDATE Description SET description='" + this.description.getDesc() + "' WHERE connID=" + this.connID; } else //Insert the description { query = "INSERT INTO Description (connID, description) VALUES(" + this.connID + ", '" + this.description.getDesc() + "')"; } transactionQuery.Add(query); } //Delete all icons query = "DELETE FROM Icon WHERE connID=" + this.connID; transactionQuery.Add(query); //There are icons if (this.icons.Count > 0) { //Add all current icons to the database foreach (Icon i in this.icons) { query = "INSERT INTO Icon (connID, iconLibraryID) VALUES(" + this.connID + ", " + i.getId() + ")"; transactionQuery.Add(query); String newQuery = "DECLARE @uniqueIconID int;SET @uniqueIconID = (SELECT ID FROM Icon WHERE connID=" + this.connID + " AND " + "iconLibraryID=" + i.getId() + ");"; //Add all conditions foreach (Condition c in i.getConditions()) { query = newQuery + "INSERT INTO IconCondition (iconID, connID, lowerBound, upperBound, " + "lowerOperator, upperOperator, fieldName, tableName) VALUES(@uniqueIconID" + ", " + this.connID + ", '" + c.getLowerBound() + "', '" + c.getUpperBound() + "', " + Condition.operatorStringToInt(c.getLowerOperator()) + ", " + Condition.operatorStringToInt(c.getUpperOperator()) + ", '" + c.getFieldName() + "', '" + c.getTableName() + "')"; transactionQuery.Add(query); } } } //Delete all icons query = "DELETE FROM Overlay WHERE connID=" + this.connID; transactionQuery.Add(query); //There are overlays if (this.overlays.Count > 0) { //Add all current icons to the database foreach (Overlay o in this.overlays) { query = "INSERT INTO Overlay (connID, color) VALUES(" + this.connID + ", '" + o.getColor() + "')"; transactionQuery.Add(query); String newQuery = "DECLARE @OverlayID int;SET @OverlayID = (SELECT ID FROM Overlay WHERE connID=" + this.connID + " AND " + "color='" + o.getColor() + "');"; //Add all conditions foreach (Condition c in o.getConditions()) { query = newQuery + "INSERT INTO OverlayCondition (overlayID, connID, lowerBound, upperBound, " + "lowerOperator, upperOperator, fieldName, tableName) VALUES(@OverlayID" + ", " + this.connID + ", '" + c.getLowerBound() + "', '" + c.getUpperBound() + "', " + Condition.operatorStringToInt(c.getLowerOperator()) + ", " + Condition.operatorStringToInt(c.getUpperOperator()) + ", '" + c.getFieldName() + "', '" + c.getTableName() + "')"; transactionQuery.Add(query); } } } } else //All flags are not true { return(5); } } catch (ODBC2KMLException err) { throw err; } try { db.executeQueryLocal(transactionQuery); } catch (ODBC2KMLException ex) { throw ex; } //The connection was saved and properly updated return(0); }
public static ConnInfo getConnInfo(int connID) { ConnInfo connInfo = new ConnInfo(); Database localDatabase = new Database(); //Construct the connInfo query and retrieve the DataTable string query = "SELECT * FROM Connection WHERE ID=" + connID + " ORDER BY ID"; DataTable table = null; try { table = localDatabase.executeQueryLocal(query); } catch (ODBC2KMLException ex) { ex.errorText = "Error getting connection information for the connection"; throw ex; } //Cycle through each row and column foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) { //Set all connInfo switch (col.ColumnName) { case "name": connInfo.setConnectionName(row[col].ToString()); break; case "dbName": connInfo.setDatabaseName(row[col].ToString()); break; case "userName": connInfo.setUserName(row[col].ToString()); break; case "password": connInfo.setPassword(row[col].ToString()); break; case "port": connInfo.setPortNumber(row[col].ToString()); break; case "address": connInfo.setServerAddress(row[col].ToString()); break; case "type": connInfo.setDatabaseType((int)row[col]); break; case "protocol": connInfo.setOracleProtocol(row[col].ToString()); break; case "serviceName": connInfo.setOracleServiceName(row[col].ToString()); break; case "SID": connInfo.setOracleSID(row[col].ToString()); break; default: break; } } }//End outer loop return(connInfo); }