云服务器ecs_宁波效果图公司_重庆专业seo_设计公司排名前十强

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

1、安装包

dotnet add package FreeSql
dotnet add package FreeSql.Provider.SqlServer-- 仓储
dotnet add package FreeSql.DbContext

2、Program.cs 文件

using FreeSql;
using Microsoft.OpenApi.Models;
using System.Configuration;var builder = WebApplication.CreateBuilder(args);builder.Services.AddControllers();#region FreeSqlFunc<IServiceProvider, IFreeSql> fsqlFactory = r =>
{IFreeSql fsql = new FreeSqlBuilder().UseConnectionString(DataType.SqlServer, builder.Configuration.GetConnectionString("SqlServerDefault"))//.UseConnectionString(FreeSql.DataType.SqlServer, r.GetService<IConfiguration>()["ConnectionStrings:Default"]).UseMonitorCommand(cmd => Console.WriteLine($"Sql：{cmd.CommandText}"))//监听SQL语句//.UseAutoSyncStructure(true) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。.UseLazyLoading(false).UseNoneCommandParameter(true).Build();return fsql;
};
/// 仓储
builder.Services.AddFreeRepository();
builder.Services.AddScoped<UnitOfWorkManager>();builder.Services.AddSingleton(fsqlFactory);#endregionvar 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": {"SqlServerDefault": "data source=.;initial catalog=dbTest;uid=sa;password=000000;TrustServerCertificate=True;","Default": "Data Source=localhost;Port=3306;Database=dbTest;uid=root;pwd=1q2w3E*;charset=utf8mb4;Allow User Variables=true;AllowLoadLocalInfile=true;MinimumPoolSize=50;MaximumPoolSize=1000","DbType": "MySql"},"AllowedHosts": "*"
}

4、FreeSqlController.cs 文件

using CodeFirst.Entity;
using Microsoft.AspNetCore.Mvc;namespace Trial.WebAPI.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_FreeSql{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_FreeSql> 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_FreeSql>();for (int i = 0; i < 10; i++){items.Add(new Transfer_Amount_Relation_copy2_FreeSql{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(){// 如果查询结果为空，ToOne方法会返回null，而不是抛出异常‌var toOne = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>().Where(u => u.Id == 1000).ToOne();// 如果查询结果为空，First方法会抛出异常(经过测试，不会抛异常)var first = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>().Where(u => u.Id == 1000).First();return Ok(new { data = toOne });}[HttpGet]public IActionResult PageTest(){long total = 0;decimal actualPaymentFee = 100;#region 分页1var page1 = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>().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_FreeSql>().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_FreeSql>().Where(u => u.ActualPaymentFee > 0).OrderBy(u => u.ActualPaymentFee);total = page3.Count();var list = page3.Page(1, 30).ToList();#endregionreturn Ok(new { data = list });}/// <summary>/// sql 语句/// </summary>/// <returns></returns>[HttpGet]public IActionResult WithSqlTest(){#region selectvar list = _fsql.Select<Transfer_Amount_Relation_copy2_FreeSql>().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_FreeSql>(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 });}/// <summary>/// sql 语句/// </summary>/// <returns></returns>[HttpGet]public IActionResult AdoTest(){#region 多条记录var list = _fsql.Ado.Query<Transfer_Amount_Relation_copy2>("select top 10 * from Transfer_Amount_Relation_copy2").ToList();#endregion#region 单条记录var model = _fsql.Ado.QuerySingle<Transfer_Amount_Relation_copy2>("select * from Transfer_Amount_Relation_copy2 where Id = @Id", new { Id = 50 });#endregion#region 多个结果集var sql1 = "select top 10 * from Transfer_Amount_Relation_copy2";var sql2 = "select top 20 * from Transfer_Amount_Relation_copy2";var result = _fsql.Ado.Query<Transfer_Amount_Relation_copy2, Transfer_Amount_Relation_copy2>($"{sql1};{sql2}");List<Transfer_Amount_Relation_copy2> list1 = result.Item1;List<Transfer_Amount_Relation_copy2> list2 = result.Item2;#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、SimpleFreeSqlRepository.cs 文件

using CodeFirst.Entity;
using FreeSql;namespace Trial.Repository.AmountRelation
{public interface ISimpleFreeSqlRepository{Task<long> Create(Transfer_Amount_Relation_copy2_FreeSql input);Task<long> Modify(Transfer_Amount_Relation_copy2_FreeSql input);Task<Transfer_Amount_Relation_copy2_FreeSql> GetAsync(long id);Task<List<Transfer_Amount_Relation_copy2_FreeSql>> GetPageList();/// <summary>/// WithSql/// </summary>/// <returns></returns>Task<List<Transfer_Amount_Relation_copy2_FreeSql>> GetPageListWithSql();}public class SimpleFreeSqlRepository : BaseRepository<Transfer_Amount_Relation_copy2_FreeSql>, ISimpleFreeSqlRepository{public SimpleFreeSqlRepository(IFreeSql fsql) : base(fsql) { }/// <summary>/// /// </summary>/// <param name="input"></param>/// <returns></returns>public async Task<long> Create(Transfer_Amount_Relation_copy2_FreeSql input){Transfer_Amount_Relation_copy2_FreeSql model = await InsertAsync(input);return model.Id;}/// <summary>/// /// </summary>/// <param name="input"></param>/// <returns></returns>public async Task<long> Modify(Transfer_Amount_Relation_copy2_FreeSql input){int count = await UpdateAsync(input);return count;}/// <summary>/// /// </summary>/// <param name="id"></param>/// <returns></returns>public async Task<Transfer_Amount_Relation_copy2_FreeSql> GetAsync(long id){var model = await Select.WhereDynamic(id).ToOneAsync<Transfer_Amount_Relation_copy2_FreeSql>();var model1 = Select.Where(x => x.Id == id).First();return model;}/// <summary>/// /// </summary>/// <returns></returns>public async Task<List<Transfer_Amount_Relation_copy2_FreeSql>> GetPageList(){var list = await Select.Where(u => u.ActualPaymentFee > 0).Where(x => x.Id > 0).OrderBy(x => x.Id).Skip(1).Take(100).ToListAsync();return list;}public async Task<List<Transfer_Amount_Relation_copy2_FreeSql>> GetPageListWithSql(){string sql = "select * from Transfer_Amount_Relation_copy2";var list = await Select.WithSql(sql).Where(x => x.Id > 0).OrderBy(x => x.Id).Skip(1).Take(100).ToListAsync();return list;}}
}

6、FreeSqlSimpleController.cs 仓储 

using CodeFirst.Entity;
using Microsoft.AspNetCore.Mvc;
using Trial.Repository.AmountRelation;namespace Trial.WebAPI.Controllers
{[Route("api/FreeSqlSimple/[action]")][ApiController]public class FreeSqlSimpleController : ControllerBase{private readonly ISimpleFreeSqlRepository _repository;/// <summary>/// /// </summary>/// <param name="repository"></param>public FreeSqlSimpleController(ISimpleFreeSqlRepository repository){_repository = repository;}/// <summary>/// /// </summary>/// <returns></returns>[HttpGet]public async Task<ActionResult> Get(){/// 插入long id = await _repository.Create(new Transfer_Amount_Relation_copy2_FreeSql{UserName = "UserName-FreeSql",ActualPaymentFee = 100,Recipient = "Recipient-FreeSql",BankCardNumber = "BankCardNumber-FreeSql",IDNumber = "IDNumber-FreeSql",PaymentTime = DateTime.Now,OrderNo = "OrderNo-FreeSql",InputDetailId = 1,BigAmountId = 1,UsageAmount = 10});/// 查询单条var model = await _repository.GetAsync(50);/// 分页var list = await _repository.GetPageList();/// 分页var withSqlList = await _repository.GetPageListWithSql();return Ok();}}
}

7、实体类   实体特性  

using FreeSql.DataAnnotations;namespace CodeFirst.Entity
{/// <summary>/// FreeSql/// </summary>[Table(Name = "Transfer_Amount_Relation_copy2")]public class Transfer_Amount_Relation_copy2_FreeSql{[Column(IsIdentity = true, IsPrimary = true)]public long Id { get; set; }/// <summary>/// 用户名/// </summary>public string UserName { get; set; }/// <summary>/// 实付费用/// </summary>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>public decimal UsageAmount { get; set; }}
}

==================== 仓储 abstract 抽象类 ====================
用于 仓储 扩展方法，自定义方法
创建自定义 IRepositoryBase.cs 接口文件，并且继承 FreeSql框架本身的 IBaseRepository 接口

using System;
using System.Linq.Expressions;
using FreeSql;namespace Trial.Repository.FreeSql
{public interface IRepositoryBase<TEntity, TKey> : IBaseRepository<TEntity, TKey> where TEntity : class{/// <summary>/// 获得Dto/// </summary>/// <typeparam name="TDto"></typeparam>/// <param name="id"></param>/// <returns></returns>Task<TDto> GetAsync<TDto>(TKey id);/// <summary>/// 根据条件获取实体/// </summary>/// <param name="exp"></param>/// <returns></returns>Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> exp);/// <summary>/// 根据条件获取Dto/// </summary>/// <param name="exp"></param>/// <returns></returns>Task<TDto> GetAsync<TDto>(Expression<Func<TEntity, bool>> exp);/// <summary>/// 软删除/// </summary>/// <param name="id"></param>/// <returns></returns>Task<bool> SoftDeleteAsync(TKey id);/// <summary>/// 批量删除/// </summary>/// <param name="id"></param>/// <returns></returns>Task<bool> SoftDeleteAsync(TKey[] id);}public interface IRepositoryBase<TEntity> : IRepositoryBase<TEntity, long> where TEntity : class{}
}

创建自定义 RepositoryBase.cs 类文件，并且继承 FreeSql框架本身的 BaseRepository 类

using FreeSql;
using System.Linq.Expressions;namespace Trial.Repository.FreeSql
{public abstract class RepositoryBase<TEntity, TKey> : BaseRepository<TEntity, TKey> where TEntity : class, new(){protected RepositoryBase(UnitOfWorkManager uowm) : base(uowm.Orm){uowm.Binding(this);}/// <summary>/// 查询单条/// </summary>/// <typeparam name="TDto"></typeparam>/// <param name="id"></param>/// <returns></returns>public virtual Task<TDto> GetAsync<TDto>(TKey id){return Select.WhereDynamic(id).ToOneAsync<TDto>();}/// <summary>/// 根据条件获取实体/// </summary>/// <param name="exp"></param>/// <returns></returns>public virtual Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> exp){return Select.Where(exp).ToOneAsync();}/// <summary>/// 根据条件获取Dto/// </summary>/// <typeparam name="TDto"></typeparam>/// <param name="exp"></param>/// <returns></returns>public virtual Task<TDto> GetAsync<TDto>(Expression<Func<TEntity, bool>> exp){return Select.Where(exp).ToOneAsync<TDto>();}public async Task<bool> SoftDeleteAsync(TKey id){await UpdateDiy.SetDto(new { IsDeleted = true }).WhereDynamic(id).ExecuteAffrowsAsync();return true;}public async Task<bool> SoftDeleteAsync(TKey[] ids){await UpdateDiy.SetDto(new { IsDeleted = true }).WhereDynamic(ids).ExecuteAffrowsAsync();return true;}}public abstract class RepositoryBase<TEntity> : RepositoryBase<TEntity, long> where TEntity : class, new(){protected RepositoryBase(UnitOfWorkManager uowm) : base(uowm){}}
}

*、AmountRelationFreeSqlRepository.cs 文件
因为继承了 FreeSql框架本身的 IBaseRepository 接口，可以直接使用InsertAsync，UpdateAsync，DeleteAsync 等方法，所以不用再自定义 增删改查 方法 

using CodeFirst.Entity;
using FreeSql;
using Trial.Repository.FreeSql;namespace Trial.Repository.AmountRelation
{/// <summary>/// 继承了基类/// </summary>public interface IAmountRelationFreeSqlRepository : IRepositoryBase<Transfer_Amount_Relation_copy2_FreeSql>{}public class AmountRelationFreeSqlRepository : RepositoryBase<Transfer_Amount_Relation_copy2_FreeSql>, IAmountRelationFreeSqlRepository{public AmountRelationFreeSqlRepository(UnitOfWorkManager uowm) : base(uowm){}}
}

*、FreeSqlAmountRelationController.cs 文件 

using CodeFirst.Entity;
using Microsoft.AspNetCore.Mvc;
using Trial.Repository.AmountRelation;namespace Trial.WebAPI.Controllers
{[Route("api/AmountRelationFreeSql/[action]")][ApiController]public class FreeSqlAmountRelationController : ControllerBase{private readonly IAmountRelationFreeSqlRepository _repository;public FreeSqlAmountRelationController(IAmountRelationFreeSqlRepository repository){_repository = repository;}[HttpGet]public async Task<ActionResult> Get(){var info = await _repository.InsertAsync(new Transfer_Amount_Relation_copy2_FreeSql{UserName = "UserName-FreeSql",ActualPaymentFee = 100,Recipient = "Recipient-FreeSql",BankCardNumber = "BankCardNumber-FreeSql",IDNumber = "IDNumber-FreeSql",PaymentTime = DateTime.Now,OrderNo = "OrderNo-FreeSql",InputDetailId = 1,BigAmountId = 1,UsageAmount = 10});int count = await _repository.UpdateAsync(new Transfer_Amount_Relation_copy2_FreeSql{Id = info.Id,UserName = "UserName-FreeSql-修改",ActualPaymentFee = 100,Recipient = "Recipient-FreeSql",BankCardNumber = "BankCardNumber-FreeSql",IDNumber = "IDNumber-FreeSql",PaymentTime = DateTime.Now,OrderNo = "OrderNo-FreeSql",InputDetailId = 1,BigAmountId = 1,UsageAmount = 10});/// 框架方法var result0 = await _repository.GetAsync(50);/// 自定义方法var result1 = await _repository.GetAsync<Transfer_Amount_Relation_copy2_FreeSql>(51);/// 自定义方法var result2 = await _repository.GetAsync(x => x.Id == 52);return Ok();}}
}

*
*
*
*
*
*