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_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 LookupAsync_ReadsItem()
{
await PrepareTodoTable();
long date = (long)(testDate - epoch).TotalSeconds;
// insert a row and make sure it is inserted
TestUtilities.ExecuteNonQuery(TestDbName, "INSERT INTO todo (id, createdAt) VALUES ('abc', " + date + ")");
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 1L);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
JObject item = await store.LookupAsync(TestTable, "abc");
Assert.IsNotNull(item);
Assert.AreEqual(item.Value<string>("id"), "abc");
Assert.AreEqual(item.Value<DateTime>("createdAt"), testDate);
}
}
public async Task LookupAsync_ReadsItem()
{
await PrepareTodoTable();
long date = (long)(testDate - epoch).TotalSeconds;
// insert a row and make sure it is inserted
TestUtilities.ExecuteNonQuery(TestDbName, "INSERT INTO todo (id, __createdAt) VALUES ('abc', " + date + ")");
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.AreEqual(count, 1L);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
JObject item = await store.LookupAsync(TestTable, "abc");
Assert.IsNotNull(item);
Assert.AreEqual(item.Value <string>("id"), "abc");
Assert.AreEqual(item.Value <DateTime>("__createdAt"), testDate);
}
}
public async Task LookupAsync_ReadsItem()
{
await PrepareTodoTable();
string date = testDate.ToString();
// insert a row and make sure it is inserted
TestUtilities.ExecuteNonQuery(TestDbName, "INSERT INTO todo (id, createdAt) VALUES ('abc', '" + date + "')");
long count = TestUtilities.CountRows(TestDbName, TestTable);
Assert.Equal(1L, count);
using (var store = new MobileServiceSQLiteStore(TestDbName))
{
DefineTestTable(store);
await store.InitializeAsync();
JObject item = await store.LookupAsync(TestTable, "abc");
Assert.NotNull(item);
Assert.Equal("abc", item.Value <string>("id"));
Assert.Equal(item.Value <DateTime>("createdAt"), testDate);
}
}
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 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_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<List<Patient>> GetItemsNotDoneAsync()
* {
* return await database.ExecuteQueryAsync("Patient", "SELECT * FROM [Patient] WHERE [Done] = 0");
* }*/
public async Task <Patient> GetItemAsync(Guid id)
{
var patient = await database.LookupAsync("Patient", id.ToString());
return(JsonConvert.DeserializeObject <Patient>(patient.ToString()));
}
