freeSql的使⽤与讲解
本⽂讲解freesql的使⽤
话不多说，直接上代码，注释的很清楚了，如果还是看不懂，那我认为要不是你基础不够，去补补基础再来学习这个，要不就是..你不适合⼲开发了
增删改查
using System;
using System.Collections.Generic;
using System.Data;
using FreeSql;
namespace ConsoleForTestFreeSql
{
class Program
{
private static IFreeSql _FreeSqlInstance = null;
static void Main(string[] args)
{
try
{
CreateFreeSqlInstance();
int count = 0;//增删改影响的数据⾏数
//ado操作
//查询
var dt = _FreeSqlInstance.Ado.ExecuteDataTable(@"SELECT * FROM dbo.UserInfoTest");
/
/增删改
count = _FreeSqlInstance.Ado.ExecuteNonQuery(@"UPDATE dbo.UserInfoTest SET LastUpdateDate = GETDATE() WHERE Sex = '男'"); //都是可以带参数和事务执⾏的（具体更多⼤家可以转到定义⾃⼰前去查看）
//新增操作
var addList = new List<UserInfo>()
{
new UserInfo()
{
Name = "Ling、bug",
Age = 25,
Sex = "男",
Status = 1,
CreateDate = DateTime.Now
},
new UserInfo()
{
Name = "张三",
Sex = "⼥",
Status = 2,
CreateDate = DateTime.Now
}
};
/
/新增单条数据，返回新增数据实体集合
var addResultList = _FreeSqlInstance.Insert(addList[0]).ExecuteInserted();
//新增单条数据，返回新增⾏数
count = _FreeSqlInstance.Insert(addList[0]).ExecuteAffrows();
//新增单条数据，返回⾃增字段值（确保要有⾃增字段）
var identityValue = _FreeSqlInstance.Insert(addList[0]).ExecuteIdentity();
count = _FreeSqlInstance.Insert(addList).ExecuteAffrows();
//不使⽤参数化新增（可以查看打印的sql，⾄于不使⽤参数化担⼼sql注⼊问题，freesql内部也进⾏了处理，不⽤担⼼）
count = _FreeSqlInstance.Insert(addList[0]).NoneParameter().ExecuteAffrows();
//新增指定列
count = _FreeSqlInstance.Insert(addList[0]).InsertColumns(addList[0].GetChanged()).ExecuteAffrows();
//不新增忽略的列
count = _FreeSqlInstance.Insert(addList[0]).IgnoreColumns(addList[0].GetChanged()).ExecuteAffrows();
//说明：优先级为：全部列 < 指定列(InsertColumns) < 忽略列(IgnoreColumns)
//更新操作
//更新指定列（Update⽅法的参数指的是主键参数），返回修改的数据实体集合writeline使用方法python
var updateResultList = _FreeSqlInstance.Update<UserInfo>(1)
.Set(r => r.LastUpdateDate, DateTime.Now)
.ExecuteUpdated();
//更新多个指定列，Set可以拼接多个，返回影响的⾏数
count = _FreeSqlInstance.Update<UserInfo>(1)
.Set(r => r.LastUpdateDate, DateTime.Now)
.Set(r => r.Age, 18)
.ExecuteAffrows();
//表达式⽬录树指定更新，可以在原有的数据基础上更新，如++，--等等
count = _FreeSqlInstance.Update<UserInfo>(1)
.Set(r => new UserInfo()
{
LastUpdateDate = DateTime.Now,
Age = r.Age + 1
})
.
ExecuteAffrows();
//⾃定义where条件
count = _FreeSqlInstance.Update<UserInfo>()
.Set(r => r.LastUpdateDate, DateTime.Now)
.Where(r => r.Name == "张三")
.ExecuteAffrows();
var updateModel = new UserInfo()
{
Age = 28,
LastUpdateDate = DateTime.Now
};
/
/实体更新
count = _FreeSqlInstance.Update<UserInfo>()
.SetDto(updateModel)
.Where(r => r.Name == "张三")
.ExecuteAffrows();
//更新指定列
count = _FreeSqlInstance.Update<UserInfo>()
.SetDto(updateModel)
.UpdateColumns(updateModel.GetChanged())
.Where(r => r.Name == "张三")
.ExecuteAffrows();
/
/指定忽略哪些列不更新
count = _FreeSqlInstance.Update<UserInfo>()
.SetDto(updateModel)
.IgnoreColumns(updateModel.GetUnChanged())
.Where(r => r.Name == "张三")
.ExecuteAffrows();
//说明：优先级和新增的⼀样
//还有类似于EF的实体跟踪更新，需要使⽤FreeSql.Repository扩展，这个我觉得⿇烦就不演⽰了，不难，我相信⼤家⼀看就会，毕竟⼤家都是从EF过来的⼈ //如果没有更新条件（没有主键参数也没有where参数），freesql为了防⽌误修改全表数据，不会执⾏更新操作，要想修改全表数据，可以使⽤where("1=1")
//删除操作，这⾥不做过多介绍，就延时最简单的删除，官⽅也建议在实际开发中，将删除操作作为软
删除去操作，⽽不要真实删除，毕竟...后期增加个回收 _FreeSqlInstance.Delete<UserInfo>().Where(r => r.UserId == 1).ExecuteAffrows();
//事务操作：核⼼（WithTransaction(UnitOfWork.GetOrBeginTransaction())）
_FreeSqlInstance.Transaction(IsolationLevel.Serializable, () =>
{
var addItem = new UserInfo()
{
Name = "李四",
Age = 18,
Status = 1,
CreateDate = DateTime.Now
};
var addItemId = _FreeSqlInstance.Insert(addItem).ExecuteIdentity();
count = _FreeSqlInstance.Update<UserInfo>().Set(r => r.Age, 20).Set(r => r.LastUpdateDate, DateTime.Now).Where(r => r.UserId == addItemId).Ex //默认事务中⽆需WithTransaction，⽆需⼿动提交事务
});
//使⽤DbContext（需要导⼊包Install-Package FreeSql.DbContext）
using (var dbContext = _FreeSqlInstance.CreateDbContext())
{
//通过DbContext的当前⼯作单元获取⼀个事务，如果没有，则新建⼀个事务，可以指定事务隔离级别
dbContext.UnitOfWork.IsolationLevel = IsolationLevel.Serializable;
try
{
var addItem = new UserInfo()
{
Name = "王五",
Age = 18,
Status = 1,
CreateDate = DateTime.Now
};
var addItemId = _FreeSqlInstance.Insert(addItem)
.WithTransaction(dbContext.UnitOfWork.GetOrBeginTransaction())
.ExecuteIdentity();
count = _FreeSqlInstance.Update<UserInfo>()
.
Set(r => r.Age, 20)
.Set(r => r.LastUpdateDate, DateTime.Now)
.Where(r => r.UserId == addItemId)
.WithTransaction(dbContext.UnitOfWork.GetOrBeginTransaction())
.ExecuteAffrows();
//需要WithTransaction，需要⼿动提交事务
dbContext.UnitOfWork.Commit();
}
catch (Exception ex)
{
dbContext.UnitOfWork.Rollback();
throw;
}
}
//通过当前⼯作单元获取⼀个事务，如果没有，则新建⼀个事务，可以指定事务隔离级别
using (var unit = _FreeSqlInstance.CreateUnitOfWork())
{
unit.IsolationLevel = IsolationLevel.Serializable;
try
{
var addItem = new UserInfo()
{
Name = "赵六",
Age = 28,
Status = 1,
CreateDate = DateTime.Now
};
.WithTransaction(unit.GetOrBeginTransaction())
.ExecuteIdentity();
count = _FreeSqlInstance.Update<UserInfo>()
.Set(r => r.Age, 20)
.Set(r => r.LastUpdateDate, DateTime.Now)
.Where(r => r.UserId == addItemId)
.
WithTransaction(unit.GetOrBeginTransaction())
.ExecuteAffrows();
//需要WithTransaction，需要⼿动提交事务
unit.Commit();
}
catch (Exception ex)
{
unit.Rollback();
throw;
}
}
/
/通过⼯作单元统⼀管理器，创建⼀个⼯作单元，然后就跟⼯作单元操作⼀样啦
using (var unitManager = new UnitOfWorkManager(_FreeSqlInstance))
{
//可以开启多个⼯作单元，但是使⽤的都是同⼀个事务
using (var unit = unitManager.Begin())
{
unit.IsolationLevel = IsolationLevel.ReadCommitted;
try
{
var addItem = new UserInfo()
{
Name = "赵六",
Age = 28,
Status = 1,
CreateDate = DateTime.Now
};
var addItemId = _FreeSqlInstance.Insert(addItem)
.WithTransaction(unit.GetOrBeginTransaction())
.ExecuteIdentity();
count = _FreeSqlInstance.Update<UserInfo>()
.Set(r => r.Age, 20)
.Set(r => r.LastUpdateDate, DateTime.Now)
.
Where(r => r.UserId == addItemId)
.WithTransaction(unit.GetOrBeginTransaction())
.ExecuteAffrows();
//可以在⼯作单元中再开启⼯作单元，并且可以指定使⽤哪个⼯作单元的事务
using (var unit2 = unitManager.Begin())
{
try
{
var addItem2 = new UserInfo()
{
Name = "赵六",
Age = 28,
Status = 1,
CreateDate = DateTime.Now
};
var addItemId2 = _FreeSqlInstance.Insert(addItem)
.WithTransaction(unit.GetOrBeginTransaction())//使⽤⽗⼯作单元的事务 .ExecuteIdentity();
.Set(r => r.Age, 20)
.Set(r => r.LastUpdateDate, DateTime.Now)
.Where(r => r.UserId == addItemId)
.WithTransaction(unit2.GetOrBeginTransaction())//使⽤⾃⾝事务
.ExecuteAffrows();
unit2.Commit();
}
catch (Exception ex2)
{
unit2.Rollback();
throw;
}
}
//需要WithTransaction，需要⼿动提交事务
unit.Commit();
}
catch (Exception ex)
{
unit.Rollback();
throw;
}
}
using (var unit1 = unitManager.Begin())
{
var addItem = new UserInfo()
{
Name = "赵六",
Age = 28,
Status = 1,
CreateDate = DateTime.Now
};
var addItemId = _FreeSqlInstance.Insert(addItem)
.WithTransaction(unit1.GetOrBeginTransaction())
.ExecuteIdentity();
count = _FreeSqlInstance.Update<UserInfo>()
.Set(r => r.Age, 20)
.Set(r => r.LastUpdateDate, DateTime.Now)
.Where(r => r.UserId == addItemId)
.
WithTransaction(unit1.GetOrBeginTransaction())
.ExecuteAffrows();
}
}
}
catch (Exception ex)
{
Console.WriteLine("发⽣异常：" + ex.Message);
}
Console.ReadKey();
}
private static void CreateFreeSqlInstance()
{
//初始化实例对象
_FreeSqlInstance = new FreeSqlBuilder()
//指定数据库类型以及数据库连接
.UseConnectionString(DataType.SqlServer, "Data Source=.;Integrated Security=True;Initial Catalog=Lingbug;Pooling=true;Min Pool Size=1;uid=sa;pwd= //aop监听sql
.UseMonitorCommand(cmd =>//执⾏前
{
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论