Extracting a subset of DataTable columns in C# and then sending them to browser client as a JSON string

Question

Here's the scenario: C# middle tier webservice has the data sent from SQL Server in a System.Data.DataTable object. There are dozens of columns in the DataTable's columns collection. The browser client only needs to see about six of them. The stored proc on the server is general purpose, not designed for this specific task. Assume we have to use it, rather than writing a new SP.

Is there a simple way, perhaps using generics, to extract the desired subset of columns from that DataTable into a List or a Collection, and then convert the resulting list or collection to JSON using JSON.NET?

Is it possible to create a skeletal C# class definition foo with the relevant field names (and datatypes) and then match on those field names "automagically" and thereby generate a List<foo> from the DataTable's rows collection?

Ideally, during the JSON conversion, any SQL Server datetime values (e.g. 2014-06-24T18:45:00) would be converted to a value that would make it easy to instantiate a javascript Date in the client without having to do string manipulation of the date representation.

Answer 1

Full working Console App code pasted below. But the 2 main methods you need are as follows.

For this code to work, you will have to do the following in your Project.

1. Add the JSON.Net Nuget Package to the Project.

2. Add a reference to System.Web.Extensions (if you get a compile error in the line where System.Web.Script.Serialization.JavaScriptSerializer is being referenced in the GetJson method.

   /// <summary>
   /// Returns Json representation of Generic class with only matching properties from the DataTable (passed as parameter)
   /// </summary>
   /// <typeparam name="T"></typeparam>
   /// <param name="dt"></param>
   /// <returns></returns>
   public static string GetJsonFromDataTable<T>(DataTable dt) where T : new()
   {
       string json = GetJson(dt);
       return JsonConvert.SerializeObject(JsonConvert.DeserializeObject<List<T>>(json));
   }
   
   /// <summary>
   /// Returns a JSON string for entire DataTable (passed as parameter)
   /// </summary>
   /// <param name="dt"></param>
   /// <returns></returns>
   public static string GetJson(DataTable dt)
   {
       System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
       List<Dictionary<string, object>> rows = (from DataRow dr in dt.Rows select dt.Columns.Cast<DataColumn>().ToDictionary(col => col.ColumnName.Trim(), col => dr[col])).ToList();
       return serializer.Serialize(rows);
   }
   

Fully working Console App code.

Create a new console App, and replace everything in the Program.cs with this code. Also add JSON.Net to the Console App Project and add the references to System.Web.Extensions.

namespace DataTable2Json
{
    using Newtonsoft.Json;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;

    public class Patient
    {
        public string FullName { get; set; }
        public string PatientID { get; set; }
        public int NumberOfIllnesses { get; set; }
        public DateTime DateAdmitted { get; set; }
    }

    public class PatientDrug
    {
        public string Patient { get; set; }
        public string Drug { get; set; }
    }


    internal class Program
    {
        private static void Main(string[] args)
        {
            DataTable patientDrugDataTable = GetPatientDrugTable();
            DataTable patientDataTable = GetPatientTable();

            string patientDrugJson = GetJsonFromDataTable<PatientDrug>(patientDrugDataTable);
            Console.WriteLine("Json for PatientDrug:\n{0}",patientDrugJson);

            string patientJson = GetJsonFromDataTable<Patient>(patientDataTable);
            Console.WriteLine("\nJson for Patient:\n{0}", patientJson);

            Console.WriteLine("\n\nPress a key to Exit...");
            Console.ReadKey();
        }

        private static DataTable GetPatientDrugTable()
        {
        //
        // Here we create a DataTable with four columns.
        //
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));

        //
        // Here we add five DataRows.
        //
        table.Rows.Add(25, "Indocin", "David", DateTime.Now);
        table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
        table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
        table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
        table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
        return table;
        }

        private static DataTable GetPatientTable()
        {
            //
            // Here we create a DataTable with four columns.
            //
            DataTable table = new DataTable();
            table.Columns.Add("NumberOfIllnesses", typeof(int));
            table.Columns.Add("PatientID", typeof(string));
            table.Columns.Add("FullName", typeof(string));
            table.Columns.Add("DateAdmitted", typeof(DateTime));
            table.Columns.Add("StreetAddress1", typeof(string));
            table.Columns.Add("City", typeof(string));
            table.Columns.Add("State", typeof(string));
            //
            // Here we add five DataRows.
            //
            table.Rows.Add(2, "PAT-00001", "David", DateTime.Now, "1 Mill Ln", "Schenectady", "NY");
            table.Rows.Add(1, "PAT-00002", "Sam", DateTime.Now, "1915 Boylston Steet", "Boston", "MA");
            table.Rows.Add(3, "PAT-00003", "Christoff", DateTime.Now, "15 Polk Steet", "San Francisco", "CA");
            table.Rows.Add(4, "PAT-00004", "Janet", DateTime.Now, "10 Waverly St", "Los Angeles", "CA");
            table.Rows.Add(5, "PAT-00005", "Melanie", DateTime.Now, "50 Kapaa St", "Kailua", "HI");
            return table;
        }

        /// <summary>
        /// Returns Json representation of Generic class with only matching properties from the DataTable (passed as parameter)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string GetJsonFromDataTable<T>(DataTable dt) where T : new()
        {
            string json = GetJson(dt);
            return JsonConvert.SerializeObject(JsonConvert.DeserializeObject<List<T>>(json));
        }

        /// <summary>
        /// Returns a JSON string for entire DataTable (passed as parameter)
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string GetJson(DataTable dt)
        {
            System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            List<Dictionary<string, object>> rows = (from DataRow dr in dt.Rows select dt.Columns.Cast<DataColumn>().ToDictionary(col => col.ColumnName.Trim(), col => dr[col])).ToList();
            return serializer.Serialize(rows);
        }
    }
}

Explanation of Code:

Notice I have 2 classes, Patient and PatientDrug. I wrote helper methods to return data tables for both classes, that have additional columns. Then the following 2 lines get the JSON for the class representation for Patient and PatientDrug respectively, while ignoring the additional data columns in DataTable that don't match names.

string patientDrugJson = GetJsonFromDataTable<PatientDrug>(patientDrugDataTable);
string patientJson = GetJsonFromDataTable<Patient>(patientDataTable);

Output in Console Windows (the json strings)