Making a second database query based on the first

So I am porting a legacy application over from coldfusion to asp.net/c#. My problem is, (and I have searched all over for it, but I may not be wording my problem properly to get good results), is that I want to take my results from a the first query I have, and perform a second query to fill in that column.

Here's how I did it in coldfusion:

<cfquery name="p" datasource="db">
   select * from table
</cfquery>

<cfloop query="p">
   <tr>
      <td>
         <a href="page.cfm?id=#p.id#">#p.title#</a>
      </td>
      <td">
         #p.category#
      </td>
  开发者_如何学编程    <td>
         #CreateObject("component","/components.dao").getuser(p.userid).user_fullname()#
      </td>
   </tr>
</cfloop>

You'll notice I call a component and method that I send the userid from the query too. This method has another query that calls a seperate database, and returns information on that user, in this case the full name rather than just the userid. This is where I am having problems in asp.net/c# for that I have created the following code:

<asp:Repeater id="program_list" runat="server">
   <ItemTemplate>
      <tr>
         <td>
            <a href="page.aspx?id=<%# Eval("id") %>"><%# Eval("title") %></a>
         </td>
         <td>
            <%# Eval("category") %>
         </td>
         <td>
            <%# Eval("userid")%> (needs full name convert)
         </td>
      </tr>
   </ItemTemplate>
</asp:Repeater>

and in the codebehind

protected void Page_Load(object sender, EventArgs e)
{
   try
   {
      DbConnection connection = new SqlConnection();
      connection.ConnectionString = "***";
      connection.Open();

      SqlCommand cmd = (SqlCommand)connection.CreateCommand();
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = "SELECT * FROM table";

      SqlDataReader reader = null;
      reader = cmd.ExecuteReader();
      program_list.DataSource = reader;
      program_list.DataBind();
      reader.Close();
      connection.Close();
   }
   catch (Exception ex)
   {
      Response.Write(ex);
   }
}

As you can see, it only does the first part, outputting the original query, but I am not sure how to interact with that query in order to call the database the second time for the users details. Any ideas would be greatly appreciated, thanks.

so you need to call an sql query each line of repeater. you should use ItemDataBoundEvent , in this event you can have processing rows user_id and can make another query.

for example

protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        DataRowView drw = (DataRowView)e.Item.DataItem
        String sql = String.Format("Select * FROM UserInfo WHERE user_id={0}", drw["user_id"]);


        Label lblUserName = (Label) e.Item.FindControl("lblUserName");
        lblUserName.Text = //WWhatever you get from query...
    }

You are looking for a ItemDataBound event. You can do a query in that event and populate other controls.