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

1、开发环境1.1、开发工具VS2022.NET 81.2、GUI框架使用WPF1.3、MVVM使用的是 Prism1.4、WPF控件库使用的是HandyControl1.5、数据库使用的是Sqlite数据库。1.6、Sqlite3安装与数据库创建教程环境设置与基本操作2、项目整体结构如下图。3、主要代码如下。3.1、 App.xaml和App.xaml.cs代码如下。prism:PrismApplication x:ClassFreeSqlDemo.App xmlnshttp://schemas.microsoft.com/winfx/2006/xaml/presentation xmlns:xhttp://schemas.microsoft.com/winfx/2006/xaml xmlns:localclr-namespace:FreeSqlDemo xmlns:hchttps://handyorg.github.io/handycontrol xmlns:prismhttp://prismlibrary.com/ Application.Resources ResourceDictionary ResourceDictionary.MergedDictionaries ResourceDictionary Sourcepack://application:,,,/HandyControl;component/Themes/SkinDefault.xaml/ ResourceDictionary Sourcepack://application:,,,/HandyControl;component/Themes/Theme.xaml/ /ResourceDictionary.MergedDictionaries /ResourceDictionary /Application.Resources /prism:PrismApplicationusing 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.ResolveMainWindow(); } protected override void RegisterTypes(IContainerRegistry containerRegistry) { } protected override void ConfigureViewModelLocator() { base.ConfigureViewModelLocator(); ViewModelLocationProvider.RegisterMainWindow,MainWindowViewModel(); } } }3.2、MainWindow.xaml代码如下。Window x:ClassFreeSqlDemo.Views.MainWindow xmlnshttp://schemas.microsoft.com/winfx/2006/xaml/presentation xmlns:xhttp://schemas.microsoft.com/winfx/2006/xaml xmlns:dhttp://schemas.microsoft.com/expression/blend/2008 xmlns:mchttp://schemas.openxmlformats.org/markup-compatibility/2006 xmlns:localclr-namespace:FreeSqlDemo mc:Ignorabled xmlns:hchttps://handyorg.github.io/handycontrol TitleMainWindow Height450 Width800 StackPanel OrientationVertical Label Content{Binding Info} Margin10 Style{DynamicResource LabelInfo}/ Button Style{StaticResource ButtonPrimary} Content数据库连接测试 Command{Binding InitDbCommand}/ /StackPanel /Window3.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 LazyIFreeSql sqliteLazy new LazyIFreeSql(() { var fsql new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.Sqlite, Data SourceDB\DBDemo.db; AttachsDBDemo.db; Poolingtrue;Min Pool Size1;Version3;Journal ModeWAL;Busy Timeout10000;) .UseAutoSyncStructure(true) .Build(); return fsql; }); public static IFreeSql Sqlite sqliteLazy.Value; } }4、运行效果如下图。5、示例代码如下收点积分。https://download.csdn.net/download/xingchengaiwei/901556266、增删改查代只需要替换掉上面例子中的两部分代码即可。6.1、操作界面MainWindow.xaml。Window x:ClassFreeSqlDemo.Views.MainWindow xmlnshttp://schemas.microsoft.com/winfx/2006/xaml/presentation xmlns:xhttp://schemas.microsoft.com/winfx/2006/xaml xmlns:dhttp://schemas.microsoft.com/expression/blend/2008 xmlns:mchttp://schemas.openxmlformats.org/markup-compatibility/2006 xmlns:localclr-namespace:FreeSqlDemo mc:Ignorabled xmlns:hchttps://handyorg.github.io/handycontrol TitleMainWindow Height450 Width800 StackPanel OrientationVertical Label Content{Binding Info} Margin10 Style{DynamicResource LabelInfo}/ Button Style{StaticResource ButtonPrimary} Content数据库连接测试 Command{Binding InitDbCommand} Margin10/ Button Style{StaticResource ButtonPrimary} Content添加数据测试 Command{Binding InsertDbCommand} Margin10/ Button Style{StaticResource ButtonPrimary} Content查询数据测试 Command{Binding QueryDbCommand} Margin10/ Button Style{StaticResource ButtonPrimary} Content更新数据测试 Command{Binding UpdateDbCommand} Margin10/ Button Style{StaticResource ButtonPrimary} Content删除数据测试 Command{Binding DeleteDbCommand} Margin10/ /StackPanel /Window6.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.InsertBlog().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.SelectBlog() .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.UpdateBlog() .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.DeleteBlog() .Where(b b.BlogId 1) .ExecuteAffrows(); Info 共删除数据 blogs.ToString() 条; } #endregion } }