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