void SendResultInfoAsJson(GetEmployeesAvailBtwnResponse res) { string strJson = JsonConvert.SerializeObject(res); Response.ContentType = "application/json; charset=utf-8"; Response.AppendHeader("Access-Control-Allow-Origin", "*"); Response.Write(strJson); Response.End(); }
protected void Page_Load(object sender, EventArgs e) { GetEmployeesAvailBtwnRequest req; GetEmployeesAvailBtwnResponse res = new GetEmployeesAvailBtwnResponse(); res.error = String.Empty; // 1. Deserialize the incoming Json. try { req = GetRequestInfo(); } catch (Exception ex) { res.error = ex.Message.ToString(); // Return the results as Json. SendResultInfoAsJson(res); return; } SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); try { connection.Open(); string getAvailEmps = @"SELECT EmployeeID, FirstName, LastName FROM Employee WHERE EmployeeID IN ( SELECT DISTINCT e.EmployeeID FROM Employee e LEFT JOIN AvailabilityTbl a ON e.EmployeeID = a.EmployeeID AND a.DayOfWeek = DATEPART(WEEKDAY, @StartDate) WHERE Cast(@StartDate as time) >= Cast(a.StartTime as time) AND Cast(a.EndTime as time) >= Cast(@EndDate as time) ) AND EmployeeID NOT IN ( SELECT DISTINCT e.EmployeeID FROM Employee e LEFT JOIN Schedule s ON e.EmployeeID = s.EmployeeID WHERE @StartDate >= s.StartOfShift AND s.EndOfShift >= @EndDate ) AND StoreID = @StoreID AND JobType = 1" ; SqlCommand getAvailEmpsCmd = new SqlCommand(getAvailEmps, connection); getAvailEmpsCmd.Parameters.Add("@StoreID", SqlDbType.Int); getAvailEmpsCmd.Parameters.Add("@StartDate", SqlDbType.DateTime); getAvailEmpsCmd.Parameters.Add("@EndDate", SqlDbType.DateTime); getAvailEmpsCmd.Parameters["@StoreID"].Value = req.StoreID; getAvailEmpsCmd.Parameters["@StartDate"].Value = req.StartDate; getAvailEmpsCmd.Parameters["@EndDate"].Value = req.EndDate; SqlDataReader reader = getAvailEmpsCmd.ExecuteReader(); res.employees = new List <Employee>(); while (reader.Read()) { Employee curE = new Employee(); curE.EmployeeID = Convert.ToInt32(reader["EmployeeID"]); curE.FirstName = Convert.ToString(reader["FirstName"]); curE.LastName = Convert.ToString(reader["LastName"]); res.employees.Add(curE); } reader.Close(); } catch (Exception ex) { res.error = ex.Message.ToString(); } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } // Return the results as Json. SendResultInfoAsJson(res); }