Link for csharp, asp.net, ado.net, dotnet basics and sql server video tutorial playlists
[ Ссылка ]

Link for text version of this video
[ Ссылка ]

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
[ Ссылка ]

Step 1: Create a stored procedure to retrieve employees by page index and page size. This procedure also has an output parameter to return the total

number of rows in the database table.
Create proc spGetEmployees_by_PageIndex_and_PageSize
@PageIndex int,
@PageSize int,
@TotalRows int output
as
Begin
Declare @StartRowIndex int
Declare @EndRowIndex int

Set @StartRowIndex = (@PageIndex * @PageSize) + 1;
Set @EndRowIndex = (@PageIndex + 1) * @PageSize;

Select EmployeeId, Name, Gender, City from
(Select ROW_NUMBER() over (order by EmployeeId) as RowNumber, EmployeeId, Name, Gender, City
from Sample.dbo.tblEmployee) Employees
--Replace ] GREATERTHAN symbol and "[" with LESSTHAN symbol
Where RowNumber ]= @StartRowIndex and RowNumber [= @EndRowIndex

Select @TotalRows = COUNT(*) from tblEmployee
End

Step 2: Add a class file with name = "EmployeeDataAccessLayer.cs".
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
public class Employee
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public string City { get; set; }
}

public class EmployeeDataAccessLayer
{
// Replace square brackets with angular brackets
public static List[Employee] GetEmployees(int pageIndex, int pageSize, out int totalRows)
{
// Replace square brackets with angular brackets
List[Employee] listEmployees = new List[Employee]();

string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spGetEmployees_by_PageIndex_and_PageSize", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter paramStartIndex = new SqlParameter();
paramStartIndex.ParameterName = "@PageIndex";
paramStartIndex.Value = pageIndex;
cmd.Parameters.Add(paramStartIndex);

SqlParameter paramMaximumRows = new SqlParameter();
paramMaximumRows.ParameterName = "@PageSize";
paramMaximumRows.Value = pageSize;
cmd.Parameters.Add(paramMaximumRows);

SqlParameter paramOutputTotalRows = new SqlParameter();
paramOutputTotalRows.ParameterName = "@TotalRows";
paramOutputTotalRows.Direction = ParameterDirection.Output;
paramOutputTotalRows.SqlDbType = SqlDbType.Int;

cmd.Parameters.Add(paramOutputTotalRows);

con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
employee.Name = rdr["Name"].ToString();
employee.Gender = rdr["Gender"].ToString();
employee.City = rdr["City"].ToString();

listEmployees.Add(employee);
}

rdr.Close();
totalRows = (int)cmd.Parameters["@TotalRows"].Value;

}
return listEmployees;
}
}
}

Step 3: Drag and drop gridview and a repeater control and set the required properties accordingly.

Step 4: Copy and paste the following code in webform1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
int totalRows = 0;
GridView1.DataSource = EmployeeDataAccessLayer.
GetEmployees(0, GridView1.PageSize, out totalRows);
GridView1.DataBind();

DatabindRepeater(0, GridView1.PageSize, totalRows);
}
}
protected void linkButton_Click(object sender, EventArgs e)
{
int totalRows = 0;
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
pageIndex -= 1;
GridView1.PageIndex = pageIndex;
GridView1.DataSource = EmployeeDataAccessLayer.
GetEmployees(pageIndex, GridView1.PageSize, out totalRows);
GridView1.DataBind();
DatabindRepeater(pageIndex, GridView1.PageSize, totalRows);
}

свернуть