[原创]修正SubSonic v2.2.1的一处BUG，以及如何使用SubSonic进行多表查询、子查询以及数据库分页

最新推荐文章于 2019-12-29 11:21:25 发布
最新推荐文章于 2019-12-29 11:21:25 发布
阅读量943 收藏
点赞数
分类专栏： FineUI
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010918003/article/details/9160575
版权

相信很多同学都用过SubSonic，在07 - 10年ORM兴起的时代，SubSonic可以说是DotNet开发人员的救星。虽说现在 EntityFramework大有一统江湖的趋势，不过在DotNet2.0框架下，SubSonic依然是为数不多的选择。

 

最近在维护基于 ExtAspNet 的通用权限管理项目 AppBox ，在使用SubSonic进行多表查询和数据库分页时遇到了点问题，下面我会详细分享这一经过，以及如何通过修改SubSonic的源代码来修正这一问题。

 

我要实现如下的功能

我要实现的功能非常简单：用户管理，角色管理，角色用户管理（一个用户可以属于多个角色）。相信很多同学闭着眼睛就能把数据库给构造出来，不是吗？

 

1. 用户表

image

 

2. 角色表

image

 

3. 角色用户表

image

 

其中用户管理和角色管理都很简单，我要实现的角色用户管理界面如下所示：

1. 查看角色下的所有用户

image

 

2. 向角色添加现有用户

image

 

 

 

数据库查询时遇到问题

在查看角色下的所有用户页面，需要进行表关联，相关的SubSonic代码如下所示：

   1:  // 查询 X_User 表
   2:  SqlQuery q = new Select().From<XUser>();
   3:  q.Where("1").IsEqualTo("1");
   4:   
   5:  // 在用户名称中搜索
   6:  string searchText = ttbSearchUser.Text.Trim();
   7:  if (!String.IsNullOrEmpty(searchText))
   8:  {
   9:      q.And(XUser.NameColumn).ContainsString(searchText);
  10:  }
  11:   
  12:  // 过滤选中角色下的所有用户
  13:  object[] values = Grid1.DataKeys[Grid1.SelectedRowIndexArray[0]];
  14:  int roleId = Convert.ToInt32(values[0]);
  15:  SqlQuery subQ = new Select(XRoleUser.UserIdColumn).From<XRoleUser>().Where(XRoleUser.RoleIdColumn).IsEqualTo(roleId);
  16:   
  17:  q.And(XUser.IdColumn).In(subQ);
  18:   
  19:   
  20:  // 在查询添加之后，排序和分页之前获取总记录数
  21:  // Grid1总共有多少条记录
  22:  Grid2.RecordCount = q.GetRecordCount();
  23:   
  24:  // 排列
  25:  q.OrderBys.Add(GetSortExpression(Grid2, XUser.Schema));
  26:   
  27:  // 数据库分页
  28:  q.Paged(Grid2.PageIndex + 1, Grid2.PageSize);
  29:  items = q.ExecuteAsCollection<XUserCollection>();

令人不解的时，居然报如下错误：

image

 

很明显，SubSonic生成的SQL脚本不对，经过调试发现生成的脚本如下所示：

   1:  DECLARE @Page int
   2:  DECLARE @PageSize int
   3:   
   4:  SET @Page = 1
   5:  SET @PageSize = 20
   6:   
   7:  SET NOCOUNT ON
   8:   
   9:  -- create a temp table to hold order ids
  10:  DECLARE @TempTable TABLE (IndexId int identity, _keyID Int)
  11:   
  12:  -- insert the table ids and row numbers into the memory table
  13:  INSERT INTO @TempTable
  14:  (
  15:      _keyID
  16:  )
  17:  SELECT [dbo].[X_User].[Id]
  18:  FROM [dbo].[X_User]
  19:  WHERE 1 = @10
  20:  AND [dbo].[X_User].[Id] IN (SELECT [dbo].[X_RoleUser].[UserId]
  21:      FROM [dbo].[X_RoleUser]
  22:      WHERE [dbo].[X_RoleUser].[RoleId] = @RoleId0
  23:      )
  24:   
  25:  AND 1 = @10
  26:  AND [dbo].[X_User].[Id] IN (SELECT [dbo].[X_RoleUser].[UserId]
  27:      FROM [dbo].[X_RoleUser]
  28:      AND [dbo].[X_RoleUser].[RoleId] = @RoleId0
  29:      )
  30:   
  31:   ORDER BY Name DESC
  32:   
  33:  -- select only those rows belonging to the proper page
  34:  SELECT [dbo].[X_User].[Id], [dbo].[X_User].[Name], [dbo].[X_User].[Password], [dbo].[X_User].[Enabled], [dbo].[X_User].[Email], [dbo].[X_User].[Gender], [dbo].[X_User].[RealName], [dbo].[X_User].[QQ], [dbo].[X_User].[MSN], [dbo].[X_User].[CellPhone], [dbo].[X_User].[OfficePhone], [dbo].[X_User].[HomePhone], [dbo].[X_User].[Remark], [dbo].[X_User].[DeptId], [dbo].[X_User].[RoleId], [dbo].[X_User].[CreateTime]
  35:   
  36:  FROM [dbo].[X_User]
  37:  INNER JOIN [dbo].[X_RoleUser] ON [dbo].[X_User].[Id] = [dbo].[X_RoleUser].[UserId]
  38:   
  39:  INNER JOIN @TempTable t ON [dbo].[X_User].[Id] = t._keyID
  40:  WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

这里面有两处错误：

1. 首先，25 – 29 行的Where子句重复了，相信这个问题一直存在于SubSonic2.2中，只不过大家都没发现而已

2. 其次，重复的子查询中Where被替换成了AND，导致这个子查询没有Where子句，从而报错！

 

如果撇去分页的SQL脚本不管，正确的SQL脚本应该是这样的：

   1:  SELECT [dbo].[X_User].[Id]
   2:  FROM [dbo].[X_User]
   3:  WHERE 1 = @10
   4:  AND [dbo].[X_User].[Id] IN (SELECT [dbo].[X_RoleUser].[UserId]
   5:      FROM [dbo].[X_RoleUser]
   6:      WHERE [dbo].[X_RoleUser].[RoleId] = @RoleId0
   7:      )
   8:   
   9:   ORDER BYName DESC

很明显，SubSonic在生成带子查询的分页SQL脚本时除了问题。

 

修改SubSonic的源代码

从Github下载SubSonic2.0的源代码：https://nodeload.github.com/subsonic/SubSonic-2.0/zip/master

其实下载下来的是SubSonic2.2.1，找到其中的 SqlQuery\SqlGenerators\ANSISqlGenerator.cs 文件：

   1:   
   2:  public virtual string BuildPagedSelectStatement()
   3:  {
   4:      // 省略的代码...
   5:      
   6:      string wheres = GenerateWhere();
   7:   
   8:      //have to doctor the wheres, since we're using a WHERE in the paging
   9:      //bits. So change all "WHERE" to "AND"
  10:      string tweakedWheres = wheres.Replace("WHERE", "AND");
  11:      
  12:      // 省略的代码...
  13:   
  14:      string sql = string.Format(PAGING_SQL, idColumn, String.Concat(fromLine, joins, wheres), String.Concat(tweakedWheres, orderby, havings),
  15:          String.Concat(select, fromLine, joins), query.CurrentPage, query.PageSize, sqlType);
  16:      return sql;
  17:  }

其中 tweakedWheres 是关键，作者还特别指出要把其中 WHERE 替换成 AND，殊不知这样做对子查询是破坏性操作，而且下面连接SQL脚本时重复添加了WHERE子句。

修改后的代码：

   1:   
   2:  public virtual string BuildPagedSelectStatement()
   3:  {
   4:      // 省略的代码...
   5:      
   6:      string wheres = GenerateWhere();
   7:   
   8:      //have to doctor the wheres, since we're using a WHERE in the paging
   9:      //bits. So change all "WHERE" to "AND"
  10:      //string tweakedWheres = wheres.Replace("WHERE", "AND");
  11:      
  12:      // 省略的代码...
  13:   
  14:      string sql = string.Format(PAGING_SQL, idColumn, String.Concat(fromLine, joins, wheres), String.Concat(orderby, havings),
  15:          String.Concat(select, fromLine, joins), query.CurrentPage, query.PageSize, sqlType);
  16:      return sql;
  17:  }

 

搞定!

 

子查询与表关联查询（查看角色下所有用户）

在上面的例子中，我们使用的是子查询，对于“查看角色下所有用户”这个案例，我们还有如下另一种解决办法（效果完全一样）：

 

   1:  // 查询 X_User 表
   2:  SqlQuery q = new Select().From<XUser>().InnerJoin(XRoleUser.UserIdColumn, XUser.IdColumn);
   3:  q.Where("1").IsEqualTo("1");
   4:   
   5:  // 在用户名称中搜索
   6:  string searchText = ttbSearchUser.Text.Trim();
   7:  if (!String.IsNullOrEmpty(searchText))
   8:  {
   9:      q.And(XUser.NameColumn).ContainsString(searchText);
  10:  }
  11:   
  12:  // 过滤选中角色下的所有用户
  13:  object[] values = Grid1.DataKeys[Grid1.SelectedRowIndexArray[0]];
  14:  int roleId = Convert.ToInt32(values[0]);
  15:  q.And(XRoleUser.RoleIdColumn).IsEqualTo(roleId);
  16:   
  17:   
  18:  // 在查询添加之后，排序和分页之前获取总记录数
  19:  // Grid1总共有多少条记录
  20:  Grid2.RecordCount = q.GetRecordCount();
  21:   
  22:  // 排列
  23:  q.OrderBys.Add(GetSortExpression(Grid2, XUser.Schema));
  24:   
  25:  // 数据库分页
  26:  q.Paged(Grid2.PageIndex + 1, Grid2.PageSize);
  27:  items = q.ExecuteAsCollection<XUserCollection>();

 

再来看下这段代码生成的SQL脚本（修正SubSonic2.2.1中的BUG后）：

   1:  DECLARE @Page int
   2:  DECLARE @PageSize int
   3:   
   4:  SET @Page = 1
   5:  SET @PageSize = 20
   6:   
   7:  SET NOCOUNT ON
   8:   
   9:  -- create a temp table to hold order ids
  10:  DECLARE @TempTable TABLE (IndexId int identity, _keyID Int)
  11:   
  12:  -- insert the table ids and row numbers into the memory table
  13:  INSERT INTO @TempTable
  14:  (
  15:      _keyID
  16:  )
  17:  SELECT [dbo].[X_User].[Id]
  18:  FROM [dbo].[X_User]
  19:  INNER JOIN [dbo].[X_RoleUser] ON [dbo].[X_User].[Id] = [dbo].[X_RoleUser].[UserId]
  20:      WHERE 1 = @10
  21:      AND [dbo].[X_RoleUser].[RoleId] = @RoleId1
  22:   
  23:  ORDER BY Name DESC
  24:   
  25:  -- select only those rows belonging to the proper page
  26:  SELECT [dbo].[X_User].[Id], [dbo].[X_User].[Name], [dbo].[X_User].[Password], [dbo].[X_User].[Enabled], [dbo].[X_User].[Email], [dbo].[X_User].[Gender], [dbo].[X_User].[RealName], [dbo].[X_User].[QQ], [dbo].[X_User].[MSN], [dbo].[X_User].[CellPhone], [dbo].[X_User].[OfficePhone], [dbo].[X_User].[HomePhone], [dbo].[X_User].[Remark], [dbo].[X_User].[DeptId], [dbo].[X_User].[RoleId], [dbo].[X_User].[CreateTime]
  27:   
  28:  FROM [dbo].[X_User]
  29:  INNER JOIN [dbo].[X_RoleUser] ON [dbo].[X_User].[Id] = [dbo].[X_RoleUser].[UserId]
  30:   
  31:  INNER JOIN @TempTable t ON [dbo].[X_User].[Id] = t._keyID
  32:  WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

 

向当前角色添加现有用户

对于这个情况，我们要注意一点，就是供选择的现有用户不应当包括哪些已经属于当前角色的用户，可以用子查询来实现： 

   1:  SqlQuery q = new Select().From<XUser>(); //.LeftOuterJoin(XRoleUser.UserIdColumn, XUser.IdColumn);
   2:  q.Where("1").IsEqualTo("1");
   3:   
   4:  // 在职务名称中搜索
   5:  string searchText = ttbSearchMessage.Text.Trim();
   6:  if (!String.IsNullOrEmpty(searchText))
   7:  {
   8:      q.And(XUser.NameColumn).ContainsString(searchText);
   9:  }
  10:   
  11:  // 排除已经属于本角色的用户
  12:  int currentRoleId = GetQueryIntValue("id");
  13:  SqlQuery subQ = new Select(XRoleUser.UserIdColumn).From<XRoleUser>().Where(XRoleUser.RoleIdColumn).IsEqualTo(currentRoleId);
  14:   
  15:  q.And(XUser.IdColumn).NotIn(subQ);
  16:  //q.And(XUser.IdColumn).IsNotEqualTo(1);
  17:              
  18:  // 只列出不在当前角色中的用户
  19:  //q.AndExpression(XUser.RoleIdColumn.ColumnName).IsNotEqualTo(GetQueryIntValue("id")).Or(XUser.RoleIdColumn).IsNull().CloseExpression();
  20:   
  21:  // 在查询添加之后，排序和分页之前获取总记录数
  22:  // Grid1总共有多少条记录
  23:  Grid1.RecordCount = q.GetRecordCount();
  24:   
  25:  // 排列
  26:  q.OrderBys.Add(GetSortExpression(Grid1, XUser.Schema));
  27:   
  28:  // 数据库分页
  29:  q.Paged(Grid1.PageIndex + 1, Grid1.PageSize);
  30:  XUserCollection items = q.ExecuteAsCollection<XUserCollection>();

 

小结

虽然SubSonic2.2的代码已经不更新了，但是在实际应用中，我们可以恰当的修正其源代码来满足需求，这也归功于开源的力量。同时也希望大家能多关注同样是完全开源的ExtAspNet（基于ExtJS的专业ASP.NET2.0控件库）。

 

注：AppBox是捐赠软件，也就是说你可以通过捐赠作者来获得AppBox源代码。

sanshi_zhang
关注
专栏目录
百度地图接口
weixin_43283797的博客
01-04 2207
# 接口示例 import urllib.parse import urllib.request import json # http://api.map.baidu.com/place/v2/search?query=ATM机&amp;amp;amp;tag=银行&amp;amp;amp;region=北京&amp;amp;amp;output=json&amp;amp;amp;ak=您的ak //GET请求 args = {'query':'ATM机',...
MB_SubSonic:MusicBee SubSonic插件
05-05
您可以使用多个“播放器”访问该集合，其中包括Web前端（Subsonic随附），智能手机播放器（例如Android，iPhone）和台式机。 有了此插件，MusicBee也将添加此功能。 从2.9版开始，该插件也正式支持LibreSonic...
[原创]修正SubSonic v2.2.1的一处BUG，以及如何使用SubSonic进行多表查询、子查询以及数据库分页...
weixin_34308389的博客
11-04 118
相信很多同学都用过SubSonic，在07 - 10年ORM兴起的时代，SubSonic可以说是DotNet开发人员的救星。虽说现在 EntityFramework大有一统江湖的趋势，不过在DotNet2.0框架下，SubSonic依然是为数不多的选择。   最近在维护基于 ExtAspNet 的通用权限管理项目 AppBox ，在使用SubSonic进行多表查询和数据库分页时遇到了点问题，下...
Subsonic的复杂查询
zjq2008141049的专栏
12-16 432
/// /// 取得分类中的最新数据（带分页） /// /// 分类ID /// 每页数据条数 /// 页码（从0开始） /// 文章列表 public static List GetArticleModel(int categoryid, int iPageCount, int i
SubSonic 绑定多个数据库
weixin_30848775的博客
12-28 73
<configSections> <section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" allowDefinition="MachineToApplication" restartOnExternalChanges="true" /> </configSe...
Petapoco的查询语句使用
weixin_39462109的博客
07-24 1847
  Database DBHelper = new Database("Sqlconnection"); 1：查询一个对象，如果不存在就返回null public object SearchByNameAndPwd(string name,string password) { Sql sql = Sql.Builder; ...
SubSonic3.0使用Demo1.0——应用了T4模版可减少开发过程中70%以上的代码量以及80%以上的出错率
09-23
SubSonic3.0使用Demo1.0——应用了T4模版可减少开发过程中70%以上的代码量以及80%以上的出错率 　应网友的要求，抽了点时间写了这个Demo，希望对2.2版想升级到3.0的朋友或正在使用3.0的朋友有所帮助。大家在使用...
SubSonic2.2
12-19
这个动态链接库是SubSonic框架的基础，包含了处理数据库查询、插入、更新和删除等基本操作的类和方法。通过它，开发者可以快速地创建和管理数据库对象，如Table、Column、PrimaryKey等，无需编写大量的SQL语句。同时...
SubSonic操作指南：查询与关联查询实践
本文档主要介绍了SubSonic 2.1版本的基本操作方法，包括不同方式的查询，关联查询以及分页查询。此外，还提到了执行查询后返回的各种类型的数据结果。" SubSonic 提供了多种查询数据库的方式，适用于不同的需求和...
SubSonic3.0.0.4.1源码包，已修复了一些Bug
08-10
2、使用SubSonic.Query.Select和SubSonic.Query.SqlQuery查询时，设置了Top属性后，产生的“未处理InvalidOperationException异常（关键字'TOP'附近有语法错误）”Bug 3、生成的表名自动加复数（s）产生的“用户代码...
SubSonic+2.0.3
11-06
subsonic一直用，是我看过的最好的轻量级orm框架。3.0还没有用过，用的时候基本还处于2.0的时代，还没有好的代码生成器，不过我感觉基于active record的原理应该是没有变的
SubSonic3.0使用外连接查询时查询不出数据的问题修改
weixin_34252090的博客
11-21 114
　今天在开发时，要使用到外连接查询，如图 　　 　　老是查不出数据，所以就追踪了一下代码，发现查询后生成的SQL语句变成了内连接了，真是晕 　　 　　然后继续Debug，发现原来SqlQuery类在调用LeftInnerJoin函数时传入的Join.JoinType.LeftInner参数，并没有被CreateJoin函数使用上，如下图 　　 ...
豆瓣API
weixin_40083227的博客
12-29 823
获取正在热映的电影：https://douban.uieee.com/v2/movie/in_theaters 访问参数： start : 数据的开始项 count：单页条数 city：城市 获取电影Top250：https://douban.uieee.com/v2/movie/top250 访问参数： start : 数据的开始项 count：单页条数 获取即将上映电影：h...
ElasticSearch的映射详细
AAA821的专栏
09-20 372
字段设置参数 “type”: “text”, // 指定字段的数据类型 “analyzer”:”ik _max_word”, //指定分词器的名称,即可用使用内置的分词器也可以使用第三方分词器  默认standard， 内置的分析器有whitespace 、 simple和english 第三方分词器：ik分词器 包括ik_max_word和ik_smart，ik_max_word：...
Subsonic中的inner join操作
weixin_34345560的博客
11-25 86
  有如下两表,Rule跟,RuleGroup, RuleGroup是 Rule表与Group表的关联表(Rule,跟 Group为多对多关系),应此,RuleGroup到Rule应该为 n:1(多对一)反之为一对多关系,现在对两张表做innerjion操作,使用Subsonic可以有如下两种写法.  注意,为保证生成正确的SQL语句,在Subsoinc中需要交叉放置两张表的位置,即From&...
Stack源码解析
https://gitee.com/hello-piper/PiperChat
10-23 197
简介 Stack是一个先进先出（LIFO）的队列。通过五个操作扩展了Vector类，所以能被当作栈对待。 分别是，push、pop、peek、empty、search。 类继承关系 方法 //构造一个空对象 public Stack() { } //入栈 public E push(E item) { addElement(item); return item; } //出栈 ...
微信LBC基础开发
weixin_34319999的博客
12-06 135
2019独角兽企业重金招聘Python工程师标准>>> ...
PetaPoco 基础操作
weixin_30947043的博客
05-03 381
//初始化数据库连接 var db=new PetaPoco.Database("connectionStringName"); //查询单个值 long count=db.ExecuteScalar<long>("SELECT Count(*) FROM articles"); //返回一条记录 var a = db.SingleOrDefault&lt...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值