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