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);
}
