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