public ActionResult Create([Bind(Include = "PONO,ITCODE,QTY")] PoDetail pODETAIL) { if (ModelState.IsValid) { using (var client = new HttpClient()) { client.BaseAddress = new Uri("http://localhost:57888/Api/"); //HTTP POST var postTask = client.PostAsJsonAsync <PoDetail>("PODetails", pODETAIL); postTask.Wait(); var result = postTask.Result; if (result.IsSuccessStatusCode) { return(RedirectToAction("Index")); } } ModelState.AddModelError(string.Empty, "Server Error. Please contact administrator."); return(RedirectToAction("Index")); } ViewBag.ITCODE = new SelectList(GetItems(), "ITCODE", "ITDESC"); ViewBag.PONO = new SelectList(GetPOMasters(), "PONO", "SUPLNO"); return(View(pODETAIL)); }
public IHttpActionResult PutPoDetail(string id, string itcode, PoDetail poDetail) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } if (id != poDetail.PoNo && itcode != poDetail.Itcode) { return(BadRequest()); } db.Entry(poDetail).State = EntityState.Modified; try { db.SaveChanges(); } catch (DbUpdateConcurrencyException) { if (!PoDetailExists(id)) { return(NotFound()); } else { throw; } } return(StatusCode(HttpStatusCode.NoContent)); }
// GET: PODETAILs/Details/5 public ActionResult Details(string id) { if (id == null) { return(new HttpStatusCodeResult(HttpStatusCode.BadRequest)); } PoDetail pODETAIL = null; using (var client = new HttpClient()) { client.BaseAddress = new Uri("http://localhost:57888/Api/"); //HTTP GET var responseTask = client.GetAsync("PoDetails/" + id); responseTask.Wait(); var result = responseTask.Result; if (result.IsSuccessStatusCode) { var readTask = result.Content.ReadAsAsync <PoDetail>(); readTask.Wait(); pODETAIL = readTask.Result; } else //web api sent error response { //log response status here.. ModelState.AddModelError(string.Empty, "Server error. Please contact administrator."); } } if (pODETAIL == null) { return(HttpNotFound()); } return(View(pODETAIL)); }
// GET: PODETAILs/Edit/5 public ActionResult Edit(string id) { if (id == null) { return(new HttpStatusCodeResult(HttpStatusCode.BadRequest)); } PoDetail pODETAIL = null; using (var client = new HttpClient()) { client.BaseAddress = new Uri("http://localhost:57888/Api/"); //HTTP GET var responseTask = client.GetAsync("PoDetails/" + id); responseTask.Wait(); var result = responseTask.Result; if (result.IsSuccessStatusCode) { var readTask = result.Content.ReadAsAsync <PoDetail>(); readTask.Wait(); pODETAIL = readTask.Result; } } if (pODETAIL == null) { return(HttpNotFound()); } ViewBag.ITCODE = new SelectList(GetItems(), "ITCODE", "ITDESC"); ViewBag.PONO = new SelectList(GetPOMasters(), "PONO", "SUPLNO"); return(View(pODETAIL)); }
public IHttpActionResult DeletePoDetail(string id, string itcode) { //PoDetail poDetail = db.PoDetails.Find(id); var poDetails = db.PoDetails.Where(a => a.PoNo == id) .Where(a => a.Itcode == itcode); PoDetail poDetail = null; foreach (var r1 in poDetails.ToList()) { poDetail = r1; //poDetail.PoNo = r1.PoNo; //poDetail.Itcode = r1.Itcode; //poDetail.Qty = r1.Qty; } if (poDetail == null) { return(NotFound()); } db.PoDetails.Remove(poDetail); db.SaveChanges(); return(Ok(poDetail)); }
public ActionResult AddPurhcaseDetail(string id) { PoDetail poDetail = new PoDetail(); poDetail.PoNo = id; ViewBag.Message = "Make new purchase."; var client = new HttpClient(); var response = client.GetAsync("http://localhost:50788/api/Items").Result; var items = response.Content.ReadAsAsync <IEnumerable <Item> >().Result; List <SelectListItem> itemsList = new List <SelectListItem>(); foreach (Item itm in items.ToList()) { itemsList.Add(new SelectListItem { Text = itm.ItDesc, Value = itm.ItCode }); } poDetail.Items = itemsList; response = client.GetAsync("http://localhost:50788/api/PoMasters").Result; var PoNos = response.Content.ReadAsAsync <IEnumerable <PoMaster> >().Result; List <SelectListItem> PoNoList = new List <SelectListItem>(); foreach (PoMaster poMaster in PoNos.ToList()) { PoNoList.Add(new SelectListItem { Text = poMaster.PoNo, Value = poMaster.PoNo }); } poDetail.PoNos = PoNoList; return(View(poDetail)); }
public IHttpActionResult PostPoDetail(PoDetail poDetail) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } db.PoDetails.Add(poDetail); try { db.SaveChanges(); } catch (DbUpdateException) { if (PoDetailExists(poDetail.PoNo)) { return(Conflict()); } else { throw; } } return(CreatedAtRoute("DefaultApi", new { id = poDetail.PoNo }, poDetail)); }
public string SavePoDetails(PoDetailsModel poDetailsModel) { string result = string.Empty; try { PoDetail poDetails = _OperationalPortalEntities.PoDetails.FirstOrDefault(x => x.PoDetailsID == poDetailsModel.PoDetailsID); if (poDetails != null) { Mapper.Map(poDetailsModel, poDetails); result = "PoDetails Updated Successfully"; } else { poDetails = new PoDetail(); Mapper.Map(poDetailsModel, poDetails); _OperationalPortalEntities.PoDetails.Add(poDetails); result = "PoDetails Saved Successfully"; } _OperationalPortalEntities.SaveChanges(); } catch (Exception ex) { result = ex.Message.ToString(); } return(result); }
public ActionResult Edit([Bind(Include = "PONO,ITCODE,QTY")] PoDetail pODETAIL) { if (ModelState.IsValid) { using (var client = new HttpClient()) { client.BaseAddress = new Uri("http://localhost:57888/Api/podetails/" + pODETAIL.PONO); client.DefaultRequestHeaders.Accept.Clear(); client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json")); HttpResponseMessage response = client.PutAsJsonAsync(pODETAIL.PONO, pODETAIL).Result; if (response.IsSuccessStatusCode) { return(RedirectToAction("Index")); } } return(RedirectToAction("Index")); } ViewBag.ITCODE = new SelectList(GetItems(), "ITCODE", "ITDESC"); ViewBag.PONO = new SelectList(GetPOMasters(), "PONO", "SUPLNO"); return(View(pODETAIL)); }
public IHttpActionResult GetPoDetail(string id, string itcode) { //PoDetail poDetail = db.PoDetails.Find(id); var poDetails = db.PoDetails.Where(a => a.PoNo == id) .Where(a => a.Itcode == itcode); PoDetail poDetail = null; foreach (var r1 in poDetails.ToList()) { poDetail = new PoDetail(); poDetail.PoNo = r1.PoNo; poDetail.Itcode = r1.Itcode; poDetail.Qty = r1.Qty; } if (poDetail == null) { return(NotFound()); } return(Ok(poDetail)); }
public string DeletePoDetails(int podId) { string result = string.Empty; try { PoDetail poDetails = _OperationalPortalEntities.PoDetails.FirstOrDefault(x => x.PoDetailsID == podId); if (poDetails != null) { _OperationalPortalEntities.PoDetails.Remove(poDetails); _OperationalPortalEntities.SaveChanges(); result = "PoDetails Removed Successfully"; } else { result = "PoDetails Does not exist"; } } catch (Exception ex) { result = ex.Message.ToString(); } return(result); }
public async Task <Po> GetPoAsync(long poId, SqlConnection conn, SqlTransaction transaction) { var po = new Po(); var commandText = string.Format($"SELECT [Id] ,[CompanyId] ,[SupplierId] ,[PoNo] ,[PoDate] ,[EmailIds] ,[Remarks] ,[IsClosed] ,[ClosingDate] ,[IsAcknowledged] ,[AcknowledgementDate] ,[PaymentTerms] ,[DeliveryTerms],[DueDate] FROM [dbo].[PoMaster] where Id = '{poId}' "); using (SqlCommand cmd = new SqlCommand(commandText, conn, transaction)) { cmd.CommandType = CommandType.Text; var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.Default); while (dataReader.Read()) { po.Id = Convert.ToInt64(dataReader["Id"]); po.CompanyId = Convert.ToInt32(dataReader["CompanyId"]); po.SupplierId = Convert.ToInt32(dataReader["SupplierId"]); po.PoNo = Convert.ToString(dataReader["PoNo"]); po.PoDate = Convert.ToDateTime(dataReader["PoDate"]); po.EmailIds = Convert.ToString(dataReader["EmailIds"]); po.Remarks = Convert.ToString(dataReader["Remarks"]); po.IsClosed = Convert.ToBoolean(dataReader["IsClosed"]); if (dataReader["ClosingDate"] != DBNull.Value) { po.ClosingDate = Convert.ToDateTime(dataReader["ClosingDate"]); } else { po.ClosingDate = null; } if (dataReader["DueDate"] != DBNull.Value) { po.DueDate = Convert.ToDateTime(dataReader["DueDate"]); } else { po.DueDate = null; } po.IsAcknowledged = Convert.ToBoolean(dataReader["IsAcknowledged"]); if (dataReader["AcknowledgementDate"] != DBNull.Value) { po.AcknowledgementDate = Convert.ToDateTime(dataReader["AcknowledgementDate"]); } else { po.AcknowledgementDate = null; } po.PaymentTerms = Convert.ToString(dataReader["PaymentTerms"]); po.DeliveryTerms = Convert.ToString(dataReader["DeliveryTerms"]); } dataReader.Close(); } List <PoDetail> poDetails = new List <PoDetail>(); commandText = string.Format($"SELECT [Id] ,[PoId] ,[PartId] ,[ReferenceNo] ,[Qty] ,[UnitPrice] ,[DueDate] ,[Note] ,[AckQty] ,[InTransitQty] ,[ReceivedQty] ,[IsClosed] ,[ClosingDate],[SrNo],[IsForceClosed] FROM [dbo].[PoDetails] where poid = '{ po.Id}'"); using (SqlCommand cmd1 = new SqlCommand(commandText, conn, transaction)) { cmd1.CommandType = CommandType.Text; var dataReader1 = cmd1.ExecuteReader(CommandBehavior.Default); while (dataReader1.Read()) { var poDetail = new PoDetail(); poDetail.Id = Convert.ToInt64(dataReader1["Id"]); poDetail.PoId = Convert.ToInt64(dataReader1["PoId"]); poDetail.PartId = Convert.ToInt64(dataReader1["PartId"]); poDetail.ReferenceNo = Convert.ToString(dataReader1["ReferenceNo"]); poDetail.Qty = Convert.ToInt32(dataReader1["Qty"]); poDetail.UnitPrice = Convert.ToDecimal(dataReader1["UnitPrice"]); poDetail.DueDate = Convert.ToDateTime(dataReader1["DueDate"]); poDetail.Note = Convert.ToString(dataReader1["Note"]); poDetail.AckQty = Convert.ToInt32(dataReader1["AckQty"]); poDetail.InTransitQty = Convert.ToInt32(dataReader1["InTransitQty"]); poDetail.ReceivedQty = Convert.ToInt32(dataReader1["ReceivedQty"]); poDetail.IsClosed = Convert.ToBoolean(dataReader1["IsClosed"]); poDetail.IsForceClosed = Convert.ToBoolean(dataReader1["IsForceClosed"]); if (dataReader1["SrNo"] != DBNull.Value) { poDetail.SrNo = Convert.ToInt32(dataReader1["SrNo"]); } else { poDetail.SrNo = 0; } if (dataReader1["ClosingDate"] != DBNull.Value) { poDetail.ClosingDate = Convert.ToDateTime(dataReader1["ClosingDate"]); } else { poDetail.ClosingDate = null; } poDetails.Add(poDetail); } dataReader1.Close(); } po.poDetails = poDetails; List <PoTerm> poTerms = new List <PoTerm>(); commandText = string.Format("SELECT [Id] ,[PoId] ,[SequenceNo] ,[Term] FROM [dbo].[PoTerms] where poid = '{0}'", po.Id); using (SqlCommand cmd1 = new SqlCommand(commandText, conn, transaction)) { cmd1.CommandType = CommandType.Text; var dataReader1 = cmd1.ExecuteReader(CommandBehavior.Default); while (dataReader1.Read()) { var poTerm = new PoTerm(); poTerm.Id = Convert.ToInt64(dataReader1["Id"]); poTerm.PoId = Convert.ToInt64(dataReader1["PoId"]); poTerm.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]); poTerm.Term = Convert.ToString(dataReader1["Term"]); poTerms.Add(poTerm); } dataReader1.Close(); } po.poTerms = poTerms; return(po); }
public async Task <Po> GetPoByAccessIdAsync(string poId) { var po = new Po(); SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString); var commandText = string.Format($"SELECT PM.[Id] ,CUS.Name AS CompanyName, PM.[CompanyId] ,SUP.[Name] AS SupplierName ,SUP.ContactPersonName,SupplierId,[PoNo] ,[PoDate] ,[EmailIds] ,[Remarks] ,[IsClosed] ,[ClosingDate] ,[IsAcknowledged] ,[AcknowledgementDate] ,[PaymentTerms] ,[DeliveryTerms],[DueDate],[AccessId] FROM [dbo].[PoMaster] PM INNER JOIN supplier SUP ON SUP.ID = PM.SupplierId INNER JOIN customer CUS ON CUS.ID = PM.CompanyId where AccessId = '{poId}' "); using (SqlCommand cmd = new SqlCommand(commandText, conn)) { cmd.CommandType = CommandType.Text; conn.Open(); var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection); while (dataReader.Read()) { po.Id = Convert.ToInt64(dataReader["Id"]); po.CompanyName = Convert.ToString(dataReader["CompanyName"]); po.SupplierName = Convert.ToString(dataReader["SupplierName"]); po.ContactPersonName = Convert.ToString(dataReader["ContactPersonName"]); po.CompanyId = Convert.ToInt32(dataReader["CompanyId"]); po.SupplierId = Convert.ToInt32(dataReader["SupplierId"]); po.PoNo = Convert.ToString(dataReader["PoNo"]); po.PoDate = Convert.ToDateTime(dataReader["PoDate"]); po.EmailIds = Convert.ToString(dataReader["EmailIds"]); po.Remarks = Convert.ToString(dataReader["Remarks"]); po.IsClosed = Convert.ToBoolean(dataReader["IsClosed"]); //po.IsForceClosed = Convert.ToBoolean(dataReader["IsForceClosed"]); if (dataReader["ClosingDate"] != DBNull.Value) { po.ClosingDate = Convert.ToDateTime(dataReader["ClosingDate"]); } else { po.ClosingDate = null; } if (dataReader["DueDate"] != DBNull.Value) { po.DueDate = Convert.ToDateTime(dataReader["DueDate"]); } else { po.DueDate = null; } po.IsAcknowledged = Convert.ToBoolean(dataReader["IsAcknowledged"]); if (dataReader["AcknowledgementDate"] != DBNull.Value) { po.AcknowledgementDate = Convert.ToDateTime(dataReader["AcknowledgementDate"]); } else { po.AcknowledgementDate = null; } po.PaymentTerms = Convert.ToString(dataReader["PaymentTerms"]); po.DeliveryTerms = Convert.ToString(dataReader["DeliveryTerms"]); po.AccessId = Convert.ToString(dataReader["AccessId"]); } dataReader.Close(); conn.Close(); } List <PoDetail> poDetails = new List <PoDetail>(); commandText = string.Format($"SELECT [Id] ,[PoId] ,[PartId] ,[ReferenceNo] ,[Qty] ,[UnitPrice] ,[DueDate] ,[Note] ,[AckQty] ,[InTransitQty] ,[ReceivedQty] ,[IsClosed] ,[ClosingDate],[SrNo] FROM [dbo].[PoDetails] where poid = '{ po.Id}'"); using (SqlCommand cmd1 = new SqlCommand(commandText, conn)) { cmd1.CommandType = CommandType.Text; conn.Open(); var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection); while (dataReader1.Read()) { var poDetail = new PoDetail(); poDetail.Id = Convert.ToInt64(dataReader1["Id"]); poDetail.PoId = Convert.ToInt64(dataReader1["PoId"]); poDetail.PartId = Convert.ToInt64(dataReader1["PartId"]); poDetail.ReferenceNo = Convert.ToString(dataReader1["ReferenceNo"]); poDetail.Qty = Convert.ToInt32(dataReader1["Qty"]); poDetail.UnitPrice = Convert.ToDecimal(dataReader1["UnitPrice"]); poDetail.DueDate = Convert.ToDateTime(dataReader1["DueDate"]); poDetail.Note = Convert.ToString(dataReader1["Note"]); poDetail.AckQty = Convert.ToInt32(dataReader1["AckQty"]); poDetail.InTransitQty = Convert.ToInt32(dataReader1["InTransitQty"]); poDetail.ReceivedQty = Convert.ToInt32(dataReader1["ReceivedQty"]); poDetail.IsClosed = Convert.ToBoolean(dataReader1["IsClosed"]); if (dataReader1["SrNo"] != DBNull.Value) { poDetail.SrNo = Convert.ToInt32(dataReader1["SrNo"]); } else { poDetail.SrNo = 0; } if (dataReader1["ClosingDate"] != DBNull.Value) { poDetail.ClosingDate = Convert.ToDateTime(dataReader1["ClosingDate"]); } else { poDetail.ClosingDate = null; } poDetails.Add(poDetail); } dataReader1.Close(); conn.Close(); } po.poDetails = poDetails; List <PoTerm> poTerms = new List <PoTerm>(); commandText = string.Format("SELECT [Id] ,[PoId] ,[SequenceNo] ,[Term] FROM [dbo].[PoTerms] where poid = '{0}'", po.Id); using (SqlCommand cmd1 = new SqlCommand(commandText, conn)) { cmd1.CommandType = CommandType.Text; conn.Open(); var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection); while (dataReader1.Read()) { var poTerm = new PoTerm(); poTerm.Id = Convert.ToInt64(dataReader1["Id"]); poTerm.PoId = Convert.ToInt64(dataReader1["PoId"]); poTerm.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]); poTerm.Term = Convert.ToString(dataReader1["Term"]); poTerms.Add(poTerm); } dataReader1.Close(); conn.Close(); } po.poTerms = poTerms; return(po); }
public async Task <IEnumerable <Po> > GetAllPosAsync(int companyId, int userId) { List <Po> pos = new List <Po>(); var userInfo = await userRepository.GeUserbyIdAsync(userId); var commandText = ""; if (userInfo.UserTypeId == 1) { commandText = string.Format($"SELECT [Id] ,[CompanyId] ,[SupplierId] ,[PoNo] ,[PoDate] ,[EmailIds] ,[Remarks] ,[IsClosed] ,[ClosingDate] ,[IsAcknowledged] ,[AcknowledgementDate] ,[PaymentTerms] ,[DeliveryTerms],[DueDate] FROM [dbo].[PoMaster] where CompanyId = '{companyId}' "); } if (userInfo.UserTypeId == 2) { return(pos); } if (userInfo.UserTypeId == 3) { string companylist = string.Join(",", userInfo.CompanyIds); commandText = string.Format($"SELECT [Id] ,[CompanyId] ,[SupplierId] ,[PoNo] ,[PoDate] ,[EmailIds] ,[Remarks] ,[IsClosed] ,[ClosingDate] ,[IsAcknowledged] ,[AcknowledgementDate] ,[PaymentTerms] ,[DeliveryTerms],[DueDate] FROM [dbo].[PoMaster] where CompanyId = '{companyId}' and [SupplierId] in ({companylist})"); } SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString); using (SqlCommand cmd = new SqlCommand(commandText, conn)) { cmd.CommandType = CommandType.Text; conn.Open(); var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection); while (dataReader.Read()) { var po = new Po(); po.Id = Convert.ToInt64(dataReader["Id"]); po.CompanyId = Convert.ToInt32(dataReader["CompanyId"]); po.SupplierId = Convert.ToInt32(dataReader["SupplierId"]); po.PoNo = Convert.ToString(dataReader["PoNo"]); po.PoDate = Convert.ToDateTime(dataReader["PoDate"]); po.EmailIds = Convert.ToString(dataReader["EmailIds"]); po.Remarks = Convert.ToString(dataReader["Remarks"]); po.IsClosed = Convert.ToBoolean(dataReader["IsClosed"]); if (dataReader["ClosingDate"] != DBNull.Value) { po.ClosingDate = Convert.ToDateTime(dataReader["ClosingDate"]); } else { po.ClosingDate = null; } if (dataReader["DueDate"] != DBNull.Value) { po.DueDate = Convert.ToDateTime(dataReader["DueDate"]); } else { po.DueDate = null; } po.IsAcknowledged = Convert.ToBoolean(dataReader["IsAcknowledged"]); if (dataReader["AcknowledgementDate"] != DBNull.Value) { po.AcknowledgementDate = Convert.ToDateTime(dataReader["AcknowledgementDate"]); } else { po.AcknowledgementDate = null; } po.PaymentTerms = Convert.ToString(dataReader["PaymentTerms"]); po.DeliveryTerms = Convert.ToString(dataReader["DeliveryTerms"]); pos.Add(po); } dataReader.Close(); conn.Close(); } foreach (Po po in pos) { List <PoDetail> poDetails = new List <PoDetail>(); commandText = string.Format($"SELECT [Id] ,[PoId] ,[PartId] ,[ReferenceNo] ,[Qty] ,[UnitPrice] ,[DueDate] ,[Note] ," + $"[AckQty] ,[InTransitQty] ,[ReceivedQty] ,[IsClosed] ,[ClosingDate] , [SrNo],[IsForceClosed] FROM [dbo].[PoDetails] where poid = '{ po.Id}'"); using (SqlCommand cmd1 = new SqlCommand(commandText, conn)) { cmd1.CommandType = CommandType.Text; conn.Open(); var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection); while (dataReader1.Read()) { var poDetail = new PoDetail(); poDetail.Id = Convert.ToInt64(dataReader1["Id"]); poDetail.PoId = Convert.ToInt64(dataReader1["PoId"]); poDetail.PartId = Convert.ToInt64(dataReader1["PartId"]); poDetail.ReferenceNo = Convert.ToString(dataReader1["ReferenceNo"]); poDetail.Qty = Convert.ToInt32(dataReader1["Qty"]); poDetail.UnitPrice = Convert.ToDecimal(dataReader1["UnitPrice"]); poDetail.DueDate = Convert.ToDateTime(dataReader1["DueDate"]); poDetail.Note = Convert.ToString(dataReader1["Note"]); poDetail.AckQty = Convert.ToInt32(dataReader1["AckQty"]); poDetail.InTransitQty = Convert.ToInt32(dataReader1["InTransitQty"]); poDetail.ReceivedQty = Convert.ToInt32(dataReader1["ReceivedQty"]); poDetail.IsClosed = Convert.ToBoolean(dataReader1["IsClosed"]); poDetail.IsForceClosed = Convert.ToBoolean(dataReader1["IsForceClosed"]); if (dataReader1["ClosingDate"] != DBNull.Value) { poDetail.ClosingDate = Convert.ToDateTime(dataReader1["ClosingDate"]); } else { poDetail.ClosingDate = null; } if (dataReader1["SrNo"] != DBNull.Value) { poDetail.SrNo = Convert.ToInt32(dataReader1["SrNo"]); } else { poDetail.SrNo = 0; } poDetails.Add(poDetail); } dataReader1.Close(); } po.poDetails = poDetails; conn.Close(); } foreach (Po po in pos) { foreach (PoDetail poDetail in po.poDetails) { commandText = string.Format("SELECT [InvoiceNo] FROM [dbo].[SupplierInvoiceMaster] where id in (select [InvoiceId] FROM [dbo].[SupplierInvoicePoDetails] where PODetailId = '{0}')", poDetail.Id); using (SqlCommand cmd1 = new SqlCommand(commandText, conn)) { cmd1.CommandType = CommandType.Text; conn.Open(); var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection); while (dataReader1.Read()) { if (poDetail.InvoiceNo == null || poDetail.InvoiceNo == string.Empty) { poDetail.InvoiceNo = Convert.ToString(dataReader1["InvoiceNo"]); } else { poDetail.InvoiceNo = poDetail.InvoiceNo + "," + Convert.ToString(dataReader1["InvoiceNo"]); } } dataReader1.Close(); } conn.Close(); } } foreach (Po po in pos) { List <PoTerm> poTerms = new List <PoTerm>(); commandText = string.Format("SELECT [Id] ,[PoId] ,[SequenceNo] ,[Term] FROM [dbo].[PoTerms] where poid = '{0}'", po.Id); using (SqlCommand cmd1 = new SqlCommand(commandText, conn)) { cmd1.CommandType = CommandType.Text; conn.Open(); var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection); while (dataReader1.Read()) { var poTerm = new PoTerm(); poTerm.Id = Convert.ToInt64(dataReader1["Id"]); poTerm.PoId = Convert.ToInt64(dataReader1["PoId"]); poTerm.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]); poTerm.Term = Convert.ToString(dataReader1["Term"]); poTerms.Add(poTerm); } } po.poTerms = poTerms; conn.Close(); } return(pos.OrderBy(x => x.DueDate)); }