FreeSql基础入门
一、了解到FreeSql
ORM框架是很早之前就在接触了比如CYQ.Data，这段时间在群里面发现有的猿在用一个新的ORM框架,这个也是由于自己刚刚得知有这么个ORM的原因，那就是FreeSql,这是一个功能强大的对象关系映射程序(O/RM)，支持 .NETCore 2.1+ 或 .NETFramework 4.5+,目前更新到了0.9版本，FreeSql 使用模型执行数据访问，模型由实体类表示数据库表或视图，用于查询和保存数据。
二、安装FreeSql
第1种： 通过NuGet程序包里面搜索FreeSql,可以搜索到FreeSql的安装包和支持的数据库安装包,FreeSql.DbContext是必须要的,然后是数据库连接,此处用的是SqlServer,所以安装了FreeSql.Provider.SqlServer。
第2种：通过控制台程序命令安装,GitHub提供的是 dotnet add package FreeSql,但是此处安装中会提示找不到包,估计是由于不是原始包的原因,采用了dotnet add 项目名称 package FreeSql 就可以解决,不过还是建议采用第一种方式进行安装.
三、项目配置和使用:此处采用的是dotnet Core API
1、创建数据库,配置数据
2、添加dotnet core api项目(这里不再详细说明,反正大家都会....)
3、配置项目,此处只是实例,所以没有用到批量注入,如有需要了解ORM批量注入的猿友可以查看:https://www.cnblogs.com/chj929555796/p/11114684.html
public static IFreeSql Fsql { get; private set; } public Startup(IConfiguration configuration) { Configuration = configuration; Fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.SqlServer, "Data Source=.;Initial Catalog=FQDemo;Integrated Security=False;User ID=sa;Password=123456") .UseAutoSyncStructure(true) .UseLazyLoading(true) .UseNoneCommandParameter(true) .Build(); } public IConfiguration Configuration { get; } // This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2); //注入 services.AddSingleton<IFreeSql>(Fsql); services.AddFreeDbContext<StudentContext>(options => options.UseFreeSql(Fsql)); }
4、添加Model
public class StuClass { public int ID { get; set; } public string ClassName { get; set; } } //如果数据库表名和实体表名不一致的情况下需要通过Table来指定数据库表名 [Table(Name = "Student")] public class Student { //唯一键标识,不然数据库表字段会根据实体重新生成 [Column(IsIdentity = true)] public int StuNo { get; set; } public string StuName { get; set; } public int StuAge { get; set; } public int ClassID { get; set; } public StuClass StuClass { get; set; } }
5、添加一个StudentController控制器，通过构造函数注入
获取数据实例：
public Object GetStudentList() { ISelect<Student> select = _fsOrm.Select<Student>(); //多表联合查询(无导航属性联表) List<Student> stuJoinDHList = select.LeftJoin<StuClass>((s, c) => s.ClassID == c.ID).ToList(); //多表联合查询(导航属性联表：s.StuClass.ID为导航属性) List<Student> stuJoinList = select.LeftJoin(s => s.ClassID == s.StuClass.ID).ToList(); //查询所有 List<Student> stuList = select.ToList(); //条件查询 List<Student> stuWhereList = select.Where(s => s.ClassID == 2).ToList(); //分页查询Page(页码:1开始,每页大小:如每页20条),OrderByDescending倒序,默认为正序 List<Student> stuPageList = select.Where(s => s.ClassID == 2).Page(1, 20).OrderByDescending(o => o.StuNo).ToList(); return Newtonsoft.Json.JsonConvert.SerializeObject(stuList); }
新增数据实例:
public Object AddStudent() { FreeSql.IInsert<Student> addStu = _fsOrm.Insert<Student>(); //添加一行 Student student = new Student(); student.StuName = "王五"; student.StuAge = 0; int count1 = addStu.AppendData(student).ExecuteAffrows(); //批量添加 List<Student> students = new List<Student>(); students.Add(new Student() { StuName = "雨", StuAge = 1 }); students.Add(new Student() { StuName = "雪", StuAge = 1 }); int count2 = addStu.AppendData(students).ExecuteAffrows(); return true; }
修改数据实例：
public Object UpdateStudent() { IUpdate<Student> updateStu = _fsOrm.Update<Student>(); //更新单列 int count = updateStu.Set(s => s.StuAge, 0).Where(w => w.StuNo == 5).ExecuteAffrows(); //更新多列 Student item = new Student { StuNo = 5, StuAge = 1, StuName = "风" }; int count1 = updateStu.SetSource(item).ExecuteAffrows(); //批量跟新 List<Student> students = new List<Student>(); students.Add(new Student { StuNo = 4, StuName = "张飞", StuAge = 1 }); students.Add(new Student { StuNo = 5, StuName = "毕傲婵", StuAge = 0 }); int count2 = updateStu.SetSource(students).ExecuteAffrows(); students.Clear(); //保存实体忽略列 students.Add(new Student { StuNo = 4, StuName = "张飞1", StuAge = 0 }); students.Add(new Student { StuNo = 5, StuName = "毕傲婵1", StuAge = 1 }); int count3 = updateStu.SetSource(students).IgnoreColumns(i => i.StuAge).ExecuteAffrows(); students.Clear(); //只更新指定的列 students.Add(new Student { StuNo = 4, StuName = "张飞", StuAge = 0 }); students.Add(new Student { StuNo = 5, StuName = "毕傲婵", StuAge = 1 }); int count4 = updateStu.UpdateColumns(s => s.StuName).ExecuteAffrows(); return count; }
删除数据实例，此处删除数据只用了"事务"删除,包括了Transaction、UnitOfWork两种,其他不通过事务和DBContext(暂未发现有Delete操作,不知道后期造轮子的大佬会不会加一颗零件)请自行发挥。
public Object DeleteStu() { IDelete<Student> delStu = _fsOrm.Delete<Student>(); IDelete<StuClass> delStuClass = _fsOrm.Delete<StuClass>(); //同线程事务 _fsOrm.Transaction(() => { int count = delStu.Where(w => w.ClassID == 1).ExecuteAffrows(); if (count > 0) count = delStuClass.Where(w => w.ID == 1).ExecuteAffrows(); if (count < 1) throw new Exception("处理失败,事务回滚"); }); //CreateUnitOfWork(UnitOfWork + Repository) using (IRepositoryUnitOfWork sw = _fsOrm.CreateUnitOfWork()) { var delStu1 = sw.GetRepository<Student>(); var delStuClass1 = sw.GetRepository<StuClass>(); int count = delStu1.Delete(d => d.ClassID == 2); count = delStuClass1.Delete(d => d.ID == 5); if (count > 0) { sw.Commit(); } else { sw.Rollback(); throw new Exception("处理失败,事务回滚"); } }; return true; }
以上就是学习FreeSql的入门记录,还有很多实例没有一一列举,如有需要的猿友可到https://github.com/2881099/FreeSql/wiki了解,如有不对的地方,感谢指导！
