public void Update_SingleRow(bool explicitConnection)
{
var categories = new Category(ProviderName, explicitConnection);
DbConnection connection = null;
if (explicitConnection)
{
MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnection(categories);
connection = categories.OpenConnection(WhenDevart.AddLicenseKey(ProviderName, MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.WriteTestConnection, ProviderName)));
}
using (connection)
{
// insert something to update first.
var inserted = categories.Insert(new { CategoryName = "Cool stuff", Description = "You know... cool stuff! Cool. n. stuff." }, connection: connection);
int insertedCategoryID = inserted.CategoryID;
Assert.IsTrue(insertedCategoryID > 0);
// update it, with a better description
inserted.Description = "This is all jolly marvellous";
Assert.AreEqual(1, categories.Update(connection, inserted), "Update should have affected 1 row");
var updatedRow = categories.Single(new { inserted.CategoryID }, connection: connection);
Assert.IsNotNull(updatedRow);
Assert.AreEqual(inserted.CategoryID, Convert.ToInt32(updatedRow.CategoryID)); // convert from uint
Assert.AreEqual(inserted.Description, updatedRow.Description);
// reset description to NULL
updatedRow.Description = null;
Assert.AreEqual(1, categories.Update(connection, updatedRow), "Update should have affected 1 row");
var newUpdatedRow = categories.Single(new { updatedRow.CategoryID }, connection: connection);
Assert.IsNotNull(newUpdatedRow);
Assert.AreEqual(updatedRow.CategoryID, newUpdatedRow.CategoryID);
Assert.AreEqual(updatedRow.Description, newUpdatedRow.Description);
}
}
public async Task Devart_ParameterCheck(bool explicitConnection)
{
var db = new SPTestsDatabase(ProviderName, explicitConnection);
if (explicitConnection)
{
MightyTests.ConnectionStringUtils.CheckConnectionStringRequiredForOpenConnectionAsync(db);
}
dynamic result;
using (var connection = await db.OpenConnectionAsync(
explicitConnection ?
WhenDevart.AddLicenseKey(ProviderName, MightyTests.ConnectionStringUtils.GetConnectionString(TestConstants.ReadTestConnection, ProviderName)) :
null))
{
using (var command = db.CreateCommandWithParams("testproc_in_out", isProcedure: true, connection: connection))
{
// uses a dynamic cast to set a provider-specific property without explicitly depending on the provider library
((dynamic)command).ParameterCheck = true;
// Devart-specific: makes a round-trip to the database to fetch the parameter names
command.Prepare();
command.Parameters["param1"].Value = 10;
await db.ExecuteAsync(command, connection : connection);
result = db.ResultsAsExpando(command);
}
}
Assert.AreEqual(20, result.param2);
}
public async Task Use_GlobalConnectionString()
{
MightyOrm.GlobalConnectionString = WhenDevart.AddLicenseKey(ProviderName, string.Format(TestConstants.ReadTestConnection, ProviderName));
dynamic film = new MightyOrm(tableName: "sakila.film");
var singleInstance = await film.SingleAsync(new { film_id = 43 });
Assert.AreEqual(43, singleInstance.film_id);
}
public async Task Guid_Arg()
{
// MySQL has native Guid parameter support, but the SELECT output is a string
var db = new MightyOrm(WhenDevart.AddLicenseKey(ProviderName, string.Format(TestConstants.ReadTestConnection, ProviderName)));
var guid = Guid.NewGuid();
dynamic item;
using (var command = db.CreateCommand("SELECT @0 AS val", null, guid))
{
Assert.AreEqual(DbType.Guid, command.Parameters[0].DbType);
item = await db.SingleAsync(command);
}
Assert.AreEqual(typeof(string), item.val.GetType());
Assert.AreEqual(guid, new Guid(item.val));
}
public async Task Update_SingleRow_MappedExpando()
{
// Apply some quick crazy-ass mapping... to an ExpandoObject :-)
// Remember, we're mapping from crazy fake 'class' names to the sensible underlying column names
var categories = new MightyOrm(
WhenDevart.AddLicenseKey(ProviderName, string.Format(TestConstants.WriteTestConnection, ProviderName)),
"MassiveWriteTests.Categories",
primaryKeys: "MYCATEGORYID",
columns: "MYCATEGORYID, TheName, ItsADescription",
mapper: new SqlNamingMapper(columnNameMapping: (t, c) => c
// 'class' names come first
.Map("MYCATEGORYID", "CategoryID")
.Map("TheName", "CategoryName")
.Map("ItsADescription", "Description")));
// insert something to update first.
var inserted = await categories.InsertAsync(new { TheName = "Cool stuff", ItsADescription = "You know... cool stuff! Cool. n. stuff." });
int insertedCategoryID = inserted.MYCATEGORYID;
Assert.IsTrue(insertedCategoryID > 0);
// update it, with a better description
inserted.ItsADescription = "This is all jolly marvellous";
Assert.AreEqual(1, await categories.UpdateAsync(inserted), "Update should have affected 1 row");
var updatedRow = await categories.SingleAsync(new { inserted.MYCATEGORYID });
Assert.IsNotNull(updatedRow);
Assert.AreEqual(inserted.MYCATEGORYID, Convert.ToInt32(updatedRow.MYCATEGORYID)); // convert from uint
Assert.AreEqual(inserted.ItsADescription, updatedRow.ItsADescription);
// reset description to NULL
updatedRow.ItsADescription = null;
Assert.AreEqual(1, await categories.UpdateAsync(updatedRow), "Update should have affected 1 row");
var newUpdatedRow = await categories.SingleAsync(new { updatedRow.MYCATEGORYID });
Assert.IsNotNull(newUpdatedRow);
Assert.AreEqual(updatedRow.MYCATEGORYID, newUpdatedRow.MYCATEGORYID);
Assert.AreEqual(updatedRow.ItsADescription, newUpdatedRow.ItsADescription);
}
public void CleanUp()
{
var db = new MightyOrm(WhenDevart.AddLicenseKey(ProviderName, string.Format(TestConstants.WriteTestConnection, ProviderName)));
db.ExecuteProcedure("pr_clearAll");
}
public void BoolTypes()
{
var db = new MightyOrm(WhenDevart.AddLicenseKey(ProviderName, string.Format(TestConstants.WriteTestConnection, ProviderName)), "bittest");
var results = db.All();
foreach (var result in results)
{
if (ProviderName == "Devart.Data.MySql")
{
// I am not sure that what Devart is doing here for different sizes of TINYINT makes any sense?
// (c.f. the test called Function_Call_Byte())
// bool/boolean is just an alias for tinyint(1)
Assert.That(result.tinyint_one.GetType(), Is.EqualTo(typeof(short)), "tinyint_one");
Assert.That(result.tinyint_three.GetType(), Is.EqualTo(typeof(byte)), "tinyint_three");
Assert.That(result.tinyint_bool.GetType(), Is.EqualTo(typeof(short)), "tinyint_bool");
// bit(1) is a special case in Devart (which seems to have changed at some point: https://forums.devart.com/viewtopic.php?t=19967)
Assert.That(result.bit_one.GetType(), Is.EqualTo(typeof(bool)), "bit_one");
// all other bit sizes come back as long
Assert.That(result.bit_two.GetType(), Is.EqualTo(typeof(long)), "bit_two");
Assert.That(result.bit_eight.GetType(), Is.EqualTo(typeof(long)), "bit_eight");
Assert.That(result.bit_sixtyfour.GetType(), Is.EqualTo(typeof(long)), "bit_sixtyfour");
// check the actual bool value
Assert.That(result.bit_one, Is.EqualTo(result.id == 2), "bit_one");
// check all other values
Assert.That(result.tinyint_one, Is.EqualTo(result.id - 1), "tinyint_one");
Assert.That(result.tinyint_three, Is.EqualTo(result.id - 1), "tinyint_three");
Assert.That(result.tinyint_bool, Is.EqualTo(result.id - 1), "tinyint_bool");
Assert.That(result.bit_two, Is.EqualTo(result.id - 1), "bit_two");
Assert.That(result.bit_eight, Is.EqualTo(result.id - 1), "bit_eight");
Assert.That(result.bit_sixtyfour, Is.EqualTo(result.id - 1), "bit_sixtyfour");
}
else if (ProviderName == "MySql.Data.MySqlClient")
{
// this makes sense: TINYINT(1) and its aliases BOOL and BOOLEAN come back as bool, other sizes of TINYINT as byte
Assert.That(result.tinyint_one.GetType(), Is.EqualTo(typeof(bool)), "tinyint_one");
Assert.That(result.tinyint_three.GetType(), Is.EqualTo(typeof(byte)), "tinyint_three");
Assert.That(result.tinyint_bool.GetType(), Is.EqualTo(typeof(bool)), "tinyint_bool");
// all sizes of BIT come back as ulong in MySql.Data.MySqlClient
Assert.That(result.bit_one.GetType(), Is.EqualTo(typeof(ulong)), "bit_one");
Assert.That(result.bit_two.GetType(), Is.EqualTo(typeof(ulong)), "bit_two");
Assert.That(result.bit_eight.GetType(), Is.EqualTo(typeof(ulong)), "bit_eight");
Assert.That(result.bit_sixtyfour.GetType(), Is.EqualTo(typeof(ulong)), "bit_sixtyfour");
// check the actual bool values
Assert.That(result.tinyint_bool, Is.EqualTo(result.id != 1), "tinyint_bool");
Assert.That(result.tinyint_one, Is.EqualTo(result.id != 1), "tinyint_one");
// check all other values
Assert.That(result.tinyint_three, Is.EqualTo(result.id - 1), "tinyint_three");
Assert.That(result.bit_one, Is.EqualTo(result.id == 2 ? 1 : 0), "bit_one");
Assert.That(result.bit_two, Is.EqualTo(result.id - 1), "bit_two");
Assert.That(result.bit_eight, Is.EqualTo(result.id - 1), "bit_eight");
Assert.That(result.bit_sixtyfour, Is.EqualTo(result.id - 1), "bit_sixtyfour");
}
else
{
Assert.That(false, $"Unexpected provider name \"{ProviderName}\"");
}
}
}
public async Task CleanUp()
{
var db = new MightyOrm(WhenDevart.AddLicenseKey(TestConstants.WriteTestConnection, ProviderName));
await db.ExecuteProcedureAsync("pr_clearAll");
}
