The Page Page_Load Event Vs The Repeater ItemCommand

Question

I'm running some code in Page_Load then I store the results from this code in some local variables.

Now I use this local variables to assign them to the controls inside my repeater item template

The Problem Is >> the page doesn't displays the item template but there's no data bound to it..I think the repeater can't load the data assigned to it in the Page_Load and it get's initialized -life cycle related issues-

Any idea what the problem is exactly ? and how to solve this ?

EDIT

Some example code :

public partial class MyPage: System.Web.UI.Page
    {

        int UserId = 0;

        protected void Page_Load(object sender, EventArgs e)
        {

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString);

            SqlCommand comm = new SqlCommand("SELECT * From Users, conn);
            SqlDataReader reader;

            conn.Open();
            reader = comm.ExecuteReader();

//I'm not sure if I need those two lines:
            AllBrandsRptr.DataSource = reader;
            AllBrandsRptr.DataBind();

            while (reader.Read())
            {

                    UserId = (int)reader["UserId"];
            }
                conn.Close();

            }
        }


    protected void AllBrandsRptr_ItemCommand(object source, RepeaterCommandEventArgs e)
    {

        Label LabelTest = (Label)e.Item.FindControl("MyTestLabel");
        LabelTest.Text = UserId.ToString();

    }

EDIT 2

My Sql SELECT Statement

string command1 = "SELECT Brands.BrandId, Brands.BrandName, Brands.BrandLogo, Brands.BrandWebsite, Brands.IsBrandVisible, Cuisines.CuisineType, VenueTypes.VenueTypeName FROM         Brands FULL OUTER JOIN BrandCuisines ON BrandCuisines.BrandId = Brands.BrandId FULL OUTER JOIN Cuisines ON Cuisines.CuisineId = BrandCuisines.CuisineId FULL OUTER JOIN BrandVenueTypes ON BrandVenueTypes.BrandId = Brands.BrandId FULL OUTER JOIN VenueTypes ON VenueTypes.VenueTypeId = BrandVenueTypes.VenueTypeId";

My Filtration Code

            conn.Open();
            reader = comm.ExecuteReader();
            AllBrandsRptr.DataSource = reader;
            AllBrandsRptr.DataBind();

            while (reader.Read())
            {

                  if (((int)reader["BrandId"]) == BrandId) //this line to pass collecting some info, if I already iterated through the same Id 
                  {
                    BrandId = (int)reader["BrandId"];
                    BrandName = (string)reader["BrandName"];
                    BrandLogo = (string)reader["BrandLogo"];
                    BrandWebsite = (string)reader["BrandWebsite"];
                    IsVisible = (bool)reader["IsBrandVisible"];
                  }

                if (reader["CuisineType"] != DBNull.Value)
                {
                    Cuisines += (string)reader["CuisineType"];
                }

                if (reader["VenueTypeName"] != DBNull.Value)
                {
                    VenueTypes += ", " + (string)reader["VenueTypeName"];
                }

                conn.Close();

My Initial Problem

How to use in my application a SELECT statement that must return more than one record to show multiple values for a certain field (m:m relation)

Answer 1

You shouldn't manually iterate over the DataReader at all. It is a forward-only tool. Only the Repeater or your while loop may iterate through the results. I believe your immediate problem is that your while loop is exhausting the DataReader before the Repeater renders.

When you call DataBind(), you're instructing the Repeater to render its template for every item in the collection you assigned as its DataSource. So, any filtration would need to happen before. Ideally, you should probably add that filtration logic as a where clause to your SQL statement.

Can you be more specific about the real problem you're trying to solve? It's hard to give you accurate advice otherwise.

---

Update:

Keep in mind that while(reader.Read()) does not work like an event handler or otherwise similar to how it might semantically sound. In other words, you aren't telling the program to do something when the DataReader is read, you're telling it to start reading through the data immediately (unrelated to the Repeater).

Here's what I suggest you try:

string sql = "Your current SQL here";
string connectionString = "Your connection string here";

// The using block ensures that the connection will be closed and freed up,
//  even if an unhandled exception occurs inside the block.
using (SqlConnection conn = new SqlConnection(connectionString))
{
  SqlCommand cmd = new SqlCommand(sql, conn);

  SqlDataAdapter da = new SqlDataAdapter(cmd);

  DataTable dt = new DataTable();

  da.Fill(dt);

  AllBrandsRptr.DataSource = dt;
  AllBrandsRptr.DataBind();

  var cuisineTypes = from row in dt.AsEnumerable()
                     select row["CuisineType"];

  string cuisines = string.Join(", ", cuisineTypes.Distinct());

  var venueTypes = from row in dt.AsEnumerable()
                   select row["VenueTypeName"];

  string venues = string.Join(", ", venueTypes.Distinct());
}

By using a DataTable instead of DataReader, you're able to iterate through the data as many times as necessary. The DataReader is more performant, if a single, forward-only read through the data is all you need, but the DataTable is much more powerful and is helpful in these situations beyond the DataReader's capabilities.

---

It's worth mentioning that if you care about this project in the long-term and want to make it maintainable, you should consider eventually moving some of this data access code to a separate layer. You should strive to never have SQL statements or direct data access code in your .aspx.cs files.

Then, your code in Page_Load could be strongly typed and easier to work with:

List<Brand> brands = Brand.GetAllBrands();

AllBrandsRptr.DataSource = brands;
AllBrandsRptr.DataBind();

var cuisineTypes = from brand in brands
                   select brand.CuisineType;

string cuisines = string.join(", ", cuisineTypes.Distinct());

var venueTypes = from brand in brands
                 select brand.VenueType;

string venues = string.join(", ", venueTypes.Distinct());