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);
}
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(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 void Delete(PurchaseOrderItem entity)
{
Delete(null, entity);
}
public static void Update(PurchaseOrderItem entity)
{
Update(null, entity);
}
