public async Task <JsonResult> GetBalance(string GeneralNumber) { VacationBalance vb = new VacationBalance(); SqlConnection con = new SqlConnection(cs); await Task.Delay(7000); using (con) { try { con.Open(); //read annual balance SqlCommand com = new SqlCommand("Workflow_GetAnnualBalanceNewF", con); com.CommandType = CommandType.StoredProcedure; com.Parameters.AddWithValue("@GENERALNO", GeneralNumber); com.Parameters.AddWithValue("@dat", "03/14/2018"); SqlDataReader rdr = com.ExecuteReader(); rdr.Read(); vb.Annual = Convert.ToInt32(rdr["TotalBalance"]); rdr.Close(); //read other balance SqlCommand com2 = new SqlCommand("Workflow_GetUserOvertimeHolidaysUrgentMng", con); com2.CommandType = CommandType.StoredProcedure; com2.Parameters.AddWithValue("@GENERALNO", "63012460"); com2.Parameters.AddWithValue("@YearUrgent", "1439"); SqlDataReader rdr2 = com2.ExecuteReader(); rdr2.Read(); vb.Overtime = Convert.ToInt32(rdr2["Overtime"]); vb.Holyday = Convert.ToInt32(rdr2["Holyday"]); vb.Urgent = Convert.ToInt32(rdr2["Urgent"]); vb.Mng = Convert.ToInt32(rdr2["Mng"]); vb.Priv = Convert.ToInt32(rdr2["Priv"]); rdr2.Close(); } catch (Exception err) { con.Close(); return(Json(err.Message, JsonRequestBehavior.AllowGet)); } finally { con.Close(); } } con.Close(); return(Json(vb, JsonRequestBehavior.AllowGet)); }
public IQueryable <VacationBalance> GetVacationBalanceAsQueryable() { var result = new List <VacationBalance>(); string vacationBalanceView = _customSettings.DbViewVacationBalance; if (vacationBalanceView == null) { //_logger.Log($"{this.GetType().Name}, GetEmployeesAsQueryable(), DATABASE_VIEW_VACATIONBALANCE is null", null); } using (var sqlConnection1 = new SqlConnection(_connectionString)) { var cmd = new SqlCommand { CommandText = "SELECT * FROM " + vacationBalanceView, CommandType = CommandType.Text, Connection = sqlConnection1 }; sqlConnection1.Open(); var reader = cmd.ExecuteReader(); while (reader.Read()) { var currentRow = new VacationBalance { MunicipalityInfo = SafeGetString(reader, 0), SocialSecurityNumber = SafeGetString(reader, 1), EmploymentRelationshipNumber = SafeGetString(reader, 2), SalaryKind = SafeGetString(reader, 3), VacationEarnedYear = SafeGetString(reader, 4), BalanceDate = SafeGetString(reader, 5), VacationHoursWithPay = SafeGetInt32(reader, 6), PossibleVacationDaysWithPay = SafeGetInt32(reader, 7), VacationHoursWithoutPay = SafeGetInt32(reader, 8), PossibleVacationDaysWithoutPay = SafeGetInt32(reader, 9), TransferredVacationHours = SafeGetInt32(reader, 10), PossibleTransferredVacationDays = SafeGetInt32(reader, 11), FreeVacationHoursTotal = SafeGetInt32(reader, 12), VacationHoursWithPayDec = SafeGetDouble(reader, 13), VacationHoursWithoutPayDec = SafeGetDouble(reader, 14), TransferredVacationHoursDec = SafeGetDouble(reader, 15), FreeVacationHoursTotalDec = SafeGetDouble(reader, 16), UpdateDate = SafeGetDate(reader, 17) }; result.Add(currentRow); } } return(result.AsQueryable()); }