官网
实体特性
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 FreeSql
Func<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);
#endregion
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": {
"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();
/// 返回自增
/// 表有自增列,插入数据后应该要返回 id
long 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
});
}
/// 批量插入 Affrows
int 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 分页1
var 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 分页2
var 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();
#endregion
return Ok(new { data = list });
}
/// <summary>
/// sql 语句
/// </summary>
/// <returns></returns>
[HttpGet]
public IActionResult WithSqlTest()
{
#region select
var 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 select
var sql = "select top 10 * from Transfer_Amount_Relation_copy2";
var list1 = _fsql.Ado.Query<Transfer_Amount_Relation_copy2_FreeSql>(sql).ToList();
#endregion
#region INSERT
var 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
});
#endregion
return 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 INSERT
var 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
});
#endregion
return 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();
}
}
}
*
*
*
*
*
*