Insql 1.2.5 发布，轻量级.NET ORM，数据库访问利器
轻量级的.NET ORM类库 . 对象映射基于Dapper , Sql配置灵感来自于Mybatis.
1.2.5版更新内容：
添加CodeSmith代码生成器文件
支持同一Insql Type类型的多个insql.xml文件合并，用于方便配置多数据库Sql
添加each配置元素，用以支持select in list 多参数功能
优化SqlResolve的跨数据库匹配功能
优化DbContext，DbSession的配置和创建过程，减少内存分配和加快初始化时间
CodeSmith代码生成器：
以上配置生成后，会在选择目录中生成Context\BookDbContext.cs,BookDbContext.insql.xml，Model\BookPo.cs,BookCategoryPo.cs
多Insql.xml文件合并：
insql.xml文件随意布局，只需要保证同一DbContext类型的insql type保持一致。
新增each配置元素：
<?xml version="1.0" encoding="utf-8" ?>
<insql type="Insql.Tests.EachSectionElementTests,Insql.Tests" >
<select id="EachIn">
select * from user_info where user_id in <each name="userIdList" open="(" separator="," close=")" prefix="@" />
</select>
<select id="EachInNull">
select * from user_info
<where>
<if test="userIdList !=null and userIdList.length >0">
user_id in <each name="userIdList" open="(" separator="," close=")" prefix="@" />
</if>
</where>
</select>
</insql>
在Sql Resolve 之后将会被转换为：
select * from user_info where user_id in (@userIdList1,@userIdList2)
类库用法介绍：
1.精简用法：
只将Insql用作加载和解析Sql语句来使用。
注入ISqlResolver
在Domain Service中使用语句解析器，将ISqlResolver<T>注入到UserService中，其中T类型我们指定为UserService类型
public class UserService : IUserService
{
private readonly ISqlResolver<UserService> sqlResolver;
public UserService(ISqlResolver<UserService> sqlResolver)
{
this.sqlResolver = sqlResolver;
}
public void DeleteUser(int userId)
{
var resolveResult = this.sqlResolver.Resolve("DeleteUser", new { userId });
//如果需要指定数据库(匹配SqlId后缀为.SqlServer)，则需要设置DbType的参数
//var resolveResult = this.sqlResolver.Resolve("SqlServer", "DeleteUser", new { userId });
//connection.Execute(resolveResult.Sql,resolveResult.Param) ...
}
}
创建UserService.insql.xml
创建UserService.insql.xml，用作Sql语句配置，insql type 指定为ISqlResolver<T>的T类型,当然也可以针对每个Model创建。
<insql type="Insql.Tests.Domain.Services.UserService,Insql.Tests" > <delete id="DeleteUser"> delete from user_info where user_id = @userId </delete> </insql>
添加 Insql
public void ConfigureServices(IServiceCollection services)
{
services.AddInsql();
services.AddScoped<IUserService, UserService>();
}
2.基本用法：
基本用法可以通过创建DbContext来使用
添加 Insql
public void ConfigureServices(IServiceCollection services)
{
services.AddInsql();
services.AddInsqlDbContext<UserDbContext>(options =>
{
//options.UseSqlServer(this.Configuration.GetConnectionString("sqlserver"));
options.UseSqlite(this.Configuration.GetConnectionString("sqlite"));
});
}
创建 DbContext
public class UserDbContext : Insql.DbContext
{
public UserDbContext(Insql.DbContextOptions<UserDbContext> options)
: base(options)
{
}
public IEnumerable<UserInfo> GetUserList(string userName)
{
//sqlId = "GetUserList"
//sqlParam is PlainObject or IDictionary<string,object>
return this.Query<UserInfo>(nameof(GetUserList), new { userName, userGender = Gender.W });
}
public void InsertUser(UserInfo info)
{
var userId = this.ExecuteScalar<int>(nameof(InsertUser),info);
info.UserId = userId;
}
public void UpdateUserSelective(UserInfo info)
{
this.Execute(nameof(UpdateUserSelective), info);
}
}
//user model
public class UserInfo
{
public int UserId { get; set; }
public string UserName { get; set; }
public Gender? UserGender { get; set; }
}
public enum Gender
{
M,
W
}
创建 DbContext.insql.xml
创建 UserDbContext.insql.xml 文件并且修改这个文件的属性为嵌入式文件类型 . insql type 与 UserDbContext 类型对应.
<insql type="Example.Domain.Contexts.UserDbContext,Example.Domain" > <sql id="selectUserColumns"> select user_id as UserId,user_name as UserName,user_gender as UserGender from user_info </sql> <select id="GetUserList"> <include refid="selectUserColumns" /> <where> <if test="userName != null"> <bind name="likeUserName" value="'%' + userName + '%'" /> user_name like @likeUserName </if> <if test="userGender != null and userGender != 'M' "> and user_gender = @userGender </if> </where> order by user_id </select> <insert id="InsertUser"> insert into user_info (user_name,user_gender) values (@UserName,@UserGender); select last_insert_rowid() from user_info; </insert> <update id="UpdateUserSelective"> update user_info <set> <if test="UserName != null"> [email protected], </if> <if test="UserGender != null"> [email protected] </if> </set> where user_id = @UserId </update> </insql>
使用 DbContext
使用 UserDbContext 在Domain Service中或者Web Controller中
public class ValuesController : ControllerBase
{
private readonly UserDbContext userDbContext;
public ValuesController(UserDbContext userDbContext)
{
this.userDbContext = userDbContext;
}
[HttpGet]
public ActionResult<IEnumerable<string>> Get()
{
//可以这样使用事务
this.userDbContext.DoWithTransaction(() =>
{
var userInfo = new Domain.UserInfo
{
UserName = "loveW",
UserGender = Domain.Gender.M
};
this.userDbContext.InsertUser(userInfo);
this.userDbContext.UpdateUserSelective(new Domain.UserInfo
{
UserId = userInfo.UserId,
UserName = "loveWWW",
});
});
var list = this.userDbContext.GetUserList("love");
//todo return
}
}
项目源码地址：码云 ????谢谢支持！！