WPF MVVM, SqlSugar access MySQL batch by batch

Install-Package NewtonSoft.Json;
Install-Package MySQL.Data;
Install-Package SqlSugar;

 

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `firstname` varchar(100) NOT NULL DEFAULT '',
  `lastname` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `fn_ln_index` (`firstname`,`lastname`)
) ENGINE=InnoDB AUTO_INCREMENT=458600003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

 

 

 

public class BookService
{
    private readonly SqlSugarScope db;
    public BookService()
    {
        db = DatabaseConfig.GetSqlSugarClient();
    }

    public Book GetBookById(int id)
    {
        return db.Queryable<Book>().Where(x => x.Id == id).First();
    }

    public List<Book> GetBooksRangeList(int startId, int endId)
    {
        return db.Queryable<Book>().Where(x => x.Id >= startId && x.Id <= endId).ToList();
    }

    public List<Book> GetUsersByConditions(string fnValue, string lnValue)
    {
        return db.Queryable<Book>()
            .Where(x => string.Equals(x.FirstName, fnValue, StringComparison.InvariantCultureIgnoreCase)
            || string.Equals(x.LastName, lnValue, StringComparison.InvariantCultureIgnoreCase)).ToList();
    }


    public (List<Book> Data, int Total) GetBooksPaged(int pageIdx, int pageSize)
    {
        int total = 0;
        var data = db.Queryable<Book>()
            .ToPageList(pageIdx, pageSize, ref total);
        return (data, total);
    }
}


  private void NextCommandExecuted(object? obj)
  {
      ++batchIdx;
      BooksCollection = new ObservableCollection<Book>(bookService.GetBooksRangeList(batchIdx * batchSize+1, (batchIdx + 1) * batchSize));
      MainTitle = $"Loaded from{batchIdx * batchSize + 1} to {(batchIdx + 1) * batchSize}";
  }

 

 

 

 

 

 

 

 

 

 

 

 

<Application x:Class="WpfApp33.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:local="clr-namespace:WpfApp33"
             StartupUri="MainWindow.xaml">
    <Application.Resources>
        <Style TargetType="Button">
            <Setter Property="FontSize" Value="50"/>
            <Style.Triggers>
                <Trigger Property="IsMouseOver" Value="True">
                    <Setter Property="Foreground" Value="Red"/>
                </Trigger>
            </Style.Triggers>
        </Style>
    </Application.Resources>
</Application>


<Window x:Class="WpfApp33.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:WpfApp33"
        mc:Ignorable="d"
        WindowState="Maximized"
        Title="{Binding MainTitle}" Height="450" Width="800">
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition/>
            <RowDefinition Height="Auto"/>
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition/>
            <ColumnDefinition/>
        </Grid.ColumnDefinitions>
        <DataGrid Grid.Row="0" Grid.Column="0" Grid.ColumnSpan="2"
                  ItemsSource="{Binding BooksCollection,Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"
                  VirtualizingPanel.IsVirtualizing="True"
                  VirtualizingPanel.VirtualizationMode="Recycling"
                  VirtualizingPanel.CacheLengthUnit="Item"
                  VirtualizingPanel.CacheLength="2,2"
                  ScrollViewer.IsDeferredScrollingEnabled="True"
                  ScrollViewer.CanContentScroll="True"
                  AutoGenerateColumns="False"
                  CanUserAddRows="False">
            <DataGrid.Columns>
                <DataGridTemplateColumn>
                    <DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <Grid Width="{Binding DataContext.GridWidth,RelativeSource={RelativeSource AncestorType=Window}}"
                                  Height="{Binding DataContext.GridHeight,RelativeSource={RelativeSource AncestorType=Window}}">
                                <Grid.Resources>
                                    <Style TargetType="TextBlock">
                                        <Setter Property="FontSize" Value="30"/>
                                        <Style.Triggers>
                                            <Trigger Property="IsMouseOver" Value="True">
                                                <Setter Property="FontSize" Value="50"/>
                                                <Setter Property="Foreground" Value="Red"/>
                                            </Trigger>
                                        </Style.Triggers>
                                    </Style>
                                </Grid.Resources>
                                <Grid.ColumnDefinitions>
                                    <ColumnDefinition/>
                                    <ColumnDefinition/>
                                    <ColumnDefinition/>
                                </Grid.ColumnDefinitions>
                                <TextBlock Grid.Column="0" Text="{Binding Id}"/>
                                <TextBlock Grid.Column="1" Text="{Binding FirstName}"/>
                                <TextBlock Grid.Column="2" Text="{Binding LastName}"/>
                            </Grid>
                        </DataTemplate>
                    </DataGridTemplateColumn.CellTemplate>
                </DataGridTemplateColumn>
            </DataGrid.Columns>
        </DataGrid>
        <Grid Grid.Row="1" Grid.Column="0" Grid.ColumnSpan="2">
            <Grid.ColumnDefinitions>
                <ColumnDefinition/>
                <ColumnDefinition/>
            </Grid.ColumnDefinitions>
            <Button Grid.Column="0" Content="Prev" Command="{Binding PrevCommand}"/>
            <Button Grid.Column="1" Content="Next" Command="{Binding NextCommand}"/>
        </Grid>
    </Grid>
</Window>

using SqlSugar;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Runtime.CompilerServices;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace WpfApp33
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            var vm = new MainVM();
            this.DataContext = vm;
            this.Loaded += async (s, e) =>
            {
                await vm.InitBooksCollection();
                vm.GridWidth = this.ActualWidth;
                vm.GridHeight= this.ActualHeight/10;
            };
        }
    }

    public class MainVM : INotifyPropertyChanged
    {
        int batchSize = 1_000_000;
        int batchIdx = 0;
        private BookService bookService;

        public MainVM()
        {
            bookService = new BookService();
            PrevCommand = new DelCommand(PrevCommandExecuted);
            NextCommand = new DelCommand(NextCommandExecuted);
        }

        private void NextCommandExecuted(object? obj)
        {
            ++batchIdx;
            BooksCollection = new ObservableCollection<Book>(bookService.GetBooksRangeList(batchIdx * batchSize+1, (batchIdx + 1) * batchSize));
            MainTitle = $"Loaded from{batchIdx * batchSize + 1} to {(batchIdx + 1) * batchSize}";
        }

        private void PrevCommandExecuted(object? obj)
        {
            if (--batchIdx >= 0)
            {
                BooksCollection = new ObservableCollection<Book>(bookService.GetBooksRangeList(batchIdx * batchSize+1, (batchIdx + 1) * batchSize));
                MainTitle = $"Loaded from{batchIdx * batchSize + 1} to {(batchIdx + 1) * batchSize}";
            }
        }

        public async Task InitBooksCollection()
        {
            BooksCollection = new ObservableCollection<Book>(bookService.GetBooksRangeList(batchIdx * batchSize+1, (batchIdx + 1) * batchSize));
            MainTitle = $"Loaded from{batchIdx * batchSize + 1} to {(batchIdx + 1) * batchSize}";
        }

        public event PropertyChangedEventHandler? PropertyChanged;
        private void OnPropertyChanged([CallerMemberName] string propName = "")
        {
            var handler = PropertyChanged;
            if (handler != null)
            {
                handler?.Invoke(this, new PropertyChangedEventArgs(propName));
            }
        }


        private ObservableCollection<Book> booksCollection;
        public ObservableCollection<Book> BooksCollection
        {
            get
            {
                return booksCollection;
            }
            set
            {
                if (value != booksCollection)
                {
                    booksCollection = value;
                    OnPropertyChanged(nameof(BooksCollection));
                }
            }
        }


        private double gridWidth;
        public double GridWidth
        {
            get
            {
                return gridWidth;
            }
            set
            {
                if(value != gridWidth)
                {
                    gridWidth = value;
                    OnPropertyChanged();
                }
            }
        }

        private double gridHeight;
        public double GridHeight
        {
            get
            {
                return gridHeight;
            }
            set
            {
                if(value!=gridHeight)
                {
                    gridHeight = value;
                    OnPropertyChanged();
                }
            }
        }

        private string mainTitle;
        public string MainTitle
        {
            get
            {
                return mainTitle; 
            }
            set
            {
                if(value!=mainTitle)
                {
                    mainTitle = value;
                    OnPropertyChanged();
                }
            }
        }

        public ICommand PrevCommand { get; set; }
        public ICommand NextCommand { get; set; }
    }


    [SugarTable("t1")]
    public class Book
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }


    public class BookService
    {
        private readonly SqlSugarScope db;
        public BookService()
        {
            db = DatabaseConfig.GetSqlSugarClient();
        }

        public Book GetBookById(int id)
        {
            return db.Queryable<Book>().Where(x => x.Id == id).First();
        }

        public List<Book> GetBooksRangeList(int startId, int endId)
        {
            var resultList= db.Queryable<Book>().Where(x => x.Id >= startId && x.Id <= endId).ToList();
            if (resultList == null || !resultList.Any())
            {
                MessageBox.Show("No proper data set retrieved!");
                return null;
            }
            return resultList;
        }

        public List<Book> GetUsersByConditions(string fnValue, string lnValue)
        {
            return db.Queryable<Book>()
                .Where(x => string.Equals(x.FirstName, fnValue, StringComparison.InvariantCultureIgnoreCase)
                || string.Equals(x.LastName, lnValue, StringComparison.InvariantCultureIgnoreCase)).ToList();
        }


        public (List<Book> Data, int Total) GetBooksPaged(int pageIdx, int pageSize)
        {
            int total = 0;
            var data = db.Queryable<Book>()
                .ToPageList(pageIdx, pageSize, ref total);
            return (data, total);
        }
    }


    public class DatabaseConfig
    {
        public static SqlSugarScope GetSqlSugarClient()
        {
            return new SqlSugarScope(new ConnectionConfig()
            {
                ConnectionString = "Server=localhost;Database=databasename;Uid=uidvalue;Pwd=passwordValue",
                DbType = DbType.MySql,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute
            });
        }
    }


    public class DelCommand : ICommand
    {
        private Action<object?> execute;
        private Predicate<object?> canExecute;

        public DelCommand(Action<object?> executeValue, Predicate<object?> canExecuteValue = null)
        {
            execute = executeValue;
            canExecute = canExecuteValue;
        }

        public event EventHandler? CanExecuteChanged
        {
            add
            {
                CommandManager.RequerySuggested += value;
            }
            remove
            {
                CommandManager.RequerySuggested -= value;
            }
        }

        public bool CanExecute(object? parameter)
        {
            return canExecute == null ? true : canExecute(parameter);
        }

        public void Execute(object? parameter)
        {
            execute(parameter);
        }
    }
}