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");}}
