Post
Ask Question

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

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.
  1. create table table_column(      
  2.    t_id int not null identity(1,1),      
  3.    column_name nvarchar(max),      
  4.    page_name nvarchar(100),      
  5.    orderby int,  
  6.    visible int      
  7. )   
Create another Table that stores employee data.
  1. create table employee(    
  2.    eid int not null identity(1,1),    
  3.    emp_name nvarchar(max),    
  4.    emp_gender nvarchar(50),    
  5.    emp_salary float    
  6. )   
Now insert some employee to employee table.
  1. insert into employee(emp_name,emp_gender,emp_salary)values    
  2. ('AbC','Male','1000000'),    
  3. ('Xyz','Male','5825600'),    
  4. ('Mno','Male','7852300'),    
  5. ('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).
  1. insert into table_column(column_name,page_name,orderby,visible) values    
  2. ('emp_name','Employee','1','1'),    
  3. ('emp_gender','Employee','3','1'),    
  4. ('emp_salary','Employee','2','1'),    
  5. ('eid','Employee','4','1')  
Now come to aspx page and take a gridview control. Remember autogeneratecolumns property must be true.
Code
  1. DataTable dt2 = new DataTable();  
  2. using(SqlCommand cmd2 = new SqlCommand())  
  3. {  
  4.     cmd2.CommandText = " select column_name,page_name from table_column where visible='1' and                                                                    page_name='Employee' order by orderby ";  
  5.     cmd2.Connection = con;  
  6.     SqlDataAdapter adp = new SqlDataAdapter(cmd2);  
  7.     adp.Fill(dt2);  
  8. }  
  9. DataTable dt = new DataTable();  
  10. using(SqlCommand cmd = new SqlCommand())  
  11. {  
  12.     cmd.CommandText = "select eid,emp_name,emp_gender,emp_salary from employee";  
  13.     cmd.Connection = con;  
  14.     SqlDataAdapter da = new SqlDataAdapter(cmd);  
  15.     da.Fill(dt);  
  16. }  
  17. DataTable dta = new DataTable();  
  18. if (dt.Rows.Count > 0)  
  19. {  
  20.     if (dt2.Rows.Count > 0)  
  21.     {  
  22.         for (int i = 0; i < dt2.Rows.Count; i++)  
  23.         {  
  24.             string nam = dt2.Rows[i]["column_name"].ToString();  
  25.             dta.Columns.Add(nam);  
  26.         }  
  27.         DataRow dr1 = dta.NewRow();  
  28.         for (int j = 0; j < dt.Rows.Count; j++)  
  29.         {  
  30.             for (int i = 0; i < dt2.Rows.Count; i++)  
  31.             {  
  32.                 dr1[dt2.Rows[i]["column_name"].ToString()] = dt.Rows[j][dt2.Rows[i]["column_name"].ToString()].ToString();  
  33.             }  
  34.             dta.Rows.Add(dr1.ItemArray);  
  35.         }  
  36.         gvaccount.DataSource = dta;  
  37.     }  
  38.     else  
  39.     {  
  40.         gvaccount.DataSource = dt;  
  41.     }  
  42. }  
  43. gvaccount.DataBind();  
  44. }  
After this Run your project and you see the below output with the column order we define in the database.

  1. 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.