protected void genKMLFunction(object sender, EventArgs e)
{
try
{
//Generate the KML from the connection
ImageButton sendBtn = (ImageButton)sender;
String serverPath = "http://" + Request.ServerVariables["SERVER_NAME"] + ":" + Request.ServerVariables["SERVER_PORT"];
string args = sendBtn.CommandArgument.ToString();
KMLGenerator kml = new KMLGenerator(ConnInfo.getConnInfo(Convert.ToInt32(args)).getConnectionName(), serverPath);
//Generate the KML string based on the connection id
String kmlString = kml.generateKML(int.Parse(args));
Connection conn = new Connection(int.Parse(args));
conn.populateFields();
//Write the KML string to a downloadable file
Response.ClearHeaders();
Response.ClearContent();
Response.ContentType = "application/vnd.google-earth.kml+xml kml";
Response.AddHeader("Content-Disposition", "attachment; filename=\"" + (conn.getConnInfo()).getConnectionName() + ".kml");
Response.Write(kmlString);
Response.End();
return;
}
catch (ODBC2KMLException ex)
{
ErrorHandler err = new ErrorHandler(ex.errorText, errorPanel1);
err.displayError();
return;
}
//Response.Redirect("Main.aspx", true);
}
//Functions
//Constructor
public Connection()
{
icons = new ArrayList();
overlays = new ArrayList();
description = new Description();
mapping = new Mapping();
connInfo = new ConnInfo();
}
public bool isValid(int connID)
{
//If oracle specific information is missing for oracle, return false
if (this.databaseType == ConnInfo.ORACLE)
{
if (!ConnInfo.validSName(this.oracleServiceName) && !ConnInfo.validSID(this.oracleSID))
{
return(false);
}
if (!ConnInfo.validProtocol(this.oracleProtocol))
{
return(false);
}
}
try
{
//If anything specific to any connection is missing, return false
if (!ConnInfo.validConnName(this.connectionName, connID))
{
return(false);
}
else if (!ConnInfo.validDBAddress(this.serverAddress))
{
return(false);
}
else if (!ConnInfo.validPort(this.portNumber))
{
return(false);
}
else if (!ConnInfo.validDBName(this.databaseName))
{
return(false);
}
else if (!ConnInfo.validUserName(this.userName))
{
return(false);
}
else if (!ConnInfo.validPassword(this.password))
{
return(false);
}
}
catch (ODBC2KMLException ex)
{
throw ex;
}
//All is good!
return(true);
}
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;
}
}
/// <summary>
/// Populate fields uses the connID passed into the constructor
/// and retrieves all of the information about that specific connection
/// from the database. It will then set all of the classes variables
/// based on this information.
/// </summary>
public void populateFields()
{
try
{
this.connInfo = ConnInfo.getConnInfo(this.connID);
this.overlays = Overlay.getOverlays(this.connID);
this.description = Description.getDescription(this.connID);
this.mapping = Mapping.getMapping(this.connID);
this.icons = Icon.getIcons(this.connID);
}
catch (ODBC2KMLException e) //Add whatever exceptions are needed and error handling code
{
throw e;
}
}
/// <summary>
/// Returns a connection string based on the current ConnInfo object.
/// </summary>
/// <param name="info">ConnInfo --> current database information</param>
/// <returns>String --> connection string</returns>
public static String getConnectionString(ConnInfo info)
{
if (ConnInfo.MYSQL == info.getDatabaseType())
{
//My SQL connection string
return("Driver={MySQL ODBC 5.1 Driver};Server="
+ info.getServerAddress() + ";Port=" + info.portNumber + ";Database="
+ info.getDatabaseName() + ";User="******"; Password="******";Option=3;");
}//Database type = MS SQL
else if (ConnInfo.MSSQL == info.getDatabaseType())
{
//MS SQL connection string
return("Driver={SQL Native Client};Server="
+ info.getServerAddress() + ";Port=" + info.portNumber + ";Database="
+ info.getDatabaseName() + ";Uid=" + info.getUserName()
+ ";Pwd=" + info.getPassword() + ";");
}
else if (ConnInfo.ORACLE == info.getDatabaseType())
{
string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS="
+ "(PROTOCOL=" + info.getOracleProtocol() + ")(HOST=" + info.getServerAddress()
+ ")(PORT=" + info.getPortNumber() + ")))(CONNECT_DATA=(SERVER=DEDICATED)";
if (info.getOracleServiceName().Length != 0)
{
connectionString += "(SERVICE_NAME="
+ info.getOracleServiceName() + ")";
}
else if (info.getOracleSID().Length != 0)
{
connectionString += "(SID="
+ info.getOracleSID() + ")";
}
connectionString += "));User Id=" + info.getUserName() + ";Password="******";";
//Oracle connection string
return(connectionString);
}
return("");
}
/// <summary>
/// Returns a connection string based on the current ConnInfo object.
/// </summary>
/// <param name="info">ConnInfo --> current database information</param>
/// <returns>String --> connection string</returns>
public static String getConnectionString(ConnInfo info)
{
if (ConnInfo.MYSQL == info.getDatabaseType())
{
//My SQL connection string
return "Driver={MySQL ODBC 5.1 Driver};Server="
+ info.getServerAddress() + ";Port=" + info.portNumber + ";Database="
+ info.getDatabaseName() + ";User="******"; Password="******";Option=3;";
}//Database type = MS SQL
else if (ConnInfo.MSSQL == info.getDatabaseType())
{
//MS SQL connection string
return "Driver={SQL Native Client};Server="
+ info.getServerAddress() + ";Port=" + info.portNumber + ";Database="
+ info.getDatabaseName() + ";Uid=" + info.getUserName()
+ ";Pwd=" + info.getPassword() + ";";
}
else if (ConnInfo.ORACLE == info.getDatabaseType())
{
string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS="
+ "(PROTOCOL=" + info.getOracleProtocol() + ")(HOST=" + info.getServerAddress()
+ ")(PORT=" + info.getPortNumber() + ")))(CONNECT_DATA=(SERVER=DEDICATED)";
if (info.getOracleServiceName().Length != 0)
{
connectionString += "(SERVICE_NAME="
+ info.getOracleServiceName() + ")";
}
else if (info.getOracleSID().Length != 0)
{
connectionString += "(SID="
+ info.getOracleSID() + ")";
}
connectionString += "));User Id=" + info.getUserName() + ";Password="******";";
//Oracle connection string
return connectionString;
}
return "";
}
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>"));
}
//Accepts a connection info class
public Database(ConnInfo connInfo)
{
this.connInfo = connInfo;
}
//Constructors
//Default
public Database()
{
connInfo = null;
}
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);
}
//Accepts a connection info class
public Database(ConnInfo connInfo)
{
this.connInfo = connInfo;
}
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;
}
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>"));
* }*/
}
//Retrieve connInfo
public void setConnInfo(ConnInfo connInfo)
{
this.connInfo = connInfo;
}
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;
}
}
//Constructors
//Default
public Database()
{
connInfo = null;
}
protected ArrayList getColumnsForTable(ConnInfo cInfo, string tableName)
{
ArrayList result = new ArrayList();
sessionSave();
return result;
}
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>"));
}*/
}
/// <summary>
/// Set the databases conninfo object.
/// </summary>
/// <param name="info">ConnInfo --> Remote database information</param>
public void setConnInfo(ConnInfo info)
{
connInfo = info;
}
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 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");
}
}
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>
/// 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;
}
/// <summary>
/// Set the databases conninfo object.
/// </summary>
/// <param name="info">ConnInfo --> Remote database information</param>
public void setConnInfo(ConnInfo info)
{
connInfo = info;
}
