private void BatchInsert(string tableName, IEnumerable <JObject> items, List <ColumnDefinition> columns)
{
if (columns.Count == 0) // we need to have some columns to insert the item
{
return;
}
// Generate the prepared insert statement
string sqlBase =
$"INSERT OR IGNORE INTO {SqlHelpers.FormatTableName(tableName)} ({String.Join(", ", columns.Select(c => c.Name).Select(SqlHelpers.FormatMember))}) VALUES ";
// Use int division to calculate how many times this record will fit into our parameter quota
int batchSize = ValidateParameterCount(columns.Count);
foreach (var batch in items.Split(maxLength: batchSize))
{
var sql = new StringBuilder(sqlBase);
var parameters = new Dictionary <string, object>();
foreach (JObject item in batch)
{
AppendInsertValuesSql(sql, parameters, columns, item);
sql.Append(",");
}
if (parameters.Any())
{
sql.Remove(sql.Length - 1, 1); // remove the trailing comma
ExecuteNonQueryInternal(sql.ToString(), parameters);
}
}
}
/// <summary>
/// Executes a lookup against a local table.
/// </summary>
/// <param name="tableName">Name of the local table.</param>
/// <param name="id">The id of the item to lookup.</param>
/// <returns>A task that will return with a result when the lookup finishes.</returns>
public override Task <JObject> LookupAsync(string tableName, string id)
{
if (tableName == null)
{
throw new ArgumentNullException(nameof(tableName));
}
if (id == null)
{
throw new ArgumentNullException(nameof(id));
}
EnsureInitialized();
string sql =
$"SELECT * FROM {SqlHelpers.FormatTableName(tableName)} WHERE {MobileServiceSystemColumns.Id} = @id";
var parameters = new Dictionary <string, object>
{
{ "@id", id }
};
return(_operationSemaphore.WaitAsync()
.ContinueWith(t =>
{
try
{
IList <JObject> results = ExecuteQueryInternal(tableName, sql, parameters);
return results.FirstOrDefault();
}
finally
{
_operationSemaphore.Release();
}
}));
}
internal virtual void CreateTableFromObject(string tableName, IEnumerable <ColumnDefinition> columns)
{
ColumnDefinition idColumn = columns.FirstOrDefault(c => c.Name.Equals(MobileServiceSystemColumns.Id));
var colDefinitions = columns.Where(c => c != idColumn).Select(c =>
$"{SqlHelpers.FormatMember(c.Name)} {c.StoreType}").ToList();
if (idColumn != null)
{
colDefinitions.Insert(0, $"{SqlHelpers.FormatMember(idColumn.Name)} {idColumn.StoreType} PRIMARY KEY");
}
String tblSql =
$"CREATE TABLE IF NOT EXISTS {SqlHelpers.FormatTableName(tableName)} ({String.Join(", ", colDefinitions)})";
ExecuteNonQueryInternal(tblSql, parameters: null);
string infoSql = $"PRAGMA table_info({SqlHelpers.FormatTableName(tableName)});";
IDictionary <string, JObject> existingColumns = ExecuteQueryInternal("table_info", (TableDefinition)null, infoSql, parameters: null)
.ToDictionary(c => c.Value <string>("name"), StringComparer.OrdinalIgnoreCase);
// new columns that do not exist in existing columns
var columnsToCreate = columns.Where(c => !existingColumns.ContainsKey(c.Name));
foreach (ColumnDefinition column in columnsToCreate)
{
string createSql =
$"ALTER TABLE {SqlHelpers.FormatTableName(tableName)} ADD COLUMN {SqlHelpers.FormatMember(column.Name)} {column.StoreType}";
ExecuteNonQueryInternal(createSql, parameters: null);
}
// NOTE: In SQLite you cannot drop columns, only add them.
}
private void BatchUpdate(string tableName, IEnumerable <JObject> items, List <ColumnDefinition> columns)
{
if (columns.Count <= 1)
{
return; // For update to work there has to be at least once column besides Id that needs to be updated
}
ValidateParameterCount(columns.Count);
string sqlBase = $"UPDATE {SqlHelpers.FormatTableName(tableName)} SET ";
foreach (JObject item in items)
{
var sql = new StringBuilder(sqlBase);
var parameters = new Dictionary <string, object>();
ColumnDefinition idColumn = columns.FirstOrDefault(c => c.Name.Equals(MobileServiceSystemColumns.Id));
if (idColumn == null)
{
continue;
}
foreach (var column in columns.Where(c => c != idColumn))
{
string paramName = AddParameter(item, parameters, column);
sql.AppendFormat("{0} = {1}", SqlHelpers.FormatMember(column.Name), paramName);
sql.Append(",");
}
if (parameters.Any())
{
sql.Remove(sql.Length - 1, 1); // remove the trailing comma
}
sql.AppendFormat(" WHERE {0} = {1}", SqlHelpers.FormatMember(MobileServiceSystemColumns.Id), AddParameter(item, parameters, idColumn));
ExecuteNonQueryInternal(sql.ToString(), parameters);
}
}
/// <summary>
/// Deletes items from local table with the given list of ids
/// </summary>
/// <param name="tableName">Name of the local table.</param>
/// <param name="ids">A list of ids of the items to be deleted</param>
/// <returns>A task that completes when delete query has executed.</returns>
public override Task DeleteAsync(string tableName, IEnumerable <string> ids)
{
if (tableName == null)
{
throw new ArgumentNullException(nameof(tableName));
}
if (ids == null)
{
throw new ArgumentNullException(nameof(ids));
}
EnsureInitialized();
string idRange = String.Join(",", ids.Select((_, i) => "@id" + i));
string sql =
$"DELETE FROM {SqlHelpers.FormatTableName(tableName)} WHERE {MobileServiceSystemColumns.Id} IN ({idRange})";
var parameters = new Dictionary <string, object>();
int j = 0;
foreach (string id in ids)
{
parameters.Add("@id" + (j++), id);
}
return(_operationSemaphore.WaitAsync()
.ContinueWith(t =>
{
try
{
ExecuteNonQueryInternal(sql, parameters);
}
finally
{
_operationSemaphore.Release();
}
}));
}