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

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.