public static void Delete(EpTransaction epTran, PurchaseOrderItem entity) { SqlCommand cm = new SqlCommand(); cm.CommandType = CommandType.Text; //set connection SqlConnection connection; if (epTran == null) { connection = DataManager.GetConnection(); } else { connection = epTran.GetSqlConnection(); } if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } cm.Connection = connection; //set transaction if (epTran != null) { cm.Transaction = epTran.GetSqlTransaction(); } //Check whether record exists PurchaseOrderItem checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.PurchaseItemSequenceNumber); if (checkEntity == null) { throw new Exception("Record doesn't exist."); } //Update cm.CommandText = "DELETE FROM PURDTL WHERE EBELN=@EBELN AND EBELP=@EBELP"; SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10); cm.Parameters.Add(p1); p1.Value = entity.OrderNumber; SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5); cm.Parameters.Add(p2); p2.Value = entity.PurchaseItemSequenceNumber; cm.ExecuteNonQuery(); if (epTran == null) { if (connection.State != System.Data.ConnectionState.Closed) { connection.Close(); } } }
public static PurchaseOrderItem RetrieveByKey(EpTransaction epTran, string orderNumber, string SequenceNO) { PurchaseOrderItem entity = null; string whereClause = " EBELN='" + DataManager.EscapeSQL(orderNumber) + "' "; whereClause += "AND EBELP='" + DataManager.EscapeSQL(SequenceNO) + "'"; Collection <PurchaseOrderItem> entities = Retrieve(epTran, whereClause, ""); if (entities.Count > 0) { entity = entities[0]; } return(entity); }
public static void Insert(EpTransaction epTran, PurchaseOrderItem entity) { SqlCommand cm = new SqlCommand(); cm.CommandType = CommandType.Text; //set connection SqlConnection connection; if (epTran == null) { connection = DataManager.GetConnection(); } else { connection = epTran.GetSqlConnection(); } if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } cm.Connection = connection; //set transaction if (epTran != null) { cm.Transaction = epTran.GetSqlTransaction(); } //Check whether record exists PurchaseOrderItem checkEntity = RetrieveByKey(epTran, entity.PurchaseOrderNumber, entity.PurchaseItemSequenceNumber); if (checkEntity != null) { throw new Exception("Record already exists."); } //Insert cm.CommandText = "INSERT INTO PURDTL ([EBELN],[EBELP],[PSTYP],[MATNR],[TXZ01],[BISMT],[MENGE],[PEINH],[MEINS],[NETPR],[REMARK],[WEMNG],[TEXT80],[AUFNR],[LGORT_D],[ASFNR],[STS2],[RECSTS],[ACKSTS]) VALUES(@EBELN, @EBELP, @PSTYP, @MATNR, @TXZ01,@BISMT, @MENGE, @PEINH, @MEINS , @NETPR, @REMARK, @WEMNG, @TEXT80, @AUFNR, @LGORT_D, @ASFNR, @STS2, @RECSTS, @ACKSTS)"; SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10); cm.Parameters.Add(p1); p1.Value = entity.PurchaseOrderNumber; SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5); cm.Parameters.Add(p2); p2.Value = entity.PurchaseItemSequenceNumber; SqlParameter p3 = new SqlParameter("@PSTYP", SqlDbType.Char, 1); cm.Parameters.Add(p3); p3.Value = entity.PurchaseOrderType; SqlParameter p4 = new SqlParameter("@MATNR", SqlDbType.Char, 18); cm.Parameters.Add(p4); p4.Value = entity.MaterialNumber; SqlParameter p5 = new SqlParameter("@TXZ01", SqlDbType.VarChar, 40); cm.Parameters.Add(p5); p5.Value = entity.ShortText; SqlParameter p6 = new SqlParameter("@BISMT", SqlDbType.Char, 18); cm.Parameters.Add(p6); p6.Value = entity.OldMaterialNumber; SqlParameter p7 = new SqlParameter("@MENGE", SqlDbType.Decimal, 13); cm.Parameters.Add(p7); if (entity.OrderQuantity.HasValue) { p7.Value = entity.OrderQuantity.Value; } else { p7.Value = DBNull.Value; } SqlParameter p8 = new SqlParameter("@PEINH", SqlDbType.Decimal, 13); cm.Parameters.Add(p8); if (entity.PricePerUnit.HasValue) { p8.Value = entity.PricePerUnit.Value; } else { p7.Value = DBNull.Value; } SqlParameter p9 = new SqlParameter("@MEINS", SqlDbType.Char, 3); cm.Parameters.Add(p9); p9.Value = entity.UnitofMeasure; SqlParameter p10 = new SqlParameter("@NETPR", SqlDbType.Decimal, 13); cm.Parameters.Add(p10); if (entity.NetPrice.HasValue) { p10.Value = entity.NetPrice.Value; } else { p10.Value = DBNull.Value; } SqlParameter p11 = new SqlParameter("@REMARK", SqlDbType.VarChar, 60); cm.Parameters.Add(p11); p11.Value = entity.Remarks; SqlParameter p12 = new SqlParameter("@WEMNG", SqlDbType.Decimal, 13); cm.Parameters.Add(p12); if (entity.DeliveredQuantity.HasValue) { p12.Value = entity.DeliveredQuantity.Value; } else { p12.Value = DBNull.Value; } SqlParameter p13 = new SqlParameter("@TEXT80", SqlDbType.VarChar, 80); cm.Parameters.Add(p13); p13.Value = entity.LongTextDescription; SqlParameter p14 = new SqlParameter("@AUFNR", SqlDbType.VarChar, 12); cm.Parameters.Add(p14); p14.Value = entity.OrderNumber; SqlParameter p15 = new SqlParameter("@LGORT_D", SqlDbType.Char, 4); cm.Parameters.Add(p15); p15.Value = entity.StorageLocation; SqlParameter p16 = new SqlParameter("@ASFNR", SqlDbType.Char, 3); cm.Parameters.Add(p16); p16.Value = entity.ItemStatus; SqlParameter p17 = new SqlParameter("@STS2", SqlDbType.Char, 1); cm.Parameters.Add(p17); p17.Value = entity.DeletionStatusIndicator; SqlParameter p18 = new SqlParameter("@RECSTS", SqlDbType.Char, 1); cm.Parameters.Add(p18); p18.Value = entity.RecordStatus; SqlParameter p19 = new SqlParameter("@RECSTS", SqlDbType.Char, 1); cm.Parameters.Add(p19); p19.Value = entity.RecordStatus; SqlParameter p20 = new SqlParameter("@ACKSTS", SqlDbType.Char, 1); cm.Parameters.Add(p20); p20.Value = entity.AcknowledgementStatus; cm.ExecuteNonQuery(); if (epTran == null) { if (connection.State != System.Data.ConnectionState.Closed) { connection.Close(); } } }
public static void Insert(PurchaseOrderItem entity) { Insert(null, entity); }
private static Collection <PurchaseOrderItem> Retrieve(EpTransaction epTran, string whereClause, string sortClaues) { Collection <PurchaseOrderItem> entities = new Collection <PurchaseOrderItem>(); SqlCommand cm = new SqlCommand(); cm.CommandType = CommandType.Text; //set connection SqlConnection connection; if (epTran == null) { connection = DataManager.GetConnection(); } else { connection = epTran.GetSqlConnection(); } if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } cm.Connection = connection; //set transaction if (epTran != null) { cm.Transaction = epTran.GetSqlTransaction(); } //Retrieve Data string selectCommand = "SELECT [EBELN],[EBELP],[PSTYP],[MATNR],[TXZ01],[BISMT],[MENGE],[PEINH],[MEINS],[NETPR],[REMARK],[WEMNG],[TEXT80],[AUFNR],[LGORT_D],[ASFNR],[STS2],[RECSTS],[ACKSTS] FROM purdtl"; if (!string.IsNullOrEmpty(whereClause)) { selectCommand += " where " + whereClause; } if (!string.IsNullOrEmpty(sortClaues)) { selectCommand += " order by " + sortClaues; } cm.CommandText = selectCommand; SqlDataReader rd = cm.ExecuteReader(); while (rd.Read()) { PurchaseOrderItem entity = new PurchaseOrderItem(); entity.PurchaseOrderNumber = rd["EBELN"].ToString(); entity.PurchaseItemSequenceNumber = rd["EBELP"].ToString(); entity.PurchaseOrderType = rd["PSTYP"].ToString(); entity.MaterialNumber = rd["MATNR"].ToString(); entity.ShortText = rd["TXZ01"].ToString(); entity.OldMaterialNumber = rd["BISMT"].ToString(); if (rd.IsDBNull(6)) { entity.OrderQuantity = null; } else { entity.OrderQuantity = Convert.ToDecimal(rd["MENGE"].ToString()); } if (rd.IsDBNull(7)) { entity.PricePerUnit = null; } else { entity.PricePerUnit = Convert.ToDecimal(rd["PEINH"].ToString()); } entity.UnitofMeasure = rd["MEINS"].ToString(); if (rd.IsDBNull(8)) { entity.NetPrice = null; } else { entity.NetPrice = Convert.ToDecimal(rd["NETPR"].ToString()); } entity.Remarks = rd["REMARK"].ToString(); if (rd.IsDBNull(9)) { entity.DeliveredQuantity = null; } else { entity.DeliveredQuantity = Convert.ToDecimal(rd["WEMNG"].ToString()); } entity.LongTextDescription = rd["TEXT80"].ToString(); entity.OrderNumber = rd["AUFNR"].ToString(); entity.StorageLocation = rd["LGORT_D"].ToString(); entity.ItemStatus = rd["ASFNR"].ToString(); entity.DeletionStatusIndicator = rd["STS2"].ToString(); entity.RecordStatus = rd["RECSTS"].ToString(); entity.AcknowledgementStatus = rd["ACKSTS"].ToString(); entities.Add(entity); } // close reader rd.Close(); if (epTran == null) { if (connection.State != System.Data.ConnectionState.Closed) { connection.Close(); } } return(entities); }
public static void Delete(PurchaseOrderItem entity) { Delete(null, entity); }
public static void Insert(EpTransaction epTran, PurchaseOrderItem entity) { SqlCommand cm = new SqlCommand(); cm.CommandType = CommandType.Text; //set connection SqlConnection connection; if (epTran == null) connection = DataManager.GetConnection(); else connection = epTran.GetSqlConnection(); if (connection.State != System.Data.ConnectionState.Open) connection.Open(); cm.Connection = connection; //set transaction if (epTran != null) cm.Transaction = epTran.GetSqlTransaction(); //Check whether record exists PurchaseOrderItem checkEntity = RetrieveByKey(epTran, entity.PurchaseOrderNumber, entity.PurchaseItemSequenceNumber); if (checkEntity != null) { throw new Exception("Record already exists."); } //Insert cm.CommandText = "INSERT INTO PURDTL ([EBELN],[EBELP],[PSTYP],[MATNR],[TXZ01],[BISMT],[MENGE],[PEINH],[MEINS],[NETPR],[REMARK],[WEMNG],[TEXT80],[AUFNR],[LGORT_D],[ASFNR],[STS2],[RECSTS],[ACKSTS]) VALUES(@EBELN, @EBELP, @PSTYP, @MATNR, @TXZ01,@BISMT, @MENGE, @PEINH, @MEINS , @NETPR, @REMARK, @WEMNG, @TEXT80, @AUFNR, @LGORT_D, @ASFNR, @STS2, @RECSTS, @ACKSTS)"; SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10); cm.Parameters.Add(p1); p1.Value = entity.PurchaseOrderNumber; SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5); cm.Parameters.Add(p2); p2.Value = entity.PurchaseItemSequenceNumber; SqlParameter p3 = new SqlParameter("@PSTYP", SqlDbType.Char, 1); cm.Parameters.Add(p3); p3.Value = entity.PurchaseOrderType; SqlParameter p4 = new SqlParameter("@MATNR", SqlDbType.Char, 18); cm.Parameters.Add(p4); p4.Value = entity.MaterialNumber; SqlParameter p5 = new SqlParameter("@TXZ01", SqlDbType.VarChar, 40); cm.Parameters.Add(p5); p5.Value = entity.ShortText; SqlParameter p6 = new SqlParameter("@BISMT", SqlDbType.Char, 18); cm.Parameters.Add(p6); p6.Value = entity.OldMaterialNumber; SqlParameter p7 = new SqlParameter("@MENGE", SqlDbType.Decimal,13); cm.Parameters.Add(p7); if (entity.OrderQuantity.HasValue) p7.Value = entity.OrderQuantity.Value; else p7.Value = DBNull.Value; SqlParameter p8 = new SqlParameter("@PEINH", SqlDbType.Decimal, 13); cm.Parameters.Add(p8); if (entity.PricePerUnit.HasValue) p8.Value = entity.PricePerUnit.Value; else p7.Value = DBNull.Value; SqlParameter p9 = new SqlParameter("@MEINS", SqlDbType.Char, 3); cm.Parameters.Add(p9); p9.Value = entity.UnitofMeasure; SqlParameter p10 = new SqlParameter("@NETPR", SqlDbType.Decimal, 13); cm.Parameters.Add(p10); if (entity.NetPrice.HasValue) p10.Value = entity.NetPrice.Value; else p10.Value = DBNull.Value; SqlParameter p11 = new SqlParameter("@REMARK", SqlDbType.VarChar, 60); cm.Parameters.Add(p11); p11.Value = entity.Remarks; SqlParameter p12 = new SqlParameter("@WEMNG", SqlDbType.Decimal, 13); cm.Parameters.Add(p12); if (entity.DeliveredQuantity.HasValue) p12.Value = entity.DeliveredQuantity.Value; else p12.Value = DBNull.Value; SqlParameter p13 = new SqlParameter("@TEXT80", SqlDbType.VarChar, 80); cm.Parameters.Add(p13); p13.Value = entity.LongTextDescription; SqlParameter p14 = new SqlParameter("@AUFNR", SqlDbType.VarChar, 12); cm.Parameters.Add(p14); p14.Value = entity.OrderNumber; SqlParameter p15 = new SqlParameter("@LGORT_D", SqlDbType.Char, 4); cm.Parameters.Add(p15); p15.Value = entity.StorageLocation; SqlParameter p16 = new SqlParameter("@ASFNR", SqlDbType.Char, 3); cm.Parameters.Add(p16); p16.Value = entity.ItemStatus; SqlParameter p17 = new SqlParameter("@STS2", SqlDbType.Char, 1); cm.Parameters.Add(p17); p17.Value = entity.DeletionStatusIndicator; SqlParameter p18 = new SqlParameter("@RECSTS", SqlDbType.Char, 1); cm.Parameters.Add(p18); p18.Value = entity.RecordStatus; SqlParameter p19 = new SqlParameter("@RECSTS", SqlDbType.Char, 1); cm.Parameters.Add(p19); p19.Value = entity.RecordStatus; SqlParameter p20 = new SqlParameter("@ACKSTS", SqlDbType.Char, 1); cm.Parameters.Add(p20); p20.Value = entity.AcknowledgementStatus; cm.ExecuteNonQuery(); if (epTran == null) if (connection.State != System.Data.ConnectionState.Closed) connection.Close(); }
public static void Update(PurchaseOrderItem entity) { Update(null, entity); }
private static Collection<PurchaseOrderItem> Retrieve(EpTransaction epTran, string whereClause, string sortClaues) { Collection<PurchaseOrderItem> entities = new Collection<PurchaseOrderItem>(); SqlCommand cm = new SqlCommand(); cm.CommandType = CommandType.Text; //set connection SqlConnection connection; if (epTran == null) connection = DataManager.GetConnection(); else connection = epTran.GetSqlConnection(); if (connection.State != System.Data.ConnectionState.Open) connection.Open(); cm.Connection = connection; //set transaction if (epTran != null) cm.Transaction = epTran.GetSqlTransaction(); //Retrieve Data string selectCommand = "SELECT [EBELN],[EBELP],[PSTYP],[MATNR],[TXZ01],[BISMT],[MENGE],[PEINH],[MEINS],[NETPR],[REMARK],[WEMNG],[TEXT80],[AUFNR],[LGORT_D],[ASFNR],[STS2],[RECSTS],[ACKSTS] FROM purdtl"; if (!string.IsNullOrEmpty(whereClause)) selectCommand += " where " + whereClause; if (!string.IsNullOrEmpty(sortClaues)) selectCommand += " order by " + sortClaues; cm.CommandText = selectCommand; SqlDataReader rd = cm.ExecuteReader(); while (rd.Read()) { PurchaseOrderItem entity = new PurchaseOrderItem(); entity.PurchaseOrderNumber = rd["EBELN"].ToString(); entity.PurchaseItemSequenceNumber = rd["EBELP"].ToString(); entity.PurchaseOrderType = rd["PSTYP"].ToString(); entity.MaterialNumber = rd["MATNR"].ToString(); entity.ShortText = rd["TXZ01"].ToString(); entity.OldMaterialNumber = rd["BISMT"].ToString(); if (rd.IsDBNull(6)) entity.OrderQuantity = null; else entity.OrderQuantity = Convert.ToDecimal(rd["MENGE"].ToString()); if (rd.IsDBNull(7)) entity.PricePerUnit = null; else entity.PricePerUnit = Convert.ToDecimal(rd["PEINH"].ToString()); entity.UnitofMeasure = rd["MEINS"].ToString(); if (rd.IsDBNull(8)) entity.NetPrice = null; else entity.NetPrice = Convert.ToDecimal(rd["NETPR"].ToString()); entity.Remarks = rd["REMARK"].ToString(); if (rd.IsDBNull(9)) entity.DeliveredQuantity = null; else entity.DeliveredQuantity = Convert.ToDecimal(rd["WEMNG"].ToString()); entity.LongTextDescription = rd["TEXT80"].ToString(); entity.OrderNumber = rd["AUFNR"].ToString(); entity.StorageLocation = rd["LGORT_D"].ToString(); entity.ItemStatus = rd["ASFNR"].ToString(); entity.DeletionStatusIndicator = rd["STS2"].ToString(); entity.RecordStatus = rd["RECSTS"].ToString(); entity.AcknowledgementStatus = rd["ACKSTS"].ToString(); entities.Add(entity); } // close reader rd.Close(); if (epTran == null) if (connection.State != System.Data.ConnectionState.Closed) connection.Close(); return entities; }
public static void Delete(EpTransaction epTran, PurchaseOrderItem entity) { SqlCommand cm = new SqlCommand(); cm.CommandType = CommandType.Text; //set connection SqlConnection connection; if (epTran == null) connection = DataManager.GetConnection(); else connection = epTran.GetSqlConnection(); if (connection.State != System.Data.ConnectionState.Open) connection.Open(); cm.Connection = connection; //set transaction if (epTran != null) cm.Transaction = epTran.GetSqlTransaction(); //Check whether record exists PurchaseOrderItem checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.PurchaseItemSequenceNumber); if (checkEntity == null) { throw new Exception("Record doesn't exist."); } //Update cm.CommandText = "DELETE FROM PURDTL WHERE EBELN=@EBELN AND EBELP=@EBELP"; SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10); cm.Parameters.Add(p1); p1.Value = entity.OrderNumber; SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5); cm.Parameters.Add(p2); p2.Value = entity.PurchaseItemSequenceNumber; cm.ExecuteNonQuery(); if (epTran == null) if (connection.State != System.Data.ConnectionState.Closed) connection.Close(); }