public static List <CompanyProducts> GetProductsByCompanyID(int inpCompanyID)
        {
            SqlConnection conn    = ConnectionHelper.GetSQLConnection();
            SqlCommand    command = new SqlCommand("Select Ex.Name, CPID.ExpID, CPID.EquipID, CP.ProductName " +
                                                   "From Exporters as Ex Left Outer Join CompanyProductID as CPID " +
                                                   "On (Ex.ExporterID=CPID.ExpID) Left Outer Join CompanyProducts as CP " +
                                                   "On (CPID.EquipID=CP.ParentID) " +
                                                   "Where ExpID=@id Group BY Ex.Name, CPID.ExpID, CPID.EquipID, CP.ProductName Order By CPID.EquipID", conn);

            command.Parameters.AddWithValue("@id", inpCompanyID);

            List <CompanyProducts> retVal = new List <CompanyProducts>();

            command.Connection.Open();

            SqlDataReader rs = command.ExecuteReader();

            while (rs.Read())
            {
                CompanyProducts CP = new CompanyProducts();

                CP.parentID    = Convert.ToInt32(rs["EquipID"]);
                CP.productName = Convert.ToString(rs["ProductName"]);
                CP.companyName = Convert.ToString(rs["Name"]);

                retVal.Add(CP);
            }
            rs.Close();
            command.Connection.Close();

            return(retVal);
        }
        public static List <CompanyProducts> GetAll()
        {
            SqlConnection conn    = ConnectionHelper.GetSQLConnection();
            SqlCommand    command = new SqlCommand("SELECT CompanyProducts.ParentID, CompanyProductCategories.ChildID, CompanyProducts.ProductName, CompanyProductCategories.CategoryName " +
                                                   "FROM CompanyProducts INNER JOIN CompanyProductCategories ON CompanyProducts.ParentID = CompanyProductCategories.ParentID", conn);
            //return baseCollectionCreator(command);
            List <CompanyProducts> retVal = new List <CompanyProducts>();

            command.Connection.Open();

            SqlDataReader rs = command.ExecuteReader();

            while (rs.Read())
            {
                CompanyProducts CP = new CompanyProducts();

                CP.parentID            = Convert.ToInt32(rs["ParentID"]);
                CP.childID             = Convert.ToInt32(rs["ChildID"]);
                CP.productName         = Convert.ToString(rs["ProductName"]);
                CP.categoryProductName = Convert.ToString(rs["CategoryName"]);

                retVal.Add(CP);
            }
            rs.Close();
            command.Connection.Close();

            return(retVal);
        }
        private static List <CompanyProducts> baseCollectionCreator(SqlCommand command)
        {
            List <CompanyProducts> retVal = new List <CompanyProducts>();

            command.Connection.Open();

            SqlDataReader rs = command.ExecuteReader();

            while (rs.Read())
            {
                CompanyProducts CP = new CompanyProducts();

                CP.ExpID              = Convert.ToInt32(rs["ExpID"]);
                CP.CompanyName        = Convert.ToString(rs["Name"]);
                CP.ProductDescription = Convert.ToString(rs["ProductDescription"]);
                CP.Location           = Convert.ToString(rs["Location"]);
                CP.ContactPersonName  = Convert.ToString(rs["ContactPerson"]);
                CP.Phone              = Convert.ToString(rs["Phone"]);
                CP.Fax                 = Convert.ToString(rs["Fax"]);
                CP.CellPhone           = Convert.ToString(rs["CellPhone"]);
                CP.Email               = Convert.ToString(rs["Email"]);
                CP.ParentID            = Convert.ToInt32(rs["EquipID"]);
                CP.productName         = Convert.ToString(rs["ProductName"]);
                CP.categoryProductName = Convert.ToString(rs["CategoryName"]);

                retVal.Add(CP);
            }
            rs.Close();
            command.Connection.Close();

            return(retVal);
        }