IBatis.Net -- 我的记录

public GroupInfo GetGroupInfoById(int grId)
{
    Hashtable hs = new Hashtable();
    hs.Add("GrId", grId);

    return IBatisMapper.Load<GroupInfo>("bw_Groups_Select", hs);
}

public IList<GroupInfo> GetGroupList()
{
    Hashtable hs = new Hashtable();
    hs.Add("IsEnabled", 1);

    return IBatisMapper.Select<GroupInfo>("bw_Groups_Select", hs);
}


isPropertyAvailable : 当对象存在时,语句起作用

<select id="bw_Groups_Select" parameterClass="Hashtable" resultMap="GroupInfo_Result" >
    SELECT [GrId],[GrName],[PictureLarge],[PictureSmall],[Description],[IsSecrecy],[IsEnabled]
    FROM [dbo].[bw_Groups]
    <dynamic prepend="where">
    <isPropertyAvailable prepend="and" property="GrId">
      [GrId] = #GrId#
    </isPropertyAvailable>
    <isPropertyAvailable prepend="and" property="IsSecrecy">
      [IsSecrecy] = #IsSecrecy#
    </isPropertyAvailable>
    <isPropertyAvailable prepend="and" property="IsEnabled">
      [IsEnabled] = #IsEnabled#
    </isPropertyAvailable>
    <isPropertyAvailable prepend="and" property="UserName">
      [GrId] in (SELECT GrId FROM bw_GroupMembers WHERE UserName=#UserName#)
    </isPropertyAvailable>
    <isPropertyAvailable prepend="and" property="Creator" >
      [Creator] = #Creator#
    </isPropertyAvailable>
    <isPropertyAvailable prepend="and" property="Manager" >
      [GrId] IN (select Grid from bw_GroupMembers where DutyType=2 and UserName=#Manager#)
    </isPropertyAvailable>
  </dynamic>
</select>

 

存储过程
------------------------------------------------



存储过程 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--使用字符串进行分页
ALTER PROC [dbo].[spPageViewByStr]
@TableName     sysname,               --要分页显示的表名
@FieldKey   sysname,               --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1,             --要显示的页码
@PageSize   int=10,                --每页的大小(记录数)
@FieldShow  nvarchar(1000)='',  --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder  nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序
@Where     nvarchar(1000)='',      --查询条件
@RecordCount  int OUTPUT            --总记录数
AS

DECLARE @sql nvarchar(4000)
--SET NOCOUNT ON

--检查对象是否有效
IF OBJECT_ID(@TableName) IS NULL
BEGIN
    RAISERROR(N'对象"%s"不存在',1,16,@TableName)
    RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsTable')=0
    AND OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsView')=0
    AND OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsTableFunction')=0
BEGIN
    RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@TableName)
    RETURN
END

--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
    RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
    RETURN
END

--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@RecordCount,0)<1 SET @RecordCount=0

--每页最多显示500条   PageSize <= 4000/主鍵字段长度
IF @PageSize > 500 
    SET @PageSize = 500

IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
    SET @FieldOrder=N''
ELSE
    SET @FieldOrder = N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where, N'')=N''
    SET @Where = N''
ELSE
    SET @Where = N' WHERE ('+ @Where + N')'

--如果RecordCount为小于等于值,则计算总记录数(这样设计可以只在第一次计算总记录数,以后调用时,把总记录数传回给存储过程,避免再次计算总记录数,对于不想计算总记录数的处理而言,可以给@RecordCount赋值)
IF @RecordCount <= 0
BEGIN
    SET @sql=N'SELECT @RecordCount=COUNT(*)'
        +N' FROM '+@TableName
        +N' with(nolock) '+@Where
    EXEC sp_executesql @sql, N'@RecordCount int OUTPUT', @RecordCount OUTPUT
    --SET @PageCount=(@RecordCount+@PageSize-1)/@PageSize
END

DECLARE @TotalPage INT
SET @TotalPage = (@RecordCount + @PageSize - 1) / @PageSize

IF @PageCurrent > @TotalPage
BEGIN
    IF @TotalPage > 0
        SET @PageCurrent = @PageCurrent
    ELSE
        SET @PageCurrent = 1    
END

--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
    @TopN1=@PageCurrent*@PageSize    

--第一页直接显示
IF @PageCurrent=1

    SET @sql = (N'SELECT TOP '+@TopN
        +N' '+@FieldShow
        +N' FROM '+@TableName + N' with(nolock)'
        +N' '+@Where
        +N' '+@FieldOrder)
ELSE
BEGIN
    SELECT @PageCurrent=@TopN1,
        @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
            +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
            +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@TableName + N' with(nolock)'
            +N' '+@Where
            +N' '+@FieldOrder
    SET ROWCOUNT @PageCurrent
    
    EXEC sp_executesql @sql,
        N'@n int,@s nvarchar(4000) OUTPUT',
        @PageCurrent,@sql OUTPUT

    --SET ROWCOUNT 0
    IF @sql = N''
        SET @sql = (N'SELECT TOP 0'+ N' '+@FieldShow + N' FROM '+ @TableName + N' with(nolock)' )
    ELSE
    BEGIN
        IF ISNULL(@Where, N'') = N''
            SET @Where = N' WHERE '
        ELSE
            SET @Where = @Where + N' AND '
        SET @sql = STUFF(@sql, 1, 1, N'')        
        --执行查询
        SET @sql = (N'SELECT TOP '+ @TopN + N' '+ @FieldShow + N' FROM '+ @TableName + N' with(nolock)'
            + @Where + @FieldKey + N' IN('+ @sql + N')'
            + @FieldOrder)
    END
END
EXEC (@sql)

--SET NOCOUNT OFF
 

IBatisDbHelper.cs
------------------------------------------------

IBatisDbHelper.cs 
/// <summary>
/// 获取已分页列表.
/// </summary>
/// <param name="tableName">要分页显示的表(视图)名.</param>
/// <param name="fieldKey">于定位记录的主键(惟一键)字段,只能是单个字段.</param>
/// <param name="pageIndex">要显示的页码.</param>
/// <param name="pageSize">每页的大小(记录数).</param>
/// <param name="fieldShow">以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段.</param>
/// <param name="fieldOrder">以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 用于指定排序顺序.</param>
/// <param name="where">查询条件.</param>
/// <param name="recordCount">总记录数.</param>
/// <returns></returns>
public static DataTable GetPagedList(string tableName, string fieldKey, int pageIndex, int pageSize, string fieldShow, string fieldOrder, string where, ref int recordCount)
{
    //由从0开始的页码改为从1开始

    pageIndex++;

    IDbDataParameter[] paramSet = IBatisDbHelper.CreateParameterSet(8);

    paramSet[0].ParameterName = "@TableName";
    paramSet[0].Value = tableName;
    paramSet[1].ParameterName = "@FieldKey";
    paramSet[1].Value = fieldKey;
    paramSet[2].ParameterName = "@PageCurrent";
    paramSet[2].Value = pageIndex;
    paramSet[3].ParameterName = "@PageSize";
    paramSet[3].Value = pageSize;
    paramSet[4].ParameterName = "@FieldShow";
    paramSet[4].Value = fieldShow;
    paramSet[5].ParameterName = "@FieldOrder";
    paramSet[5].Value = fieldOrder;
    paramSet[6].ParameterName = "@Where";
    paramSet[6].Value = where;
    paramSet[7].ParameterName = "@RecordCount";
    paramSet[7].Direction = ParameterDirection.InputOutput;
    paramSet[7].Value = recordCount;

    DataSet ds = ExecuteDataset(CommandType.StoredProcedure, "spPageViewByStr", paramSet);
    recordCount = Convert.ToInt32(paramSet[7].Value);

    return ds.Tables[0];
}


 


ModelService.cs
------------------------------------------------
ModelService.cs 
public DataTable GetGroupListPager(string keyword, string userName, int pageIndex, int pageSize, ref int recordCount)
{
    string tableName = "vw_bwiap_Groups";
    string fieldKey = "GrId";
    string fieldShow = "[GrId],[GrName],[PictureLarge],[PictureSmall],[Description],[IsSecrecy],[Creator],[CreatedTime],[LastModifier],[LastModTime],[ProjectId],[pname]";
    string fieldOrder = "CreatedTime desc";

    StringBuilder where = new StringBuilder();
    where.Append(" IsEnabled=1 ");
    if (!string.IsNullOrEmpty(keyword))
    {
        where.AppendFormat(" AND (GrName like '%{0}%')", DbUtils.FormatInputString(keyword));
    }
    if (!string.IsNullOrEmpty(userName))
    {
        where.AppendFormat(" AND (([Creator] = '{0}') or ([GrId] in (SELECT GrId FROM bw_GroupMembers WHERE UserName='{0}')))", userName);
    }

    System.Data.DataTable dt = IBatisDbHelper.GetPagedList(tableName, fieldKey, pageIndex, pageSize, fieldShow, fieldOrder, where.ToString(), ref recordCount);
    return dt;
}

 

 

public IDictionary<string, GroupMembersInfo> GetGroupMemberDictionary(int grId)
{
    IList<GroupMembersInfo> members = GetGroupMembers(grId);

    IDictionary<string, GroupMembersInfo> memberDic = new Dictionary<string, GroupMembersInfo>();
    foreach (GroupMembersInfo memberInfo in members)
    {
        memberDic.Add(memberInfo.UserName, memberInfo);
    }

    return memberDic;
}