sql server - Extracting a subset of DataTable columns in C# and then sending them to browser client as a JSON string -
here's scenario: c# middle tier webservice has data sent sql server in system.data.datatable object. there dozens of columns in datatable's columns collection. browser client needs see 6 of them. stored proc on server general purpose, not designed specific task. assume have use it, rather writing new sp.
is there simple way, perhaps using generics, extract desired subset of columns datatable list or collection, , convert resulting list or collection json using json.net?
is possible create skeletal c# class definition foo relevant field names (and datatypes) , match on field names "automagically" , thereby generate list<foo> datatable's rows collection?
ideally, during json conversion, sql server datetime values (e.g. 2014-06-24t18:45:00) converted value make easy instantiate javascript date in client without having string manipulation of date representation.
full working console app code pasted below. 2 main methods need follows.
for code work, have following in project.
- add
json.netnuget package project. add reference
system.web.extensions(if compile error in linesystem.web.script.serialization.javascriptserializerbeing referenced ingetjsonmethod./// <summary> /// returns json representation of generic class matching properties datatable (passed parameter) /// </summary> /// <typeparam name="t"></typeparam> /// <param name="dt"></param> /// <returns></returns> public static string getjsonfromdatatable<t>(datatable dt) t : new() { string json = getjson(dt); return jsonconvert.serializeobject(jsonconvert.deserializeobject<list<t>>(json)); } /// <summary> /// returns json string entire datatable (passed 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 new console app, , replace in program.cs code. add json.net console app project , add references 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 patientdrug:\n{0}",patientdrugjson); string patientjson = getjsonfromdatatable<patient>(patientdatatable); console.writeline("\njson patient:\n{0}", patientjson); console.writeline("\n\npress key exit..."); console.readkey(); } private static datatable getpatientdrugtable() { // // here create datatable 4 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 add 5 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 create datatable 4 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 add 5 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 matching properties datatable (passed parameter) /// </summary> /// <typeparam name="t"></typeparam> /// <param name="dt"></param> /// <returns></returns> public static string getjsonfromdatatable<t>(datatable dt) t : new() { string json = getjson(dt); return jsonconvert.serializeobject(jsonconvert.deserializeobject<list<t>>(json)); } /// <summary> /// returns json string entire datatable (passed 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 have 2 classes, patient , patientdrug. wrote helper methods return data tables both classes, have additional columns. following 2 lines json class representation patient , patientdrug respectively, while ignoring additional data columns in datatable don't match names.
string patientdrugjson = getjsonfromdatatable<patientdrug>(patientdrugdatatable); string patientjson = getjsonfromdatatable<patient>(patientdatatable); output in console windows (the json strings)
Comments
Post a Comment