Load ListView Values between two Dates SQLite Xamarin Forms

Amasu 96 Reputation points
2021-09-01T16:02:01+00:00

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.

Xamarin
Xamarin
A Microsoft open-source app platform for building Android and iOS apps with .NET and C#.
5,313 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,079 questions
0 comments No comments
{count} votes

Accepted answer
  1. Leon Lu (Shanghai Wicresoft Co,.Ltd.) 70,646 Reputation points Microsoft Vendor
    2021-09-28T09:56:53.973+00:00

    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
    135844-image.png

    filter end

    135845-image.png

    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.


1 additional answer

Sort by: Most helpful
  1. Leon Lu (Shanghai Wicresoft Co,.Ltd.) 70,646 Reputation points Microsoft Vendor
    2021-09-02T08:02:16.303+00:00

    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.

    128538-image.png

    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.