void CreateForwardLinkQuery() { var sb = new StringBuilder("SELECT "); var lkD = false; foreach (var link in _domainLinks) { if (lkD) { sb.Append(", "); } else { lkD = true; } if (link.TargetKey != null) { sb.AppendFormat("(SELECT t.ItemID FROM [domain_{0}] t WHERE t.[{1}]=src.[{2}]),{3}", _parent.GetDomain(link.TargetDomain).Name, link.TargetKey, link.SourceName, link.TargetDomain); } else { sb.AppendFormat("src.[{0}],{1}", link.SourceName, link.TargetDomain); } } if (!lkD) { _getOutLinks = null; return; } sb.AppendFormat(" FROM [domain_{0}] src where src.ItemId=@itemid", Name); _getOutLinks = _parent.BackEnd.CreateCommand(); _getOutLinks.CommandText = sb.ToString(); _getOutLinks.Parameters.Add("@itemid", System.Data.DbType.Int32); _getOutLinks.Prepare(); }
public static JSONArray ExecuteQuerySync(string sql, Dictionary <string, object> parameters, bool useTransaction) { // Force transaction if it is an update, insert or delete. if (GAUtilities.StringMatch(sql.ToUpperInvariant(), "^(UPDATE|INSERT|DELETE)")) { useTransaction = true; } // Get database connection from singelton sharedInstance SqliteConnection sqlDatabasePtr = Instance.SqlDatabase; // Create mutable array for results JSONArray results = new JSONArray(); SqliteTransaction transaction = null; SqliteCommand command = null; try { if (useTransaction) { transaction = sqlDatabasePtr.BeginTransaction(); } command = sqlDatabasePtr.CreateCommand(); if (useTransaction) { command.Transaction = transaction; } command.CommandText = sql; command.Prepare(); // Bind parameters if (parameters.Count != 0) { foreach (KeyValuePair <string, object> pair in parameters) { command.Parameters.AddWithValue(pair.Key, pair.Value); } } using (SqliteDataReader reader = command.ExecuteReader()) { // Loop through results while (reader.Read()) { // get columns count int columnCount = reader.FieldCount; JSONObject row = new JSONObject(); for (int i = 0; i < columnCount; i++) { string column = reader.GetName(i); if (string.IsNullOrEmpty(column)) { continue; } row[column] = reader.GetValue(i).ToString(); } results.Add(row); } } if (useTransaction) { transaction.Commit(); } } catch (SqliteException e) { // TODO(nikolaj): Should we do a db validation to see if the db is corrupt here? GALogger.E("SQLITE3 ERROR: " + e); results = null; if (useTransaction) { if (transaction != null) { try { transaction.Rollback(); } catch (SqliteException ex) { GALogger.E("SQLITE3 ROLLBACK ERROR: " + ex); } finally { transaction.Dispose(); } } } } finally { if (command != null) { command.Dispose(); } if (transaction != null) { transaction.Dispose(); } } // Return results return(results); }