/// <summary>
/// Update the tblOptions for the revision of the
/// project file.
/// </summary>
/// <param name="project">Project to check.</param>
/// <param name="revision">Revision of the project.</param>
/// <returns>Number of rows in the table.</returns>
public static void UpdateProjectVersion(Project project, string revision)
{
if (project != null)
{
string query = String.Format("UPDATE tblOptions SET {0} = {1} WHERE ID=1;", DbCommon.COL_CMD_REV, revision);
DbCommon.RunStatmentOnProjectDb(project, query);
}
}
/// <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>
/// Query the tblOptions for the revision of the
/// project file.
/// </summary>
/// <param name="project">Project to check.</param>
/// <returns>Number of rows in the table.</returns>
public static string GetProjectVersion(Project project)
{
if (project != null)
{
string query = String.Format("SELECT {0} FROM {1} WHERE ID=1;", DbCommon.COL_CMD_REV, DbCommon.TBL_ENS_OPTIONS);
return(Convert.ToString(DbCommon.RunQueryOnProjectDbObj(project, query)));
}
else
{
return(string.Empty);
}
}
/// <summary>
/// Query the tblEnsemble for the number of rows it contains.
/// Return the value.
/// </summary>
/// <param name="project">Project to check.</param>
/// <returns>Number of rows in the table.</returns>
public static int GetNumberOfEnsembles(Project project)
{
if (project != null)
{
string query = String.Format("SELECT COUNT(*) FROM {0};", DbCommon.TBL_ENS_ENSEMBLE);
return(DbCommon.RunQueryOnProjectDb(project, query));
}
else
{
return(0);
}
}
/// <summary>
/// Retrieve the dataset based off the index and project. Limit it
/// to the first dataset found with the correct index. The index is
/// auto incremented so there should only be 1 ensemble per index.
/// </summary>
/// <param name="cnn">Sqlite Database Connection.</param>
/// <param name="project">Project containing the ensemble.</param>
/// <param name="index">Row ID</param>
/// <returns>Dataset based off the Row ID given.</returns>
public DataSet.Ensemble QueryForDataSet(SQLiteConnection cnn, Project project, long index)
{
// Query for the ensemble
string queryEns = String.Format("SELECT * FROM {0} WHERE ID={1} LIMIT 1;", DbCommon.TBL_ENS_ENSEMBLE, index.ToString());
DataTable data = DbCommon.GetDataTableFromProjectDb(cnn, project, queryEns);
if (data.Rows.Count > 0)
{
DataSet.Ensemble dataset = ParseDataTables(project, data.Rows[0]);
return(dataset);
}
return(null);
}
/// <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>
/// Get a list of ensembles from the database. This will query for a list of
/// rows from the database starting from the index and getting the size given.
/// Then parse and add the data to the list.
/// </summary>
/// <param name="project">Project containing the ensemble.</param>
/// <param name="index">Row ID</param>
/// <param name="size">Number of ensembles to get from the database.</param>
/// <returns>List of dataset based off the Row ID given and size.</returns>
public Cache <long, DataSet.Ensemble> QueryForDataSet(Project project, long index, uint size)
{
Cache <long, DataSet.Ensemble> cache = new Cache <long, DataSet.Ensemble>(size);
// Query for the ensemble
string queryEns = String.Format("SELECT * FROM {0} WHERE ID>={1} LIMIT {2};", DbCommon.TBL_ENS_ENSEMBLE, index.ToString(), size.ToString());
DataTable data = DbCommon.GetDataTableFromProjectDb(project, queryEns);
foreach (DataRow row in data.Rows)
{
int id = 0;
try { id = Convert.ToInt32(row[DbCommon.COL_ENS_ID]); }
catch (Exception) { }
DataSet.Ensemble dataset = ParseDataTables(project, row);
cache.Add(id, dataset);
}
return(cache);
}
/// <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;
}
}
