ALTER PROCEDURE dbo.Select_tbl_AlbumMaster
@AlbumCode numeric(18,0),
@PageNo int = 1,
@RecordCount int = 10,
@TotalCount int output
AS
/* SET NOCOUNT ON */
BEGIN
IF(@AlbumCode > 0)
BEGIN
SELECT AlbumCode, AlbumName
FROM tbl_AlbumMaster
WHERE (AlbumCode = @AlbumCode)
SET @TotalCount = 0
END
ELSE
BEGIN
Declare @RFrom int;
--Declare @RUpto int;
SET @RFrom = (@PageNo * @RecordCount) - (@RecordCount - 1);
--SET @RUpto = (@PageNo * @RecordCount);
DECLARE @first_id int;
SET ROWCOUNT @RFrom
--print @RFrom
SELECT @first_id = AlbumCode FROM tbl_AlbumMaster ORDER BY AlbumCode
--print @first_id
SET ROWCOUNT @RecordCount
SELECT AlbumCode, AlbumName
FROM tbl_AlbumMaster
WHERE AlbumCode >= @first_id
SELECT @TotalCount = COUNT(*) FROM tbl_AlbumMaster
END
END
My Database table is
I have following store procedure & i want to fill gridveiw usin this
My code for filldata is
int PageCount = 0;
SqlDataAdapter adapter;
SqlCommand command = new SqlCommand();
DataSet dsData = new DataSet();
con.Open();
command = new SqlCommand("Select_tbl_AlbumMaster", con); //stored procedure Name
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@AlbumCode", AlbumCode); //for username
command.Parameters.AddWithValue("@PageNo", PageNo); //for password
command.Parameters.AddWithValue("@RecordCount", RecordCount);
command.Parameters.AddWithValue("@TotalCount", 18);
command.Parameters["@TotalCount"].Direction = ParameterDirection.Output;
adapter = new SqlDataAdapter(command);
adapter.Fill(dsData);
lblTotalPage.Text = Convert.ToString(PageCount);
grdData.DataSource = dsData.Tables[0];
grdData.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}