Load ListView Values between two Dates SQLite Xamarin Forms

Question

Hi Xamarin Community,

I Have a question about how can I load the values on a listview between two dates using sqlite, for example if I choose use 2 datespicker with diferents dates, I need to load the values between those dates

My model:

public class Gastos
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string NombrePlan { get; set; }
        public decimal MontoTotal { get; set; }
        public DateTime FechaPlan { get; set; }
        **public DateTIme Start {get;set;}
        public DateTIme End {get;set;}**
        public decimal? Total => MontoTotal;

        public Gastos()
        {

        }

Hope you can help me with this example

Thanks
Regards.

Answer 1

Hello,​

Welcome to our Microsoft Q&A platform!

Here is example about, I do not know you want to filter which datas, So I make a test with start date and end date.

Filter before

filter end

First of all, here is Order.cs

   using SQLite;  
   using System;  
   using System.Collections.Generic;  
   using System.ComponentModel;  
   using System.Runtime.CompilerServices;  
   using System.Text;  
     
   namespace DataPickerWithListviewSqlite  
   {  
       public class Order : INotifyPropertyChanged  
       {  
           [PrimaryKey, AutoIncrement]  
           public int Id { get; set; }  
           public float Amount { get; set; }  
     
           string _date;  
           public string Date  
           {  
               get  
               {  
                   return _date;  
               }  
               set  
               {  
                   if (_date != value)  
                   {  
                       _date = value;  
                       OnPropertyChanged("Date");  
                   }  
               }  
           }  
           public DateTime Start { get; set; }  
           public DateTime End { get; set; }  
          
           public int CustomerId { get; set; }  
           #region INotifyPropertyChanged  
           public event PropertyChangedEventHandler PropertyChanged;  
           void OnPropertyChanged([CallerMemberName] string propertyName = null)  
           {  
               PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));  
           }  
           #endregion  
       }  
   }  

Here is OrderDatabase.cs, it achieved CRUD.

   using SQLite;  
   using System;  
   using System.Collections.Generic;  
   using System.Runtime.CompilerServices;  
   using System.Text;  
   using System.Threading.Tasks;  
     
   namespace DataPickerWithListviewSqlite  
   {  
       public class OrderDatabase  
       {  
           static SQLiteAsyncConnection Database;  
     
           public static readonly AsyncLazy<OrderDatabase> Instance = new AsyncLazy<OrderDatabase>(async () =>  
           {  
               var instance = new OrderDatabase();  
               CreateTableResult result = await Database.CreateTableAsync<Order>();  
               return instance;  
           });  
     
           public OrderDatabase()  
           {  
               Database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);  
           }  
     
           public async Task<List<Order>> GetItemsAsync()  
           {  
               List<Order> orders= await Database.Table<Order>().ToListAsync();  
               return orders;  
           }  
     
           public Task<List<Order>> GetItemsFromDateAsync(DateTime Start , DateTime end)  
           {  
               return Database.QueryAsync<Order>("SELECT * FROM [Order] WHERE [Start] >= ? or [End]<= ?", Start, end);  
           }  
     
           public Task<Order> GetItemAsync(int id)  
           {  
               return Database.Table<Order>().Where(i => i.Id == id).FirstOrDefaultAsync();  
           }  
     
           public Task<int> SaveItemAsync(Order item)  
           {  
               if (item.Id != 0)  
               {  
                   return Database.UpdateAsync(item);  
               }  
               else  
               {  
                   return Database.InsertAsync(item);  
               }  
           }  
     
           public Task<int> DeleteItemAsync(Order item)  
           {  
               return Database.DeleteAsync(item);  
           }  
       }  
     
       public class AsyncLazy<T>  
       {  
           readonly Lazy<Task<T>> instance;  
     
           public AsyncLazy(Func<T> factory)  
           {  
               instance = new Lazy<Task<T>>(() => Task.Run(factory));  
           }  
     
           public AsyncLazy(Func<Task<T>> factory)  
           {  
               instance = new Lazy<Task<T>>(() => Task.Run(factory));  
           }  
     
           public TaskAwaiter<T> GetAwaiter()  
           {  
               return instance.Value.GetAwaiter();  
           }  
       }  
   }  

Here is my layout.xml

   <?xml version="1.0" encoding="utf-8" ?>  
   <ContentPage xmlns="http://xamarin.com/schemas/2014/forms"  
                xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"  
                x:Class="DataPickerWithListviewSqlite.MainPage">  
       <StackLayout>  
     
           <StackLayout Orientation="Horizontal">  
               <DatePicker x:Name="startDate"></DatePicker>  
               <DatePicker x:Name="endDate"></DatePicker>  
     
               <Button Text="search" Clicked="Button_Clicked"></Button>  
           </StackLayout>  
     
           <ListView ItemsSource="{Binding Orders}" HasUnevenRows="True"  SelectionMode="None">  
               <ListView.ItemTemplate>  
                   <DataTemplate>  
                       <ViewCell>  
                           <StackLayout >  
                               <Label Text="{Binding Date}"></Label>  
     
                               <Label  Text="{Binding Start}"></Label>  
                               <Label Text="{Binding End}"></Label>  
                           </StackLayout>  
                       </ViewCell>  
                   </DataTemplate>  
               </ListView.ItemTemplate>  
           </ListView>  
       </StackLayout>  
        
     
   </ContentPage>  

Here is my layout background code.

   using System;  
   using System.Collections.Generic;  
   using System.Collections.ObjectModel;  
   using System.ComponentModel;  
   using System.Linq;  
   using System.Text;  
   using System.Threading.Tasks;  
   using Xamarin.Forms;  
     
   namespace DataPickerWithListviewSqlite  
   {  
       public partial class MainPage : ContentPage  
       {  
           MyViewModel myViewModel;  
           public MainPage()  
           {  
               InitializeComponent();  
               myViewModel =  new MyViewModel();  
               this.BindingContext = myViewModel;  
           }  
           protected async override void OnAppearing()  
           {  
               base.OnAppearing();  
     
     
               //insert some data for testing  
               //OrderDatabase orderDatabase = await OrderDatabase.Instance;  
     
     
               //await orderDatabase.SaveItemAsync(new Order { Amount = 25.7f, Start = new DateTime(2014, 5, 15, 11, 30, 15), End = new DateTime(2014, 5, 16, 10, 30, 15) });  
     
               //await orderDatabase.SaveItemAsync(new Order { Amount = 15.2f, Start = new DateTime(2014, 3, 7, 13, 59, 1), End = new DateTime(2015, 5, 16, 10, 30, 15) });  
     
               //await orderDatabase.SaveItemAsync(new Order { Amount = 0.5f, Start = new DateTime(2014, 4, 5, 7, 3, 0), End = new DateTime(2014, 5, 16, 10, 30, 15) });  
     
               //await orderDatabase.SaveItemAsync(new Order { Amount = 106.6f, Start = new DateTime(2014, 7, 20, 21, 20, 24), End = new DateTime(2014, 6, 16, 10, 30, 15) });  
     
               //await orderDatabase.SaveItemAsync(new Order { Amount = 98f, Start = new DateTime(2014, 02, 1, 22, 31, 7), End = new DateTime(2017, 5, 16, 10, 30, 15) });  
           }  
     
           private async void Button_Clicked(object sender, EventArgs e)  
           {  
               OrderDatabase orderDatabase = await OrderDatabase.Instance;  
     
                 List<Order>    orders=await  orderDatabase.GetItemsFromDateAsync(startDate.Date,endDate.Date);  
               myViewModel.Orders.Clear();  
     
               foreach (var item in orders)  
               {  
                   myViewModel.Orders.Add(item);  
               }  
           }  
       }  
     
       internal class MyViewModel  
       {  
           public  ObservableCollection<Order> Orders { get; set; }  
           OrderDatabase orderDatabase;  
           public MyViewModel()  
           {  
                 
               Orders = new ObservableCollection<Order>();  
     
     
               getAllItems();  
     
           }  
     
           public  async void getAllItems()  
           {  
               orderDatabase = await OrderDatabase.Instance;  
               List<Order> orders = await orderDatabase.GetItemsAsync();  
               foreach (Order item in orders)  
               {  
                   Orders.Add(item);  
               }  
                
           }  
       }  
   }  

Best Regards,

Leon Lu

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello,​

Welcome to our Microsoft Q&A platform!

Do you want to get the value from the two datapicker? Then Datapicker bind the value from sqlite DB in the Listview.

If so, here is a simple about it.

For example. I add three properties to Order tablet. I add INotifyPropertyChanged interface to achieve the Date change at the runtime. And Start

   public class Order: INotifyPropertyChanged  
       {  
           [PrimaryKey, AutoIncrement]  
           public int Id { get; set; }  
     
           public float Amount { get; set; }  
     
           
           string _date ;  
           public string Date  
           {  
               get  
               {  
                   return _date;  
               }  
     
               set  
               {  
                   if (_date != value)  
                   {  
                       _date = value;  
                       OnPropertyChanged("Date");  
     
                   }  
               }  
     
           }  
     
     
           public DateTime Start { get; set; }  
           public DateTime End { get; set; }  
     
           [ForeignKey(typeof(Customer))]  
           public int CustomerId { get; set; }  
     
     
           #region INotifyPropertyChanged  
           public event PropertyChangedEventHandler PropertyChanged;  
     
           void OnPropertyChanged([CallerMemberName] string propertyName = null)  
           {  
               PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));  
           }  
           #endregion  
       }  

Then you can get the data from viewmodel's constructor. I get the Values between two Date with TimeSpan span= item.End - item.Start;, set value to the Date property in model.

   public class MyViewModel  
       {  
           SQLiteConnection db;  
     
           public  ObservableCollection<Order> Orders { get; set; }  
     
           public MyViewModel()  
           {  
               db = Utils.CreateConnection();  
               Orders = new ObservableCollection<Order>();  
     
               List<Order> orders = db.Table<Order>().ToList();  
     
               foreach (Order item in orders) {  
     
                   
                   TimeSpan  span= item.End - item.Start;  
                   item.Date= span.ToString();  
                   Orders.Add(item);  
               }  
     
           }  
       }  

Here is my layout.

   <ListView ItemsSource="{Binding Orders}" HasUnevenRows="True"  SelectionMode="None">  
               <ListView.ItemTemplate>  
                   <DataTemplate>  
                       <ViewCell>  
     
                           <StackLayout >  
                               <Label Text="{Binding Date}"></Label>  
                                 
                               <DatePicker  Date="{Binding Start}"></DatePicker>  
                               <DatePicker Date="{Binding End}"></DatePicker>  
                           </StackLayout>  
                       </ViewCell>  
                   </DataTemplate>  
               </ListView.ItemTemplate>  
     
           </ListView>  

Here is my layout background code.

   public partial class MainPage : ContentPage  
       {  
           public MainPage()  
           {  
               InitializeComponent();  
              this.BindingContext = new MyViewModel();  
           }  
           SQLiteConnection db;  
           protected override void OnAppearing()  
           {  
               base.OnAppearing();  
                db = Utils.CreateConnection();  
     
               //insert some data for testing  
     
     
               //db.CreateTable<Customer>();  
               //db.CreateTable<Order>();  
     
               //var customer1 = new Customer()  
               //{  
               //    Name = "Test1",  
               //    Orders = new[]  
               //    {  
               //        new Order { Amount = 25.7f, Start = new DateTime(2014, 5, 15, 11, 30, 15),End=new DateTime(2014, 5, 16, 10, 30, 15) },  
               //        new Order { Amount = 15.2f, Start = new DateTime(2014, 3, 7, 13, 59, 1),End=new DateTime(2015, 5, 16, 10, 30, 15) },  
               //        new Order { Amount = 0.5f, Start = new DateTime(2014, 4, 5, 7, 3, 0) ,End=new DateTime(2014, 5, 16, 10, 30, 15)},  
               //        new Order { Amount = 106.6f, Start = new DateTime(2014, 7, 20, 21, 20, 24),End=new DateTime(2014, 6, 16, 10, 30, 15)},   
               //        new Order { Amount = 98f, Start = new DateTime(2014, 02, 1, 22, 31, 7) ,End=new DateTime(2017, 5, 16, 10, 30, 15)},                }  
               //};  
     
     
     
               //var customer2 = new Customer()  
               //{  
               //    Name = "Test2",  
               //    Orders = new[]  
               //   {  
     
               //        new Order { Amount = 110.5f, Start = new DateTime(2013, 02, 1, 22, 31, 6) ,End=new DateTime(2017, 5, 16, 10, 30, 15) },  
               //        new Order { Amount = 102226.6f, Start = new DateTime(2014, 02, 1, 22, 3, 7) ,End=new DateTime(2015, 5, 16, 10, 30, 15) },  
     
               //    }  
               //};  
     
     
     
     
               //db.InsertWithChildren(customer1, recursive: true);  
     
               //db.InsertWithChildren(customer2, recursive: true);  
           }  

Here is running screenshot.

Best Regards,

Leon Lu

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.