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