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 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 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"); }
private void searchButton_Click(object sender, EventArgs e) { DateSearch aDateSearch = new DateSearch(); DateTime from = fromDateTimePicker.Value; DateTime to = todateTimePicker.Value; if (from > to) { MessageBox.Show("From date is greater then To date"); } else { aDateSearch.FromDate = fromDateTimePicker.Text; aDateSearch.ToDate = todateTimePicker.Text; List <DateSearch> dateSearchResult = aDateSearchManager.searchDate(aDateSearch); serchByDatelistView.Items.Clear(); int counter = 0; foreach (DateSearch date in dateSearchResult) { ListViewItem item = new ListViewItem(); item.Text = (++counter).ToString(); item.SubItems.Add(date.ItemName); item.SubItems.Add(date.Sell.ToString()); serchByDatelistView.Items.Add(item); } } }
public void Succeeds() { var search = new DateSearch("Value"); var result = search.ApplyToQuery(Items().AsQueryable()).ToArray(); result.Length.Should().Be(6); }
public void ApplyToQuery_EqualDateNullable_CorrectResultReturned() { var criteria = new DateSearch(); criteria.Property = "DateNullable"; criteria.TargetTypeName = typeof(SomeClass).AssemblyQualifiedName; criteria.SearchTerm = new System.DateTime(2000, 1, 1); criteria.SearchTerm2 = null; criteria.Comparator = DateComparators.Equal; Assert.Equal(2, criteria.ApplyToQuery(new Repository().GetQuery()).Count()); }
public override void BuildFechaFilter() { var criteria = new DateSearch(); criteria.Property = "Fecha"; criteria.TargetTypeName = typeof(Venta).AssemblyQualifiedName; criteria.SearchTerm = new System.DateTime(2017, 10, 20); criteria.SearchTerm2 = null; criteria.Comparator = DateComparators.Equal; AbstractSearches.Add(criteria); }
public void Equal_Succeeds() { var search = new DateSearch("Value") { Is = DateSearch.Comparer.Equal, Term1 = new DateTime(2020, 1, 1) }; var result = search.ApplyToQuery(Items().AsQueryable()).ToArray(); result.Length.Should().Be(1); result[0].Id.Should().Be(1); }
private DateSearch BuildPropertyDate <T>(string property, string dateSearch1, string dateSearch2, string comparatorValue) { var teste = DateTime.TryParse(dateSearch1, out DateTime resultS1); var otherTest = resultS1; var criteria = new DateSearch(); criteria.Property = property; criteria.TargetTypeName = typeof(T).AssemblyQualifiedName; criteria.SearchTerm = ConvertDate(dateSearch1); criteria.SearchTerm2 = ConvertDate(dateSearch2); criteria.Comparator = (DateComparators)Enum.Parse(typeof(DateComparators), comparatorValue); return(criteria); }
//Load Date search Item on Grid vie public DataTable LoadDateSearchItem(DateSearch dateSearch) { SqlConnection conn = new SqlConnection(connection.connectionDb); //string query = @"SELECT ite.Name ItemName, sto.Quantity Quantity FROM StockOut sto INNER JOIN Items ite ON sto.ItemId = ite.Id WHERE Date BETWEEN '" + dateSearch.FromDate + "' AND '" + dateSearch.ToDate + "' AND MediumId = 1 ORDER BY sto.Id DESC"; string query = @"SELECT Name ItemName, SUM(Quantity) Quantity from StockoutDetail WHERE Date Between '" + Utility.Utility.GetSqlDateOnly(dateSearch.FromDate) + "' AND '" + Utility.Utility.GetSqlDateOnly(dateSearch.ToDate) + "' group by Name, Medium Having Medium = 'Sell' ORDER BY Quantity DESC"; SqlCommand command = new SqlCommand(query, conn); conn.Open(); SqlDataReader sd = command.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(sd); conn.Close(); return(dt); }
public void Greater_Succeeds() { var search = new DateSearch("Value") { Is = DateSearch.Comparer.Greater, Term1 = new DateTime(2020, 1, 3) }; var result = search.ApplyToQuery(Items().AsQueryable()).ToArray(); result.Length.Should().Be(3); result[0].Id.Should().Be(4); result[1].Id.Should().Be(5); result[2].Id.Should().Be(6); }
public void InRange_Succeeds() { var search = new DateSearch("Value") { Is = DateSearch.Comparer.InRange, Term1 = new DateTime(2020, 1, 2), Term2 = new DateTime(2020, 1, 4) }; var result = search.ApplyToQuery(Items().AsQueryable()).ToArray(); result.Length.Should().Be(3); result[0].Id.Should().Be(2); result[1].Id.Should().Be(3); result[2].Id.Should().Be(4); }
private void BuildFechaFilterQuery() { if (FilterEntitie.UsarFecha) { var criteria = new DateSearch { Property = "FechaCreacion", TargetTypeName = typeof(SubPedidoEntry).AssemblyQualifiedName, SearchTerm = FilterEntitie.Desde, SearchTerm2 = FilterEntitie.Hasta, Comparator = DateComparators.InRange }; FilterQuerys.Add(criteria); } }
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 List <DateSearch> searchDate(DateSearch aDateSearch) { Query = "SELECT * FROM DateSearch Where Date BETWEEN @from and @to"; Command = new SqlCommand(Query, Connection); Command.Connection.Open(); Command.Parameters.AddWithValue("from", aDateSearch.FromDate); Command.Parameters.AddWithValue("to", aDateSearch.ToDate); Reader = Command.ExecuteReader(); List <DateSearch> DateSearchList = new List <DateSearch>(); while (Reader.Read()) { DateSearch DateSearch = new DateSearch(); DateSearch.ItemName = Reader["ItemName"].ToString(); DateSearch.Sell = (int)Reader["Sell"]; DateSearchList.Add(DateSearch); } Reader.Close(); Connection.Close(); return(DateSearchList); }
private static AbstractSearch CreateSearchCriterion(Type targetType, Type propertyType, string property) { AbstractSearch result = null; if (propertyType.IsCollectionType()) { propertyType = propertyType.GetGenericArguments().First(); } if (propertyType.IsEnum) { result = new EnumSearch(propertyType); } else if (propertyType == typeof(string)) { result = new TextSearch(); } else if (propertyType == typeof(bool) || propertyType == typeof(bool?)) { result = new BooleanSearch(); } else if (propertyType == typeof(byte) || propertyType == typeof(byte?)) { result = new ByteSearch(); } else if (propertyType == typeof(char) || propertyType == typeof(char?)) { result = new CharacterSearch(); } else if (propertyType == typeof(DateTime) || propertyType == typeof(DateTime?)) { result = new DateSearch(); } else if (propertyType == typeof(decimal) || propertyType == typeof(decimal?)) { result = new DecimalSearch(); } else if (propertyType == typeof(double) || propertyType == typeof(double?)) { result = new DoubleSearch(); } else if (propertyType == typeof(float) || propertyType == typeof(float?)) { result = new FloatSearch(); } else if (propertyType == typeof(int) || propertyType == typeof(int?)) { result = new IntegerSearch(); } else if (propertyType == typeof(long) || propertyType == typeof(long?)) { result = new LongSearch(); } else if (propertyType == typeof(sbyte) || propertyType == typeof(sbyte?)) { result = new SByteSearch(); } else if (propertyType == typeof(short) || propertyType == typeof(short?)) { result = new ShortSearch(); } else if (propertyType == typeof(uint) || propertyType == typeof(uint?)) { result = new UnsignedIntegerSearch(); } else if (propertyType == typeof(ulong) || propertyType == typeof(ulong?)) { result = new UnsignedLongSearch(); } else if (propertyType == typeof(ushort) || propertyType == typeof(ushort?)) { result = new UnsignedShortSearch(); } if (result != null) { result.Property = property; result.TargetTypeName = targetType.AssemblyQualifiedName; } return(result); }
public List <DateSearch> searchDate(DateSearch aDateSearch) { return(aDateSearchGateway.searchDate(aDateSearch)); }
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"); }