public async Task UpsertAsync_UpdatesTheRow_WhenItExists() { await PrepareTodoTable(); // insert a row and make sure it is inserted using (var store = new MobileServiceSQLiteStore(TestDbName)) { DefineTestTable(store); await store.InitializeAsync(); await store.UpsertAsync(TestTable, new[] { new JObject() { { "id", "abc" }, { "text", "xyz" }, { "__createdAt", DateTime.Now } } }, ignoreMissingColumns : false); await store.UpsertAsync(TestTable, new[] { new JObject() { { "id", "abc" }, { "__createdAt", new DateTime(200, 1, 1) } } }, ignoreMissingColumns : false); JObject result = await store.LookupAsync(TestTable, "abc"); Assert.AreEqual(result.Value <string>("id"), "abc"); Assert.AreEqual(result.Value <string>("text"), "xyz"); Assert.AreEqual(result.Value <string>("__createdAt"), "01/01/0200 00:00:00"); } long count = TestUtilities.CountRows(TestDbName, TestTable); Assert.AreEqual(count, 1L); }
private static async Task <MobileServiceSQLiteStore> SetupTestTable() { if (!queryTableInitialized) { TestUtilities.DropTestTable(TestDbName, TestTable); } var store = new MobileServiceSQLiteStore(TestDbName); store.DefineTable(TestTable, new JObject() { { "col1", String.Empty }, { "col2", 0L }, { "col3", 0f }, { "col4", DateTime.UtcNow }, { "col5", false }, { "col6", Guid.Empty } }); await store.InitializeAsync(); if (!queryTableInitialized) { await store.UpsertAsync(TestTable, testData, ignoreMissingColumns : false); } queryTableInitialized = true; return(store); }
private static async Task <MobileServiceSQLiteStore> SetupMathTestTable(JObject[] mathTestData) { TestUtilities.DropTestTable(TestDbName, MathTestTable); // first create a table called todo var store = new MobileServiceSQLiteStore(TestDbName); store.DefineTable(MathTestTable, new JObject() { { "id", String.Empty }, { "val", 0f }, { "expected", 0f } }); await store.InitializeAsync(); foreach (JObject item in mathTestData) { item[MobileServiceSystemColumns.Id] = Guid.NewGuid().ToString(); } await store.UpsertAsync(MathTestTable, mathTestData, ignoreMissingColumns : false); return(store); }
public async Task Upsert_Succeeds_IfCaseIsDifferentButNameIsSame() { string tableName = "itemWithDate"; ResetDatabase(tableName); using (var store = new MobileServiceSQLiteStore(TestDbName)) { store.DefineTable(tableName, new JObject() { { "id", String.Empty }, { "date", DateTime.Now } }); await store.InitializeAsync(); await store.UpsertAsync("ITEMwithDATE", new[] { new JObject() { { "ID", Guid.NewGuid() }, { "dATE", DateTime.UtcNow } } }, ignoreMissingColumns : false); } }
public async Task SyncAsync() { ReadOnlyCollection <MobileServiceTableOperationError> syncErrors = null; try { //await this._client.SyncContext.PushAsync(); //_productTable.DeleteAsync(); //var productList = await _productTable.ToListAsync(); //productList.ForEach(p => _productTable.DeleteAsync(p)); var deleteQuery = new Microsoft.WindowsAzure.MobileServices.Query.MobileServiceTableQueryDescription("Product"); await _store.DeleteAsync(deleteQuery); var productList = await GetProductsAsyncAPI(); //var productTableQuery = this._productTable.CreateQuery(); await _store.UpsertAsync("Product", productList, true); //_client.SyncContext.Store. //await this._productTable.PullAsync( // //The first parameter is a query name that is used internally by the client SDK to implement incremental sync. // //Use a different query name for each unique query in your program // "availableProducts", productTableQuery); } catch (MobileServicePushFailedException exc) { if (exc.PushResult != null) { syncErrors = exc.PushResult.Errors; } } catch (Exception ex) { } // Simple error/conflict handling. A real application would handle the various errors like network conditions, // server conflicts and others via the IMobileServiceSyncHandler. if (syncErrors != null) { foreach (var error in syncErrors) { if (error.OperationKind == MobileServiceTableOperationKind.Update && error.Result != null) { //Update failed, reverting to server's copy. await error.CancelAndUpdateItemAsync(error.Result); } else { // Discard local change. await error.CancelAndDiscardItemAsync(); } Debug.WriteLine(@"Error executing sync operation. Item: {0} ({1}). Operation discarded.", error.TableName, error.Item["id"]); } } }
public async Task UpsertAsync_DoesNotThrow_WhenItemIsEmpty() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { store.DefineTable(TestTable, new JObject() { { "id", String.Empty }, { "dob", DateTime.UtcNow } }); await store.InitializeAsync(); await store.UpsertAsync(TestTable, new[] { new JObject() }, ignoreMissingColumns : true); await store.UpsertAsync(TestTable, new[] { new JObject() }, ignoreMissingColumns : false); } }
public async Task UpsertAsync_CanProcessManyRecordsAtOnce() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { var template = new JObject { { "id", 0 }, { "value1", "Hello, world" }, { "value2", "Hello, world" }, { "value3", "Hello, world" }, { "value4", "Hello, world" }, { "value5", "Hello, world" } }; store.DefineTable(TestTable, template); //create the table await store.InitializeAsync(); //add a whole bunch of items. We want {number of items} * {number of fields} to exceed sqlite's parameter limit const int insertedItemCount = 500; var itemsToInsert = Enumerable.Range(1, insertedItemCount) .Select(id => { var o = new JObject(template) { ["id"] = id }; return(o); }) .ToArray(); //Insert the items await store.UpsertAsync(TestTable, itemsToInsert, ignoreMissingColumns : false); JArray records = (JArray)await store.ReadAsync(MobileServiceTableQueryDescription.Parse(TestTable, "$orderby=id")); //Verify that all 500 records were inserted Assert.Equal(records.Count, insertedItemCount); //Verify that all fields are intact for (var i = 0; i < insertedItemCount; i++) { Assert.True(JToken.DeepEquals(itemsToInsert[i], records[i]), "Results retrieved from DB do not match input"); } } }
public async Task UpsertAsync_ThenReadAsync_AllTypes() { TestUtilities.DropTestTable(TestDbName, TestTable); // first create a table called todo using (MobileServiceSQLiteStore store = new MobileServiceSQLiteStore(TestDbName)) { store.DefineTable(TestTable, JObjectTypes.GetObjectWithAllTypes()); await store.InitializeAsync(); var upserted = new JObject() { { "id", "xyz" }, { "Object", new JObject() { { "id", "abc" } } }, { "Array", new JArray() { new JObject() { { "id", 3 } } } }, { "Integer", 123L }, { "Float", 12.5m }, { "String", "def" }, { "Boolean", true }, { "Date", new DateTime(2003, 5, 6, 4, 5, 1, DateTimeKind.Utc) }, { "Bytes", new byte[] { 1, 2, 3 } }, { "Guid", new Guid("AB3EB1AB-53CD-4780-928B-A7E1CB7A927C") }, { "TimeSpan", new TimeSpan(1234) } }; await store.UpsertAsync(TestTable, new[] { upserted }, false); var query = new MobileServiceTableQueryDescription(TestTable); var items = await store.ReadAsync(query) as JArray; Assert.IsNotNull(items); Assert.AreEqual(items.Count, 1); var lookedup = items.First as JObject; Assert.AreEqual(upserted.ToString(Formatting.None), lookedup.ToString(Formatting.None)); } }
public async Task UpsertAsync_InsertsTheRow_WhenItemHasNullValues() { TestUtilities.DropTestTable(TestDbName, TestTable); // insert a row and make sure it is inserted using (var store = new MobileServiceSQLiteStore(TestDbName)) { store.DefineTable(TestTable, new JObject() { { "id", String.Empty }, { "dob", DateTime.UtcNow }, { "age", 0 }, { "weight", 3.5 }, { "code", Guid.NewGuid() }, { "options", new JObject() { } }, { "friends", new JArray() { } }, { "__version", String.Empty } }); await store.InitializeAsync(); var inserted = new JObject() { { "id", "abc" }, { "dob", null }, { "age", null }, { "weight", null }, { "code", null }, { "options", null }, { "friends", null }, { "__version", null } }; await store.UpsertAsync(TestTable, new[] { inserted }, ignoreMissingColumns : false); JObject read = await store.LookupAsync(TestTable, "abc"); Assert.AreEqual(inserted.ToString(), read.ToString()); } }
public async Task UpsertAsync_InsertsTheRow_WhenItDoesNotExist() { await PrepareTodoTable(); // insert a row and make sure it is inserted using (var store = new MobileServiceSQLiteStore(TestDbName)) { DefineTestTable(store); await store.InitializeAsync(); await store.UpsertAsync(TestTable, new[] { new JObject() { { "id", "abc" }, { "__createdAt", DateTime.Now } } }, ignoreMissingColumns : false); } long count = TestUtilities.CountRows(TestDbName, TestTable); Assert.AreEqual(count, 1L); }
public async Task UpsertAsync_Throws_WhenColumnInItemIsNotDefinedAndItIsLocal() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { store.DefineTable(TestTable, new JObject() { { "id", String.Empty }, { "dob", DateTime.UtcNow } }); await store.InitializeAsync(); var ex = await ThrowsAsync <InvalidOperationException>(() => store.UpsertAsync(TestTable, new[] { new JObject() { { "notDefined", "okok" } } }, ignoreMissingColumns: false)); Assert.AreEqual(ex.Message, "Column with name 'notDefined' is not defined on the local table 'todo'."); } }
public async Task <JsonResult> Get(string username) { try { MobileServiceSQLiteStore Store = new MobileServiceSQLiteStore("local.db"); Store.DefineTable <Player>(); await Store.InitializeAsync(); var result = await Store.LookupAsync("Player", username); Player p = null; try { p = JsonConvert.DeserializeObject <Player>(result.ToString()); p.Roundssurvived++; } catch { } if (p == null) { p = new Player() { id = username, Roundssurvived = 1 }; } await Store.UpsertAsync("Player", new List <JObject>() { JObject.FromObject(p) }, true); } catch (Exception ex) { return(new JsonResult(ex.Message)); } return(new JsonResult("200")); }
public async Task Upsert_ThenLookup_ThenUpsert_ThenDelete_ThenLookup() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { // define item with all type of supported fields var originalItem = new JObject() { { "id", "abc" }, { "bool", true }, { "int", 45 }, { "double", 123.45d }, { "guid", Guid.NewGuid() }, { "date", testDate }, { "options", new JObject(){ {"class", "A"} } }, { "friends", new JArray(){ "Eric", "Jeff" } } }; store.DefineTable(TestTable, originalItem); // create the table await store.InitializeAsync(); // first add an item await store.UpsertAsync(TestTable, new[] { originalItem }, ignoreMissingColumns: false); // read the item back JObject itemRead = await store.LookupAsync(TestTable, "abc"); // make sure everything was persisted the same Assert.AreEqual(originalItem.ToString(), itemRead.ToString()); // change the item originalItem["double"] = 111.222d; // upsert the item await store.UpsertAsync(TestTable, new[] { originalItem }, ignoreMissingColumns: false); // read the updated item JObject updatedItem = await store.LookupAsync(TestTable, "abc"); // make sure the float was updated Assert.AreEqual(updatedItem.Value<double>("double"), 111.222d); // make sure the item is same as updated item Assert.AreEqual(originalItem.ToString(), updatedItem.ToString()); // make sure item is not same as its initial state Assert.AreNotEqual(originalItem.ToString(), itemRead.ToString()); // now delete the item await store.DeleteAsync(TestTable, new[] { "abc" }); // now read it back JObject item4 = await store.LookupAsync(TestTable, "abc"); // it should be null because it doesn't exist Assert.IsNull(item4); } }
public async Task Upsert_ThenLookup_ThenUpsert_ThenDelete_ThenLookup() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { // define item with all type of supported fields var originalItem = new JObject() { { "id", "abc" }, { "bool", true }, { "int", 45 }, { "double", 123.45d }, { "guid", Guid.NewGuid() }, { "date", testDate }, { "options", new JObject() { { "class", "A" } } }, { "friends", new JArray() { "Eric", "Jeff" } } }; store.DefineTable(TestTable, originalItem); // create the table await store.InitializeAsync(); // first add an item await store.UpsertAsync(TestTable, new[] { originalItem }, ignoreMissingColumns : false); // read the item back JObject itemRead = await store.LookupAsync(TestTable, "abc"); // make sure everything was persisted the same Assert.AreEqual(originalItem.ToString(), itemRead.ToString()); // change the item originalItem["double"] = 111.222d; // upsert the item await store.UpsertAsync(TestTable, new[] { originalItem }, ignoreMissingColumns : false); // read the updated item JObject updatedItem = await store.LookupAsync(TestTable, "abc"); // make sure the float was updated Assert.AreEqual(updatedItem.Value <double>("double"), 111.222d); // make sure the item is same as updated item Assert.AreEqual(originalItem.ToString(), updatedItem.ToString()); // make sure item is not same as its initial state Assert.AreNotEqual(originalItem.ToString(), itemRead.ToString()); // now delete the item await store.DeleteAsync(TestTable, new[] { "abc" }); // now read it back JObject item4 = await store.LookupAsync(TestTable, "abc"); // it should be null because it doesn't exist Assert.IsNull(item4); } }
public async Task <int> SaveItemAsync(Patient item) { await database.UpsertAsync("Patient", new List <JObject>() { JObject.FromObject(item) }, true); return(200); }
public async Task UpsertAsync_Throws_WhenColumnInItemIsNotDefinedAndItIsLocal() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { store.DefineTable(TestTable, new JObject() { { "id", String.Empty }, { "dob", DateTime.UtcNow } }); await store.InitializeAsync(); var ex = await ThrowsAsync<InvalidOperationException>(() => store.UpsertAsync(TestTable, new[] { new JObject() { { "notDefined", "okok" } } }, ignoreMissingColumns: false)); Assert.AreEqual(ex.Message, "Column with name 'notDefined' is not defined on the local table 'todo'."); } }
public async Task UpsertAsync_Throws_WhenInsertingRecordsWhichAreTooLarge() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { var template = new JObject { { "id", 0 }, }; //SQLite limits us to 999 "parameters" per prepared statement for (var i = 0; i < 1000; i++) { template["column" + i] = "Hello, world"; } store.DefineTable(TestTable, template); //create the table await store.InitializeAsync(); //attempt to insert a couple of items var item1 = new JObject(template); item1["id"] = 1; var item2 = new JObject(template); item1["id"] = 2; InvalidOperationException ex = await AssertEx.Throws <InvalidOperationException>(() => store.UpsertAsync(TestTable, new[] { item1, item2 }, ignoreMissingColumns: false)); Assert.AreEqual("The number of fields per entity in an upsert operation is limited to 800.", ex.Message); } }
public async Task UpsertAsync_InsertsTheRow_WhenItemHasNullValues() { TestUtilities.DropTestTable(TestDbName, TestTable); // insert a row and make sure it is inserted using (var store = new MobileServiceSQLiteStore(TestDbName)) { store.DefineTable(TestTable, new JObject() { { "id", String.Empty }, { "dob", DateTime.UtcNow }, { "age", 0}, { "weight", 3.5 }, { "code", Guid.NewGuid() }, { "options", new JObject(){} }, { "friends", new JArray(){} }, { "version", String.Empty } }); await store.InitializeAsync(); var inserted = new JObject() { { "id", "abc" }, { "dob", null }, { "age", null }, { "weight", null }, { "code", null }, { "options", null }, { "friends", null }, { "version", null } }; await store.UpsertAsync(TestTable, new[] { inserted }, ignoreMissingColumns: false); JObject read = await store.LookupAsync(TestTable, "abc"); Assert.AreEqual(inserted.ToString(), read.ToString()); } }
public async Task UpsertAsync_DoesNotThrow_WhenItemIsEmpty() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { store.DefineTable(TestTable, new JObject() { { "id", String.Empty }, { "dob", DateTime.UtcNow } }); await store.InitializeAsync(); await store.UpsertAsync(TestTable, new[] { new JObject() }, ignoreMissingColumns: true); await store.UpsertAsync(TestTable, new[] { new JObject() }, ignoreMissingColumns: false); } }
public async Task UpsertAsync_InsertsTheRow_WhenItDoesNotExist() { await PrepareTodoTable(); // insert a row and make sure it is inserted using (var store = new MobileServiceSQLiteStore(TestDbName)) { DefineTestTable(store); await store.InitializeAsync(); await store.UpsertAsync(TestTable, new[]{new JObject() { { "id", "abc" }, { "createdAt", DateTime.Now } }}, ignoreMissingColumns: false); } long count = TestUtilities.CountRows(TestDbName, TestTable); Assert.AreEqual(count, 1L); }
public async Task UpsertAsync_UpdatesTheRow_WhenItExists() { await PrepareTodoTable(); // insert a row and make sure it is inserted using (var store = new MobileServiceSQLiteStore(TestDbName)) { DefineTestTable(store); await store.InitializeAsync(); await store.UpsertAsync(TestTable, new[]{new JObject() { { "id", "abc" }, { "text", "xyz" }, { "createdAt", DateTime.Now } }}, ignoreMissingColumns: false); await store.UpsertAsync(TestTable, new[]{new JObject() { { "id", "abc" }, { "createdAt", new DateTime(200,1,1) } }}, ignoreMissingColumns: false); JObject result = await store.LookupAsync(TestTable, "abc"); Assert.AreEqual(result.Value<string>("id"), "abc"); Assert.AreEqual(result.Value<string>("text"), "xyz"); Assert.AreEqual(result.Value<string>("createdAt"), "01/01/0200 00:00:00"); } long count = TestUtilities.CountRows(TestDbName, TestTable); Assert.AreEqual(count, 1L); }
public async Task UpsertAsync_Throws_WhenInsertingRecordsWhichAreTooLarge() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { var template = new JObject { { "id", 0 }, }; //SQLite limits us to 999 "parameters" per prepared statement for (var i = 0; i < 1000; i++) { template["column" + i] = "Hello, world"; } store.DefineTable(TestTable, template); //create the table await store.InitializeAsync(); //attempt to insert a couple of items var item1 = new JObject(template); item1["id"] = 1; var item2 = new JObject(template); item1["id"] = 2; InvalidOperationException ex = await AssertEx.Throws<InvalidOperationException>(() => store.UpsertAsync(TestTable, new[] { item1, item2 }, ignoreMissingColumns: false)); Assert.AreEqual("The number of fields per entity in an upsert operation is limited to 800.", ex.Message); } }
public async Task UpsertAsync_ThenReadAsync_AllTypes() { TestUtilities.DropTestTable(TestDbName, TestTable); // first create a table called todo using (MobileServiceSQLiteStore store = new MobileServiceSQLiteStore(TestDbName)) { store.DefineTable(TestTable, JObjectTypes.GetObjectWithAllTypes()); await store.InitializeAsync(); var upserted = new JObject() { { "id", "xyz" }, { "Object", new JObject() { {"id", "abc"} }}, { "Array", new JArray() { new JObject(){{"id", 3}} } }, { "Integer", 123L }, { "Float", 12.5m }, { "String", "def" }, { "Boolean", true }, { "Date", new DateTime(2003, 5, 6, 4, 5, 1, DateTimeKind.Utc) }, { "Bytes", new byte[] { 1, 2, 3} }, { "Guid", new Guid("AB3EB1AB-53CD-4780-928B-A7E1CB7A927C") }, { "TimeSpan", new TimeSpan(1234) } }; await store.UpsertAsync(TestTable, new[] { upserted }, false); var query = new MobileServiceTableQueryDescription(TestTable); var items = await store.ReadAsync(query) as JArray; Assert.IsNotNull(items); Assert.AreEqual(items.Count, 1); var lookedup = items.First as JObject; Assert.AreEqual(upserted.ToString(Formatting.None), lookedup.ToString(Formatting.None)); } }
public async Task UpsertAsync_CanProcessManyRecordsAtOnce() { TestUtilities.DropTestTable(TestDbName, TestTable); using (var store = new MobileServiceSQLiteStore(TestDbName)) { var template = new JObject { { "id", 0 }, { "value1", "Hello, world" }, { "value2", "Hello, world" }, { "value3", "Hello, world" }, { "value4", "Hello, world" }, { "value5", "Hello, world" } }; store.DefineTable(TestTable, template); //create the table await store.InitializeAsync(); //add a whole bunch of items. We want {number of items} * {number of fields} to exceed sqlite's parameter limit const int insertedItemCount = 500; var itemsToInsert = Enumerable.Range(1, insertedItemCount) .Select(id => { var o = new JObject(template); o["id"] = id; return o; }) .ToArray(); //Insert the items await store.UpsertAsync(TestTable, itemsToInsert, ignoreMissingColumns: false); JArray records = (JArray)await store.ReadAsync(MobileServiceTableQueryDescription.Parse(TestTable, "$orderby=id")); //Verify that all 500 records were inserted Assert.AreEqual(records.Count, insertedItemCount); //Verify that all fields are intact for (var i = 0; i < insertedItemCount; i++) { Assert.IsTrue(JToken.DeepEquals(itemsToInsert[i], records[i]), "Results retrieved from DB do not match input"); } } }