Select All Columns From Table Whose Value Is Not Null?

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.

Answer 1

User1120430333 posted

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

Answer 2

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