SQL to JSON with C#

This seems to work quite well. If there is a more efficient way please let me know.

string SQLtoJSON(string _sqlCon, string _sqlQuery) 
 using (SqlConnection con = new SqlConnection(_sqlCon))
 using (SqlCommand cmd = new SqlCommand(_sqlQuery, con))
 using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))

 DataTable sqlData = new DataTable();

System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
 List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>(); 
Dictionary<string, object> row;

 foreach (DataRow dr in sqlData.Rows) 
row = new Dictionary<string, object>();
 foreach (DataColumn col in sqlData.Columns)
 row.Add(col.ColumnName, dr[col]);
return serializer.Serialize(rows); } } } } 

void SendJSON(string json) {
 Response.ContentType = "application/json; charset=utf-8"; 


1 Comment

  1. I saw recently that it appears that newer versions of MSSQL have the ability to output directly to JSON, which is fantastic. Unfortunately i deal with a few older installations which do not apparently have this feature.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.