FreeSql

官网  
实体特性 
Ado 它包括所有对 SQL 操作的封装，提供 ExecuteReader、ExecuteDataSet、ExecuteDataTable、ExecuteNonQuery、ExecuteScalar 等方法，使用起来和传统 SqlHelper 一样。

1、安装包

Install-Package FreeSql
Install-Package FreeSql.Provider.SqlServer

2、Program.cs 文件

using FreeSql;
using Microsoft.OpenApi.Models;
using System.Configuration;var builder = WebApplication.CreateBuilder(args);builder.Services.AddControllers();Func<IServiceProvider, IFreeSql> fsqlFactory = r =>
{IFreeSql fsql = new FreeSqlBuilder().UseConnectionString(DataType.SqlServer, builder.Configuration.GetConnectionString("Default"))//.UseConnectionString(FreeSql.DataType.SqlServer, r.GetService<IConfiguration>()["ConnectionStrings:Default"]).UseMonitorCommand(cmd => Console.WriteLine($"Sql：{cmd.CommandText}"))//监听SQL语句//.UseAutoSyncStructure(true) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。.Build();return fsql;
};builder.Services.AddSingleton<IFreeSql>(fsqlFactory);var app = builder.Build();// Configure the HTTP request pipeline.app.UseAuthorization();//在项目启动时，从容器中获取IFreeSql实例，并执行一些操作：同步表，种子数据,FluentAPI等
using (IServiceScope serviceScope = app.Services.CreateScope())
{var fsql = serviceScope.ServiceProvider.GetRequiredService<IFreeSql>();//fsql.CodeFirst.SyncStructure(typeof(Topic));//Topic 为要同步的实体类//同步实体类到数据库
}app.MapControllers();app.Run();

3、appsettings.json 文件

{"ConnectionStrings": {"Default": "data source=.;initial catalog=dbTest;uid=sa;password=000000;TrustServerCertificate=True;"},"AllowedHosts": "*"
}

4、FreeSqlController 文件

using Microsoft.AspNetCore.Mvc;namespace WebApp.Controllers
{[Route("api/FreeSql/[action]")][ApiController]public class FreeSqlController : ControllerBase{private readonly IFreeSql _fsql;public FreeSqlController(IFreeSql freeSql){_fsql = freeSql;}/// <summary>/// 单条插入/// </summary>/// <returns></returns>[HttpGet]public IActionResult Insert(){var model = new Transfer_Amount_Relation_copy2{UserName = "UserName",ActualPaymentFee = 100,Recipient = "Recipient",BankCardNumber = "BankCardNumber",IDNumber = "IDNumber",PaymentTime = DateTime.Now,OrderNo = "OrderNo",InputDetailId = 1,BigAmountId = 1,UsageAmount = 10};/// 返回即将执行的 SQL 语句string sqlString = _fsql.Insert(model).ToSql();/// 返回插入后的记录List<Transfer_Amount_Relation_copy2> t1 = _fsql.Insert(model).ExecuteInserted();/// 返回影响的行数int t2 = _fsql.Insert(model).ExecuteAffrows();/// 返回自增/// 表有自增列，插入数据后应该要返回 idlong id = _fsql.Insert(model).ExecuteIdentity();return Ok(new { data = model });}/// <summary>/// 批量插入/// </summary>/// <returns></returns>[HttpGet]public IActionResult BulkCopy(){var items = new List<Transfer_Amount_Relation_copy2>();for (int i = 0; i < 10; i++){items.Add(new Transfer_Amount_Relation_copy2{UserName = "UserName" + i.ToString(),ActualPaymentFee = 100,Recipient = "Recipient" + i.ToString(),BankCardNumber = "BankCardNumber" + i.ToString(),IDNumber = "IDNumber" + i.ToString(),PaymentTime = DateTime.Now,OrderNo = "OrderNo",InputDetailId = 1,BigAmountId = 1,UsageAmount = 10});}/// 批量插入 Affrowsint t = _fsql.Insert(items).ExecuteAffrows();/// 批量插入 SqlBulkCopy_fsql.Insert(items).ExecuteSqlBulkCopy();return Ok();}[HttpGet]public IActionResult SelectTest(){var info = _fsql.Select<Transfer_Amount_Relation_copy2>().Where(u => u.ActualPaymentFee > 0).First();var model = _fsql.Select<Transfer_Amount_Relation_copy2>().Where(u => u.Id == 1).First();return Ok(new { data = info });}[HttpGet]public IActionResult PageTest(){long total = 0;decimal actualPaymentFee = 100;#region 分页1var page1 = _fsql.Select<Transfer_Amount_Relation_copy2>().WhereIf(actualPaymentFee > 0, x => x.ActualPaymentFee > 0).OrderBy(b => b.Id).Skip(0).Limit(10) //第100行-110行的记录.ToList();#endregion#region 分页2var page2 = _fsql.Select<Transfer_Amount_Relation_copy2>().Where(u => u.ActualPaymentFee > 0).OrderBy(u => u.ActualPaymentFee).Count(out total) //总记录数量.Page(1, 20).ToList();#endregion#region 分页3/// 数据量大一般不建议查 Count/CountAsync，而应该采用流式分页（上一页、下一页、不返回总数量）total = 0;var page3 = _fsql.Select<Transfer_Amount_Relation_copy2>().Where(u => u.ActualPaymentFee > 0).OrderBy(u => u.ActualPaymentFee);total = page3.Count();var list = page3.Page(1, 30).ToList();#endregionreturn Ok(new { data = list });}[HttpGet]public IActionResult WithSqlTest(){#region selectvar list = _fsql.Select<Transfer_Amount_Relation_copy2>().WithSql("select * from Transfer_Amount_Relation_copy2 where ActualPaymentFee > @val", new { val = 0 }).Page(1, 10).ToList();#endregion#region selectvar sql = "select top 10 * from Transfer_Amount_Relation_copy2";var list1 = _fsql.Ado.Query<Transfer_Amount_Relation_copy2>(sql).ToList();#endregion#region INSERTvar insertSql = "INSERT INTO [Transfer_Amount_Relation_copy2]" +"([UserName], [ActualPaymentFee], [Recipient], [BankCardNumber], [IDNumber], [OrderNo], [PaymentTime], [BigAmountId], [InputDetailId], [UsageAmount]) " +"VALUES" +"(@UserName, @ActualPaymentFee, @Recipient, @BankCardNumber, @IDNumber, @OrderNo, @PaymentTime, @BigAmountId, @InputDetailId, @UsageAmount)";var affectedRows = _fsql.Ado.ExecuteNonQuery(insertSql, new{UserName = "UserName100",ActualPaymentFee = 100,Recipient = "Recipient",BankCardNumber = "BankCardNumber",IDNumber = "IDNumber",OrderNo = "OrderNo",PaymentTime = DateTime.Now,BigAmountId = 1,InputDetailId = 1,UsageAmount = 10});#endregionreturn Ok(new { data = list });}}
}

5、实体类   实体特性  

using FreeSql.DataAnnotations;namespace WebApp
{[Table(Name = "Transfer_Amount_Relation_copy2")]public class Transfer_Amount_Relation_copy2{[Column(IsIdentity = true, IsPrimary = true)]public long Id { get; set; }/// <summary>/// 用户名/// </summary>[Column(StringLength = 100)]public string UserName { get; set; }/// <summary>/// 实付费用/// </summary>[Column(IsNullable = false)]public decimal ActualPaymentFee { get; set; }/// <summary>/// 收款人/// </summary>public string Recipient { get; set; }/// <summary>/// 银行卡号/// </summary>public string BankCardNumber { get; set; }/// <summary>/// 身份证号/// </summary>public string IDNumber { get; set; }/// <summary>/// 订单号/// </summary>public string OrderNo { get; set; }/// <summary>/// 支付时间/// </summary>public DateTime PaymentTime { get; set; }/// <summary>/// /// </summary>public int BigAmountId { get; set; }/// <summary>/// /// </summary>public int InputDetailId { get; set; }/// <summary>/// 使用金额/// </summary>[Column(Precision = 10, Scale = 2)]public decimal UsageAmount { get; set; }}
}

*
*
*
*
*
*