SmartSql 快速使用指南
SmartSql 快速使用指南(https://github.com/Ahoo-Wang/SmartSql)
ISmartSqlMapper 常用(部分)接口概述
函数 | 说明 |
---|---|
Execute | IDbCommand.ExecuteNonQuery,执行返回受影响行数 |
ExecuteScalar | IDbCommand.ExecuteScalar,执行并返回查询返回的ReultSet中第一行的第一列 |
Query | 执行返回实体列表 |
QuerySingle | 执行返回单个实体 |
GetDataTable | 执行返回DataTable |
GetDataSet | 执行返回DataSet |
BeginTransaction | 开启事务 |
CommitTransaction | 提交事务 |
RollbackTransaction | 回滚事务 |
新增
Statement
<Statement Id="Insert">
INSERT INTO T_User
(UserName
,Password
,Status
,LastLoginTime
,CreationTime)
VALUES
(?UserName
,?Password
,?Status
,?LastLoginTime
,?CreationTime)
;Select Last_Insert_Id();
</Statement>
返回主键
ISmartSqlMapper SqlMapper = MapperContainer.Instance.GetSqlMapper();
long userId = _smartSqlMapper.ExecuteScalar<long>(new RequestContext
{
Scope = "User",
SqlId = "Insert",
Request = new User
{
UserName = request.UserName,
Pwd = request.Pwd,
Status = Entitiy.UserStatus.Ok,
CreationTime = DateTime.Now,
}
});
新增返回受影响行数
SqlMapper.Execute(new RequestContext
{
Scope = "User",
SqlId = "Insert",
Request = new User
{
UserName = request.UserName,
Pwd = request.Pwd,
Status = Entitiy.UserStatus.Ok,
CreationTime = DateTime.Now,
}
});
删除
<Statement Id="Delete">
Delete FROM T_User
Where Id=?Id
</Statement>
SqlMapper.Execute(new RequestContext
{
Scope = "User",
SqlId = "Delete",
Request = new { Id = 3 }
});
更新
Statement.Update
<Statement Id="Update">
UPDATE T_User
<Set>
<IsProperty Prepend="," Property="UserName">
UserName = ?UserName
</IsProperty>
<IsProperty Prepend="," Property="Password">
Password = ?Password
</IsProperty>
<IsProperty Prepend="," Property="Status">
Status = ?Status
</IsProperty>
<IsProperty Prepend="," Property="LastLoginTime">
LastLoginTime = ?LastLoginTime
</IsProperty>
<IsProperty Prepend="," Property="CreationTime">
CreationTime = ?CreationTime
</IsProperty>
</Set>
Where Id=?Id
</Statement>
全量更新
SqlMapper.Execute(new RequestContext
{
Scope = "User",
SqlId = "Update",
Request = new User
{
Id=1,
UserName = request.UserName,
Pwd = request.Pwd,
Status = Entitiy.UserStatus.Ok,
CreationTime = DateTime.Now,
}
});
局部更新
SqlMapper.Execute(new RequestContext
{
Scope = "User",
SqlId = "Update",
Request = new { Id=1 , Pwd = "SmartSql" }
});
查询 返回List
<Statement Id="Query">
SELECT T.* From T_User T
<Where>
<IsNotEmpty Prepend="And" Property="EqUserName">
T.UserName=$EqUserName
</IsNotEmpty>
<IsNotEmpty Prepend="And" Property="UserName">
T.UserName Like Concat('%',$UserName,'%')
</IsNotEmpty>
</Where>
<Switch Prepend="Order By" Property="OrderBy">
<Default>
T.Id Desc
</Default>
</Switch>
<IsNotEmpty Prepend="Limit" Property="Taken">?Taken</IsNotEmpty>
</Statement>
var list = SqlMapper.Query<User>(new RequestContext
{
Scope = "User",
SqlId = "Query",
Request = new
{
Taken = 10
}
});
查询 返回单个实体
<Statement Id="GetEntity">
Select T.* From T_User T
<Where>
<IsNotEmpty Prepend="And" Property="Id">
T.Id=?Id
</IsNotEmpty>
</Where>
Limit 1
</Statement>
var user = SqlMapper.QuerySingle<User>(new RequestContext
{
Scope = "User",
SqlId = "GetEntity",
Request = new { Id = 1 }
});
事务
try
{
SqlMapper.BeginTransaction();
//BizCode();
SqlMapper.CommitTransaction();
}
catch (Exception ex)
{
SqlMapper.RollbackTransaction();
throw ex;
}
存储过程
DbParameterCollection dbParameterCollection = new DbParameterCollection();
dbParameterCollection.Add(new DbParameter
{
Name = "Total",
DbType = System.Data.DbType.Int32,
Direction = System.Data.ParameterDirection.Output
});
RequestContext context = new RequestContext
{
CommandType = System.Data.CommandType.StoredProcedure,
RealSql = "SP_QueryByPage",
Request = dbParameterCollection
};
var list = SqlMapper.Query<User>(context);
var total = dbParameterCollection.GetValue<int>("Total");
作者:Ahoo Wang (阿虎)
Github: https://github.com/Ahoo-Wang/
SmartSql(高性能、高生产力,超轻量级的ORM!): https://github.com/Ahoo-Wang/SmartSql
SmartCode(不只是代码生成器!): https://github.com/Ahoo-Wang/SmartCode
CoSky 高性能、低成本微服务治理平台 : https://github.com/Ahoo-Wang/CoSky
CosId 通用、灵活、高性能的分布式 ID 生成器 : https://github.com/Ahoo-Wang/CosId
Wow 基于 DDD、EventSourcing 的现代响应式 CQRS 架构微服务开发框架: https://github.com/Ahoo-Wang/Wow
CoSec 基于 RBAC 和策略的多租户响应式安全框架: https://github.com/Ahoo-Wang/CoSec
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 推荐几款开源且免费的 .NET MAUI 组件库
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· Trae初体验