WPF SQLite SQLiteStudio

1.Download SQLiteStudio via  below url and then install step by step

https://release-assets.githubusercontent.com/github-production-release-asset/117119718/433e0deb-ce58-4e38-8152-b361a9934502?sp=r&sv=2018-11-09&sr=b&spr=https&se=2026-05-21T10%3A51%3A08Z&rscd=attachment%3B+filename%3DSQLiteStudio-3.4.21-windows-x64-installer.exe&rsct=application%2Foctet-stream&skoid=96c2d410-5711-43a1-aedd-ab1947aa7ab0&sktid=398a6654-997b-47e9-b12b-9515b896b4de&skt=2026-05-21T09%3A50%3A36Z&ske=2026-05-21T10%3A51%3A08Z&sks=b&skv=2018-11-09&sig=LtzOcE4WE9G6s8Ufnci2ltgUQ%2Bjkly%2BL%2FVsckC5plUs%3D&jwt=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmVsZWFzZS1hc3NldHMuZ2l0aHVidXNlcmNvbnRlbnQuY29tIiwia2V5Ijoia2V5MSIsImV4cCI6MTc3OTM1ODkwNSwibmJmIjoxNzc5MzU3MTA1LCJwYXRoIjoicmVsZWFzZWFzc2V0cHJvZHVjdGlvbi5ibG9iLmNvcmUud2luZG93cy5uZXQifQ.UOZtNR6qWrhwt7L0DS0hQuBPCnmPuSdu2P0zDLmmBzU&response-content-disposition=attachment%3B%20filename%3DSQLiteStudio-3.4.21-windows-x64-installer.exe&response-content-type=application%2Foctet-stream

2.

Install-Package Microsoft.Data.Sqlite.Core
Install-Package SQLitePCLRaw.bundle_e_sqlite3

 

3.

private void OpenSqliteConnection(string dbName="")
{
    SQLitePCL.Batteries.Init();
    if (string.IsNullOrWhiteSpace(dbName))
    {
        dbName = $"DB_{DateTime.Now.ToString("yyyyMM")}";
    }
    string connStr = $"Data Source={dbName}.db";
    sqliteConn = new SqliteConnection(connStr);
    sqliteConn.Open();
}


private void InsertIntoSQLiteCmdExecuted(object? obj)
{
    var dg = obj as DataGrid;
    if (dg != null && dg.Items != null && dg.Items.Count>0)
    {
        var itemsList = dg.Items.Cast<Book>()?.ToList();

        InsertIntoItemsToSQLite(itemsList);
    }
}

private void InsertIntoItemsToSQLite(List<Book>? booksList)
{
    if(booksList==null || !booksList.Any())
    {
        return;
    }
    string dbName = $"DB_{DateTime.Now.ToString("yyyyMM")}";
    string tableName = $"Book_{DateTime.Now.ToString("yyyyMMdd")}";
    CreateTableIfNotExists(dbName,tableName);
    InsertIntoTableInBatch(tableName, booksList, 100000);
}

private void InsertIntoTableInBatch(string tableName, List<Book> booksList, int batchSize=10000)
{
    int booksCnt = booksList.Count;
    int batches = (booksCnt + batchSize - 1) / batchSize;
    for(int i=0;i<batches;i++)
    {
        int start_idx=i*batchSize;
        int end_idx=Math.Min((i+1)*batchSize, booksCnt);
        var batchBooks=booksList.Skip(start_idx).Take(batchSize).ToList();
        StringBuilder insertBuilder = new StringBuilder();
        insertBuilder.Append($"insert into {tableName} (name,isbn,author,abstract,content,comment,summary,title,topic) values ");

        foreach (var bk in batchBooks)
        {
            insertBuilder.Append($"('{bk.Name}','{bk.ISBN}','{bk.Author}','{bk.Abstract}','{bk.Content}','{bk.Comment}','{bk.Summary}','{bk.Title}','{bk.Topic}'),");
        }
        string insertSQL = insertBuilder.ToString();
        insertSQL = insertSQL.Substring(0, insertSQL.Length - 1);
        ExecuteSQL(insertSQL);
        System.Diagnostics.Debug.WriteLine($"{DateTime.Now},Insert between First Id:{batchBooks.FirstOrDefault()?.Id} and Last Id:{batchBooks.LastOrDefault()?.Id} into {tableName} successfully");
    }
}

private void CreateTableIfNotExists(string dbName = "DB_202605",string tableName="Book_20260521")
{
    string useDbSql = $"use {dbName}";
    string createTableSQL = $"create table if not exists {tableName} (id integer primary key autoincrement," +
          "name varchar(100) not null default '',ISBN varchar(100) not null default '',Author varchar(100) not null default '',"
          + "Abstract varchar(100) not null default '',Content varchar(100) not null default '',Comment varchar(100) not null default '',"
      + "Summary varchar(100) not null default '',Title varchar(100) not null default '',Topic varchar(100) not null default '')";
    ExecuteSQL(createTableSQL);
    System.Diagnostics.Debug.WriteLine($"{DateTime.Now},create table {tableName} successfully");
}

private void ExecuteSQL(string sql)
{
    using(SqliteCommand cmd=new SqliteCommand(sql,sqliteConn))
    {
        cmd.ExecuteNonQuery();
    }
}

private void OpenSqliteConnection(string dbName="")
{
    SQLitePCL.Batteries.Init();
    if (string.IsNullOrWhiteSpace(dbName))
    {
        dbName = $"DB_{DateTime.Now.ToString("yyyyMM")}";
    }
    string connStr = $"Data Source={dbName}.db";
    sqliteConn = new SqliteConnection(connStr);
    sqliteConn.Open();
}

 

 

2026-05-21 20:18:20,create table Book_20260521 successfully
2026-05-21 20:18:20,Insert between First Id:27152001 and Last Id:27252000 into Book_20260521 successfully
2026-05-21 20:18:21,Insert between First Id:27252001 and Last Id:27352000 into Book_20260521 successfully
2026-05-21 20:18:21,Insert between First Id:27352001 and Last Id:27452000 into Book_20260521 successfully
2026-05-21 20:18:21,Insert between First Id:27452001 and Last Id:27552000 into Book_20260521 successfully
2026-05-21 20:18:22,Insert between First Id:27552001 and Last Id:27652000 into Book_20260521 successfully
2026-05-21 20:18:22,Insert between First Id:27652001 and Last Id:27752000 into Book_20260521 successfully
2026-05-21 20:18:22,Insert between First Id:27752001 and Last Id:27852000 into Book_20260521 successfully
2026-05-21 20:18:23,Insert between First Id:27852001 and Last Id:27952000 into Book_20260521 successfully
2026-05-21 20:18:23,Insert between First Id:27952001 and Last Id:28052000 into Book_20260521 successfully
2026-05-21 20:18:23,Insert between First Id:28052001 and Last Id:28152000 into Book_20260521 successfully

 

 

 

 

 

 

 

 

 

 

<Window x:Class="WpfApp34.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:WpfApp34"
        mc:Ignorable="d"
        Title="{Binding MainTitle}" WindowState="Maximized">
    <Window.DataContext>
        <local:MainVM/>
    </Window.DataContext>
    <Grid>
        <DataGrid ItemsSource="{Binding BooksCollection}"
                  IsReadOnly="True"
                  SelectionMode="Extended"
                  VirtualizingPanel.IsVirtualizing="True"
                  VirtualizingPanel.VirtualizationMode="Recycling"
                  VirtualizingPanel.CacheLengthUnit="Item"
                  VirtualizingPanel.CacheLength="5,5"
                  ScrollViewer.IsDeferredScrollingEnabled="True"
                  ScrollViewer.CanContentScroll="True"
                  UseLayoutRounding="True"
                  SnapsToDevicePixels="True"
                  AutoGenerateColumns="True"
                  CanUserAddRows="False"
                  EnableRowVirtualization="True"
                  EnableColumnVirtualization="True"
                 >
            <DataGrid.Resources>
                <Style TargetType="DataGridRow">
                    <Setter Property="FontSize" Value="30"/>
                    <Style.Triggers>
                        <Trigger Property="IsMouseOver" Value="True">
                            <Setter Property="FontSize" Value="40"/>
                            <Setter Property="Foreground" Value="Red"/>
                        </Trigger>
                    </Style.Triggers>
                </Style>
            </DataGrid.Resources>
            <DataGrid.ContextMenu>
                <ContextMenu>
                    <MenuItem Header="Refresh Data"
                              Command="{Binding DataContext.RefreshDataCmd,RelativeSource={RelativeSource AncestorType={x:Type ContextMenu}}}"/>

                    <MenuItem Header="Insert Into SQLite"
                              Command="{Binding DataContext.InsertIntoSQLiteCmd,RelativeSource={RelativeSource AncestorType={x:Type ContextMenu}}}"
                              CommandParameter="{Binding RelativeSource={RelativeSource AncestorType={x:Type ContextMenu}},Path=PlacementTarget}"/>
                </ContextMenu>
            </DataGrid.ContextMenu>
        </DataGrid>
    </Grid>
</Window>


using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data.Common;
using System.Diagnostics.Eventing.Reader;
using System.Net;
using System.Net.Http;
using System.Runtime.CompilerServices;
using System.Runtime.Serialization;
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;
using System.Windows.Threading;
using Microsoft.Data.Sqlite;
using Newtonsoft.Json;
using SQLitePCL;

namespace WpfApp34
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
    }

    public class MainVM : INotifyPropertyChanged
    {
        string requestUrl = "http://localhost:7777/BookService.svc/getbookslist?cnt=";
        private HttpClient client;
        private DispatcherTimer tmr;
        public ICommand InsertIntoSQLiteCmd { get; set; }
        public ICommand RefreshDataCmd { get; set;  }

        SqliteConnection sqliteConn;

        public MainVM()
        {
            if (!DesignerProperties.GetIsInDesignMode(new DependencyObject()))
            {
                InitCmds();
                OpenSqliteConnection();
                client = new HttpClient();
                Task.Run(async () =>
                {
                    await LoadDataFromServiceAsync();
                });
            }
        }

        private void InitCmds()
        {
            InsertIntoSQLiteCmd = new DelCmd(InsertIntoSQLiteCmdExecuted);
            RefreshDataCmd = new DelCmd(RefreshDataCmdExecuted);
        }

        private void RefreshDataCmdExecuted(object? obj)
        {
            Task.Run(async () =>
            {
                await LoadDataFromServiceAsync();
            });
        }

        private void InsertIntoSQLiteCmdExecuted(object? obj)
        {
            var dg = obj as DataGrid;
            if (dg != null && dg.Items != null && dg.Items.Count>0)
            {
                var itemsList = dg.Items.Cast<Book>()?.ToList();

                InsertIntoItemsToSQLite(itemsList);
            }
        }

        private void InsertIntoItemsToSQLite(List<Book>? booksList)
        {
            if(booksList==null || !booksList.Any())
            {
                return;
            }
            string dbName = $"DB_{DateTime.Now.ToString("yyyyMM")}";
            string tableName = $"Book_{DateTime.Now.ToString("yyyyMMdd")}";
            CreateTableIfNotExists(dbName,tableName);
            InsertIntoTableInBatch(tableName, booksList, 100000);
        }

        private void InsertIntoTableInBatch(string tableName, List<Book> booksList, int batchSize=10000)
        {
            int booksCnt = booksList.Count;
            int batches = (booksCnt + batchSize - 1) / batchSize;
            for(int i=0;i<batches;i++)
            {
                int start_idx=i*batchSize;
                int end_idx=Math.Min((i+1)*batchSize, booksCnt);
                var batchBooks=booksList.Skip(start_idx).Take(batchSize).ToList();
                StringBuilder insertBuilder = new StringBuilder();
                insertBuilder.Append($"insert into {tableName} (name,isbn,author,abstract,content,comment,summary,title,topic) values ");

                foreach (var bk in batchBooks)
                {
                    insertBuilder.Append($"('{bk.Name}','{bk.ISBN}','{bk.Author}','{bk.Abstract}','{bk.Content}','{bk.Comment}','{bk.Summary}','{bk.Title}','{bk.Topic}'),");
                }
                string insertSQL = insertBuilder.ToString();
                insertSQL = insertSQL.Substring(0, insertSQL.Length - 1);
                ExecuteSQL(insertSQL);
                System.Diagnostics.Debug.WriteLine($"{DateTime.Now},Insert between First Id:{batchBooks.FirstOrDefault()?.Id} and Last Id:{batchBooks.LastOrDefault()?.Id} into {tableName} successfully");
            }
        }

        private void CreateTableIfNotExists(string dbName = "DB_202605",string tableName="Book_20260521")
        {
            string useDbSql = $"use {dbName}";
            string createTableSQL = $"create table if not exists {tableName} (id integer primary key autoincrement," +
                  "name varchar(100) not null default '',ISBN varchar(100) not null default '',Author varchar(100) not null default '',"
                  + "Abstract varchar(100) not null default '',Content varchar(100) not null default '',Comment varchar(100) not null default '',"
              + "Summary varchar(100) not null default '',Title varchar(100) not null default '',Topic varchar(100) not null default '')";
            ExecuteSQL(createTableSQL);
            System.Diagnostics.Debug.WriteLine($"{DateTime.Now},create table {tableName} successfully");
        }

        private void ExecuteSQL(string sql)
        {
            using(SqliteCommand cmd=new SqliteCommand(sql,sqliteConn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private void OpenSqliteConnection(string dbName="")
        {
            SQLitePCL.Batteries.Init();
            if (string.IsNullOrWhiteSpace(dbName))
            {
                dbName = $"DB_{DateTime.Now.ToString("yyyyMM")}";
            }
            string connStr = $"Data Source={dbName}.db";
            sqliteConn = new SqliteConnection(connStr);
            sqliteConn.Open();
        }

        private async Task LoadDataFromServiceAsync(int cnt = 1000000)
        {
            MainTitle = $"{DateTime.Now},loading from service...";
            string jsonStr = await client.GetStringAsync($"{requestUrl}{cnt}");
            if (!string.IsNullOrWhiteSpace(jsonStr))
            {
                List<Book>? bksList = JsonConvert.DeserializeObject<List<Book>>(jsonStr);
                if (bksList != null && bksList.Any())
                {
                    BooksCollection = new ObservableCollection<Book>(bksList);
                    MainTitle = $"{DateTime.Now},loaded {BooksCollection.Count()} items,{GetMem()}";
                }
            }
        }

        private string GetMem()
        {
            return $"memory {System.Diagnostics.Process.GetCurrentProcess().PrivateMemorySize64 / 1024 / 1024:N2} M";
        }


        private static long idx = 0;
        public static long GetIncrementIdx()
        {
            return Interlocked.Increment(ref idx);
        }

        private string mainTitle = $"{DateTime.Now},loading...";
        public string MainTitle
        {
            get
            {
                return mainTitle;
            }
            set
            {
                if (value != mainTitle)
                {
                    mainTitle = value;
                    OnPropertyChanged();
                }
            }
        }


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

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

    public class DelCmd : ICommand
    {
        private Action<object?>? execute;
        private Predicate<object?>? canExecute;
        public DelCmd(Action<object?>? executeValue, Predicate<object?>? canExecuteValue = null)
        {
            execute = executeValue ?? throw new ArgumentNullException(nameof(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?.Invoke(parameter);
        }
    }

    [DataContract]
    public class Book
    {
        [DataMember]
        public long Id { get; set; }
        [DataMember]
        public string Name { get; set; }
        [DataMember]
        public string ISBN { get; set; }
        [DataMember]
        public string Author { get; set; }
        [DataMember]
        public string Abstract { get; set; }
        [DataMember]
        public string Comment { get; set; }
        [DataMember]
        public string Content { get; set; }
        [DataMember]
        public string Summary { get; set; }
        [DataMember]
        public string Title { get; set; }
        [DataMember]
        public string Topic { get; set; }
    }
}

 

 

SELECT * FROM sqlite_master;

select * from Book_20260521;

select count(id) from Book_20260521;

 

string createTableSQL = $"create table if not exists {tableName} (id integer primary key autoincrement," +
      "name varchar(100) not null default '',ISBN varchar(100) not null default '',Author varchar(100) not null default '',"
      + "Abstract varchar(100) not null default '',Content varchar(100) not null default '',Comment varchar(100) not null default '',"
  + "Summary varchar(100) not null default '',Title varchar(100) not null default '',Topic varchar(100) not null default '')";

 private void InsertIntoTableInBatch(string tableName, List<Book> booksList, int batchSize=10000)
 {
     int booksCnt = booksList.Count;
     int batches = (booksCnt + batchSize - 1) / batchSize;
     for(int i=0;i<batches;i++)
     {
         int start_idx=i*batchSize;
         int end_idx=Math.Min((i+1)*batchSize, booksCnt);
         var batchBooks=booksList.Skip(start_idx).Take(batchSize).ToList();
         StringBuilder insertBuilder = new StringBuilder();
         insertBuilder.Append($"insert into {tableName} (name,isbn,author,abstract,content,comment,summary,title,topic) values ");

         foreach (var bk in batchBooks)
         {
             insertBuilder.Append($"('{bk.Name}','{bk.ISBN}','{bk.Author}','{bk.Abstract}','{bk.Content}','{bk.Comment}','{bk.Summary}','{bk.Title}','{bk.Topic}'),");
         }
         string insertSQL = insertBuilder.ToString();
         insertSQL = insertSQL.Substring(0, insertSQL.Length - 1);
         ExecuteSQL(insertSQL);
         System.Diagnostics.Debug.WriteLine($"{DateTime.Now},Insert between First Id:{batchBooks.FirstOrDefault()?.Id} and Last Id:{batchBooks.LastOrDefault()?.Id} into {tableName} successfully");
     }
 }