C#中，带关键字的按页获取查询，这是部份代码，但可以看出大概实现方式

本文链接：https://blog.youkuaiyun.com/pengdayong77/article/details/50804748

本文介绍了一种通过SQL查询实现的分页获取库存详情列表的方法。该方法接受部门ID、页面大小、当前页数及关键词作为参数，并返回指定条件下的库存详情列表。此外，还提供了获取总记录数的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 /// <summary>
        /// 按页获取列表
        /// </summary>
        /// <param name="wid"></param>
        /// <returns></returns>
        public DataTable GetStockDetpListByPage(int wid, int pageSize, int pageindex, string keyword, out int totalCount)
        {
            totalCount = 0;
            if (String.IsNullOrEmpty(keyword))
            {
                keyword = "null";
            }
            else
            {
                keyword = "'" + keyword + "'";
            }
            string sqlcmd = @" 
            Select count(1)
            From Sys_Department  a
            Left Join (
	            Select ShopValue, Count(1) as StockCount
	            From VS_StockRatio  a
	            Inner Join VS_StockRatioDetail b on b.StockRatioID = a.ID And DeleteFlag = 0
	            Where ShopType = 'deptid' And WID = " + wid +
	            @"Group by ShopValue
            ) b on b.ShopValue = a.ID 
            Where a.DeptType = 'VSHOP' And  CompanyID in (Select CompanyID From WX_User Where ID = " + wid + @") and DeptName like '%'+isnull(" + keyword + ",'')+'%'";
            using (SqlConnection con = new SqlConnection(DbHelperSQL.connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = sqlcmd;
                    cmd.CommandType = System.Data.CommandType.Text;
                    try { con.Open(); totalCount = Convert.ToInt32(cmd.ExecuteScalar()); }
                    catch (Exception) { con.Close(); return null; }
                }
            }

            string strSql = @" 
            select ID,CompanyID,ParentDeptID,DeptName,DeptType,XPoint,YPoint,TelPhone,Location,SortNum,Remark,StockCount 
            from(
            Select a.* ,b.StockCount,ROW_NUMBER( ) over( order by a.id) as number
            From Sys_Department  a
            Left Join (
	            Select ShopValue, Count(1) as StockCount
	            From VS_StockRatio  a
	            Inner Join VS_StockRatioDetail b on b.StockRatioID = a.ID And DeleteFlag = 0
	            Where ShopType = 'deptid' And WID = @WID
	            Group by ShopValue
            ) b on b.ShopValue = a.ID 
            Where a.DeptType = 'VSHOP' And  CompanyID in (Select CompanyID From WX_User Where ID = @WID)) ab where 
            number between @first and @last  
            and DeptName like '%'+isnull(" + keyword + ",'')+'%'";
            SqlParameter[] parameters = { new SqlParameter("@WID", SqlDbType.Int, 4), new SqlParameter("@first", SqlDbType.Int, 4), 
                                            new SqlParameter("@last", SqlDbType.Int, 4) };

            int inipage = pageSize * (pageindex - 1) + 1;
            int lastpage = pageindex * pageSize;

            parameters[0].Value = wid;
            parameters[1].Value = inipage;
            parameters[2].Value = lastpage;

            return DbHelperSQL.GetDataTable(strSql, parameters);
        }