/// <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 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");
}
}
/// <summary>
/// This database function communicates with Oracle, MySQL, and SQL remote servers.
/// This functions requires the Database object to hold a ConnInfo object with information
/// about the remote server.
/// </summary>
/// <param name="query">String --> query to be executed</param>
/// <returns>DataTable --> Result set</returns>
public DataTable executeQueryRemote(string query)
{
DataTable dataTable = new DataTable();
string connectionString = "";
//Check the database type to determine the connection string
if (connInfo.getDatabaseType() == ConnInfo.MSSQL || connInfo.getDatabaseType() == ConnInfo.MYSQL)
{
connectionString = Database.getConnectionString(connInfo);
//Create the Odbc Connection
OdbcConnection connection = new OdbcConnection(connectionString);
connection.ConnectionTimeout = 5;
// This is your data adapter that understands SQL databases:
OdbcDataAdapter dataAdapter = new OdbcDataAdapter(query, connection);
// This is your table to hold the result set:
try
{
//Open the connection to the database
connection.Open();
// Fill the data table with select statement's query results:
int recordsAffected = dataAdapter.Fill(dataTable);
//Close connection
connection.Close();
}
catch (Exception ex)
{
throw new ODBC2KMLException(ex.Message);
}
finally
{
//Close the connection
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
//Database type = oracle
else if (ConnInfo.ORACLE == connInfo.getDatabaseType())
{
connectionString = Database.getConnectionString(connInfo);
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand command = new OracleCommand(query);
command.Connection = connection;
try
{
connection.Open();
}
catch (Exception ex)
{
throw new ODBC2KMLException(ex.Message);
}
OracleDataReader reader = null;
try
{
reader = command.ExecuteReader();
dataTable.Load(reader);
}
catch (Exception ex)
{
throw new ODBC2KMLException(ex.Message);
}
finally
{
reader.Close();
}
}
}
else
{
//Pass an error to error handler signalling an improper database type
}
//Return the data table to be operated on
return(dataTable);
}
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");
}
}