Programing QA

Programing/Developing Questions & Answers. Focus on business application material

Converting MongoDB query result to C# ADO.NET DataTable

Step 1:

Perform query against the MongoDB using the MongoDB .NET Provider like demonstrate in the post : Connect to MongoDB & execute string query using MongoDB DOT.NET Provider & Linq.

Step 2:

If you follow the sample code in previous step, it should ends with list of BsonDocument:

Code Snippet
  1. List<BsonDocument> result = toReturn.Take(100).ToList(); // now it's here.

 

Now, we will loop thru the results:

Code Snippet
  1. List<BsonDocument> result = toReturn.Take(100).ToList(); // now it's here.
  2.  
  3. DataTable dt = new DataTable(); // Create empty datatable we will fill with data.
  4.  
  5. foreach (BsonDocument obj in toReturn) // Loop thru all Bson documents returned from the query.
  6. {
  7.     DataRow dr = dt.NewRow(); // Add new row to datatable.
  8.     ExecuteFillDataTable(obj, dt, dr, string.Empty); // Recursuve method to loop thru al results json.
  9.     dt.Rows.Add(dr); // Add the newly created datarow to the table
  10. }
  11.  
  12. return dt; // return the table with all it rows inside.

 

Step 3:

For each parent Bson Document, We will use this recursive method to go true all level of json and fill our data table will all data.

 

Code Snippet
  1. private void ExecuteFillDataTable(BsonDocument doc, DataTable dt, DataRow dr, string parent)
  2. {
  3.     // arrays means 1:M relation to parent, meaning we will have to fake multi levels by adding 1 more row foreach item in array.
  4.     // i created the here because i want to add all new array rows after our main row.
  5.     List<KeyValuePair<string, BsonArray>> arrays = new List<KeyValuePair<string, BsonArray>>();
  6.     
  7.     foreach (string key in doc.Names) // this will loop thru all our json attributes.
  8.     {
  9.         object value = doc[key]; // get the value of the current json attribute.
  10.         
  11.         string x; // for my specific needs, i need all values to be save in datatable as strings. you can implument to match your needs.
  12.  
  13.         // if our attribute is BsonDocument, means relation is 1:1. we can add values to current datarow and call the data column "parent.current".
  14.         // we will use this recursive method to run thru all the child document.
  15.         if (value is BsonDocument)
  16.         {
  17.             string newParent = string.IsNullOrEmpty(parent) ? key : parent + "." + key;
  18.             ExecuteFillDataTable((BsonDocument)value, dt, dr, newParent);
  19.         }
  20.         // if our attribute is BsonArray, means relation is 1:N. we will need to add new rows, but not now.
  21.         // we will save it in queue for later use.
  22.         else if (value is BsonArray)
  23.         {
  24.             // Save array to queue for later loop.
  25.             arrays.Add(new KeyValuePair<string, BsonArray>(key, (BsonArray)value));
  26.  
  27.  
  28.         }
  29.         // if our attribute is datatime i needed it in a spesific string format.
  30.         else if (value is BsonTimestamp)
  31.         {
  32.             x = doc[key].AsBsonTimestamp.ToLocalTime().ToString("s");
  33.  
  34.         }
  35.         // if our attribute is null, i needed it converted to string.empty.
  36.         else if (value is BsonNull)
  37.         {
  38.             x = string.Empty;
  39.  
  40.         }
  41.         else
  42.         {
  43.             // for all other cases, just .ToString() it.
  44.             x = value.ToString();
  45.             
  46.             // Make sure our datatable already contains column with the right name. if not - add it.
  47.             string colName = string.IsNullOrEmpty(parent) ? key : parent + "." + key;
  48.             if (!dt.Columns.Contains(colName))
  49.                 dt.Columns.Add(colName);
  50.  
  51.             // Add the value to the datarow in the right column.
  52.             dr[colName] = value;
  53.  
  54.         }
  55.  
  56.     }
  57.  
  58.     // loop thru all arrays when finish with standart fields.
  59.     foreach (KeyValuePair<string, BsonArray> array in arrays)
  60.     {
  61.         // create column name that contains the parent name + child name.
  62.         string newParent = string.IsNullOrEmpty(parent) ? array.Key : parent + "." + array.Key;
  63.  
  64.         // save the old - we will need it so we can add it existing values to the new row.
  65.         DataRow drOld = dr;
  66.  
  67.         // loop thru all the BsonDocuments in the array
  68.         foreach (BsonDocument doc2 in array.Value)
  69.         {
  70.             // Create new datarow for each item in array.
  71.             dr = dt.NewRow();
  72.             dr.ItemArray = drOld.ItemArray; // this will copy all the main row values to the new row - might not be needed for your use.
  73.             dt.Rows.Add(dr); // the the new row to the datatable
  74.             ExecuteFillDataTable(doc2, dt, dr, newParent); // fill the new datarow withh all the values for the BsonDocument in the array.
  75.         }
  76.  
  77.         dr = drOld; // set the main data row back so we can use it values again.
  78.     }
  79. }

 

Not to complex I hope – this code can be improved – but it is working! Smile

  • Macy Lacs

    3/10/2015 6:16:04 PM |

    This is a topic which is close to my heart Take care! Exactly where are your contact details though?

  • Kitchen Counter Replacement

    3/13/2015 3:54:28 PM |

    Major thanks for the article post.

  • rental mobil murah di surabaya

    3/17/2015 11:29:49 AM |

    Enjoyed every bit of your blog article.Thanks Again. Cool.

  • authority source

    4/5/2015 2:29:37 PM |

    Spot on with this write-up, I truly think this website needs much more consideration. I’ll probably be again to read much more, thanks for that info.

Pingbacks and trackbacks (1)+

Comments are closed