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 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 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 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 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 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 createConnection(object sender, EventArgs e)
{
String ConnName = odbcNameE.Text.ToString();
String ConnDBName = odbcDNameE.Text.ToString();
String ConnDBAddress = odbcDatabaseE.Text.ToString();
String ConnPortNum = odbcPNE.Text.ToString();
String ConnUser = odbcUserE.Text.ToString();
String ConnPWD = odbcPWE.Text.ToString();
String ConnDBType = odbcDBType.SelectedValue.ToString();
String oracleProtocol = odbcProtocol.Text.ToString();
String oracleSName = odbcSName.Text.ToString();
String oracleSID = odbcSID.Text.ToString();
String DBTypeNum;
odbcDBType.SelectedIndex = 0;
if (ConnDBType.Equals("MySQL"))
{
DBTypeNum = "0";
}
else if (ConnDBType.Equals("MSSQL"))
{
DBTypeNum = "1";
}
else
{
DBTypeNum = "2";
}
if (DBTypeNum.Equals("2"))
{
if (oracleSName.Equals("") && oracleSID.Equals(""))
{
ErrorHandler eh = new ErrorHandler("Either Service Name or Service ID must be completed!", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
if (oracleProtocol.Equals(""))
{
ErrorHandler eh = new ErrorHandler("Oracle protocol must be provided!", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
}
Database dbCheck = new Database();
DataTable dtCheck = null;
try
{
dtCheck = dbCheck.executeQueryLocal("SELECT name FROM Connection WHERE name=\'" + ConnName + "\'");
}
catch (ODBC2KMLException)
{
ErrorHandler eh = new ErrorHandler("There was an error getting the Connection's name", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
if (dtCheck.Rows.Count > 0)
{
ErrorHandler eh = new ErrorHandler("Connection name already in use!", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
if (ConnName.Equals(""))
{
ErrorHandler eh = new ErrorHandler("The connection must have a unique name!", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
else if (ConnDBName.Equals(""))
{
ErrorHandler eh = new ErrorHandler("The connection must have a database name!", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
else if (ConnDBAddress.Equals(""))
{
ErrorHandler eh = new ErrorHandler("The connection must have a database address!", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
else if (ConnPortNum.Equals(""))
{
ErrorHandler eh = new ErrorHandler("The connection must have a port number!", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
else if (ConnUser.Equals(""))
{
ErrorHandler eh = new ErrorHandler("The connection must have a user name!", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
else if (ConnPWD.Equals(""))
{
ErrorHandler eh = new ErrorHandler("The connection must have a password!", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
ConnInfo testConn = new ConnInfo();
testConn.setConnectionName(ConnName);
testConn.setDatabaseName(ConnDBName);
testConn.setDatabaseType((int)Convert.ToInt32(DBTypeNum));
testConn.setPassword(ConnPWD);
testConn.setPortNumber(ConnPortNum);
testConn.setServerAddress(ConnDBAddress);
testConn.setUserName(ConnUser);
if (DBTypeNum.Equals("2"))
{
testConn.setOracleProtocol(oracleProtocol);
testConn.setOracleServiceName(oracleSName);
testConn.setOracleSID(oracleSID);
}
try
{
Database dbTest = new Database(testConn);
DataTable dtTest;
if (DBTypeNum.Equals("0"))
{
dtTest = dbTest.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
}
else if (DBTypeNum.Equals("1"))
{
dtTest = dbTest.executeQueryRemote("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
}
else
{
dtTest = dbTest.executeQueryRemote("SELECT TABLE_NAME FROM user_tables");
}
}
catch (ODBC2KMLException ex)
{
ErrorHandler eh = new ErrorHandler(ex.errorText, errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
//Call Create DB with the DB Function
Database db = new Database();
DataTable dt;
try
{
if (DBTypeNum.Equals("2"))
{
db.executeQueryLocal("INSERT INTO Connection (name, dbName, userName, password, port, address, type, protocol, serviceName, SID) VALUES ('" + ConnName + "', '" + ConnDBName + "', '" + ConnUser + "', '" + ConnPWD + "', '" + ConnPortNum + "', '" + ConnDBAddress + "', '" + DBTypeNum + "', '" + oracleProtocol + "', '" + oracleSName + "', '" + oracleSID + "')");
}
else
{
db.executeQueryLocal("INSERT INTO Connection (name, dbName, userName, password, port, address, type, protocol, serviceName, SID) VALUES ('" + ConnName + "', '" + ConnDBName + "', '" + ConnUser + "', '" + ConnPWD + "', '" + ConnPortNum + "', '" + ConnDBAddress + "', '" + DBTypeNum + "', '', '', '')");
}
}
catch (ODBC2KMLException)
{
ErrorHandler eh = new ErrorHandler("There was an error saving the connection to the database.", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
this.NewConn1ModalPopUp.Hide();
//Jump to the Modify page
try
{
dt = db.executeQueryLocal("SELECT ID FROM CONNECTION WHERE name='" + ConnName + "' AND dbName='" + ConnDBName + "' AND userName='******' AND port='" + ConnPortNum + "' AND address='" + ConnDBAddress + "' AND type='" + DBTypeNum + "'");
}
catch (ODBC2KMLException)
{
ErrorHandler eh = new ErrorHandler("There was an error retreiving the new connection's connID.", errorPanel1);
this.NewConn1ModalPopUp.Hide();
eh.displayError();
return;
}
foreach (DataRow dr in dt.Rows)
{
string connID = dr["ID"].ToString();
Response.Redirect("ConnDetails.aspx?ConnID=" + connID + "&locked=false");
}
}
protected void updateConnection(object sender, CommandEventArgs e)
{
Button sendBtn = (Button)sender;
String args = sendBtn.CommandArgument.ToString();
ConnInfo tempConnInfo = new ConnInfo();
tempConnInfo.setConnectionName(editConnName.Text);
tempConnInfo.setDatabaseName(editConnDBName.Text);
tempConnInfo.setServerAddress(editConnDBAddr.Text);
tempConnInfo.setPortNumber(editConnDBPort.Text);
tempConnInfo.setUserName(editConnUser.Text);
tempConnInfo.setPassword(editConnPass.Text);
tempConnInfo.setDatabaseType((editConnDBType.SelectedIndex));
tempConnInfo.setOracleProtocol(editOracleProtocol.Text);
tempConnInfo.setOracleServiceName(editOracleService.Text);
tempConnInfo.setOracleSID(editOracleSID.Text);
//If the connection information is bad, report the error and cancel the function. This does NOT run against the database.
try
{
if (!tempConnInfo.isValid(Convert.ToInt32(args)))
{
throw new ODBC2KMLException(""); // Throw any error. The catch is generic.
}
}
catch
{
String error = "The entered connection information is invalid. Please make sure all fields are filled and that they are in proper format.";
if (tempConnInfo.getDatabaseType() == ConnInfo.ORACLE)
{
error = "The entered connection information is invalid. Please verify that all fields have a value and the value is of proper type."
+ " Also, make sure that Oracle SID or Oracle Service Name and Oracle Protocol have been entered.";
}
ErrorHandler eh = new ErrorHandler(error, errorPanel1);
this.editConnModalPopUp.Hide();
eh.displayError();
return;
}
//Create database and test it
Database db = new Database(tempConnInfo);
//See if you can reach the database. If not, error out and don't save.
try
{
if (tempConnInfo.getDatabaseType() == ConnInfo.MSSQL)
{
String query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' AND TABLE_NAME != 'sysdiagrams'";
db.executeQueryRemote(query);
}
else if (tempConnInfo.getDatabaseType() == ConnInfo.MYSQL)
{
String query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema' && TABLE_SCHEMA != 'mysql'";
db.executeQueryRemote(query);
}
else if (tempConnInfo.getDatabaseType() == ConnInfo.ORACLE)
{
String query = "select TABLE_NAME from user_tables";
db.executeQueryRemote(query);
}
}
catch
{
ErrorHandler eh = new ErrorHandler("The database entered could not be connected to. Please verify the information is correct.", errorPanel1);
this.editConnModalPopUp.Hide();
eh.displayError();
return;
}
db.executeQueryLocal("UPDATE Connection SET name='" + tempConnInfo.getConnectionName()
+ "', dbName='" + tempConnInfo.getDatabaseName() + "', userName='******', password='******', port='" + tempConnInfo.getPortNumber()
+ "', address='" + tempConnInfo.getServerAddress() + "', type='" + tempConnInfo.getDatabaseType()
+ "', protocol='" + tempConnInfo.getOracleProtocol() + "', serviceName='" + tempConnInfo.getOracleServiceName()
+ "', SID='" + tempConnInfo.getOracleSID() + "' WHERE (ID='" + args + "')");
Connection conn = new Connection(Convert.ToInt16(args));
try
{
conn.populateFields();
//Force the connection into a safe state, if it is not
if (!conn.safeStateConnection())
{
String error = "Invalid connection information. Please verify all of your fields are filled in correctly."
+ "If you are using an oracle connection, please make sure you filled out the oracle specific information.";
ErrorHandler eh = new ErrorHandler(error, errorPanel1);
this.editConnModalPopUp.Hide();
eh.displayError();
return;
}
}
catch (ODBC2KMLException err)
{
ErrorHandler eh = new ErrorHandler(err.errorText, errorPanel1);
this.editConnModalPopUp.Hide();
eh.displayError();
return;
}
if (e.CommandName.Equals("saveConn"))
{
this.editConnModalPopUp.Hide();
Response.Redirect("Main.aspx");
}
else
{
this.editConnModalPopUp.Hide();
Response.Redirect("ConnDetails.aspx?ConnID=" + ((Button)sender).CommandArgument.ToString() + "&locked=false");
}
}
protected void Page_Load(object sender, EventArgs e)
{
Database db = new Database();
DataTable dt;
Label title = new Label();
string connID = Request["con"];
string table = Request["tbl"];
string tblQuery = "SELECT * FROM " + table;
ConnInfo info = new ConnInfo();
try
{
db = new Database();
string query = "SELECT * FROM Connection WHERE ID=" + connID;
dt = db.executeQueryLocal(query);
if (dt.HasErrors)
{
throw new ODBC2KMLException("There was a problem getting the connection information from the local database");
}
}
catch (ODBC2KMLException ex)
{
ErrorHandler eh = new ErrorHandler(ex.conErrorText, errorPanel1);
eh.displayError();
return;
}
//Cycle through each row and column
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
//Set all connInfo
switch (col.ColumnName)
{
case "name":
info.setConnectionName(row[col].ToString());
break;
case "dbName":
info.setDatabaseName(row[col].ToString());
break;
case "userName":
info.setUserName(row[col].ToString());
break;
case "password":
info.setPassword(row[col].ToString());
break;
case "port":
info.setPortNumber(row[col].ToString());
break;
case "address":
info.setServerAddress(row[col].ToString());
break;
case "type":
info.setDatabaseType(int.Parse(row[col].ToString()));
break;
case "protocol":
info.setOracleProtocol(row[col].ToString());
break;
case "serviceName":
info.setOracleServiceName(row[col].ToString());
break;
case "SID":
info.setOracleSID(row[col].ToString());
break;
default:
break;
}
}
}//End outer loop
db.setConnInfo(info);
try
{
dt = db.executeQueryRemote(tblQuery);
}
catch (ODBC2KMLException ex)
{
ErrorHandler eh = new ErrorHandler(ex.tblErrorText, errorPanel1);
eh.displayError();
return;
}
//resultsPanel.Visible = true;
bool altTables = true;
Page.Controls.Add(new LiteralControl("<span style=\"color: white; font-weight:bold; background-color:rgb(26,49,76);\"> " + table + " Database Query Results</span>"));
Page.Controls.Add(new LiteralControl("<div>"));
Page.Controls.Add(new LiteralControl("<table cellpadding=\"5\" cellspacing=\"2\">"));
Page.Controls.Add(new LiteralControl("<tr><td>"));
Page.Controls.Add(new LiteralControl("<div>"));
Page.Controls.Add(new LiteralControl("<table cellspacing=\"2\" cellpadding=\"2\" rules=\"all\">"));
Page.Controls.Add(new LiteralControl("<tr class=\"titleConn\">"));
foreach (DataColumn dc in dt.Columns)
{
Page.Controls.Add(new LiteralControl("<td><b>" + dc.ColumnName + "<br/></b></td>"));
}
Page.Controls.Add(new LiteralControl("</tr>"));
foreach (DataRow dr in dt.Rows)
{
if (altTables)
{
Page.Controls.Add(new LiteralControl("<tr class=\"evenConn\">"));
foreach (Object data in dr.ItemArray)
{
Page.Controls.Add(new LiteralControl("<td>" + data.ToString() + "</td>"));
}
Page.Controls.Add(new LiteralControl("</tr>"));
}
else
{
Page.Controls.Add(new LiteralControl("<tr class=\"oddConn\">"));
foreach (Object data in dr.ItemArray)
{
Page.Controls.Add(new LiteralControl("<td>" + data.ToString() + "</td>"));
}
Page.Controls.Add(new LiteralControl("</tr>"));
}
altTables = !altTables;
}
Page.Controls.Add(new LiteralControl("</table>"));
Page.Controls.Add(new LiteralControl("</td></tr>"));
Page.Controls.Add(new LiteralControl("</div>"));
Page.Controls.Add(new LiteralControl("</span>"));
}
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);
}
