How do I add data to a table in my code behind and display in html?

Question

I would like to pull data from my server and add to a table object. Loop thru the data afterwhich I would like to display the results on the aspx page.

DataTable dTable = new DataTable();
dTable.Columns.Add("idNum", typeof(Int64));
dTable.Columns.Add("Name", typeof(String));
dTable.Columns.Add("Age", typeof(Int64));
//Connection/Command/Statement
DataReader dr = command.ExecuteReader();
while (dr.Read()) { /*Add data to rows*/ }

How do I add the data to the rows? What is the best way to display on aspx?

Answer 1

You can call the Rows.Add method, like this:

dTable.Rows.Add(id, name, age);

Answer 2

Use an adapter to get the data and use it to fill a DataTable then you can read the DataTable row by row and copy into an object that you can use outside of your data accessing code.

Sample to follow momentarily.....

EDIT: Sample added...

public IList<your object> PopulateYourObject()
{
    DataSet ds = new DataSet();
    SqlConnection con = new SqlConnection(your connection string);
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandText = command;
    if (parameters != null)
    {
        // add any parameters here
    }
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter adp = null;

    con.Open();
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    adp.Fill(ds);

    DataTable dt = ds.Tables[0]; // Now you have a DataTable

    return PopulateYourObjectListFromData(dt);
}

To Add it to the aspx I would loop throught the rows in the dt

    private IList<your object> PopulateYourObjectListFromData(DataTable dt)
    {
        IList<your object> newsReachArticleList = new List<your object>();
        try
        {
            foreach (DataRow row in dt.Rows)
            {
                <your object> dto = new <your object>();
                <your object>.PropertyToFill = Convert.ToString(row["column name"]);
            }
        }
    }

Then you can apply the IList to the Datasource of the DataGrid in your aspx.

public void LoadData()
{
    YourDataGrid.Datasource = PopulateYourObject();
    YourDataGrid.DataBind();
}

Answer 3

Check out step 2 here: http://programming.top54u.com/post/ASP-Net-Bind-GridView-to-DataTable.aspx

Then set the datasource of your grid to your DataTable.

Answer 4

write this code in the while loop

DataRow drow = dTable.NewRow();

drow["id"]= dr["id"]

drow["name"]=dr["name"]

drow["age"]=dr["age"]

dTable.Rows.Add(drow);

now when your dTable source is ready , than you can bind this datasource to a DataGrid to display the data.

it is not tested code.

Answer 5

Try this code, I tested on my local:

protected void Page_Load(object sender, EventArgs e)
{
    if (Page.IsPostBack)
        return;

    BindDG();
}

private void BindDG()
{

    SqlConnection connection = new SqlConnection("Data Source=servername;Initial Catalog=dbname;Integrated Security=True");
    using (connection)
    {
        SqlCommand command = new SqlCommand("select * From staff;",connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Age", typeof(int));

            while (reader.Read())
            {
                int id = (int)reader["id"];
                string name = reader["name"].ToString();
                int age = (int)reader["age"];

                dt.Rows.Add(id, name, age);
            }

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
        else
        {
            Reponse.Write("No records found.");
        }
        reader.Close();
    }
}