two tables to populate grdiview

[coder91]

I have a gridview that is being populated from a dataset which is a table returned from a stored procedure. However now the stored procedure has changed and it returns 2 tables, the first returns the column headers as well as labels and other info, the second is the table with the actual data to be displayed in the gridview. I was just wanting to know how I can use the first table to get the gridview column headers and then use the second to populate the gridview. At the minute the code I have to populate the gridview is

 protected void getGrid(object sender, EventArgs e)
        {
            SqlCommand sql = new SqlCommand("store_proc");
            sql.Parameters.AddWithValue("@name", name);
            sql.Parameters.Add(new SqlParameter()
            {
                ParameterName = "RETURN_VALUE",
                Direction = ParameterDirection.ReturnValue
            });

            var dataset = conn.SPExecuteDataSet(sql);

            int returnNumber = (int)sql.Parameters["RETURN_VALUE"].Value;

            if (dataset.Tables[0].Rows.Count > 0)
            {
                datasource = dataset.Tables[0];


                if (returnNumber == 0)
                {
                    GridView1.DataSource = datasource;
                    GridView1.DataBind();
                }
                else
                {
                    Label1.Text = "No access";
                }
            }
            else
            {
                GridView1.DataSource = dataset;
                GridView1.DataBind();
            }           
        }

[djjeavons]

Hi

Could the second stored procedure that returns the data not return the data in the same format (i.e. same column headers) so that you can simply bind the DataSet to the DataGridView?

Otherwise, another option would be to loop through the returned data in the first data table and create the columns for the DataGridView and then loop through the second table and add each row as a row to the DataGridView.

[coder91]

The way they want it implemented is the second way you suggested, any chance you could give me an example of how to loop through the datatables to create the columns and then the rows. Thank you

[coder91]

The first datatable that is being returned has 5 columns, one of which is called headers, it is then in the first row that the column headers for the gridview is stored, so i've had to split them like.

var columnArray = ds["headers"].ToString().Split(',').ToArray();

Just not sure how to use columnArray to populate gridview headers.

[djjeavons]

Actually, probably a more simplistic option would be to create a new DataTable that combines the headers you have in your columnArray and the data from your second table. Then you can simply bind this new DataTable to the DataGridView:

string[] columnArray = { "Field1", "Field2" };

//Create a new data table
DataTable dt = new DataTable();

foreach (string column in columnArray)
{
    dt.Columns.Add(new DataColumn(column, typeof(string)));                
}

//Loop through your second data table and add new rows to the dt DataTable
foreach (DataRow row in YourSecondDataTable.Rows)
{
    DataRow newRow = dt.NewRow();
    newRow["Field1"] = row["Field1"].ToString();
    newRow["Field2"] = row["Field2"].ToString();
    dt.Rows.Add(newRow);
}

dataGridView1.DataSource = dt;

The above is not tested but hopefully gives you an idea.

[tinstaafl]

probably an even more simple solution would be to replace the column headers in the second table with the values from the first. Something like this should work:

var columnArray = dataset.Tables["headers"].Rows[0].ItemArray[0].ToString().Split(',');
for(int i = 0; i < columnArray.Length; i++)
{
    dataset.Tables[1].Columns[i].ColumnName = columnArray[i];
}

This assumes that the number of columns is already verified to be the same as the values in the first table.

Now the second table can be bound to the datagridview.