/// <summary>
/// Performs a database Get operation and returns all of the items that correspond to the specified criteria after building an Sql Query.
/// </summary>
/// <param name="criteria">The criteria for the where clause.</param>
/// <returns>The list of T objects matching the criteria</returns>
public List <T> FindAllSql(Dictionary <string, object> criteria)
{
List <T> listObj = new List <T>();
Dictionary <string, object> parameters = new Dictionary <string, object>();
StringBuilder whereClause = new StringBuilder();
foreach (KeyValuePair <string, object> set in criteria)
{
if (whereClause.Length > 0)
{
whereClause.Append(" and ");
}
var list = set.Value as List <string>;
if (list != null)
{
whereClause.Append(set.Key + " IN (");
int lastIndex = list.Count;
for (int i = 0; i < lastIndex; i++)
{
whereClause.Append("'" + list[i] + "'");
if (i != lastIndex - 1)
{
whereClause.Append(",");
}
}
whereClause.Append(")");
}
else
{
parameters.Add("@" + set.Key, set.Value);
whereClause.Append(set.Key + "=" + "@" + set.Key);
}
}
string sqlQuery = $"Select * from {TableLocal.TableName}";
if (!string.IsNullOrEmpty(whereClause.ToString()))
{
sqlQuery += $" where {whereClause}";
}
List <JObject> result = _store.ExecuteQueryAsync(TableLocal.TableName, sqlQuery, parameters).Result.ToList();
foreach (var obj in result)
{
listObj.Add(JsonConvert.DeserializeObject <T>(obj.ToString()));
}
return(listObj);
}
/// <summary>
/// Performs a database Get operation and returns all of the items that correspond to the specified criteria after building an Sql Query.
/// </summary>
/// <param name="criteria">The criteria for the where clause.</param>
/// <returns>The list of T objects matching the criteria</returns>
public List <T> FindAllSql(Dictionary <string, object> criteria)
{
List <T> listObj = new List <T>();
Dictionary <string, object> parameters = new Dictionary <string, object>();
StringBuilder whereClause = new StringBuilder();
foreach (KeyValuePair <string, object> set in criteria)
{
parameters.Add("@" + set.Key, set.Value);
if (whereClause.Length > 0)
{
whereClause.Append(" and ");
}
whereClause.Append(set.Key + "=" + "@" + set.Key);
}
string sqlQuery = $"Select * from {TableLocal.TableName}";
if (!string.IsNullOrEmpty(whereClause.ToString()))
{
sqlQuery += $" where {whereClause}";
}
List <JObject> result = _store.ExecuteQueryAsync(TableLocal.TableName, sqlQuery, parameters).Result.ToList();
foreach (var obj in result)
{
listObj.Add(JsonConvert.DeserializeObject <T>(obj.ToString()));
}
return(listObj);
}
public async Task <JsonResult> Get()
{
try
{
MobileServiceSQLiteStore Store = new MobileServiceSQLiteStore("local.db");
Store.DefineTable <Player>();
await Store.InitializeAsync();
var result = await Store.ExecuteQueryAsync("Player", "Select * From Player Order By Roundssurvived DESC", new Dictionary <string, object>());
return(new JsonResult(result));
}
catch (Exception ex)
{
return(new JsonResult(ex.Message));
}
}
public async Task ExecuteQueryAsync_ExecutesQuery()
{
await PrepareTodoTable();
// insert rows and make sure they are inserted
TestUtilities.ExecuteNonQuery(TestDbName, "INSERT INTO todo (id, createdAt) VALUES ('abc', 1), ('def', 2), ('ghi', 3)");
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 3L);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
var result = await store.ExecuteQueryAsync(TestTable, $"SELECT COUNT(1) FROM {TestTable}", null);
Assert.IsNotNull(result);
Assert.AreEqual(result.Count, 1);
var item = result.FirstOrDefault();
var jsonCount = item["COUNT(1)"];
Assert.AreEqual(jsonCount.ToString(), "3");
}
}
