SHARE
TWEET

Untitled

a guest Oct 3rd, 2017 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. public class Service1 : IService1
  2. {
  3.     private string connection_string = ConfigurationManager.ConnectionStrings["EmployeeDB"].ConnectionString.ToString();
  4.  
  5.     public List<Employee> getEmployees(int startRowIndex, int maximumRows)
  6.     {
  7.         List<Employee> employees = new List<Employee>();
  8.         SqlConnection connection = new SqlConnection(connection_string);
  9.  
  10.         SqlCommand command = new SqlCommand();
  11.         command.CommandText = "getEmployees";
  12.         command.CommandType = CommandType.StoredProcedure;
  13.         command.Parameters.Add(new SqlParameter("@startRowIndex", startRowIndex));
  14.         command.Parameters.Add(new SqlParameter("@maximumRows", maximumRows));
  15.  
  16.         try
  17.         {
  18.             connection.Open();
  19.             SqlDataReader dr = command.ExecuteReader();
  20.  
  21.             while (dr.Read())
  22.             {
  23.                 Employee employee = new Employee();
  24.                 employee.ID = Convert.ToInt32(dr["EmployeeID"]) == 0 ? 0 : Convert.ToInt32(dr["EmployeeID"]);
  25.                 employee.Name = dr["Name"].ToString() == string.Empty ? "" : dr["Name"].ToString();
  26.                 employee.Surname = dr["Surname"].ToString() == string.Empty ? "" : dr["Surname"].ToString();
  27.                 employee.Address = dr["Address"].ToString() == string.Empty ? "" : dr["Address"].ToString();
  28.                 employee.Telephone = dr["Telephone"].ToString() == string.Empty ? "" : dr["Telephone"].ToString();
  29.                 employees.Add(employee);
  30.             }
  31.             connection.Close();
  32.             return employees;
  33.         }
  34.         catch (Exception e) { throw e; }
  35.     }
  36.     public int getTotalEmployees()
  37.     {
  38.         int total = 0;
  39.  
  40.         using (SqlConnection connection = new SqlConnection(connection_string))
  41.         {
  42.             SqlCommand command = new SqlCommand();
  43.             command.CommandText = "getTotalEmployees";
  44.             command.CommandType = CommandType.StoredProcedure;
  45.             try
  46.             {
  47.                 connection.Open();
  48.                 total = Convert.ToInt32(command.ExecuteScalar());
  49.             }
  50.             catch (Exception ex) { throw ex; }                
  51.         }
  52.         return total;
  53.     }
  54. }
  55.    
  56. [ServiceContract]
  57. public interface IService1
  58. {
  59.     [OperationContract]
  60.     List<Employee> getEmployees(int startRowIndex, int maximumRows);
  61.     [OperationContract]
  62.     int getTotalEmployees();
  63. }
  64.  
  65. [DataContract]
  66. public class Employee
  67. {
  68.     [DataMember]
  69.     public int ID { get; set; }
  70.     [DataMember]
  71.     public string Name { get; set; }
  72.     [DataMember]
  73.     public string Surname { get; set; }
  74.     [DataMember]
  75.     public string Address { get; set; }
  76.     [DataMember]
  77.     public string Telephone { get; set; }
  78. }
  79.    
  80. public class DALEmployee
  81. {
  82.     EmployeeService.Service1Client client = new EmployeeService.Service1Client();
  83.     public List<Employee> getEmployees(int startRowIndex, int maximumRows)
  84.     {
  85.         List<Employee> employees = new List<Employee>();
  86.         var employeeData = client.getEmployees(startRowIndex, maximumRows);
  87.         if (employeeData.Count() > 0)
  88.         {
  89.             foreach (var item in employeeData)
  90.             {
  91.                 Employee employee = new Employee() { ID = item.ID, Address = item.Address, Name = item.Name, Surname = item.Surname, Telephone = item.Telephone };
  92.                 employees.Add(employee);
  93.             }
  94.         }
  95.         return employees;
  96.     }
  97.     public int getTotalEmployees()
  98.     {
  99.         return client.getTotalEmployees();
  100.     }
  101. }
  102.    
  103. public class HomeController : Controller
  104. {
  105.     private EmployeeService.Service1Client client = new EmployeeService.Service1Client();
  106.     int maximumRows = 10;
  107.  
  108.     public ActionResult Index()
  109.     {            
  110.         double startRowIndex = Convert.ToDouble(Session["startRowIndex"]) == 0 ? 1 : Convert.ToDouble(Session["startRowIndex"]);
  111.         Session["startRowIndex"] = startRowIndex;
  112.         int totalEmployees = new DALEmployee().getTotalEmployees();
  113.         ViewBag.PageNumber = Math.Floor(startRowIndex / maximumRows) == 0 ? 1 : Math.Floor(startRowIndex / maximumRows);
  114.         List<Employee> employees = new DALEmployee().getEmployees(Convert.ToInt32(startRowIndex), maximumRows);                  
  115.         return View(employees);
  116.     }
  117.  
  118.     public ActionResult Next()
  119.     {
  120.         int startRowIndex = Convert.ToInt32(Session["startRowIndex"]) <= maximumRows ? 1 + maximumRows : Convert.ToInt32(Session["startRowIndex"]) + maximumRows;
  121.         Session["startRowIndex"] = startRowIndex;
  122.         return RedirectToAction("Index");
  123.     }
  124.  
  125.     public ActionResult Previous()
  126.     {
  127.         int startRowIndex = Convert.ToInt32(Session["startRowIndex"]) <= maximumRows ? 1 : Convert.ToInt32(Session["startRowIndex"]) - maximumRows;
  128.         Session["startRowIndex"] = startRowIndex;
  129.         return RedirectToAction("Index");
  130.     }
  131. }
  132.    
  133. @model IEnumerable<ExerciseER.Models.Employee>
  134. @{
  135.     ViewBag.Title = "Employee Information";
  136. }
  137. <div class="jumbotron">
  138.     <table class="table table-bordered">
  139.         <thead>
  140.             <tr>
  141.                 <th>@Html.DisplayNameFor(model => model.Name)</th>
  142.                 <th>@Html.DisplayNameFor(model => model.Surname)</th>
  143.                 <th>@Html.DisplayNameFor(model => model.Address)</th>
  144.                 <th>@Html.DisplayNameFor(model => model.Telephone)</th>
  145.             </tr>
  146.         </thead>
  147.         <tbody>
  148.             @foreach (var item in Model)
  149.             {
  150.                 <tr>
  151.                     <td>@Html.DisplayFor(model => item.Name)</td>
  152.                     <td>@Html.DisplayFor(model => item.Surname)</td>
  153.                     <td>@Html.DisplayFor(model => item.Address)</td>
  154.                     <td>@Html.DisplayFor(model => item.Telephone)</td>
  155.                 </tr>
  156.             }
  157.             <tr>
  158.                 <td>@Html.ActionLink("Previous", "Previous", null, new { @class = "btn btn-info"})</td>
  159.                 <td>@ViewBag.PageNumber</td>
  160.                 <td>@Html.ActionLink("Next", "Next", null, new { @class = "btn btn-info" })</td>
  161.             </tr>
  162.         </tbody>
  163.     </table>
  164. </div>
  165.    
  166. create procedure getEmployees
  167. (
  168.     @startRowIndex int,
  169.     @maximumRows int
  170. )
  171. AS
  172.  
  173. declare @firstInt int, @startRow int
  174. if (@startRowIndex <= (select COUNT(EmployeeID) from dbo.[Employee]))
  175. begin
  176.     set ROWCOUNT @startRowIndex
  177.  
  178.     select @firstInt = EmployeeID from dbo.[Employee] order by EmployeeID
  179.  
  180.     set ROWCOUNT @maximumRows
  181.  
  182.     select EmployeeID, Name, Surname, Address, Telephone
  183.     from dbo.[Employee] where EmployeeID >= @firstInt order by EmployeeID
  184.  
  185.     set ROWCOUNT 0
  186. end
  187. GO
  188.    
  189. create procedure getTotalEmployees
  190. as
  191.  
  192. select COUNT(EmployeeId) from dbo.[Employee] where [Status] = 1
  193.  
  194. go
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top