public void LinqSelectTest()
{
IDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient");
TestEntityRepository repository = new TestEntityRepository(fakeDb);
SelectCommand expectedCommand = fakeDb.CreateSelectCommand(repository.TableName)
.Querys("TestColumn1", "TestColumn2", "TestColumn5", "TestColumn8")
.Query("TestColumn3", "TTTT")
.Query(SqlAggregateFunction.Max, "TestColumn4", "MMMM")
.Where(c => c.GreaterThanOrEqual("TestColumn2", 123) | (c.GreaterThan("TestColumn4", DateTime.Now) & c.LessThan("TestColumn7", DateTime.Now.AddDays(7))))
.GroupBy("TestColumn3")
.InnerJoin("TestColumn2", "TestTable", "TestColumn2")
.OrderBy("TestTable", "TestColumn6", SqlOrderType.Asc);
SelectCommand actualCommand = fakeDb.CreateSelectCommand(repository.TableName)
.Querys <TestEntity>(c => new { c.Test1, c.Test2, c.Test5, c.Test8 })
.Query <TestEntity>(c => c.Test3, "TTTT")
.Query <TestEntity>(c => c.Test4, SqlAggregateFunction.Max, "MMMM")
.Where <TestEntity>(c => c.Test2 >= 123 || (c.Test4 > DateTime.Now && c.Test7 < DateTime.Now.AddDays(7)))
.GroupBy <TestEntity>(c => c.Test3)
.InnerJoin <TestEntity, TestEntity>(c => c.Test2, d => d.Test2)
.OrderBy <TestEntity>(c => c.Test6, SqlOrderType.Asc);
Assert.AreEqual(expectedCommand, actualCommand);
}
public void EntityUpdateTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
TestEntityRepository repository = new TestEntityRepository(fakeDb);
TestEntity entity = new TestEntity()
{
Test1 = "1", Test2 = 2, Test3 = 3.0, Test4 = DateTime.Now, Test8 = 8
};
String expectedSql = "UPDATE TestTable SET TestColumn1=@PN_IDX_0,TestColumn2=@PN_IDX_1,TestColumn3=@PN_IDX_2,TestColumn4=@PN_IDX_3,TestColumn5=@PN_IDX_4,TestColumn6=@PN_IDX_5,TestColumn7=@PN_IDX_6,TestColumn8=@PN_IDX_7";
DataParameter[] expectedParameter = new DataParameter[8]
{
DataParameter.InternalCreate(fakeDb, "TestColumn1", "0", entity.Test1),
DataParameter.InternalCreate(fakeDb, "TestColumn2", "1", entity.Test2),
DataParameter.InternalCreate(fakeDb, "TestColumn3", "2", entity.Test3),
DataParameter.InternalCreate(fakeDb, "TestColumn4", "3", entity.Test4),
DataParameter.InternalCreate(fakeDb, "TestColumn5", "4", DataType.Int32, entity.Test5),
DataParameter.InternalCreate(fakeDb, "TestColumn6", "5", DataType.Double, entity.Test6),
DataParameter.InternalCreate(fakeDb, "TestColumn7", "6", DataType.DateTime, entity.Test7),
DataParameter.InternalCreate(fakeDb, "TestColumn8", "7", DataType.Int16, entity.Test8)
};
UpdateCommand cmd = fakeDb.CreateUpdateCommand(repository.TableName).Set(entity);
String actualSql = cmd.GetCommandText().Trim();
DataParameter[] actualParameter = cmd.GetAllParameters();
Assert.AreEqual(expectedSql, actualSql);
for (Int32 i = 0; i < actualParameter.Length; i++)
{
Assert.AreEqual(expectedParameter[i], actualParameter[i]);
}
}
public void LinqCreateNotInConditionTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
SelectCommand expectedCommand = fakeDb.CreateSelectCommand("");
SelectCommand actualCommand = fakeDb.CreateSelectCommand("");
SqlConditionBuilder expectedConditionBuilder = expectedCommand.ConditionBuilder;
SqlInsideParametersCondition expectedCondition = expectedConditionBuilder.NotInThese("TestColumn1", "1");
SqlInsideParametersCondition actualCondition = SqlLinqCondition.Create <TestEntity>(actualCommand, c => c.Test1.NotInThese("1")) as SqlInsideParametersCondition;
Assert.AreEqual(expectedCondition, actualCondition);
SqlInsideParametersCondition expectedCondition2 = expectedConditionBuilder.NotInThese("TestColumn1", "1", "2", "3", "4", "5");
SqlInsideParametersCondition actualCondition2 = SqlLinqCondition.Create <TestEntity>(actualCommand, c => c.Test1.NotInThese("1", "2", "3", "4", "5")) as SqlInsideParametersCondition;
Assert.AreEqual(expectedCondition2, actualCondition2);
SqlInsideParametersCondition expectedCondition3 = expectedConditionBuilder.NotInThese("TestColumn2", 1, 2, 3, 4, 5);
SqlInsideParametersCondition actualCondition3 = SqlLinqCondition.Create <TestEntity>(actualCommand, c => c.Test2.NotInThese(1, 2, 3, 4, 5)) as SqlInsideParametersCondition;
Assert.AreEqual(expectedCondition3, actualCondition3);
SqlInsideParametersCondition expectedCondition4 = expectedConditionBuilder.NotInThese("TestColumn5", 1, 2, 3, 4, 5);
SqlInsideParametersCondition actualCondition4 = SqlLinqCondition.Create <TestEntity>(actualCommand, c => c.Test5.NotInThese(1, 2, 3, 4, 5)) as SqlInsideParametersCondition;
Assert.AreEqual(expectedCondition4, actualCondition4);
TestEntityRepository repository = new TestEntityRepository(fakeDb);
TestEntity entity = new TestEntity()
{
Test1 = "1", Test2 = 2, Test3 = 3.0, Test4 = DateTime.Now, Test8 = 8
};
SqlInsideCommandCondition expectedCondition5 = expectedConditionBuilder.NotIn("TestColumn4", repository.TableName, s =>
{
s.Query("TestColumn4")
.Paged(10, 2)
.Where <TestEntity>(c => c.Test1 == "test" && c.Test2 != 222 && c.Test4 < DateTime.Now)
.OrderBy <TestEntity>(c => c.Test3, SqlOrderType.Desc);
});
Action <SelectCommand> createAnotherSelect = s =>
{
s.Query("TestColumn4")
.Paged(10, 2)
.Where <TestEntity>(sc => sc.Test1 == "test" && sc.Test2 != 222 && sc.Test4 < DateTime.Now)
.OrderBy <TestEntity>(sc => sc.Test3, SqlOrderType.Desc);
};
SqlInsideCommandCondition actualCondition5 = SqlLinqCondition.Create <TestEntity>(actualCommand, c => c.Test4.NotIn <TestEntity>(createAnotherSelect)) as SqlInsideCommandCondition;
Assert.AreEqual(expectedCondition5, actualCondition5);
}
public void LinqInsertTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
TestEntityRepository repository = new TestEntityRepository(fakeDb);
TestEntity entity = new TestEntity()
{
Test1 = "1", Test2 = 2, Test3 = 3.0, Test4 = DateTime.Now, Test8 = 8
};
String expectedSql = "INSERT INTO TestTable ( TestColumn1,TestColumn2,TestColumn3,TestColumn4,TestColumn5,TestColumn6,TestColumn7,TestColumn8 ) " +
"VALUES ( @PN_IDX_0,@PN_IDX_1,@PN_IDX_2,@PN_IDX_3,@PN_IDX_4,@PN_IDX_5,@PN_IDX_6,@PN_IDX_7 )";
DataParameter[] expectedParameter = new DataParameter[8]
{
DataParameter.InternalCreate(fakeDb, "TestColumn1", "0", entity.Test1),
DataParameter.InternalCreate(fakeDb, "TestColumn2", "1", entity.Test2),
DataParameter.InternalCreate(fakeDb, "TestColumn3", "2", entity.Test3),
DataParameter.InternalCreate(fakeDb, "TestColumn4", "3", entity.Test4),
DataParameter.InternalCreate(fakeDb, "TestColumn5", "4", DataType.Int32, entity.Test5),
DataParameter.InternalCreate(fakeDb, "TestColumn6", "5", DataType.Double, entity.Test6),
DataParameter.InternalCreate(fakeDb, "TestColumn7", "6", DataType.DateTime, entity.Test7),
DataParameter.InternalCreate(fakeDb, "TestColumn8", "7", DataType.Int16, entity.Test8)
};
InsertCommand cmd = fakeDb.CreateInsertCommand(repository.TableName)
.Set <TestEntity>(c => c.Test1, entity.Test1)
.Set <TestEntity>(c => c.Test2, entity.Test2)
.Set <TestEntity>(c => c.Test3, entity.Test3)
.Set <TestEntity>(c => c.Test4, entity.Test4)
.Set <TestEntity>(c => c.Test5, entity.Test5)
.Set <TestEntity>(c => c.Test6, entity.Test6)
.Set <TestEntity>(c => c.Test7, entity.Test7)
.Set <TestEntity>(c => c.Test8, entity.Test8);
String actualSql = cmd.GetCommandText().Trim();
DataParameter[] actualParameter = cmd.GetAllParameters();
Assert.AreEqual(expectedSql, actualSql);
for (Int32 i = 0; i < actualParameter.Length; i++)
{
Assert.AreEqual(expectedParameter[i], actualParameter[i]);
}
}
public void LinqDecreaseTest()
{
AbstractDatabase fakeDb = DatabaseFactory.CreateDatabase("", "System.Data.SqlClient") as AbstractDatabase;
TestEntityRepository repository = new TestEntityRepository(fakeDb);
TestEntity entity = new TestEntity()
{
Test1 = "1", Test2 = 2, Test3 = 3.0, Test4 = DateTime.Now, Test8 = 8
};
String expectedSql = "UPDATE TestTable SET TestColumn2=(TestColumn2-1)";
DataParameter[] expectedParameter = new DataParameter[0];
UpdateCommand cmd = fakeDb.CreateUpdateCommand(repository.TableName).Decrease <TestEntity>(c => c.Test2);
String actualSql = cmd.GetCommandText().Trim();
DataParameter[] actualParameter = cmd.GetAllParameters();
Assert.AreEqual(expectedSql, actualSql);
Assert.AreEqual(expectedParameter.Length, actualParameter.Length);
}