分页-加载更多-带like

SQL Server 分页存储过程

CREATE PROCEDURE [dbo].[SP_Search_More_Keyword]
   @KeyWord NVARCHAR(250),--关键字
   @PageSize INT,--页量
   @PageIndex INT --页码
AS
BEGIN  
   SELECT TOP (@PageSize)Id, Title, AddTime
   FROM 
	(
	 SELECT ROW_NUMBER() OVER (ORDER BY [Id] DESC) AS RowId,[Id],Title,AddTime  
	 FROM TTable WHERE Title LIKE '%' + @KeyWord + '%'
	)AT  
    WHERE RowId > @PageSize*(@PageIndex-1) 
END

CS

        #region 分页-加载更多-带like
        /// <summary>
        ///  分页-加载更多-带like
        /// </summary>
        /// <param name="KeyWord">关键字</param>
        /// <param name="PageSize">页量</param>
        /// <param name="PageIndex">页码</param>
        /// <returns></returns>
        public static DataTable Search_More_Keyword(string KeyWord, int PageSize, int PageIndex)
        {
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand cmd = db.GetStoredProcCommand("CP_Search_AI_More_Keyword"))
            {
                db.AddInParameter(cmd, "@KeyWord", DbType.String, KeyWord== null ? "" : KeyWord);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, PageSize);
                db.AddInParameter(cmd, "@PageIndex", DbType.Int32, PageIndex);
                DataSet ds = db.ExecuteDataSet(cmd);
                return ds.Tables[0];
            }
        }
        #endregion

【注】：LIKE '%%'会全表匹配，影响效率，若数据量大，尽量避免使用LIKE。