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()));
});
}
