While working on my Dashboard project, I wanted to return anonymous result set from dynamic sql in JSON format in order to consume it with visual widgets.
The solution was to return a DataTable and serialize it using Json.NET.
Controller Action
C# private AdventureWorksDB db = new AdventureWorksDB(); public JsonDotNetResult Index(string table, string[] columns) { //ex: localhost/home/index?table=Sales.Currency&columns=name,currencycode DataTable dt = new DataTable(); var conn = (SqlConnection)db.Database.Connection; ConnectionState initialState = conn.State; if (initialState != ConnectionState.Open) conn.Open(); // open connection if not already open using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = String.Format("SELECT {0} FROM {1}", string.Join(",", columns), table); var reader = cmd.ExecuteReader(); dt.Load(reader); } return new JsonDotNetResult(dt); } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 private AdventureWorksDB db = new AdventureWorksDB ( ) ; public JsonDotNetResult Index ( string table , string [ ] columns ) { //ex: localhost/home/index?table=Sales.Currency&columns=name,currencycode DataTable dt = new DataTable ( ) ; var conn = ( SqlConnection ) db . Database . Connection ; ConnectionState initialState = conn . State ; if ( initialState != ConnectionState . Open ) conn . Open ( ) ; // open connection if not already open using ( DbCommand cmd = conn . CreateCommand ( ) ) { cmd . CommandText = String . Format ( "SELECT {0} FROM {1}" , string . Join ( "," , columns ) , table ) ; var reader = cmd . ExecuteReader ( ) ; dt . Load ( reader ) ; } return new JsonDotNetResult ( dt ) ; }
Json.NET Action Result
C# public class JsonDotNetResult : ActionResult { private object _obj { get; set; } public JsonDotNetResult(object obj) { _obj = obj; } public override void ExecuteResult(ControllerContext context) { context.HttpContext.Response.AddHeader("content-type", "application/json"); context.HttpContext.Response.Write(JsonConvert.SerializeObject(_obj)); } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 public class JsonDotNetResult : ActionResult { private object _obj { get ; set ; } public JsonDotNetResult ( object obj ) { _obj = obj ; } public override void ExecuteResult ( ControllerContext context ) { context . HttpContext . Response . AddHeader ( "content-type" , "application/json" ) ; context . HttpContext . Response . Write ( JsonConvert . SerializeObject ( _obj ) ) ; } }