/// <summary> /// 取得emp by 表單上的data /// </summary> /// <param name="arg"></param> /// <returns></returns> public List <EmployeeSearchResult> GetSearchResultByArg(Models.EmployeeSearchArg arg) { DataTable dt = new DataTable(); string sql = @"SELECT hs.EmployeeID, hs.FirstName + hs.LastName AS EmployeeName, ct1.CodeId + '-' + ct1.CodeVal AS Type, convert(varchar, hs.HireDate, 111) HireDate, ct2.CodeVal AS Gender, Year(GETDATE())-Year(BirthDate) Age FROM HR.Employees hs JOIN CodeTable ct1 ON (ct1.CodeId = hs.Title AND ct1.CodeType = 'TITLE') LEFT JOIN CodeTable ct2 ON (ct2.CodeId = hs.Gender AND ct2.CodeType = 'GENDER') WHERE (@employeeID IS NULL OR @employeeID = EmployeeID) AND (@employeeName IS NULL OR FirstName LIKE '%' + @employeeName + '%' OR hs.LastName LIKE '%' + @employeeName + '%') AND (@codeVal IS NULL OR @codeVal = hs.Title) AND (@startHireDate IS NULL OR @endHireDate IS NULL OR HireDate BETWEEN @startHireDate AND @endHireDate) ;" ; using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString())) { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(new SqlParameter("@employeeID", NullToDBNullValue(arg.EmployeeId, true))); cmd.Parameters.Add(new SqlParameter("@employeeName", NullToDBNullValue(arg.EmployeeName, true))); cmd.Parameters.Add(new SqlParameter("@codeVal", NullToDBNullValue(arg.Title, true))); cmd.Parameters.Add(new SqlParameter("@startHireDate", NullToDBNullValue(arg.StartHireDate, true))); cmd.Parameters.Add(new SqlParameter("@endHireDate", NullToDBNullValue(arg.EndHireDate, true))); /*如果用string的方式,可以改成下面這兩行*/ //cmd.Parameters.Add(new SqlParameter("@startHireDate", arg.StartHireDate.ToString() == null ? string.Empty : arg.StartHireDate.ToString())); //cmd.Parameters.Add(new SqlParameter("@endHireDate", arg.EndHireDate.ToString() == null ? string.Empty : arg.EndHireDate.ToString())); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(dt); conn.Close(); } return(TableFactory.Table2EmployeeSearchResult(dt)); }
public ActionResult SearchEmployee(Models.EmployeeSearchArg arg) { Models.EmployeeService employeeService = new Models.EmployeeService(); ViewBag.Type = employeeService.GetTitle(); List <Models.EmployeeSearchResult> employeeSearchResult = employeeService.GetSearchResultByArg(arg); ViewBag.EmployeeSearchResult = employeeSearchResult; return(View(arg)); }