public IEnumerable<dynamic> Search(out int totalRows,string displayName, int currentPage, int rowsPerPage, bool sortDesc, string orderBy)
{
var qb = QueryBuilderFactory.Init("Person");
//could do one select statement here * or dbo.Person.*
// but this way shows the fluent mannery in which they can be added.
qb.AddSelect("PersonId")
.AddSelect("FirstName")
.AddSelect("LastName")
.AddSelect("DisplayName")
.AddLike("DisplayName", displayName);
var pager = new PagingFactory(innerQuery: qb, currentPage: currentPage, rowsPerPage: rowsPerPage,
sortDesc: sortDesc, orderBy: orderBy);
var query = pager.CreateQuery();
var sqlParams = pager.CreateParameters();
var countQuery = qb.CreateCount().CreateQuery();
dynamic data;
dynamic count;
using (var db = Database.OpenConnectionString(DataHelper.ConnectionStr(), DataHelper.Provider()))
{
data = db.Query(query, sqlParams);
count = db.QueryValue(countQuery,sqlParams);
}
totalRows = Convert.ToInt32(count);
return data;
}
//[Test]
public void SimpleIntegrationTest()
{
var connStr = ConfigurationManager.ConnectionStrings["MyConnStr"].ToString();
var proVider = ConfigurationManager.ConnectionStrings["MyConnStr"].ProviderName;
const string firstName = "Ja";
const string lastName = "Bond";
var qb = QueryBuilderFactory.Init("Person");
qb.AddSelect("FirstName")
.AddSelect("LastName")
.AddLike("FirstName", firstName)
.AddLike("LastName", lastName);
var pagingFactory = new PagingFactory(innerQuery: qb, currentPage: 2, rowsPerPage: 10, orderBy: "FirstName");
var query = pagingFactory.CreateQuery();
var sqlParams = pagingFactory.CreateParameters();
dynamic data;
using (var db = Database.OpenConnectionString(connStr, proVider))
{
data = db.Query(query, sqlParams);
}
foreach (var item in data)
{
Console.WriteLine(String.Format("First: {0} Last{1}", data.FirstName, data.LastName));
}
}
public IEnumerable <dynamic> Search(out int totalRows, string displayName, int currentPage, int rowsPerPage, bool sortDesc, string orderBy)
{
var qb = QueryBuilderFactory.Init("Person");
//could do one select statement here * or dbo.Person.*
// but this way shows the fluent mannery in which they can be added.
qb.AddSelect("PersonId")
.AddSelect("FirstName")
.AddSelect("LastName")
.AddSelect("DisplayName")
.AddLike("DisplayName", displayName);
var pager = new PagingFactory(innerQuery: qb, currentPage: currentPage, rowsPerPage: rowsPerPage,
sortDesc: sortDesc, orderBy: orderBy);
var query = pager.CreateQuery();
var sqlParams = pager.CreateParameters();
var countQuery = qb.CreateCount().CreateQuery();
dynamic data;
dynamic count;
using (var db = Database.OpenConnectionString(DataHelper.ConnectionStr(), DataHelper.Provider()))
{
data = db.Query(query, sqlParams);
count = db.QueryValue(countQuery, sqlParams);
}
totalRows = Convert.ToInt32(count);
return(data);
}
public void WhereClauseEmpty()
{
var firstName = string.Empty;
var lastName = string.Empty;
var ds = new DateSearch("Created", "", "");
var q = QueryBuilderFactory.Init("Person");
q.AddSelect("FirstName")
.AddSelect("LastName")
.AddLike("FirstName", firstName)
.AddLike("LastName", lastName)
.AddBetween(ds);
var pagingFactory = new PagingFactory(innerQuery: q, currentPage: 2, rowsPerPage: 10, orderBy: "FirstName");
var result = pagingFactory.CreateQuery();
Console.WriteLine(result);
var sqlParams = pagingFactory.CreateParameters();
Assert.IsTrue(sqlParams.Count() == 0);
var expectedNoEmptyWhere = "WHERE ";
Assert.IsFalse(result.Contains(expectedNoEmptyWhere));
var endAnd = AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result, endAnd.Msg);
}
public void SunnyDay()
{
const string firstName = "Ja";
const string lastName = "Bond";
var q = QueryBuilderFactory.Init("Person");
q.AddSelect("FirstName")
.AddSelect("LastName")
.AddLike("FirstName", firstName)
.AddLike("LastName", lastName);
var pagingFactory = new PagingFactory(innerQuery: q, currentPage: 2, rowsPerPage: 10, orderBy: "FirstName");
var result = pagingFactory.CreateQuery();
var sqlParams = pagingFactory.CreateParameters();
Assert.IsTrue(sqlParams.Count() == 2);
var s1 = "With OuterQuery AS (";
var s2 = "SELECT FirstName,LastName";
var s3 = "FROM Person";
var s4 = "WHERE FirstName LIKE @0 AND LastName LIKE @1";
var s5 = ") SELECT * ";
var s6 = "FROM OuterQuery";
var s7 = "WHERE RowNumber BETWEEN 11 AND 20";
var s8 = "RowNumber";
Assert.IsTrue(result.Contains(s1));
Assert.IsTrue(result.Contains(s2));
Assert.IsTrue(result.Contains(s3));
Assert.IsTrue(result.Contains(s4));
Assert.IsTrue(result.Contains(s5));
Assert.IsTrue(result.Contains(s6));
Assert.IsTrue(result.Contains(s7));
Assert.IsTrue(result.Contains(s8));
}
public void BetweenShouldWork()
{
var from = "200";
var to = "300";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere("Name","=","John")
.AddBetween("price", from, to);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, "Name");
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND price BETWEEN @1 AND @2 ";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual("John", sqlParams[0], "name");
var val1 = sqlParams[1].ToString();
Assert.AreEqual(from, val1 ,"min");
var val2 = sqlParams[2].ToString();
Assert.AreEqual(to, val2, "max");
var endAnd = AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result, endAnd.Msg);
}
public void DateNoStartDate()
{
// empty string for started and end date
var ds = new DateSearch("Created", "", "12/5/2011");
var name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(sqlParams.Count(), 3);
Assert.AreEqual(name, sqlParams[0], "name");
var date1 = sqlParams[1].ToString();
var dat1Expected = "1/1/1753";
Assert.IsTrue(date1.Contains(dat1Expected), "date1 failed");
}
public void DateBetweenSunnyDayInnerQueryTest()
{
var start = new DateTime(2011, 11, 5);
var end = new DateTime(2011, 12, 5);
var ds = new DateSearch("Created", start.ToShortDateString(), end.ToShortDateString());
const string name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(name, sqlParams[0], "name");
var date1 = sqlParams[1].ToString();
Assert.IsTrue(date1.Contains("11/5/2011"), "date1");
var date2 = sqlParams[2].ToString();
Assert.IsTrue(date2.Contains("12/5/2011"), "date2");
var endAnd= AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result,endAnd.Msg);
}
public void DateBetweenNoStartDateOrEndDate()
{
// empty string for started and end date
var ds = new DateSearch("Created", "","");
var name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(!innerQ.Contains(expected));
Assert.AreEqual(sqlParams.Count() ,1);
//Assert.AreEqual(name, sqlParams[0], "name");
//var date1 = sqlParams[1].ToString();
//var dat1Expected = DateSearch.SqlMin.ToString();
//Assert.AreEqual(dat1Expected,date1, "date1 failed");
//var date2 = sqlParams[2].ToString();
//var date2Expected = DateTime.Today;
//Assert.AreEqual(date2Expected, date2, "date2 failed");
}
public void DateBetweenSunnyDayInnerQueryTest()
{
var start = new DateTime(2011, 11, 5);
var end = new DateTime(2011, 12, 5);
var ds = new DateSearch("Created", start.ToShortDateString(), end.ToShortDateString());
const string name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(name, sqlParams[0], "name");
var date1 = sqlParams[1].ToString();
Assert.IsTrue(date1.Contains("11/5/2011"), "date1");
var date2 = sqlParams[2].ToString();
Assert.IsTrue(date2.Contains("12/5/2011"), "date2");
var endAnd = AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result, endAnd.Msg);
}
public void BetweenShouldWork()
{
var from = "200";
var to = "300";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere("Name", "=", "John")
.AddBetween("price", from, to);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, "Name");
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND price BETWEEN @1 AND @2 ";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual("John", sqlParams[0], "name");
var val1 = sqlParams[1].ToString();
Assert.AreEqual(from, val1, "min");
var val2 = sqlParams[2].ToString();
Assert.AreEqual(to, val2, "max");
var endAnd = AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result, endAnd.Msg);
}
public void DateNoEndReturnsToday()
{
// empty string for started and end date
var ds = new DateSearch("Created", "12/5/2011","");
var name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(sqlParams.Count(), 3);
Assert.AreEqual(name, sqlParams[0], "name");
var paramVal = sqlParams[2].ToString();
var date2 = new DateTime();
DateTime.TryParse(paramVal,out date2);
Assert.AreEqual(date2.ToShortDateString(),DateTime.Now.ToShortDateString(), "date2 failed");
}