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))
{
conString.Open();
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
da.Fill(dt);
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]);
columnCount++;
}
rows.Add(row);
}
conString.Close();
return serializer.Serialize(rows);

}
}

}

Advertisements