public ActionResult Edit(Employee emp)
 {
     EmployeeRepository empRepo = new EmployeeRepository();
     //empRepo.UpdateEmployee(emp);
     empRepo.UpdateEmployeeStoredProc(emp);
     return RedirectToAction("Index");
 }
        public IList<Employee> GetAllEmployees()
        {
            IList<Employee> empList = new List<Employee>();
            using (DbConnection conn = new SqlConnection(ConnectionStr))
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT [EmployeeId]
                                          ,[LastName]
                                          ,[FirstName]
                                          ,[Title]
                                          ,[ReportsTo]
                                          ,[BirthDate]
                                          ,[HireDate]
                                          ,[Address]
                                          ,[City]
                                          ,[State]
                                          ,[Country]
                                          ,[PostalCode]
                                          ,[Phone]
                                          ,[Fax]
                                          ,[Email]
                                      FROM [dbo].[Employee]";
                    conn.Open();
                    using (DbDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Employee e = new Employee()
                            {
                                EmployeeId = rdr.GetInt32(0),
                                LastName = rdr.GetString(1),
                                FirstName = rdr.GetString(2),
                                Title = rdr.IsDBNull(3) ? null : rdr.GetString(3),
                                ReportsTo = rdr.IsDBNull(4)
                                           ? (int?)null : rdr.GetInt32(4),
                                BirthDate = rdr.IsDBNull(5)
  ? null as DateTime? : rdr.GetDateTime(5),
                                HireDate = rdr.IsDBNull(6)
  ? null as DateTime? : rdr.GetDateTime(6),
                                Address = rdr.IsDBNull(7) ? null : rdr.GetString(7),
                                City = rdr.IsDBNull(8) ? null : rdr.GetString(8),
                                State = rdr.IsDBNull(9) ? null : rdr.GetString(9),
                                Country = rdr.IsDBNull(10) ? null : rdr.GetString(10),
                                /*add missing fields yourself!*/
                                PostalCode = rdr.IsDBNull(11) ? null : rdr.GetString(11),
                                Phone = rdr.IsDBNull(12) ? null : rdr.GetString(12),
                                Fax = rdr.IsDBNull(13) ? null : rdr.GetString(13),
                                Email = rdr.IsDBNull(14) ? null : rdr.GetString(14)
                            };
                            empList.Add(e);
                        }
                    }
                }
            }
            return empList;

        }
        public void CreateEmployee(Employee emp)
        {
            using (DbConnection conn = new SqlConnection(ConnectionStr))
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"INSERT INTO [dbo].[Employee]
                                                           ([LastName]
                                                           ,[FirstName]
                                                           ,[Title]
                                                           ,[ReportsTo]
                                                           ,[BirthDate]
                                                           ,[HireDate]
                                                           ,[Address]
                                                           ,[City]
                                                           ,[State]
                                                           ,[Country]
                                                           ,[PostalCode]
                                                           ,[Phone]
                                                           ,[Fax]
                                                           ,[Email])
                                                     VALUES(
                                                            @LastName,
                                                            @FirstName,
                                                            @Title,
                                                            @ReportsTo,
                                                            @BirthDate,
                                                            @HireDate,
                                                            @Address,
                                                            @City,
                                                            @State,
                                                            @Country,
                                                            @PostalCode,
                                                            @Phone,
                                                            @Fax,
                                                            @Email)";

                    DbParameter pLn = cmd.CreateParameter();
                    pLn.DbType = System.Data.DbType.String;
                    pLn.ParameterName = "@LastName";
                    pLn.Value = emp.LastName;
                    cmd.Parameters.Add(pLn);

                    DbParameter pFn = cmd.CreateParameter();
                    pFn.DbType = System.Data.DbType.String;
                    pFn.ParameterName = "@FirstName";
                    pFn.Value = emp.FirstName;
                    cmd.Parameters.Add(pFn);

                    DbParameter pTitle = cmd.CreateParameter();
                    pTitle.DbType = System.Data.DbType.String;
                    pTitle.ParameterName = "@Title";
                    pTitle.Value = emp.Title;
                    cmd.Parameters.Add(pTitle);
                    /*add missing fields yourself!*/
                    DbParameter pReportsTo = cmd.CreateParameter();
                    pReportsTo.DbType = System.Data.DbType.Int32;
                    pReportsTo.ParameterName = "@ReportsTo";
                    pReportsTo.Value = emp.ReportsTo;
                    cmd.Parameters.Add(pReportsTo);

                    DbParameter pBirthDate = cmd.CreateParameter();
                    pBirthDate.DbType = System.Data.DbType.DateTime;
                    pBirthDate.ParameterName = "@BirthDate";
                    pBirthDate.Value = emp.BirthDate;
                    cmd.Parameters.Add(pBirthDate);


                    DbParameter pHireDate = cmd.CreateParameter();
                    pHireDate.DbType = System.Data.DbType.DateTime;
                    pHireDate.ParameterName = "@HireDate";
                    pHireDate.Value = emp.HireDate;
                    cmd.Parameters.Add(pHireDate);


                    DbParameter pAddress = cmd.CreateParameter();
                    pAddress.DbType = System.Data.DbType.String;
                    pAddress.ParameterName = "@Address";
                    pAddress.Value = emp.Address;
                    cmd.Parameters.Add(pAddress);


                    DbParameter pCity = cmd.CreateParameter();
                    pCity.DbType = System.Data.DbType.String;
                    pCity.ParameterName = "@City";
                    pCity.Value = emp.City;
                    cmd.Parameters.Add(pCity);


                    DbParameter pState = cmd.CreateParameter();
                    pState.DbType = System.Data.DbType.String;
                    pState.ParameterName = "@State";
                    pState.Value = emp.State;
                    cmd.Parameters.Add(pState);


                    DbParameter pCountry = cmd.CreateParameter();
                    pCountry.DbType = System.Data.DbType.String;
                    pCountry.ParameterName = "@Country";
                    pCountry.Value = emp.Country;
                    cmd.Parameters.Add(pCountry);


                    DbParameter pPostalCode = cmd.CreateParameter();
                    pPostalCode.DbType = System.Data.DbType.String;
                    pPostalCode.ParameterName = "@PostalCode";
                    pPostalCode.Value = emp.PostalCode;
                    cmd.Parameters.Add(pPostalCode);


                    DbParameter pPhone = cmd.CreateParameter();
                    pPhone.DbType = System.Data.DbType.String;
                    pPhone.ParameterName = "@Phone";
                    pPhone.Value = emp.Phone;
                    cmd.Parameters.Add(pPhone);


                    DbParameter pFax = cmd.CreateParameter();
                    pFax.DbType = System.Data.DbType.String;
                    pFax.ParameterName = "@Fax";
                    pFax.Value = emp.Fax;
                    cmd.Parameters.Add(pFax);


                    DbParameter pEmail = cmd.CreateParameter();
                    pEmail.DbType = System.Data.DbType.String;
                    pEmail.ParameterName = "@Email";
                    pEmail.Value = emp.Email;
                    cmd.Parameters.Add(pEmail);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void UpdateEmployeeStoredProc(Employee emp)
        {
            if (emp == null || !emp.EmployeeId.HasValue)
            {
                throw new Exception("Employee object is null or EmployeeId is null!");
            }
            using (DbConnection conn = new SqlConnection(ConnectionStr))
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"udpUpdateEmployee";
                    cmd.CommandType = CommandType.StoredProcedure;

                    var pFn = cmd.CreateParameter();
                    pFn.DbType = System.Data.DbType.String;
                    pFn.ParameterName = "@FirstName";
                    pFn.Value = emp.FirstName;
                    cmd.Parameters.Add(pFn);

                    var pLn = cmd.CreateParameter();
                    pLn.DbType = System.Data.DbType.String;
                    pLn.ParameterName = "@LastName";
                    pLn.Value = emp.LastName;
                    cmd.Parameters.Add(pLn);

                    var pTitle = cmd.CreateParameter();
                    pTitle.DbType = System.Data.DbType.String;
                    pTitle.ParameterName = "@Title";
                    pTitle.Value = emp.Title;
                    cmd.Parameters.Add(pTitle);

                    var pR = cmd.CreateParameter();
                    pR.ParameterName = "@ReportsTo";
                    if (emp.ReportsTo.HasValue)
                    {
                        pR.DbType = System.Data.DbType.Int32;
                        pR.Value = emp.ReportsTo;
                    }
                    else
                    {
                        pR.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(pR);

                    //cmd.AddParameter("@ReportsTo", System.Data.DbType.Int32, emp.ReportsTo);

                    cmd.AddParameter("@BirthDate", System.Data.DbType.DateTime, emp.BirthDate);
                    cmd.AddParameter("@HireDate", System.Data.DbType.DateTime, emp.HireDate);

                    cmd.AddParameter("@Address", System.Data.DbType.String, emp.Address);
                    cmd.AddParameter("@City", System.Data.DbType.String, emp.City);
                    cmd.AddParameter("@State", System.Data.DbType.String, emp.State);
                    cmd.AddParameter("@Country", System.Data.DbType.String, emp.Country);
                    cmd.AddParameter("@PostalCode", System.Data.DbType.String, emp.PostalCode);
                    cmd.AddParameter("@Phone", System.Data.DbType.String, emp.Phone);
                    cmd.AddParameter("@Fax", System.Data.DbType.String, emp.Fax);
                    cmd.AddParameter("@Email", System.Data.DbType.String, emp.Email);

                    cmd.AddParameter("@EmployeeId", System.Data.DbType.Int32, emp.EmployeeId);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public Employee GetEmployeeById(int? employeeId)
        {
            if (!employeeId.HasValue || employeeId == 0)
            {
                throw new Exception("Employee Id should be not null!");
            }

            Employee foundEmployee = null; ;
            using (DbConnection conn = new SqlConnection(ConnectionStr))
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT [EmployeeId]
                                          ,[LastName]
                                          ,[FirstName]
                                          ,[Title]
                                          ,[ReportsTo]
                                          ,[BirthDate]
                                          ,[HireDate]
                                          ,[Address]
                                          ,[City]
                                          ,[State]
                                          ,[Country]
                                          ,[PostalCode]
                                          ,[Phone]
                                          ,[Fax]
                                          ,[Email]
                                      FROM [dbo].[Employee]
                                      WHERE EmployeeId = @EmployeeId";

                    cmd.AddParameter("EmployeeId", DbType.Int32, employeeId);

                    conn.Open();
                    using (IDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            foundEmployee = new Employee()
                            {
                                EmployeeId = rdr.GetInt32(0),
                                LastName = rdr.GetString(1),
                                FirstName = rdr.GetString(2),
                                Title = rdr.IsDBNull(3) ? null : rdr.GetString(3),
                                ReportsTo = rdr.IsDBNull(4)
                                           ? (int?)null : rdr.GetInt32(4),
                                BirthDate = rdr.IsDBNull(5) ? null as DateTime? : rdr.GetDateTime(5),
                                HireDate = rdr.IsDBNull(6) ? null as DateTime? : rdr.GetDateTime(6),
                                Address = rdr.IsDBNull(7) ? null : rdr.GetString(7),
                                City = rdr.IsDBNull(8) ? null : rdr.GetString(8),
                                State = rdr.IsDBNull(9) ? null : rdr.GetString(9),
                                Country = rdr.IsDBNull(rdr.GetOrdinal("Country")) ? null : rdr.GetString(rdr.GetOrdinal("Country"))
                                /*add missing fields yourself!*/
                            };
                        }
                    }
                }
            }
            return foundEmployee;
        }
 public ActionResult Create(Employee emp)
 {
     EmployeeRepository empRepo = new EmployeeRepository();
     empRepo.CreateEmployee(emp);
     return RedirectToAction("Index");
 }