In this post I will explain you, how can we serialize Datatable to JSON. So that, it can easily pass to JavaScript to get the AJAX done.
First of all fill a Datatable with some results.
DataTable dt = new DataTable();
SqlConnection objSqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ToString());
objSqlCon.Open();
SqlDataAdapter objSqlDa = new SqlDataAdapter("select * from Production.Product", objSqlCon);
objSqlDa.Fill(dt);
Now create a String Builder object that will contain the JSON text and JavascriptSerializer which will serialize the output in JSON.
StringBuilder objSb = new StringBuilder();
JavaScriptSerializer objSer = new JavaScriptSerializer();
Now here we are going to iterate each row and column of data table and put all of them in Dictionary
Dictionary<string, object> resultMain = new Dictionary<string, object>();
int index = 0;
foreach (DataRow dr in dt.Rows)
{
Dictionary<string, object> result = new Dictionary<string, object>();
foreach (DataColumn dc in dt.Columns)
{
result.Add(dc.ColumnName, dr[dc].ToString());
}
resultMain.Add(index.ToString(), result);
index++;
}
Notice that, I have created a new dictionary object for every row and finally put all of the dictionaries in Another dictionary I.E. resultMain.
In the end, I have simply Serialize the resultMain Dictionary to render JSON.
A complete post of utilizing Datatable in JavaScript through AJAX is in the process, I will post that soon.