output query results in html

i query a database for retreiving all th开发者_如何学Ce columns. I will probably have many rows. Im trying to output the query results into an html table. This is what i have so far:

    protected void Page_Load(Object sender, EventArgs E)
    {
        message.Text = "Welcome to your profile: " + CUser.LoginID;
        System.Data.SqlClient.SqlConnection con;
        con = new System.Data.SqlClient.SqlConnection();
        con.ConnectionString = "Data Source=.\\SQLEXPRESS; AttachDbFilename=C:\\Users\\jjj\\Documents\\Visual Studio 2010\\Projects\\App_Data\\data.mdf; Integrated Security = true; Connect Timeout = 30; User Instance = True";
        string id = CUser.LoginID;
        try
        {
            con.Open();

            SqlCommand cmd = new SqlCommand("SELECT * FROM tbl WHERE loginID = 'hussein' ", con);
            SqlDataReader reader =cmd.ExecuteReader();
                if (reader.HasRows) {
                    //reader.Read();
                    while (reader.Read())
                    {


                    } 
                } 
        }

        catch { }

        finally
        {
            con.Close();
        } 
}

How could i store the many rows and output them using html as soon as i open a page? Im using C#

---

I recommend starting out like this:

_{ASPX markup}

Welcome to your profile: <asp:Label runat="server" ID="lblUserName" />

<asp:SqlDataSource runat="server" ID="VehicleSaleSource"
    ConnectionString="Data Source=.\\SQLEXPRESS; AttachDbFilename=C:\\Users\\jjj\\Documents\\Visual Studio 2010\\Projects\\App_Data\\data.mdf; Integrated Security = true; Connect Timeout = 30; User Instance = True"
    SelectCommand="SELECT * FROM VehicleForSale WHERE loginID = 'hussein'"
/>

<asp:GridView runat="server" ID="VehicleSaleGrid" DataSourceId="VehicleSaleSource" AutoGenerateColumns="True" />

_{.cs Code-behind}

protected void Page_Load(Object sender, EventArgs e)
{
    lblUserName.Text = CUser.LoginID;
}

Note that there are some things here I would never do anymore in a production site: coding the connection string in the mark-up rather than web.config, auto-generate columns, for example. And I'd be much more likely to use an <asp:Repeater control rather than a gridview in the first place.

---

you could have a Literal control in the page and do something like

StringBuilder tableBuilder = new StringBuilder();
if (reader.HasRows)
{
    //reader.Read();
    while (reader.Read())
    {
        String col1 = reader["Column1"].ToString();
        string col2 = reader["Column2"].ToString();
        string col3 = reader["Column3"].ToString();
        tableBuilder.AppendFormat("<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>", col1, col2, col3);
    }
} 

string header = "<table><tr><th> COLUMN1 </th><th> COLUMN2 </th> </tr>";
string footer = "</table>";
string tableHtml = header + tableBuilder.ToString() + footer;
Literal1.Text = tableHtml;

---

Use the FieldCount property of the reader to iterate through the fields generating columns, the Read method of the reader to iterate through the rows.

StringBuilder tableOutput = new StringBuilder();
tableOutput.Append("<table>");
while (reader.Read()){
tableOutput.Append("<tr>");                    
   for(int i=0;i<reader.FieldCount;i++){
       tableOutput.AppendFormat("<td>{0}</td>", reader[i].ToString());
   }
tableOutput.Append("</tr>");
} 
tableOutput.Append("</table>");

You can then either write them out with the response stream or use an HtmlGeneric control to set the contents. One neat trick is to use a normal div with a runat="server" attribute and an id - then you can assign the content easily using the InnerHtml property like:

The div:

<div id="dataDiv" runat="server" />

The code behind:

dataDiv.InnerHtml = tableOutput.ToString();

Manually building the table is a bit cumbersome. It is usually much more expedient to use a control like a GridView or a template control like a DataList or Repeater to set up a template to display your results in your desired html format.

Using that approach you wipe out all the looping and building of strings and simply assign a DataSource property like:

MyGridView.DataSource = reader;
MyGridView.DataBind();

You can see an example of templating with the Repeater here. I myself prefer either Repeaters or DataList controls because they allow you to template your structure out in a much more flexible/html friendly approach.