public static List <MIS_Detail_Model> RetrieveData(SqlConnection connection, int idMISHeader, int idItem) { StringBuilder sQuery = new StringBuilder(); sQuery.Append(@" SELECT idMISDetail ,idMISHeader ,idItem ,Quantity ,Cost FROM a_MIS_Detail WHERE idMISDetail <> 0 "); if (idMISHeader != 0) { sQuery.Append("AND idMISHeader = @idMISHeader"); } if (idItem != 0) { sQuery.Append("AND idItem = @idItem"); } var lmodel = new List <MIS_Detail_Model>(); connection.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = connection; cmd.CommandText = sQuery.ToString(); cmd.CommandType = CommandType.Text; if (idItem != 0) { SqlParameter parm2 = new SqlParameter { ParameterName = "@idItem", SqlDbType = SqlDbType.Int, Value = idItem }; cmd.Parameters.Add(parm2); } if (idMISHeader != 0) { SqlParameter parm2 = new SqlParameter { ParameterName = "@idMISHeader", SqlDbType = SqlDbType.Int, Value = idMISHeader }; cmd.Parameters.Add(parm2); } var oreader = cmd.ExecuteReader(); while (oreader.Read()) { MIS_Detail_Model oModel = new MIS_Detail_Model { idMISDetail = (int)oreader["idMISDetail"], idMISHeader = (int)oreader["idMISHeader"], idItem = (int)oreader["idItem"], Quantity = (int)oreader["Quantity"], Cost = (decimal)oreader["Cost"] }; lmodel.Add(oModel); } oreader.Close(); cmd.Dispose(); } connection.Close(); return(lmodel); }
public static int Save(SqlConnection connection, MIS_Detail_Model model) { int returnValue = 0; StringBuilder sQuery = new StringBuilder(); sQuery.Append(@"INSERT INTO a_MIS_Detail (idMISHeader ,idItem ,Quantity ,Cost ) VALUES (@idMISHeader ,@idItem ,@Quantity ,@Cost ) SELECT SCOPE_IDENTITY() as 'ID' "); var GUID = SQL_Transact.GenerateGUID(); SQL_Transact.BeginTransaction(connection, GUID); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = connection; cmd.CommandText = sQuery.ToString(); cmd.CommandType = CommandType.Text; SqlParameter parm2 = new SqlParameter { ParameterName = "@idMISHeader", SqlDbType = SqlDbType.Int, Value = model.idMISHeader }; cmd.Parameters.Add(parm2); SqlParameter parm3 = new SqlParameter { ParameterName = "@idItem", SqlDbType = SqlDbType.Int, Value = model.idItem }; cmd.Parameters.Add(parm3); SqlParameter parm4 = new SqlParameter { ParameterName = "@Quantity", SqlDbType = SqlDbType.Int, Value = model.Quantity }; cmd.Parameters.Add(parm4); SqlParameter parm5 = new SqlParameter { ParameterName = "@Cost", SqlDbType = SqlDbType.Decimal, Value = model.Cost }; cmd.Parameters.Add(parm5); //if (cmd.ExecuteNonQuery() >= 1) // returnValue = true; var oreader = cmd.ExecuteReader(); try { while (oreader.Read()) { returnValue = Convert.ToInt32(oreader["ID"].ToString()); } oreader.Close(); cmd.Dispose(); cmd.Parameters.Clear(); SQL_Transact.CommitTransaction(connection, GUID); } catch { returnValue = 0; oreader.Close(); cmd.Dispose(); cmd.Parameters.Clear(); SQL_Transact.RollbackTransaction(connection, GUID); } } return(returnValue); }
protected void btnSave_Click(object sender, EventArgs e) { MIS_Header_Model mIS_Header_Model = new MIS_Header_Model { RequestNo = txtRequestNo.Text, ReferenceNo = txtRefNo.Text, POCMNumber = txtPOCMNum.Text, PreparedBy = txtPreparedBy.Text, Remarks = txtRemarks.Text, RequestDate = Convert.ToDateTime(txtDate.Text, System.Globalization.CultureInfo.InvariantCulture), Requestor = txtRequestor.Text }; int idmisheader = MIS_Header.Save(oCon, mIS_Header_Model); DataTable dtTempData = (DataTable)Session["TempData"]; for (int x = 0; x <= dtTempData.Rows.Count - 1; x++) { int idItem = Convert.ToInt32(dtTempData.Rows[x][0].ToString()); string item_number = dtTempData.Rows[x][1].ToString(); int qty = Convert.ToInt32(dtTempData.Rows[x][2].ToString()); decimal cost = Convert.ToDecimal(dtTempData.Rows[x][3].ToString()); MIS_Detail_Model mIS_Detail_Model = new MIS_Detail_Model { idMISHeader = idmisheader, Cost = cost, Quantity = qty, idItem = idItem }; int idmisdetail = MIS_Detail.Save(oCon, mIS_Detail_Model); string dtname = "Ser" + idItem.ToString() + "-" + item_number; var dt = (DataTable)Session[dtname]; for (int y = 0; y <= dt.Rows.Count - 1; y++) { Item_Serial.UpdateStockStatus(oCon, dt.Rows[y][1].ToString(), "N"); MIS_Serial_Model mIS_Serial_Model = new MIS_Serial_Model { idMISDetail = idmisdetail, idSerial = Convert.ToInt32(dt.Rows[y][0].ToString()) }; MIS_Serial.Save(oCon, mIS_Serial_Model); Trans_History_Model trans_History_Model = new Trans_History_Model { Trans_Code = "MIS", Item_Number = item_number, Site = "WH-JMS", UM = "UT", Doc_No = txtRequestNo.Text, Serial_No = dt.Rows[y][1].ToString(), Reason_Code = "", Trans_Date = DateTime.Now, Order_No = "", Invoice_No = "", Reference_No = txtRefNo.Text, Trans_Qty = -1, Trans_Amt = cost, Remarks = txtRemarks.Text, user_domain = Session["User_Domain"].ToString() }; Trans_History.Save(oCon, trans_History_Model); } } Item_Master.InventoryCheckForError(oCon); clearAll(); dvView.Visible = true; dvCreate.Visible = false; GetView(); HttpContext.Current.Response.Write("<script>alert(" + ControlChars.Quote + "MIS Saved!" + ControlChars.Quote + ");</script>"); }