public void backgroundExecuteSqlBatch(string options) { string mycbid = this.CurrentCommandCallbackId; try { String[] jsonOptions = JsonHelper.Deserialize <string[]>(options); mycbid = jsonOptions[1]; SQLitePluginExecuteSqlBatchOptions batch = JsonHelper.Deserialize <SQLitePluginExecuteSqlBatchOptions>(jsonOptions[0]); this.databaseManager.Query(batch.dbargs.name, batch.executes, mycbid); } catch (Exception) { DispatchCommandResult(new PluginResult(PluginResult.Status.JSON_EXCEPTION), mycbid); } }
public void executeSqlBatch(string options) { string mycbid = this.CurrentCommandCallbackId; //System.Diagnostics.Debug.WriteLine("SQLitePlugin.executeSqlBatch() with cbid " + mycbid + " options:" + options); //Deployment.Current.Dispatcher.BeginInvoke(() => { List <string> opt = JsonHelper.Deserialize <List <string> >(options); SQLitePluginExecuteSqlBatchOptions batch = JsonHelper.Deserialize <SQLitePluginExecuteSqlBatchOptions>(opt[0]); mycbid = opt[1]; //System.Diagnostics.Debug.WriteLine("real cbid: " + mycbid); // XXX TODO keep in a map: // check our db is not null, create a new connection if (this.db == null || !this.db.DatabasePath.Equals(dbOptions.name)) { // close open database to be safe if (this.db != null) { this.db.Close(); } // open database this.db = new SQLiteConnection(dbOptions.name); } string batchResultsStr = ""; // loop through the sql in the transaction foreach (SQLitePluginTransaction transaction in batch.executes) { string resultString = ""; string errorMessage = "unknown"; bool needQuery = true; // begin if (transaction.query.StartsWith("BEGIN", StringComparison.OrdinalIgnoreCase)) { needQuery = false; try { this.db.BeginTransaction(); resultString = "\"rowsAffected\":0"; } catch (Exception e) { errorMessage = e.Message; } } // commit if (transaction.query.StartsWith("COMMIT", StringComparison.OrdinalIgnoreCase)) { needQuery = false; try { this.db.Commit(); resultString = "\"rowsAffected\":0"; } catch (Exception e) { errorMessage = e.Message; } } // rollback if (transaction.query.StartsWith("ROLLBACK", StringComparison.OrdinalIgnoreCase)) { needQuery = false; try { this.db.Rollback(); resultString = "\"rowsAffected\":0"; } catch (Exception e) { errorMessage = e.Message; } } // create/drop table if (transaction.query.IndexOf("DROP TABLE", StringComparison.OrdinalIgnoreCase) > -1 || transaction.query.IndexOf("CREATE TABLE", StringComparison.OrdinalIgnoreCase) > -1) { needQuery = false; try { var results = db.Execute(transaction.query, transaction.query_params); resultString = "\"rowsAffected\":0"; } catch (Exception e) { errorMessage = e.Message; } } // insert/update/delete if (transaction.query.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase) || transaction.query.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase) || transaction.query.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase)) { needQuery = false; try { // execute our query var res = db.Execute(transaction.query, transaction.query_params); // get the primary key of the last inserted row var insertId = SQLite3.LastInsertRowid(db.Handle); resultString = String.Format("\"rowsAffected\":{0}", res); if (transaction.query.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase)) { resultString += String.Format(",\"insertId\":{0}", insertId); } } catch (Exception e) { errorMessage = e.Message; } } if (needQuery) { try { var results = this.db.Query2(transaction.query, transaction.query_params); string rowsString = ""; foreach (SQLiteQueryRow res in results) { string rowString = ""; if (rowsString.Length != 0) { rowsString += ","; } foreach (SQLiteQueryColumn column in res.column) { if (rowString.Length != 0) { rowString += ","; } if (column.Value != null) { if (column.Value.GetType().Equals(typeof(Int32))) { rowString += String.Format("\"{0}\":{1}", column.Key, Convert.ToInt32(column.Value)); } else if (column.Value.GetType().Equals(typeof(Double))) { rowString += String.Format("\"{0}\":{1}", column.Key, Convert.ToDouble(column.Value)); } else { rowString += String.Format("\"{0}\":\"{1}\"", column.Key, column.Value.ToString().Replace("\\", "\\\\").Replace("\"", "\\\"")); } } else { rowString += String.Format("\"{0}\":null", column.Key); } } rowsString += "{" + rowString + "}"; } resultString = "\"rows\":[" + rowsString + "]"; } catch (Exception e) { errorMessage = e.Message; } } if (batchResultsStr.Length != 0) { batchResultsStr += ","; } if (resultString.Length != 0) { batchResultsStr += "{\"qid\":\"" + transaction.queryId + "\",\"type\":\"success\",\"result\":{" + resultString + "}}"; //System.Diagnostics.Debug.WriteLine("batchResultsStr: " + batchResultsStr); } else { batchResultsStr += "{\"qid\":\"" + transaction.queryId + "\",\"type\":\"error\",\"result\":{\"message\":\"" + errorMessage.Replace("\\", "\\\\").Replace("\"", "\\\"") + "\"}}"; } } DispatchCommandResult(new PluginResult(PluginResult.Status.OK, "[" + batchResultsStr + "]"), mycbid); }//); }
public void executeSqlBatch(string options) { Deployment.Current.Dispatcher.BeginInvoke(() => { List <string> opt = JsonHelper.Deserialize <List <string> >(options); SQLitePluginExecuteSqlBatchOptions batch = JsonHelper.Deserialize <SQLitePluginExecuteSqlBatchOptions>(opt[0]); JArray batchResults = new JArray(); // check our db is not null, create a new connection if (this.db == null || !this.db.DatabasePath.Equals(dbOptions.name)) { // close open database to be safe if (this.db != null) { this.db.Close(); } // open database this.db = new SQLiteConnection(dbOptions.name); } // loop through the sql in the transaction foreach (SQLitePluginTransaction transaction in batch.executes) { JObject result = null; string errorMessage = "unknown"; bool needQuery = true; // begin if (transaction.query.StartsWith("BEGIN", StringComparison.OrdinalIgnoreCase)) { needQuery = false; try { this.db.BeginTransaction(); result = new JObject(); result.Add("rowsAffected", 0); } catch (Exception e) { errorMessage = e.Message; } } // commit if (transaction.query.StartsWith("COMMIT", StringComparison.OrdinalIgnoreCase)) { needQuery = false; try { this.db.Commit(); result = new JObject(); result.Add("rowsAffected", 0); } catch (Exception e) { errorMessage = e.Message; } } // rollback if (transaction.query.StartsWith("ROLLBACK", StringComparison.OrdinalIgnoreCase)) { needQuery = false; try { this.db.Rollback(); result = new JObject(); result.Add("rowsAffected", 0); } catch (Exception e) { errorMessage = e.Message; } } // create/drop table if (transaction.query.IndexOf("DROP TABLE", StringComparison.OrdinalIgnoreCase) > -1 || transaction.query.IndexOf("CREATE TABLE", StringComparison.OrdinalIgnoreCase) > -1) { needQuery = false; try { var results = db.Execute(transaction.query, transaction.query_params); result = new JObject(); result.Add("rowsAffected", 0); } catch (Exception e) { errorMessage = e.Message; } } // insert/update/delete if (transaction.query.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase) || transaction.query.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase) || transaction.query.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase)) { needQuery = false; try { // execute our query var res = db.Execute(transaction.query, transaction.query_params); // get the primary key of the last inserted row var insertId = SQLite3.LastInsertRowid(db.Handle); result = new JObject(); result.Add("rowsAffected", res); if (transaction.query.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase)) { result.Add("insertId", insertId); } } catch (Exception e) { errorMessage = e.Message; } } if (needQuery) { try { var results = this.db.Query2(transaction.query, transaction.query_params); JArray rows = new JArray(); foreach (SQLiteQueryRow res in results) { JObject row = new JObject(); foreach (SQLiteQueryColumn column in res.column) { if (column.Value != null) { if (column.Value.GetType().Equals(typeof(Int32))) { row.Add(column.Key, Convert.ToInt32(column.Value)); } else if (column.Value.GetType().Equals(typeof(Double))) { row.Add(column.Key, Convert.ToDouble(column.Value)); } else { row.Add(column.Key, column.Value.ToString()); } } else { row.Add(column.Key, null); } } rows.Add(row); } result = new JObject(); result.Add("rows", rows); } catch (Exception e) { errorMessage = e.Message; } } if (result != null) { JObject r = new JObject(); r.Add("qid", transaction.queryId); r.Add("type", "success"); r.Add("result", result); batchResults.Add(r); } else { JObject r = new JObject(); r.Add("qid", transaction.queryId); r.Add("type", "error"); JObject err = new JObject(); err.Add("message", errorMessage); r.Add("result", err); batchResults.Add(r); } } DispatchCommandResult(new PluginResult(PluginResult.Status.OK, batchResults.ToString())); }); }