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();
 adapter.Fill(sqlData); 

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]);
 } 
rows.Add(row); 
} 
return serializer.Serialize(rows); } } } } 

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

 

One comment

  1. ndev · March 31, 2016

    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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s