Search DropDownList and Textbox With Sortable Header in Custom Paging In GridView Using MySQL

But it does not apply the Custom Paging

Please share what you have implemented in your procedure

I don't have any, since I'm practicing the Custom Paging

Hi,

Please refer below Stored Procedure

C#

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_PageIndex", pageIndex);
cmd.Parameters.AddWithValue("_PageSize", PageSize);
cmd.Parameters.AddWithValue("_ContactName", ddlContactNames.SelectedItem.Value);//It may be Selected Text
cmd.Parameters.AddWithValue("_CompanyName", ddlCompanyNames.SelectedItem.Value);//It may be Selected Text
cmd.Parameters.Add("_RecordCount", MySqlDbType.Int32, 4);
cmd.Parameters["_RecordCount"].Direction = ParameterDirection.Output;

SQL

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetCustomers_Pager`(
   _PageIndex INT
   ,_PageSize INT
   ,_ContactName VARCHAR(100)
   ,_CompanyName VARCHAR(100)
   ,OUT _RecordCount INT
)
BEGIN
       SET @RowNumber:=0;
 
       CREATE TEMPORARY TABLE Results
       SELECT @RowNumber:=@RowNumber+1 RowNumber
              ,CustomerID
              ,ContactName
              ,CompanyName
       FROM Customers
	   WHERE (ContactName = _ContactName OR _ContactName IS NULL)
	   AND (CompanyName = _CompanyName OR _CompanyName IS NULL);
 
       SET _RecordCount =(SELECT COUNT(*) FROM Results);
 
       SELECT * FROM Results
       WHERE RowNumber BETWEEN(_PageIndex -1) * _PageSize + 1 AND(((_PageIndex -1) * _PageSize + 1) + _PageSize) - 1;
 
       DROP TEMPORARY TABLE Results;
END$$
DELIMITER ;

I hope this will help you out.