Insql 1.2.5 釋出，輕量級.NET ORM，資料庫訪問利器
摘要：
輕量級的.NET ORM類庫 . 物件對映基於Dapper , Sql配置靈感來自於Mybatis.
1.2.5版更新內容：
新增CodeSmith程式碼生成器檔案
支援同一Insql Type型別的多個insql.xml檔案合併，用於方便配置多資料庫Sql
...
輕量級的.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 byuser_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"> user_name=@UserName, </if> <if test="UserGender != null"> user_gender=@UserGender </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
}
}