Show Data According to the Columns and Rows Generated at Run Time in C#

In this blog I will show you the Gridview data corresponding to the dynamic columns with the option to define the order and the visibility of the column.

I assume everyone knows how to show data in gridview. But here I will explain how the data can be shown according to the columns and rows generated at run time. So let's get started.

First create a table that store the columns of different Gridviews. Here I took one column order for the order of columns in a Gridview and visible column for hide/show.

create table table_column(
   t_id int not null identity(1,1),
   column_name nvarchar(max),
   page_name nvarchar(100),
   orderby int,
   visible int
)

Create another Table that stores employee data.

create table employee(
   eid int not null identity(1,1),
   emp_name nvarchar(max),
   emp_gender nvarchar(50),
   emp_salary float
)

Now insert some employee to employee table.

insert into employee(emp_name,emp_gender,emp_salary)values
('AbC','Male','1000000'),
('Xyz','Male','5825600'),
('Mno','Male','7852300'),
('Pqr','Male','7458000')

Now insert the columns into the table_column table. This is the record that is used to display data to gridview.

Visible value (1 means show and 0 means hide).

insert into table_column(column_name,page_name,orderby,visible) values
('emp_name','Employee','1','1'),
('emp_gender','Employee','3','1'),
('emp_salary','Employee','2','1'),
('eid','Employee','4','1')

Now come to aspx page and take a gridview control. Remember autogeneratecolumns property must be true.

Code

DataTable dt2 = new DataTable();
using(SqlCommand cmd2 = new SqlCommand())
{
    cmd2.CommandText = " select column_name,page_name from table_column where visible='1' and                                                                    page_name='Employee' order by orderby ";
    cmd2.Connection = con;
    SqlDataAdapter adp = new SqlDataAdapter(cmd2);
    adp.Fill(dt2);
}
DataTable dt = new DataTable();
using(SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = "select eid,emp_name,emp_gender,emp_salary from employee";
    cmd.Connection = con;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dt);
}
DataTable dta = new DataTable();
if (dt.Rows.Count > 0)
{
    if (dt2.Rows.Count > 0)
    {
        for (int i = 0; i < dt2.Rows.Count; i++)
        {
            string nam = dt2.Rows[i]["column_name"].ToString();
            dta.Columns.Add(nam);
        }
        DataRow dr1 = dta.NewRow();
        for (int j = 0; j < dt.Rows.Count; j++)
        {
            for (int i = 0; i < dt2.Rows.Count; i++)
            {
                dr1[dt2.Rows[i]["column_name"].ToString()] = dt.Rows[j][dt2.Rows[i]["column_name"].ToString()].ToString();
            }
            dta.Rows.Add(dr1.ItemArray);
        }
        gvaccount.DataSource = dta;
    }
    else
    {
        gvaccount.DataSource = dt;
    }
}
gvaccount.DataBind();
}

After this Run your project and you see the below output with the column order we define in the database.

update table_Column set visible='0' where column_name='eid'

Now if I update the visibility of the column id to 0 and the resultant output is -

If suppose we did not insert the columns into the table_column table then there is no issue, the default order of columns will display.

Hope this will help you and if you have any questions, please do comment.