/// <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"); } }