question

Amasu-3175 avatar image
0 Votes"
Amasu-3175 asked Amasu-3175 commented

Load ListView Values between two Dates SQLite Xamarin Forms

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.

dotnet-xamarinformsdotnet-xamarinessentialsdotnet-sqlite
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LeonLu-MSFT avatar image
0 Votes"
LeonLu-MSFT answered Amasu-3175 commented

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.



image.png (43.4 KiB)
image.png (41.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks so much for your help, it was very very useful

Regards.
Thanks

0 Votes 0 ·
LeonLu-MSFT avatar image
0 Votes"
LeonLu-MSFT answered Amasu-3175 commented

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.



image.png (27.9 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Amasu-3175 May I know if you have got any chance to check my answer? I am glad to help if you have any other questions

0 Votes 0 ·

Hi @LeonLu-MSFT , thanks for the response and sorry for the late reply,

The process I want to make is to filter the data of the listview using two datepickers, and when users select a lenght of date between the two datepicker, I want to display the data in the listview that corresponds to those selected dates,

135199-example.png


Hoe you can help me with this
Thanks

Regards

0 Votes 0 ·
example.png (17.8 KiB)