public async Task Relations_Zero_One_And_One_One_Async()
{
var db = new Db();
db.Initialize(TestConstants.SqlClientConnectionString, "System.Data.SqlClient");
var result = await db.CreateCommand("select * from [dbo].[BaseTable] where id=@id")
.AddParameter("@id", 5)
.ReadOneMapToAsync<BaseTable>();
// zero one
if (result.ZeroOneId.HasValue)
{
var zeroOne = await db.CreateCommand("Select * from [dbo].[ZeroOne] where Id=@id")
.AddParameter("@id", result.ZeroOneId)
.ReadOneMapToAsync<ZeroOne>();
result.ZeroOne = zeroOne;
}
// one one
var oneOne = await db.CreateCommand("Select * from [dbo].[OneOne] where Id=@id")
.AddParameter("@id", result.OneOneId)
.ReadOneMapToAsync<OneOne>();
result.OneOne = oneOne;
Assert.AreEqual(result.Name.TrimEnd(), "Base 2");
Assert.IsTrue(result.ZeroOneId.HasValue && result.ZeroOneId == 1);
Assert.AreEqual(result.ZeroOne.Id, 1);
Assert.AreEqual(result.ZeroOne.Name, "Zero 1");
Assert.AreEqual(result.OneOneId, 3);
Assert.AreEqual(result.OneOne.Id, 3);
Assert.AreEqual(result.OneOne.Name, "One 2");
}
public void Relations_Zero_One_And_One_One()
{
var db = new Db();
db.Initialize(TestConstants.SqlClientConnectionString, "System.Data.SqlClient");
var result = db.CreateCommand("select * from [dbo].[BaseTable] where id=@id")
.AddParameter("@id", 4)
.ReadOneMapTo<BaseTable>();
// zero one
if (result.ZeroOneId.HasValue)
{
var zeroOne = db.CreateCommand("Select * from [dbo].[ZeroOne] where Id=@id")
.AddParameter("@id", result.ZeroOneId)
.ReadOneMapTo<ZeroOne>();
result.ZeroOne = zeroOne;
}
// one one
var oneOne = db.CreateCommand("Select * from [dbo].[OneOne] where Id=@id")
.AddParameter("@id", result.OneOneId)
.ReadOneMapTo<OneOne>();
result.OneOne = oneOne;
Assert.AreEqual(result.Name.TrimEnd(), "Base 1");
Assert.IsFalse(result.ZeroOneId.HasValue);
Assert.AreEqual(result.OneOneId,2);
Assert.AreEqual(result.OneOne.Id, 2);
Assert.AreEqual(result.OneOne.Name, "One 1");
}
public void Should_Connect_With_OleDb()
{
var db = new Db();
db.Initialize(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\romag\Documents\Visual Studio 2015\Projects\EasyDb\EasyDbTests\NorthWind.mdb", "System.Data.OleDb");
var count = db.CreateCommand("select count(*) from Categories")
.Scalar();
var result = Helper.TryParseToInt(count);
Assert.IsTrue(result.HasValue && result > 0);
}
public void Should_Map_One_With_Guid()
{
var db = new Db();
db.Initialize(TestConstants.SqlClientConnectionString, "System.Data.SqlClient");
var user = db.CreateCommand("select * from [dbo].[UsersWithGuid] where id=@id")
.AddParameter("@id", "39544cae-1342-4ab4-9d3c-921ffd93ac62")
.ReadOneMapTo<UserWithGuid>();
Assert.AreEqual(user.UserName, "Marie");
}
public void Should_Get_New_Guid_Key()
{
var db = new Db();
db.Initialize(TestConstants.SqlClientConnectionString, "System.Data.SqlClient");
var newGuid = db.CreateCommand("insert into [dbo].[UsersWithGuid](Name,Email) output INSERTED.Id values(@name,@email)")
.AddParameter("@name", "user1")
.AddParameter("@email", "*****@*****.**")
.Scalar();
Assert.IsFalse(string.IsNullOrEmpty(newGuid.ToString()));
}
public void Should_Read_One_With_Guid()
{
var db = new Db();
db.Initialize(TestConstants.SqlClientConnectionString, "System.Data.SqlClient");
var user = db.CreateCommand("select * from [dbo].[UsersWithGuid] where id=@id")
.AddParameter("@id", "39544cae-1342-4ab4-9d3c-921ffd93ac62")
.ReadOne<UserWithGuid>((reader) =>
{
return new UserWithGuid
{
Id = (Guid)reader["Id"],
UserName = (string)reader["Name"]
};
});
Assert.AreEqual(user.UserName.TrimEnd(), "Marie");
}
public void Should_Connect_With_SqlClient()
{
var db = new Db();
db.Initialize(TestConstants.SqlClientConnectionString, "System.Data.SqlClient");
var user = db.CreateCommand("select * from [dbo].[Users] where id=@id")
.AddParameter("@id", 11)
.ReadOneMapTo<User>();
Assert.AreEqual(user.UserName, "Marie");
}
public void Should_Get_Data_With_OleDb()
{
var db = new Db();
db.Initialize(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\romag\Documents\Visual Studio 2015\Projects\EasyDb\EasyDbTests\NorthWind.mdb", "System.Data.OleDb");
var category = db.CreateCommand("select * from Categories where id=@id")
.AddParameter("@id",1)
.ReadOneMapTo<Category>();
Assert.AreEqual(category.CategoryName.Trim(), "Beverages");
}
public void Should_Get_Data_With_MySQL()
{
var db = new Db();
db.Initialize(@"server=localhost;user id=root;database=mydb", "MySql.Data.MySqlClient");
var user = db.CreateCommand("select * from users where id=@id")
.AddParameter("@id", 1)
.ReadOneMapTo<MySQLUser>();
Assert.AreEqual(user.UserName.Trim(), "Marie");
}
public void Cannot_Execute_Command_With_Manual_Strategy_And_Connection_Closed()
{
bool fail = false;
var db = new Db();
db.Initialize(TestConstants.SqlClientConnectionString, "System.Data.SqlClient",ConnectionStrategyType.Manual);
try
{
var users = db.CreateCommand("select * from [dbo].[Users]")
.ReadAllMapTo<User>();
}
catch (Exception)
{
fail = true;
}
Assert.IsTrue(fail);
}
public void Each_Db_Have_Own_Connection()
{
var db = new Db();
var db2 = new Db();
db.Initialize(TestConstants.SqlClientConnectionString, "System.Data.SqlClient", ConnectionStrategyType.Manual);
db2.Initialize(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\romag\Documents\Visual Studio 2015\Projects\EasyDb\EasyDbTests\NorthWind.mdb", "System.Data.OleDb");
db.Open();
Assert.AreEqual(db.Connection.ConnectionString, TestConstants.SqlClientConnectionString);
Assert.AreEqual(db.Connection.Provider, "System.Data.SqlClient");
Assert.AreEqual(db.Connection.ConnectionStrategy, ConnectionStrategyType.Manual);
Assert.AreEqual(db.Connection.State, System.Data.ConnectionState.Open);
Assert.AreEqual(db2.Connection.ConnectionString, @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\romag\Documents\Visual Studio 2015\Projects\EasyDb\EasyDbTests\NorthWind.mdb");
Assert.AreEqual(db2.Connection.Provider, "System.Data.OleDb");
Assert.AreEqual(db2.Connection.ConnectionStrategy, ConnectionStrategyType.Automatic);
Assert.AreEqual(db2.Connection.State, System.Data.ConnectionState.Closed);
db.Close();
}
public void Relations_Many_Many()
{
var db = new Db();
db.Initialize(TestConstants.SqlClientConnectionString, "System.Data.SqlClient");
var result = db.CreateCommand("select * from [dbo].[BaseTable] where id=@id")
.AddParameter("@id", 4)
.ReadOneMapTo<BaseTable>();
// Many many
var manyManyList = db.CreateCommand("select * from [dbo].[ManyMany] where BaseTable_Id=@id")
.AddParameter("@id", 4)
.ReadAllMapTo<ManyMany>();
var manyTablesList = new List<ManyTable>();
foreach (var many in manyManyList)
{
var manyTable = db.CreateCommand("select * from [dbo].[ManyTable] where Id=@id")
.AddParameter("@id", many.ManyTable_Id)
.ReadOneMapTo<ManyTable>();
manyTablesList.Add(manyTable);
}
result.ManyList = manyTablesList;
Assert.AreEqual(result.ManyList[0].Id, 1);
Assert.AreEqual(result.ManyList[0].Name, "Many 1");
Assert.AreEqual(result.ManyList[1].Id, 2);
Assert.AreEqual(result.ManyList[1].Name, "Many 2");
}
