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.
Ebook Download
View all
Learn
View all