title: Dynamically Creating GridView
body: <p>I want to create a GridView that displays records for PDF Files. These records can have meta data attached that is customizeable by the user, so they can create their own columns and enter their own information in there. I then want it to be displayed in a GridView, so they can order each column and the column order if the column order is -1 it will not display in the GridView.</p>
  
  <p>For example there is a static table</p>
  
  <pre><code>DocumentsTable:
  ID int
  PDF_Folder varchar
  UserID int
  </code></pre>
  
  <p>Then there is another table that users can create their own columns for</p>
  
  <pre><code>MetaDataColumns:
  ID int
  userid int foreign key
  KeyName varchar
  Order int
  </code></pre>
  
  <p>and the table to hold the values</p>
  
  <pre><code>MetaDataValues:
  ID int
  UserID int foreign key
  DocumentID int foreign key
  MetaDataID int foreign key
  value varchar(100)
  </code></pre>
  
  <p>Now the problem is I need to get the columns from the MetaDataColumn to create the GridView and then populate it with the values in MetaDataValue table. My original plan is to have a function that dynamically creates the GridView and adds columns to it, however I am stuck on how to use the values in the MetaDataValue as the columns. Alternatively I could just have the GridView AutoGenerate columns but I need to customize the SQL to display the custom data. I'm a bit stuck on how to even approach this.</p>
  
  <p>One approach I have come up with is this pseudo code:</p>
  
  <pre><code>private DataTable CreateColumns()
  {
     var columns =  select * from MetaDataColumns 
                    where userid = UserId;
  
     DataTable dt = new DataTable();
  
     foreach (column in columns)
     {
         dt.Columns.Add(new DataColumn(column[keyName], typeof(string));  //assumes all string
     }
  
   return dt
  }
  
  private void PopulateDG(DataGrid dg)
  {
      var documents = select * from DocumentsTable
                       where userid=UserId;
  
      foreach (document in documents)
      {            
          var columnValues = select * from MetaDatavalues 
                             documentID == document.id;
  
          DataRow dr = dg.NewRow();
          dr[columnValues.KeyName] = columnValues.value;
  
      }
  
   }
  
   private void LoadGV()
   {  
     DataGrid dg = CreateColumns();
     PopulateDG(dg);
     GridView.datasource = dg;
     GridView.DataBind();
    }
  </code></pre>
  
  <p>One of the things I dont like about this design is for every row in the documents table it creates another query.  Im not sure if this is a problem with SQL? </p>