예제 #1
0
        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;
        }
예제 #2
0
        //[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));
            }
        }
예제 #3
0
        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);
        }
예제 #4
0
        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);
        }
예제 #5
0
        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));
        }
예제 #6
0
        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);
        }
예제 #7
0
        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");
        }
예제 #8
0
        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);
        }
예제 #9
0
        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");
        }
예제 #10
0
        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);
        }
예제 #11
0
        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);
        }
예제 #12
0
        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");
        }