/// <summary>
/// Executes a sql statement on a given table in local SQL database.
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <param name="sql">The SQL query to execute.</param>
/// <param name="parameters">The query parameters.</param>
/// <returns>The result of query.</returns>
protected virtual IList <JObject> ExecuteQuery(string tableName, string sql, IDictionary <string, object> parameters)
{
TableDefinition table = GetTable(tableName);
return(this.ExecuteQuery(table, sql, parameters));
}
private Task UpsertAsyncInternal(string tableName, IEnumerable <JObject> items, bool fromServer)
{
TableDefinition table = GetTable(tableName);
var first = items.FirstOrDefault();
if (first == null)
{
return(Task.FromResult(0));
}
// Get the columns which we want to map into the database.
var columns = new List <ColumnDefinition>();
foreach (var prop in first.Properties())
{
ColumnDefinition column;
// If the column is coming from the server we can just ignore it,
// otherwise, throw to alert the caller that they have passed an invalid column
if (!table.TryGetValue(prop.Name, out column) && !fromServer)
{
throw new InvalidOperationException(string.Format(Properties.Resources.SqlStore_ColumnNotDefined, prop.Name, tableName));
}
if (column != null)
{
columns.Add(column);
}
}
if (columns.Count == 0)
{
// no query to execute if there are no columns in the table
return(Task.FromResult(0));
}
var mergeIntoSql = String.Format("MERGE INTO {0} AS Target ", SqlHelpers.FormatTableName(tableName));
var mergeAsSourceSql = string.Format(" AS SOURCE ({0}) ", String.Join(", ", columns.Select(c => c.Name).Select(SqlHelpers.FormatMember)));
var mergeConditionSql = string.Format(" ON Target.{0} = Source.{1} ", MobileServiceSystemColumns.Id, MobileServiceSystemColumns.Id);
var whenMatchedSql = String.Format(" WHEN MATCHED THEN UPDATE SET {0} ", String.Join(", ", columns.Select(c => c.Name).Select(c => SqlHelpers.FormatMember(c) + " = " + "Source." + SqlHelpers.FormatMember(c))));
var whenNotMatchedSql = String.Format(" WHEN NOT MATCHED BY TARGET THEN INSERT ({0}) VALUES ({1}); ",
String.Join(", ", columns.Select(c => c.Name).Select(SqlHelpers.FormatMember)),
String.Join(", ", columns.Select(c => " Source." + SqlHelpers.FormatMember(c.Name))));
// Use int division to calculate how many times this record will fit into our parameter quota
int batchSize = MaxParametersPerUpsertQuery / columns.Count;
if (batchSize == 0)
{
throw new InvalidOperationException(string.Format(Properties.Resources.SqlStore_TooManyColumns, MaxParametersPerUpsertQuery));
}
foreach (var batch in items.Split(maxLength: batchSize))
{
var mergeValues = new StringBuilder();
var parameters = new Dictionary <string, object>();
foreach (JObject item in batch)
{
AppendInsertValuesSql(mergeValues, parameters, columns, item);
mergeValues.Append(",");
}
if (parameters.Any())
{
mergeValues.Remove(mergeValues.Length - 1, 1); // remove the trailing comma
var upsertSql = mergeIntoSql
+ string.Format("USING (VALUES {0})", mergeValues)
+ mergeAsSourceSql
+ mergeConditionSql
+ whenMatchedSql
+ whenNotMatchedSql;
this.ExecuteNonQuery(upsertSql, parameters);
}
}
return(Task.FromResult(0));
}
