/// <summary>
/// Open a connection to the SQLite database.
/// This will check if the givn project is valid and
/// is new. If the project has changed, then open a
/// connection. If the project is the same, then do
/// not open a new connection. It should already be
/// open.
/// </summary>
/// <param name="project">Project to open.</param>
private void OpenConnection(Project project)
{
if (project != null)
{
// Close the previous connection if it is open
CloseConnection();
// Open a new connection
_cnn = DbCommon.OpenProjectDB(project);
}
}
/// <summary>
/// Check if the given column exist in the given table for the given project.
/// If the column does not exist, it will return false.
///
/// To check if the colun exist, it will run a query to select the columnn given.
/// If the column does not exist, an exception will be given. If an exception is
/// found, it will return false.
/// </summary>
/// <param name="project">Project to check.</param>
/// <param name="column">Column to check.</param>
/// <param name="table">Table to check.</param>
/// <returns>TRUE = Column exist in the table.</returns>
public static bool CheckIfColumnExist(Project project, string column, string table)
{
// Create a query to check if the column exist in the table
// This will try to select the column, if it does not exist, an error will be thrown
string query = string.Format("SELECT {0} FROM {1};", column, table);
// Default is true
bool result = true;
try
{
// Open a connection to the database
using (SQLiteConnection cnn = DbCommon.OpenProjectDB(project))
{
// Ensure a connection can be made
if (cnn == null)
{
return(false);
}
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
// Run the query
cmd.ExecuteNonQuery();
}
// Add all the data
dbTrans.Commit();
}
// Close the connection to the database
cnn.Close();
}
}
catch (SQLiteException e)
{
log.Error(string.Format("Unknown Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return(false);
}
catch (Exception e)
{
log.Error(string.Format("Unknown Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return(false);
}
return(result);
}
/// <summary>
/// Allows the programmer to run a query against the project database
/// and return a table with the result.
/// </summary>
/// <param name="project">Project to query.</param>
/// <param name="query">The SQL Query to run</param>
/// <returns>A DataTable containing the result set.</returns>
public static DataTable GetDataTableFromProjectDb(Project project, string query)
{
DataTable dt = new DataTable();
try
{
// Open a connection to the database
using (SQLiteConnection cnn = DbCommon.OpenProjectDB(project))
{
// Ensure a connection can be made
if (cnn == null)
{
return(dt);
}
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
DbDataReader reader = cmd.ExecuteReader();
// Load the datatable with query result
dt.Load(reader);
// Close the connection
reader.Close();
cnn.Close();
}
}
}
}
catch (Exception e)
{
if (project != null)
{
log.Error(string.Format("Error populating datatable from {0} database. \n{1}", project.ProjectName, query), e);
}
else
{
log.Error(string.Format("Error populating datatable. \n{0}", query), e);
}
}
return(dt);
}
/// <summary>
/// Run a query that will return an object. Give the
/// project and the query string. The result will be returned.
/// If an error, null will be returned.
/// </summary>
/// <param name="project">Project to query.</param>
/// <param name="query">Query string.</param>
/// <returns>Result of query, if error, it will return 0.</returns>
public static object RunQueryOnProjectDbObj(Project project, string query)
{
object result = 0;
try
{
// Open a connection to the database
using (SQLiteConnection cnn = DbCommon.OpenProjectDB(project))
{
// Ensure a connection can be made
if (cnn == null)
{
return(-1);
}
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
// Get Result
result = cmd.ExecuteScalar();
}
// Add all the data
dbTrans.Commit();
}
// Close the connection to the database
cnn.Close();
}
}
catch (SQLiteException e)
{
log.Error(string.Format("Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return(0);
}
catch (Exception e)
{
log.Error(string.Format("Unknown Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return(0);
}
return(result);
}
/// <summary>
/// Run a query that will return an Integer. Give the
/// project and the query string. The result will be returned.
/// If an error, 0 will be returned.
/// </summary>
/// <param name="project">Project to query.</param>
/// <param name="query">Query string.</param>
/// <returns>Result of query, if error, it will return 0.</returns>
public static int RunQueryOnProjectDb(Project project, string query)
{
int result = 0;
try
{
// Open a connection to the database
using (SQLiteConnection cnn = DbCommon.OpenProjectDB(project))
{
// Ensure a connection can be made
if (cnn == null)
{
return(-1);
}
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
// Get Result
object resultValue = cmd.ExecuteScalar();
result = Convert.ToInt32(resultValue.ToString());
}
// Close the connection to the database
cnn.Close();
}
}
catch (SQLiteException e)
{
log.Error(string.Format("Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return(0);
}
catch (Exception e)
{
log.Error(string.Format("Unknown Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return(0);
}
return(result);
}
/// <summary>
/// Run a query on the project database that will not return a result.
/// </summary>
/// <param name="project">Project to run the query.</param>
/// <param name="statement">SQL Statement.</param>
public static void RunStatmentOnProjectDb(Project project, string statement)
{
try
{
// Open a connection to the database
using (SQLiteConnection cnn = DbCommon.OpenProjectDB(project))
{
// Ensure a connection can be made
if (cnn == null)
{
return;
}
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = statement;
// Run the query
cmd.ExecuteNonQuery();
}
// Add all the data
dbTrans.Commit();
}
// Close the connection to the database
cnn.Close();
}
}
catch (SQLiteException e)
{
log.Error(string.Format("Unknown Error running query on database: {0} \n{1}", project.ProjectName, statement), e);
return;
}
catch (Exception e)
{
log.Error(string.Format("Unknown Error running query on database: {0} \n{1}", project.ProjectName, statement), e);
return;
}
}
