FreeSql 连接Sqlite和简单的增删改查

1、开发环境

1.1、开发工具VS2022，.NET 8

1.2、GUI框架使用WPF

1.3、MVVM使用的是 Prism

1.4、WPF控件库使用的是HandyControl

1.5、数据库使用的是Sqlite数据库。

1.6、Sqlite3安装与数据库创建教程：环境设置与基本操作

2、项目整体结构如下图。

3、主要代码如下。

3.1、 App.xaml和App.xaml.cs代码如下。

<prism:PrismApplication x:Class="FreeSqlDemo.App" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:local="clr-namespace:FreeSqlDemo" xmlns:hc="https://handyorg.github.io/handycontrol" xmlns:prism="http://prismlibrary.com/"> <Application.Resources> <ResourceDictionary> <ResourceDictionary.MergedDictionaries> <ResourceDictionary Source="pack://application:,,,/HandyControl;component/Themes/SkinDefault.xaml"/> <ResourceDictionary Source="pack://application:,,,/HandyControl;component/Themes/Theme.xaml"/> </ResourceDictionary.MergedDictionaries> </ResourceDictionary> </Application.Resources> </prism:PrismApplication>

using FreeSqlDemo.ViewModels; using FreeSqlDemo.Views; using System.Configuration; using System.Data; using System.Windows; namespace FreeSqlDemo { /// <summary> /// Interaction logic for App.xaml /// </summary> public partial class App : PrismApplication { protected override Window CreateShell() { return Container.Resolve<MainWindow>(); } protected override void RegisterTypes(IContainerRegistry containerRegistry) { } protected override void ConfigureViewModelLocator() { base.ConfigureViewModelLocator(); ViewModelLocationProvider.Register<MainWindow,MainWindowViewModel>(); } } }

3.2、MainWindow.xaml代码如下。

<Window x:Class="FreeSqlDemo.Views.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:FreeSqlDemo" mc:Ignorable="d" xmlns:hc="https://handyorg.github.io/handycontrol" Title="MainWindow" Height="450" Width="800"> <StackPanel Orientation="Vertical"> <Label Content="{Binding Info}" Margin="10" Style="{DynamicResource LabelInfo}"/> <Button Style="{StaticResource ButtonPrimary}" Content="数据库连接测试" Command="{Binding InitDbCommand}"/> </StackPanel> </Window>

3.3、MainWindowViewModel.cs代码如下。

using FreeSqlDemo.DAL; using FreeSqlDemo.Models; using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FreeSqlDemo.ViewModels { public class MainWindowViewModel:BindableBase { private string _Info = "操作信息"; public string Info { get { return _Info; } set { SetProperty(ref _Info, value); } } private DelegateCommand _initDbCommand; public DelegateCommand InitDbCommand { get { return _initDbCommand ?? (_initDbCommand =new DelegateCommand(InitDb)); } } private void InitDb() { var blog = new Blog { Url = "http://sample.com" }; Info="数据库连接状态"+ DB.Sqlite.Ado.ExecuteConnectTest(); } } }

3.4、Blog.cs代码如下。

using System; using FreeSql.DataAnnotations; namespace FreeSqlDemo.Models { public class Blog { [Column(IsIdentity = true,IsPrimary = true)] public int BlogId { get; set; } public string Url { get; set; } public int Rating { get; set; } } }

3.5、DB.cs代码如下。

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FreeSqlDemo.DAL { public class DB { static Lazy<IFreeSql> sqliteLazy = new Lazy<IFreeSql>(() => { var fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=DB\DBDemo.db; Attachs=DBDemo.db; Pooling=true;Min Pool Size=1;Version=3;Journal Mode=WAL;Busy Timeout=10000;") .UseAutoSyncStructure(true) .Build(); return fsql; }); public static IFreeSql Sqlite => sqliteLazy.Value; } }

4、运行效果如下图。

5、示例代码如下（收点积分）。

https://download.csdn.net/download/xingchengaiwei/90155626

6、增删改查代只需要替换掉上面例子中的两部分代码即可。

6.1、操作界面MainWindow.xaml。

<Window x:Class="FreeSqlDemo.Views.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:FreeSqlDemo" mc:Ignorable="d" xmlns:hc="https://handyorg.github.io/handycontrol" Title="MainWindow" Height="450" Width="800"> <StackPanel Orientation="Vertical"> <Label Content="{Binding Info}" Margin="10" Style="{DynamicResource LabelInfo}"/> <Button Style="{StaticResource ButtonPrimary}" Content="数据库连接测试" Command="{Binding InitDbCommand}" Margin="10"/> <Button Style="{StaticResource ButtonPrimary}" Content="添加数据测试" Command="{Binding InsertDbCommand}" Margin="10"/> <Button Style="{StaticResource ButtonPrimary}" Content="查询数据测试" Command="{Binding QueryDbCommand}" Margin="10"/> <Button Style="{StaticResource ButtonPrimary}" Content="更新数据测试" Command="{Binding UpdateDbCommand}" Margin="10"/> <Button Style="{StaticResource ButtonPrimary}" Content="删除数据测试" Command="{Binding DeleteDbCommand}" Margin="10"/> </StackPanel> </Window>

6.2、MainWindowViewModel.cs代码如下。

using FreeSqlDemo.DAL; using FreeSqlDemo.Models; using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FreeSqlDemo.ViewModels { public class MainWindowViewModel : BindableBase { private string _Info = "操作信息"; public string Info { get { return _Info; } set { SetProperty(ref _Info, value); } } #region 连接数据库测试 private DelegateCommand _initDbCommand; public DelegateCommand InitDbCommand { get { return _initDbCommand ?? (_initDbCommand = new DelegateCommand(InitDb)); } } private void InitDb() { Info = "数据库连接状态" + DB.Sqlite.Ado.ExecuteConnectTest(); } #endregion #region 添加数据 private DelegateCommand _insertDbCommand; public DelegateCommand InsertDbCommand { get { return _insertDbCommand ?? (_insertDbCommand = new DelegateCommand(InsertDb)); } } private void InsertDb() { var blog = new Blog { Url = "http://sample.com" }; blog.BlogId = (int)DB.Sqlite.Insert<Blog>().AppendData(blog).ExecuteIdentity(); Info = "添加数据：" + "BlogId：" + blog.BlogId + "Url：" + blog.Url + "Rating：" + blog.Rating; } #endregion #region 查询数据 private DelegateCommand _queryDbCommand; public DelegateCommand QueryDbCommand { get { return _queryDbCommand ?? (_queryDbCommand = new DelegateCommand(QueryDB)); } } private void QueryDB() { var blogs = DB.Sqlite.Select<Blog>() .Where(b => b.Url != string.Empty) .OrderBy(b => b.BlogId) .Skip(2) .Limit(10) //第2行-12行的记录 .ToList(); Info = blogs.ToArray().ToString(); for (int i = 0; i < blogs.Count; i++) { Debug.WriteLine(blogs[i].BlogId + blogs[i].Url + blogs[i].Rating + "\n"); } } #endregion #region 更新数据 private DelegateCommand _updateDbCommand; public DelegateCommand UpdateDbCommand { get { return _updateDbCommand ?? (_updateDbCommand = new DelegateCommand(UpdateDB)); } } private void UpdateDB() { var blogs = DB.Sqlite.Update<Blog>() .Set(b => b.Url, "http://sample2222.com") .Where(b => b.Url == "http://sample.com") .ExecuteAffrows(); Info = "共更新数据：" + blogs.ToString() + "条"; } #endregion #region 删除数据 private DelegateCommand _deleteDbCommand; public DelegateCommand DeleteDbCommand { get { return _deleteDbCommand ?? (_deleteDbCommand = new DelegateCommand(DeleteDB)); } } private void DeleteDB() { var blogs = DB.Sqlite.Delete<Blog>() .Where(b => b.BlogId == 1) .ExecuteAffrows(); Info = "共删除数据：" + blogs.ToString() + "条"; } #endregion } }