public class Service1 : IService1
{
    private string connection_string = ConfigurationManager.ConnectionStrings["EmployeeDB"].ConnectionString.ToString();

    public List<Employee> getEmployees(int startRowIndex, int maximumRows)
    {
        List<Employee> employees = new List<Employee>();
        SqlConnection connection = new SqlConnection(connection_string);

        SqlCommand command = new SqlCommand();
        command.CommandText = "getEmployees";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@startRowIndex", startRowIndex));
        command.Parameters.Add(new SqlParameter("@maximumRows", maximumRows));

        try
        {
            connection.Open();
            SqlDataReader dr = command.ExecuteReader();

            while (dr.Read())
            {
                Employee employee = new Employee();
                employee.ID = Convert.ToInt32(dr["EmployeeID"]) == 0 ? 0 : Convert.ToInt32(dr["EmployeeID"]);
                employee.Name = dr["Name"].ToString() == string.Empty ? "" : dr["Name"].ToString();
                employee.Surname = dr["Surname"].ToString() == string.Empty ? "" : dr["Surname"].ToString();
                employee.Address = dr["Address"].ToString() == string.Empty ? "" : dr["Address"].ToString();
                employee.Telephone = dr["Telephone"].ToString() == string.Empty ? "" : dr["Telephone"].ToString();
                employees.Add(employee);
            }
            connection.Close();
            return employees;
        }
        catch (Exception e) { throw e; }
    }
    public int getTotalEmployees()
    {
        int total = 0;

        using (SqlConnection connection = new SqlConnection(connection_string))
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "getTotalEmployees";
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                connection.Open();
                total = Convert.ToInt32(command.ExecuteScalar());
            }
            catch (Exception ex) { throw ex; }
        }
        return total;
    }
}

[ServiceContract]
public interface IService1
{
    [OperationContract]
    List<Employee> getEmployees(int startRowIndex, int maximumRows);
    [OperationContract]
    int getTotalEmployees();
}

[DataContract]
public class Employee
{
    [DataMember]
    public int ID { get; set; }
    [DataMember]
    public string Name { get; set; }
    [DataMember]
    public string Surname { get; set; }
    [DataMember]
    public string Address { get; set; }
    [DataMember]
    public string Telephone { get; set; }
}

public class DALEmployee
{
    EmployeeService.Service1Client client = new EmployeeService.Service1Client();
    public List<Employee> getEmployees(int startRowIndex, int maximumRows)
    {
        List<Employee> employees = new List<Employee>();
        var employeeData = client.getEmployees(startRowIndex, maximumRows);
        if (employeeData.Count() > 0)
        {
            foreach (var item in employeeData)
            {
                Employee employee = new Employee() { ID = item.ID, Address = item.Address, Name = item.Name, Surname = item.Surname, Telephone = item.Telephone };
                employees.Add(employee);
            }
        }
        return employees;
    }
    public int getTotalEmployees()
    {
        return client.getTotalEmployees();
    }
}

public class HomeController : Controller
{
    private EmployeeService.Service1Client client = new EmployeeService.Service1Client();
    int maximumRows = 10;

    public ActionResult Index()
    {
        double startRowIndex = Convert.ToDouble(Session["startRowIndex"]) == 0 ? 1 : Convert.ToDouble(Session["startRowIndex"]);
        Session["startRowIndex"] = startRowIndex;
        int totalEmployees = new DALEmployee().getTotalEmployees();
        ViewBag.PageNumber = Math.Floor(startRowIndex / maximumRows) == 0 ? 1 : Math.Floor(startRowIndex / maximumRows);
        List<Employee> employees = new DALEmployee().getEmployees(Convert.ToInt32(startRowIndex), maximumRows);
        return View(employees);
    }

    public ActionResult Next()
    {
        int startRowIndex = Convert.ToInt32(Session["startRowIndex"]) <= maximumRows ? 1 + maximumRows : Convert.ToInt32(Session["startRowIndex"]) + maximumRows;
        Session["startRowIndex"] = startRowIndex;
        return RedirectToAction("Index");
    }

    public ActionResult Previous()
    {
        int startRowIndex = Convert.ToInt32(Session["startRowIndex"]) <= maximumRows ? 1 : Convert.ToInt32(Session["startRowIndex"]) - maximumRows;
        Session["startRowIndex"] = startRowIndex;
        return RedirectToAction("Index");
    }
}

@model IEnumerable<ExerciseER.Models.Employee>
@{
    ViewBag.Title = "Employee Information";
}
<div class="jumbotron">
    <table class="table table-bordered">
        <thead>
            <tr>
                <th>@Html.DisplayNameFor(model => model.Name)</th>
                <th>@Html.DisplayNameFor(model => model.Surname)</th>
                <th>@Html.DisplayNameFor(model => model.Address)</th>
                <th>@Html.DisplayNameFor(model => model.Telephone)</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {
                <tr>
                    <td>@Html.DisplayFor(model => item.Name)</td>
                    <td>@Html.DisplayFor(model => item.Surname)</td>
                    <td>@Html.DisplayFor(model => item.Address)</td>
                    <td>@Html.DisplayFor(model => item.Telephone)</td>
                </tr>
            }
            <tr>
                <td>@Html.ActionLink("Previous", "Previous", null, new { @class = "btn btn-info"})</td>
                <td>@ViewBag.PageNumber</td>
                <td>@Html.ActionLink("Next", "Next", null, new { @class = "btn btn-info" })</td>
            </tr>
        </tbody>
    </table>
</div>

create procedure getEmployees
(
    @startRowIndex int,
    @maximumRows int
)
AS

declare @firstInt int, @startRow int
if (@startRowIndex <= (select COUNT(EmployeeID) from dbo.[Employee]))
begin
    set ROWCOUNT @startRowIndex

    select @firstInt = EmployeeID from dbo.[Employee] order by EmployeeID

    set ROWCOUNT @maximumRows

    select EmployeeID, Name, Surname, Address, Telephone
    from dbo.[Employee] where EmployeeID >= @firstInt order by EmployeeID

    set ROWCOUNT 0
end
GO

create procedure getTotalEmployees
as

select COUNT(EmployeeId) from dbo.[Employee] where [Status] = 1

go