public void Test_ListQuery_DefaultHandler()
{
var expectedSql = "SELECT [t0].[FirstName],[t0].[LastName] FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]=@p0);";
IDbQueryBuilder query = new Csg.Data.DbQueryBuilder("dbo.Person", new Mock.MockConnection());
var queryDef = new ListQueryDefinition();
queryDef.Fields = new string[] { "FirstName", "LastName" };
queryDef.Filters = new List <ListFilter>(new ListFilter[] {
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.Equal, Value = "Bob"
}
});
var conn = new Mock.MockConnection();
var stmt = conn
.QueryBuilder("dbo.Person")
.ListQuery(queryDef)
.ValidateWith <Mock.Person>()
.Apply()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText);
Assert.AreEqual(1, stmt.Parameters.Count);
}
public void Test_ListFilterOperator_Like()
{
var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName] LIKE @p0);";
var queryDef = new ListQueryDefinition()
{
Filters = new List <ListFilter>()
{
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.Like, Value = "Bob"
}
}
};
var stmt = new Mock.MockConnection().QueryBuilder("dbo.Person")
.ListQuery(queryDef)
.ValidateWith <Mock.Person>()
.Apply()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText);
Assert.AreEqual(1, stmt.Parameters.Count);
// default is a beginswith search
Assert.AreEqual("Bob%", stmt.Parameters.First().Value.ToString());
}
public void Test_ListFilterOperator_IsNullFalse()
{
var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName] IS NOT NULL);";
var queryDef = new ListQueryDefinition()
{
Filters = new List <ListFilter>()
{
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.IsNull, Value = false
}
}
};
var stmt = new Mock.MockConnection().QueryBuilder("dbo.Person")
.ListQuery(queryDef)
.ValidateWith <Mock.Person>()
.Apply()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText);
}
public void Test_ListFilterOperator_Between()
{
var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE (([t0].[FirstName]>=@p0) AND ([t0].[FirstName]<=@p1));";
var queryDef = new ListQueryDefinition()
{
Filters = new List <ListFilter>()
{
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.Between, Value = new string[] { "Bob", "Dole" }
}
}
};
var stmt = new Mock.MockConnection().QueryBuilder("dbo.Person")
.ListQuery(queryDef)
.ValidateWith <Mock.Person>()
.Apply()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText);
Assert.AreEqual(2, stmt.Parameters.Count);
}
public void Test_ListFilterOperator_NotEquals()
{
var expectedSql = "SELECT * FROM [dbo].[Person] AS [t0] WHERE ([t0].[FirstName]<>@p0);";
var queryDef = new ListQueryDefinition()
{
Filters = new List <ListFilter>()
{
new ListFilter()
{
Name = "FirstName", Operator = ListFilterOperator.NotEqual, Value = "Bob"
}
}
};
var stmt = new Mock.MockConnection().QueryBuilder("dbo.Person")
.ListQuery(queryDef)
.ValidateWith <Mock.Person>()
.Apply()
.Render();
Assert.AreEqual(expectedSql, stmt.CommandText);
Assert.AreEqual(1, stmt.Parameters.Count);
}
