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.