public string GetNumberOfWeeks() { string _sArrayString = ""; //select first_name, last_name, email_id from ovms_users where user_id = 9 conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["dbconn"].ConnectionString); try { if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); API.Service timesheet = new API.Service(); XmlDocument xmldoc = new XmlDocument(); xmldoc.LoadXml("<XML>" + timesheet.Add_TimeSheet(Session["EmailClient"].ToString(), Session["P@ssClient"].ToString(), Session["UserIDClient"].ToString()).InnerXml + "</XML>"); string FileName = Server.MapPath("temp") + "\\response_get_timesheet_" + DateTime.Now.Millisecond.ToString() + ".xml"; XmlNodeList Response = xmldoc.SelectNodes("XML/RESPONSE/TIMESHEET"); // string weeks; // string timesheetVariable = ""; string jobID = ""; jobID = Response[0].SelectSingleNode("JOB_ID").InnerText; Session["JobID"] = jobID.ToString(); //start, end and weeks string sqlGetStartEndWeeks = " select contract_Start_date, contract_end_date, " + " DATEDIFF(wk, contract_Start_date, contract_end_date + 7) as Num_weeks " + " from ovms_jobs where job_id = " + Session["JobID"].ToString().Trim(); SqlCommand cmdStartEndWeeks = new SqlCommand(sqlGetStartEndWeeks, conn); SqlDataReader rsStartEndWeeks = cmdStartEndWeeks.ExecuteReader(); //string _svendorList = ""; while (rsStartEndWeeks.Read()) { _sArrayString = rsStartEndWeeks["contract_Start_date"].ToString() + "," + rsStartEndWeeks["contract_end_date"].ToString() + "," + rsStartEndWeeks["Num_weeks"].ToString(); } rsStartEndWeeks.Close(); cmdStartEndWeeks.Dispose(); } } catch (Exception ex) { // } finally { if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } return(_sArrayString); }