locked
Select All Columns From Table Whose Value Is Not Null? RRS feed

  • Question

  • User1197377994 posted

    Dear Sir/Madam,

    i want to select all the columns from the table whose value is not null.

    how can i do this .

    I m thankful to u in advance

    for your valuable answers.

    Wednesday, December 13, 2017 6:23 PM

All replies

  • User1120430333 posted

    You give no indication of the database technology being used. T-SQL, P-SQL, Linq ect. and ect. Maybe Houdini knows.

    Wednesday, December 13, 2017 7:58 PM
  • User-707554951 posted

    Hi 4itguy, 

    For your problem, you could use the code below.

    For example, I have UsersTable in database:

    Code:

      protected void Page_Load(object sender, EventArgs e)
            {        
                using (var context = new Database1Entities3())
                {
                    var result = (from u in context.UsersTables select u).ToList();
                    DataTable dt = ToDataTable(result);
                    DataTable resulttable = ToDataTable(result);
                 
                    foreach (DataColumn column in dt.Columns)
                    {
                        bool flag = false;
                        foreach(DataRow row in dt.Rows)
                        {
                         if (row[column].ToString()!="")
                            {
                                flag = true;
                            }
                        }
                        if (flag == false)
                        {
                            resulttable.Columns.Remove(column.ToString());
                        }
    
    
                    }
    
                    GridView1.DataSource = resulttable;
                    GridView1.DataBind();
                }
                       
            }     
                public DataTable ToDataTable<T>(List<T> items)
                {
                    DataTable dataTable = new DataTable(typeof(T).Name);
                    //Get all the properties
                    PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
                    foreach (PropertyInfo prop in Props)
    
                    {
                        //Setting column names as Property names
                        dataTable.Columns.Add(prop.Name);             
                    }
                    foreach (T item in items)
                    {
                        var values = new object[Props.Length];
                        for (int i = 0; i < Props.Length; i++)
                        {
                            //inserting property values to datatable rows
                            values[i] = Props[i].GetValue(item, null);
                        }
                        dataTable.Rows.Add(values);
                    }
                    //put a breakpoint here and check datatable
                    return dataTable;
    
                }
    

    Output:

    Best regards 

    Cathy

    Thursday, December 14, 2017 2:38 AM