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);
}