Dynamically created gridview or table from 2 SQL Server tables

Question

I have today a table in footer that have header static created and then value from a SQL Server database.

Now I want the header to be created dynamically from one table in SQL Server, and then get the values from another table.

I don't now if I should do it like table or gridview or maybe something else.

The table looks like this:

This is how I have tried to get the header from SQL Server:

aspx.cs:

private void FillQualCheckBoxList()
{
    string conn_str = ConfigurationManager.ConnectionStrings["CashGameConnectionString"].ConnectionString;

    SqlConnection conn = new SqlConnection(conn_str);

    string query = "SELECT Games FROM tblStoPokerGames";

    SqlCommand comm = new SqlCommand(query, conn);
    conn.Open();

    SqlDataAdapter adp = new SqlDataAdapter(comm);
    DataTable dt = new DataTable();
    adp.Fill(dt);

    GV1.DataSource = dt;
    GV1.DataBind();
}

Webform (.aspx):

<asp:GridView runat="server" ID="GV1" 
     AutoGenerateColumns="true" RowStyle-Wrap="false" 
     DataFormatString="{0:yyyy-MM-dd HH:mm}" 
     BackColor="White" BorderColor="#999999" 
     BorderStyle="Solid" BorderWidth="1px"  
     ForeColor="Black" GridLines="vertical" 
     RowStyle-HorizontalAlign="Center" 
     ShowHeaderWhenEmpty="True" ShowFooter="True">
    <AlternatingRowStyle BackColor="#CCCCCC" HorizontalAlign="Center" />
    <FooterStyle BackColor="#CCCCCC" />
    <HeaderStyle BackColor="Maroon" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#808080" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#383838" />
    ....
</asp:GridView>

What is the best solution for replacing the static table with dynamic data?

I'm always getting it like this:

Answer 1

Well, you can feed a gridview a data source. And you can "modify" that data BEFORE you send it to the grid view.

Let's do a really simple example.

Markup:

    <asp:GridView ID="GridView1" runat="server" 
        Width="40%" CssClass="table table-striped">
    </asp:GridView>

Code to load:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
        LoadGrid();
}


void LoadGrid()
{

    using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
    {
        string strSQL =
            @"SELECT ID, FirstName, HotelName, Description
            FROM tblHotelsA ORDER BY HotelName";

        using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
        {
            conn.Open();
            DataTable rstData = new DataTable();
            rstData.Load(cmdSQL.ExecuteReader());   

            GridView1.DataSource = rstData;
            GridView1.DataBind();   
        }
    }
}

And we now see/get this:

but, "before" we bind, we can modify the column names, like this:

conn.Open();
DataTable rstData = new DataTable();
rstData.Load(cmdSQL.ExecuteReader());

// now replace column headings with whatever
// we want
for (int i = 0; i < rstData.Columns.Count; i++)
{
    rstData.Columns[i].ColumnName = $"Col {i.ToString()}";
}

GridView1.DataSource = rstData;
GridView1.DataBind();   

and now we see/get this:

Now, of course I just used for/next loop, but I could have pulled the other table (1 row), and grabbed the columns from that table, and then shoved the column values from that table, or any thing else I dream to cook up here.

So, I could for example pull and get the columns from a different table.

We are pulling data from a table called tblhotelsA, but let's use the columns from tlbHotels

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
    string strSQL =
        @"SELECT ID, FirstName, HotelName, Description
        FROM tblHotelsA ORDER BY HotelName";

    using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
    {
        conn.Open();
        DataTable rstData = new DataTable();
        rstData.Load(cmdSQL.ExecuteReader());

        // now replace column headings with whatever
        // we want
        DataTable rstData2 = new DataTable();
        strSQL =
            @"SELECT TOP 1 ID, FirstName, HotelName, Description
            FROM tblHotels ORDER BY ID";
        cmdSQL.CommandText = strSQL;
        rstData2.Load(cmdSQL.ExecuteReader());  


        for (int i = 0; i < rstData.Columns.Count; i++)
        {
            rstData.Columns[i].ColumnName = rstData2.Columns[i].ColumnName;
        }

        GridView1.DataSource = rstData;
        GridView1.DataBind();   
    }
}

Now, in above example, the 2 tables were the same, but regardless, you can see how we are "free" to change the column headings of the data source to whatever we want before the data bind of the gv.

Edit2: change the header values in gv

Of course, you can also AFTER you data bind, then just change the gv headers, and NOT have to change the data source (table) headers.

So, you can do it this way:

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
    string strSQL =
        @"SELECT ID, FirstName, HotelName, Description
        FROM tblHotelsA ORDER BY HotelName";

    using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
    {
        conn.Open();
        DataTable rstData = new DataTable();
        rstData.Load(cmdSQL.ExecuteReader());


        GridView1.DataSource = rstData;
        GridView1.DataBind();
        for (int i = 0; i < GridView1.HeaderRow.Cells.Count; i++)
        {
            GridView1.HeaderRow.Cells[i].Text = $"Col {i.ToString()}";
        }
    }
}

So, you have 2 paths to take here. You can change the "data source" table headers, or you can simple bind gv, and then you are free to change the headers to anything you want.

Edit3: Pulling the headers from another table

So, say we have this data in a table:

Ok, so now our code becomes this:

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
    string strSQL =
        @"SELECT ID, FirstName, LastName, HotelName, Description
        FROM tblHotelsA ORDER BY HotelName";

    using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
    {
        conn.Open();

        // get data for grid
        DataTable rstData = new DataTable();
        rstData.Load(cmdSQL.ExecuteReader());
        GridView1.DataSource = rstData;
        GridView1.DataBind();

        // get headers from our "header" table (or games or whatever)
        DataTable rstHeaders = new DataTable();
        cmdSQL.CommandText = "SELECT MyHeader FROM tblHeaders ORDER BY ID";
        rstHeaders.Load(cmdSQL.ExecuteReader());

        for (int i = 0; i < rstHeaders.Rows.Count; i++)
            GridView1.HeaderRow.Cells[i].Text = rstHeaders.Rows[i]["MyHeader"].ToString();
    }
}

And now we get this: