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

相信很多同学都用过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源代码。

posted @ 2012-11-04 13:38 三生石上(FineUI控件) 阅读(...) 评论(...) 编辑 收藏