public BaseRepository(string dbTableName, bool cacheEnable = true, SqlQueryFormatter sqlQuery = null)
{
this.dbTableName = dbTableName;
this.cacheEnable = cacheEnable;
this.cacheKey = "Cache_" + dbTableName;
this.sqlQuery = sqlQuery;
}
/// <summary>
/// Deletes items from local table that match the given query.
/// </summary>
/// <param name="query">A query to find records to delete.</param>
/// <returns>A task that completes when delete query has executed.</returns>
/// <exception cref="ArgumentNullException">You must supply a query value</exception>
public override Task DeleteAsync(MobileServiceTableQueryDescription query)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
EnsureInitialized();
var formatter = new SqlQueryFormatter(query);
string sql = formatter.FormatDelete();
return(_operationSemaphore.WaitAsync()
.ContinueWith(t =>
{
try
{
ExecuteNonQueryInternal(sql, formatter.Parameters);
}
finally
{
_operationSemaphore.Release();
}
}));
}
private SqlQueryFormatResult(SqlQueryFormatter formatter, string commandText, IReadOnlyList<TypedValue> parameterValues, Dictionary<int, int> parameterIndexToPlaceholderIndexes, Dictionary<int, int> placeholderIndexToParameterIndexes)
{
this.Formatter = formatter;
this.CommandText = commandText;
this.ParameterValues = parameterValues;
this.ParameterIndexToPlaceholderIndexes = parameterIndexToPlaceholderIndexes;
this.PlaceholderIndexToParameterIndex = placeholderIndexToParameterIndexes;
}
protected virtual IDbQuery CreateSqlQueryReturningStructureIds(IQuery query)
{
var sqlExpression = SqlExpressionBuilder.Process(query);
var formatter = new SqlQueryFormatter
{
MainStructureTable = query.StructureSchema.GetStructureTableName(),
WhereAndSortingJoins = GenerateWhereAndSortingJoins(query, sqlExpression),
WhereCriteria = GenerateWhereCriteriaString(sqlExpression)
};
var parameters = GenerateParameters(query, sqlExpression);
return(new DbQuery(formatter.Format(SqlStatements.GetSql("QueryReturningStructureIds")), parameters, query.IsCacheable));
}
public void FormatCountStatement_Works(string filter, string expected, int paramCount, string expectedParameters)
{
QueryDescription query = QueryDescription.Parse("movies", filter);
string actual = SqlQueryFormatter.FormatCountStatement(query, out Dictionary <string, object> parameters);
testLogger.WriteLine(filter);
testLogger.WriteLine(actual);
testLogger.WriteLine(JObject.FromObject(parameters).ToString(Formatting.None).Replace("\"", "\\\""));
Assert.Equal(expected, actual);
Assert.NotNull(parameters);
Assert.Equal(paramCount, parameters.Count);
Assert.Equal(expectedParameters, JObject.FromObject(parameters).ToString(Formatting.None));
}
private static void TestSqlFormatting(Func <SqlQueryFormatter, Func <string> > action, string odata, string expectedSql, params object[] parameters)
{
var query = MobileServiceTableQueryDescription.Parse("test", odata);
var formatter = new SqlQueryFormatter(query);
string sql = action(formatter)();
Assert.Equal(expectedSql, sql);
Assert.Equal(formatter.Parameters.Count, parameters.Length);
for (int i = 0; i < parameters.Length; i++)
{
string name = "@p" + (i + 1);
Assert.True(formatter.Parameters.ContainsKey(name));
Assert.Equal(parameters[i], formatter.Parameters[name]);
}
}
public SqlQueryFormatResult(SqlQueryFormatter formatter, string commandText, IReadOnlyList<TypedValue> parameterValues, IReadOnlyList<Pair<int, int>> parameterIndexToPlaceholderIndexes)
{
this.Formatter = formatter;
this.CommandText = commandText;
this.ParameterValues = parameterValues;
if (parameterIndexToPlaceholderIndexes?.Count > 0)
{
this.ParameterIndexToPlaceholderIndexes = new Dictionary<int, int>();
this.PlaceholderIndexToParameterIndex = new Dictionary<int, int>();
foreach (var value in parameterIndexToPlaceholderIndexes)
{
this.ParameterIndexToPlaceholderIndexes[value.Left] = value.Right;
this.PlaceholderIndexToParameterIndex[value.Right] = value.Left;
}
}
}
/// <summary>
/// Reads data from local store by executing the query.
/// </summary>
/// <param name="query">The query to execute on local store.</param>
/// <returns>A task that will return with results when the query finishes.</returns>
public override Task <JToken> ReadAsync(MobileServiceTableQueryDescription query)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
EnsureInitialized();
var formatter = new SqlQueryFormatter(query);
string sql = formatter.FormatSelect();
return(_operationSemaphore.WaitAsync()
.ContinueWith(t =>
{
try
{
IList <JObject> rows = ExecuteQueryInternal(query.TableName, sql, formatter.Parameters);
JToken result = new JArray(rows.ToArray());
if (query.IncludeTotalCount)
{
sql = formatter.FormatSelectCount();
IList <JObject> countRows = null;
countRows = ExecuteQueryInternal(query.TableName, sql, formatter.Parameters);
long count = countRows[0].Value <long>("count");
result = new JObject()
{
{ "results", result },
{ "count", count }
};
}
return result;
}
finally
{
_operationSemaphore.Release();
}
}));
}
/// <summary>
/// Reads data from local store by executing the query.
/// </summary>
/// <param name="query">The query to execute on local store.</param>
/// <returns>A task that will return with results when the query finishes.</returns>
public override Task<JToken> ReadAsync(MobileServiceTableQueryDescription query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
this.EnsureInitialized();
var formatter = new SqlQueryFormatter(query);
string sql = formatter.FormatSelect();
return this.operationSemaphore.WaitAsync()
.ContinueWith(t =>
{
try
{
IList<JObject> rows = this.ExecuteQuery(query.TableName, sql, formatter.Parameters);
JToken result = new JArray(rows.ToArray());
if (query.IncludeTotalCount)
{
sql = formatter.FormatSelectCount();
IList<JObject> countRows = null;
countRows = this.ExecuteQuery(query.TableName, sql, formatter.Parameters);
long count = countRows[0].Value<long>("count");
result = new JObject()
{
{ "results", result },
{ "count", count }
};
}
return result;
}
finally
{
this.operationSemaphore.Release();
}
});
}
/// <summary>
/// Deletes items from local table that match the given query.
/// </summary>
/// <param name="query">A query to find records to delete.</param>
/// <returns>A task that completes when delete query has executed.</returns>
public override Task DeleteAsync(MobileServiceTableQueryDescription query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
this.EnsureInitialized();
var formatter = new SqlQueryFormatter(query);
string sql = formatter.FormatDelete();
return this.operationSemaphore.WaitAsync()
.ContinueWith(t =>
{
try
{
this.ExecuteNonQuery(sql, formatter.Parameters);
}
finally
{
this.operationSemaphore.Release();
}
});
}
/// <summary>
/// Reads data from local store by executing the query.
/// </summary>
/// <param name="query">The query to execute on local store.</param>
/// <returns>A task that will return with results when the query finishes.</returns>
public override Task<JToken> ReadAsync(MobileServiceTableQueryDescription query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
this.EnsureInitialized();
var formatter = new SqlQueryFormatter(query);
string sql = formatter.FormatSelect();
IList<JObject> rows = this.ExecuteQuery(query.TableName, sql, formatter.Parameters);
JToken result = new JArray(rows.ToArray());
if (query.IncludeTotalCount)
{
sql = formatter.FormatSelectCount();
IList<JObject> countRows = this.ExecuteQuery(query.TableName, sql, formatter.Parameters);
long count = countRows[0].Value<long>("count");
result = new JObject()
{
{ "results", result },
{ "count", count}
};
}
return Task.FromResult(result);
}
/// <summary>
/// Deletes items from local table that match the given query.
/// </summary>
/// <param name="query">A query to find records to delete.</param>
/// <returns>A task that completes when delete query has executed.</returns>
public override Task DeleteAsync(MobileServiceTableQueryDescription query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
this.EnsureInitialized();
var formatter = new SqlQueryFormatter(query);
string sql = formatter.FormatDelete();
this.ExecuteNonQuery(sql, formatter.Parameters);
return Task.FromResult(0);
}
private static void TestSqlFormatting(Func<SqlQueryFormatter, Func<string>> action, string odata, string expectedSql, params object[] parameters)
{
var query = MobileServiceTableQueryDescription.Parse("test", odata);
var formatter = new SqlQueryFormatter(query);
string sql = action(formatter)();
Assert.AreEqual(expectedSql, sql);
Assert.AreEqual(formatter.Parameters.Count, parameters.Length);
for (int i = 0; i < parameters.Length; i++)
{
string name = "@p" + (i + 1);
Assert.IsTrue(formatter.Parameters.ContainsKey(name));
Assert.AreEqual(formatter.Parameters[name], parameters[i]);
}
}
public SqlQueryFormatResult(SqlQueryFormatter formatter, string commandText, IEnumerable<TypedValue> parameterValues, IReadOnlyList<Pair<int, int>> parameterIndexToPlaceholderIndexes)
: this(formatter, commandText, parameterValues.ToReadOnlyCollection(), parameterIndexToPlaceholderIndexes)
{
}
