public int LoadProductComponentByComponentTypeID(ProductComponent obj) { string sql = @"Select [ComponentID] ,[ComponentCode] ,[ProductCode] ,[ComponentTypeID] ,[ComponentTypeName] ,[Quantity] ,[Amount] ,[Status] ,[Created] ,[CreatedBy] ,[Modified] ,[ModifiedBy] From [ProductComponent] With(NoLock) Where ComponentTypeID=@ComponentTypeID and ProductCode=@ProductCode"; SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans); SqlParameter pComponentTypeID = new SqlParameter("ComponentTypeID", Convert2DBnull(obj.ComponentTypeID)); pComponentTypeID.SqlDbType = SqlDbType.Int; cmd.Parameters.Add(pComponentTypeID); SqlParameter pProductCode = new SqlParameter("ProductCode", Convert2DBnull(obj.ProductCode)); pProductCode.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pProductCode); int ret = 0; SqlDataReader dr = cmd.ExecuteReader(); try { while (dr.Read()) { if (!Convert.IsDBNull(dr["ComponentID"])) { obj.ComponentID = (int)dr["ComponentID"]; } if (!Convert.IsDBNull(dr["ComponentCode"])) { obj.ComponentCode = (string)dr["ComponentCode"]; } if (!Convert.IsDBNull(dr["ProductCode"])) { obj.ProductCode = (string)dr["ProductCode"]; } if (!Convert.IsDBNull(dr["ComponentTypeID"])) { obj.ComponentTypeID = (int)dr["ComponentTypeID"]; } if (!Convert.IsDBNull(dr["ComponentTypeName"])) { obj.ComponentTypeName = (string)dr["ComponentTypeName"]; } if (!Convert.IsDBNull(dr["Quantity"])) { obj.Quantity = (decimal)dr["Quantity"]; } if (!Convert.IsDBNull(dr["Amount"])) { obj.Amount = (decimal)dr["Amount"]; } if (!Convert.IsDBNull(dr["Status"])) { obj.Status = (bool)dr["Status"]; } if (!Convert.IsDBNull(dr["Created"])) { obj.Created = (DateTime)dr["Created"]; } if (!Convert.IsDBNull(dr["CreatedBy"])) { obj.CreatedBy = (string)dr["CreatedBy"]; } if (!Convert.IsDBNull(dr["Modified"])) { obj.Modified = (DateTime)dr["Modified"]; } if (!Convert.IsDBNull(dr["ModifiedBy"])) { obj.ModifiedBy = (string)dr["ModifiedBy"]; } ret += 1; } } finally { dr.Close(); } return(ret); }
public List <ProductComponent> LoadProductComponentByProductCode(string productCode) { string sql = @"Select [ComponentID] ,[ComponentCode] ,[ProductCode] ,[ComponentTypeID] ,[ComponentTypeName] ,[Quantity] ,[Amount] ,[Status] ,[Created] ,[CreatedBy] ,[Modified] ,[ModifiedBy] From [ProductComponent] With(NoLock) Where ProductCode=@ProductCode"; SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans); SqlParameter pproductCode = new SqlParameter("ProductCode", Convert2DBnull(productCode)); pproductCode.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pproductCode); List <ProductComponent> ret = new List <ProductComponent>(); SqlDataReader dr = cmd.ExecuteReader(); try { while (dr.Read()) { ProductComponent iret = new ProductComponent(); if (!Convert.IsDBNull(dr["ComponentID"])) { iret.ComponentID = (int)dr["ComponentID"]; } if (!Convert.IsDBNull(dr["ComponentCode"])) { iret.ComponentCode = (string)dr["ComponentCode"]; } if (!Convert.IsDBNull(dr["ProductCode"])) { iret.ProductCode = (string)dr["ProductCode"]; } if (!Convert.IsDBNull(dr["ComponentTypeID"])) { iret.ComponentTypeID = (int)dr["ComponentTypeID"]; } if (!Convert.IsDBNull(dr["ComponentTypeName"])) { iret.ComponentTypeName = (string)dr["ComponentTypeName"]; } if (!Convert.IsDBNull(dr["Quantity"])) { iret.Quantity = (decimal)dr["Quantity"]; } if (!Convert.IsDBNull(dr["Amount"])) { iret.Amount = (decimal)dr["Amount"]; } if (!Convert.IsDBNull(dr["Status"])) { iret.Status = (bool)dr["Status"]; } if (!Convert.IsDBNull(dr["Created"])) { iret.Created = (DateTime)dr["Created"]; } if (!Convert.IsDBNull(dr["CreatedBy"])) { iret.CreatedBy = (string)dr["CreatedBy"]; } if (!Convert.IsDBNull(dr["Modified"])) { iret.Modified = (DateTime)dr["Modified"]; } if (!Convert.IsDBNull(dr["ModifiedBy"])) { iret.ModifiedBy = (string)dr["ModifiedBy"]; } ret.Add(iret); } } finally { dr.Close(); } return(ret); }
public int InsertProductComponent(ProductComponent obj) { string sql = @"Insert Into [ProductComponent]( [ComponentCode] ,[ProductCode] ,[ComponentTypeID] ,[ComponentTypeName] ,[Quantity] ,[Amount] ,[Status] ,[Created] ,[CreatedBy] ,[Modified] ,[ModifiedBy] )Values ( @ComponentCode ,@ProductCode ,@ComponentTypeID ,@ComponentTypeName ,@Quantity ,@Amount ,@Status ,@Created ,@CreatedBy ,@Modified ,@ModifiedBy )"; SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans); SqlParameter pComponentCode = new SqlParameter("ComponentCode", Convert2DBnull(obj.ComponentCode)); pComponentCode.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pComponentCode); SqlParameter pProductCode = new SqlParameter("ProductCode", Convert2DBnull(obj.ProductCode)); pProductCode.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pProductCode); SqlParameter pComponentTypeID = new SqlParameter("ComponentTypeID", Convert2DBnull(obj.ComponentTypeID)); pComponentTypeID.SqlDbType = SqlDbType.Int; cmd.Parameters.Add(pComponentTypeID); SqlParameter pComponentTypeName = new SqlParameter("ComponentTypeName", Convert2DBnull(obj.ComponentTypeName)); pComponentTypeName.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pComponentTypeName); SqlParameter pQuantity = new SqlParameter("Quantity", Convert2DBnull(obj.Quantity)); pQuantity.SqlDbType = SqlDbType.Decimal; cmd.Parameters.Add(pQuantity); SqlParameter pAmount = new SqlParameter("Amount", Convert2DBnull(obj.Amount)); pAmount.SqlDbType = SqlDbType.Decimal; cmd.Parameters.Add(pAmount); SqlParameter pStatus = new SqlParameter("Status", Convert2DBnull(obj.Status)); pStatus.SqlDbType = SqlDbType.Bit; cmd.Parameters.Add(pStatus); SqlParameter pCreated = new SqlParameter("Created", Convert2DBnull(obj.Created)); pCreated.SqlDbType = SqlDbType.DateTime; cmd.Parameters.Add(pCreated); SqlParameter pCreatedBy = new SqlParameter("CreatedBy", Convert2DBnull(obj.CreatedBy)); pCreatedBy.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pCreatedBy); SqlParameter pModified = new SqlParameter("Modified", Convert2DBnull(obj.Modified)); pModified.SqlDbType = SqlDbType.DateTime; cmd.Parameters.Add(pModified); SqlParameter pModifiedBy = new SqlParameter("ModifiedBy", Convert2DBnull(obj.ModifiedBy)); pModifiedBy.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pModifiedBy); return(cmd.ExecuteNonQuery()); }
public List <ProductComponent> LoadProductComponentByOrderID(Guid OrderID) { string sql = @"Select [ProductComponent].[ComponentID] ,[ProductComponent].[ComponentCode] ,[ProductComponent].[ProductCode] ,[ProductComponent].[ComponentTypeID] ,[ProductComponent].[ComponentTypeName] ,[ProductComponent].[Quantity] ,[ProductComponent].[Amount] ,[ProductComponent].[Status] ,[ProductComponent].[Created] ,[ProductComponent].[CreatedBy] ,[ProductComponent].[Modified] ,[ProductComponent].[ModifiedBy] From [ProductComponent] With(NoLock) LEFT JOIN [ComponentType] ON [ProductComponent].ComponentTypeID=[ComponentType].ComponentTypeID LEFT JOIN [dbo].[BE_OrderProduct] ON [BE_OrderProduct].[ProductCode]=[ProductComponent].[ProductCode] WHERE [ComponentType].ParentID=0 AND [BE_OrderProduct].OrderID=@OrderID" ; SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans); SqlParameter pOrderID = new SqlParameter("OrderID", OrderID); pOrderID.SqlDbType = SqlDbType.UniqueIdentifier; cmd.Parameters.Add(pOrderID); List <ProductComponent> ret = new List <ProductComponent>(); SqlDataReader dr = cmd.ExecuteReader(); try { while (dr.Read()) { ProductComponent iret = new ProductComponent(); if (!Convert.IsDBNull(dr["ComponentID"])) { iret.ComponentID = (int)dr["ComponentID"]; } if (!Convert.IsDBNull(dr["ComponentCode"])) { iret.ComponentCode = (string)dr["ComponentCode"]; } if (!Convert.IsDBNull(dr["ProductCode"])) { iret.ProductCode = (string)dr["ProductCode"]; } if (!Convert.IsDBNull(dr["ComponentTypeID"])) { iret.ComponentTypeID = (int)dr["ComponentTypeID"]; } if (!Convert.IsDBNull(dr["ComponentTypeName"])) { iret.ComponentTypeName = (string)dr["ComponentTypeName"]; } if (!Convert.IsDBNull(dr["Quantity"])) { iret.Quantity = (decimal)dr["Quantity"]; } if (!Convert.IsDBNull(dr["Amount"])) { iret.Amount = (decimal)dr["Amount"]; } if (!Convert.IsDBNull(dr["Status"])) { iret.Status = (bool)dr["Status"]; } if (!Convert.IsDBNull(dr["Created"])) { iret.Created = (DateTime)dr["Created"]; } if (!Convert.IsDBNull(dr["CreatedBy"])) { iret.CreatedBy = (string)dr["CreatedBy"]; } if (!Convert.IsDBNull(dr["Modified"])) { iret.Modified = (DateTime)dr["Modified"]; } if (!Convert.IsDBNull(dr["ModifiedBy"])) { iret.ModifiedBy = (string)dr["ModifiedBy"]; } ret.Add(iret); } } finally { dr.Close(); } return(ret); }
public int UpdateProductComponentByComponentID(ProductComponent obj) { string sql = @"Update [ProductComponent] Set [ComponentCode]=@ComponentCode ,[ProductCode]=@ProductCode ,[ComponentTypeID]=@ComponentTypeID ,[ComponentTypeName]=@ComponentTypeName ,[Quantity]=@Quantity ,[Amount]=@Amount ,[Status]=@Status ,[Created]=@Created ,[CreatedBy]=@CreatedBy ,[Modified]=@Modified ,[ModifiedBy]=@ModifiedBy Where ComponentID=@ComponentID"; SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans); SqlParameter pComponentID = new SqlParameter("ComponentID", Convert2DBnull(obj.ComponentID)); pComponentID.SqlDbType = SqlDbType.Int; cmd.Parameters.Add(pComponentID); SqlParameter pComponentCode = new SqlParameter("ComponentCode", Convert2DBnull(obj.ComponentCode)); pComponentCode.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pComponentCode); SqlParameter pProductCode = new SqlParameter("ProductCode", Convert2DBnull(obj.ProductCode)); pProductCode.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pProductCode); SqlParameter pComponentTypeID = new SqlParameter("ComponentTypeID", Convert2DBnull(obj.ComponentTypeID)); pComponentTypeID.SqlDbType = SqlDbType.Int; cmd.Parameters.Add(pComponentTypeID); SqlParameter pComponentTypeName = new SqlParameter("ComponentTypeName", Convert2DBnull(obj.ComponentTypeName)); pComponentTypeName.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pComponentTypeName); SqlParameter pQuantity = new SqlParameter("Quantity", Convert2DBnull(obj.Quantity)); pQuantity.SqlDbType = SqlDbType.Decimal; cmd.Parameters.Add(pQuantity); SqlParameter pAmount = new SqlParameter("Amount", Convert2DBnull(obj.Amount)); pAmount.SqlDbType = SqlDbType.Decimal; cmd.Parameters.Add(pAmount); SqlParameter pStatus = new SqlParameter("Status", Convert2DBnull(obj.Status)); pStatus.SqlDbType = SqlDbType.Bit; cmd.Parameters.Add(pStatus); SqlParameter pCreated = new SqlParameter("Created", Convert2DBnull(obj.Created)); pCreated.SqlDbType = SqlDbType.DateTime; cmd.Parameters.Add(pCreated); SqlParameter pCreatedBy = new SqlParameter("CreatedBy", Convert2DBnull(obj.CreatedBy)); pCreatedBy.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pCreatedBy); SqlParameter pModified = new SqlParameter("Modified", Convert2DBnull(obj.Modified)); pModified.SqlDbType = SqlDbType.DateTime; cmd.Parameters.Add(pModified); SqlParameter pModifiedBy = new SqlParameter("ModifiedBy", Convert2DBnull(obj.ModifiedBy)); pModifiedBy.SqlDbType = SqlDbType.NVarChar; cmd.Parameters.Add(pModifiedBy); return(cmd.ExecuteNonQuery()); }