SQL Server Table to JSON Object Array

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Web.Script.Serialization;
using System.IO;
using System.Text;
using System.Web.Services;
using System.Text.RegularExpressions;
using System.Collections;

private static string databaseTableToJsonArrayObject()
string CONNECTIONSTRING_KEY_NAME = "Your_ConnectionStrings_Name_Attribute";
string TABLE_TO_CONNECT_TO = "Your_Tables_Name";
string SELECT = "DISTINCT *"; //string SELECT = "SELECT id"
string WHERE = ""; //string WHERE = "WHERE id = 1"
string ORDERBY = ""; //string ORDERBY = "ORDER BY id ASC|DESC"

DataTable dt = new DataTable();
using (SqlConnection conString = new SqlConnection(ConfigurationManager.ConnectionStrings[CONNECTIONSTRING_KEY_NAME].ConnectionString))

using (SqlCommand sqlCmd = new SqlCommand("SELECT " + SELECT + " FROM " + TABLE_TO_CONNECT_TO + WHERE + ORDERBY, conString))
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
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 dt.Rows)
row = new Dictionary<string, object>();

int columnCount = 0;
foreach (DataColumn col in dt.Columns)
row.Add(columnCount.ToString(), dr[col]);
return serializer.Serialize(rows);



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