/// <summary> /// 尋找訂單 /// </summary> /// <param name="selectitem"></param> /// <returns></returns> public List <Orders> SelectOrder(Models.SelectSearch selectitem) { DataTable selectresult = new DataTable(); string sql = @"SELECT OrderID,b.CompanyName,CONVERT(varchar(10) ,OrderDate, 111 ) AS OrderDate,CONVERT(varchar(10) ,ShippedDate, 111 ) AS ShippedDate FROM Sales.Orders AS a JOIN Sales.Customers AS b ON a.CustomerID=b.CustomerID JOIN Sales.Shippers AS c ON a.ShipperID=c.ShipperID WHERE (b.CompanyName LIKE '%'+@CompanyName+'%' OR @CompanyName = '') AND (a.OrderID LIKE '%'+@OrderID+'%' OR @OrderID = '') AND (a.EmployeeID LIKE @EmployeeID OR @EmployeeID = '') AND (a.ShipperID LIKE @ShipperID OR @ShipperID = '') AND (OrderDate >= @OrderDate AND OrderDate < DATEADD(DAY,1,@OrderDate) OR @OrderDate = '') AND (ShippedDate >= @ShippedDate AND ShippedDate < DATEADD(DAY,1,@ShippedDate) OR @ShippedDate = '') AND (RequiredDate >= @RequiredDate AND RequiredDate < DATEADD(DAY,1,@RequiredDate) OR @RequiredDate = '')"; using (SqlConnection conn = new SqlConnection(this.GetconnectionStrings())) { conn.Open(); SqlCommand command = new SqlCommand(sql, conn); command.Parameters.Add(new SqlParameter("@OrderID", selectitem.OrderID == null ? string.Empty : selectitem.OrderID)); command.Parameters.Add(new SqlParameter("@CompanyName", selectitem.CompanyName == null ? string.Empty : selectitem.CompanyName)); command.Parameters.Add(new SqlParameter("@EmployeeID", selectitem.EmployeeID == null ? string.Empty : selectitem.EmployeeID)); command.Parameters.Add(new SqlParameter("@ShipperID", selectitem.ShipperID == null ? string.Empty : selectitem.ShipperID)); command.Parameters.Add(new SqlParameter("@OrderDate", selectitem.OrderDate == null ? string.Empty : selectitem.OrderDate)); command.Parameters.Add(new SqlParameter("@ShippedDate", selectitem.ShippedDate == null ? string.Empty : selectitem.ShippedDate)); command.Parameters.Add(new SqlParameter("@RequiredDate", selectitem.RequiredDate == null ? string.Empty : selectitem.RequiredDate)); SqlDataAdapter sqlAdapter = new SqlDataAdapter(command); //sqlAdapter.SelectCommand = new SqlCommand(sql, conn); //cmd.Parameters.Add(new SqlParameter("@CustomerID", arg.CustomerID == null ? string.Empty : arg.CustomerID)); sqlAdapter.Fill(selectresult); conn.Close(); } return(this.MapSelectOrder(selectresult)); }
// GET: Employees public ActionResult Index(Models.SelectSearch selectitem) { EmployeesService EmployeesService = new EmployeesService(); List <Employees> result = EmployeesService.GetEmployeesName(); List <SelectListItem> EmployeesData = new List <SelectListItem>(); EmployeesData.Add(new SelectListItem() { Text = "", Value = null }); foreach (var item in result) { EmployeesData.Add(new SelectListItem() { Text = item.LastName, Value = item.EmployeeID.ToString() }); ViewData["EmpData"] = EmployeesData; } ShippersService ShippersService = new ShippersService(); List <Shippers> result1 = ShippersService.GetShippersName(); List <SelectListItem> ShippersData = new List <SelectListItem>(); ShippersData.Add(new SelectListItem() { Text = "", Value = null }); foreach (var item in result1) { ShippersData.Add(new SelectListItem() { Text = item.CompanyName, Value = item.ShipperID.ToString() }); ViewData["ShippersData"] = ShippersData; } OrdersService OrdersService = new OrdersService(); ViewBag.SelectData = OrdersService.SelectOrder(selectitem); return(View()); }