DataTable to json – Using DataTable data to display table using jQuery AJAX in ASP.Net:

In many cases, we use DataTable to fill the data from the database and we want to show this data to users in table format.  If user clicks a button, we need to show the data in DataTable using jQuery Ajax. See below picture.

Sidenote: We are using Production.Product of AdventureWorks database. Complete code is attached at the bottom of this article.

But we can’t send this DataTable to browser directly.

So, we need to convert the DataTable to JSON format so that jQuery can understand while using Ajax.

Any List can be converted to JSON format without any issues. So we can convert the DataTable to List type and send it as Ajax response.

Conversion of DataTable to List and send it as JSON:

We can discuss couple of options for achieving the same

1. We can just loop through the rows in DataTable and create a new object for corresponding to each row and add it to the list

foreach (DataRow row in dtProducts.Rows)

{

string name = Convert.ToString(row["Name"]);

string productNumber = Convert.ToString(row["ProductNumber"]);

int safetyStockLevel = Convert.ToInt32(row["SafetyStockLevel"]);

int reOrderPoint = Convert.ToInt32(row["ReorderPoint"]);

Product product = new Product(name, productNumber, safetyStockLevel, reOrderPoint);

lstProducts.Add(product);

}

2. Make the DataTable as Enumerable and to create new object by accessing its properties and add it to the list.

var productslinq = (from products in dtProducts.AsEnumerable()
                   select new
                   {
                     Name = products.Field<string>("Name"),
                     ProductNumber = products.Field<string>("ProductNumber"),
                     SafetyStockLevel = products.Field<Int16>("SafetyStockLevel"),
                     ReorderPoint = products.Field<Int16>("ReorderPoint")
                   }).ToList();

foreach (var product in productslinq)
{
   lstProducts.Add(new Product(product.Name,product.ProductNumber,product.SafetyStockLevel,product.ReorderPoint));
}

Loop through JSON records to build table:

We can use map function to loop through all the records. Here data.d contains the JSON response. For each record(product in our case), we are building a table row and append it to the existing table.

$.map(data.d, function (product) {
  $('<tr> <td>' + product.Name + '</td> <td>' + product.ProductNumber + ' </td> <td>' + product.SafetyStockLevel + ' </td> <td>' + product.ReorderPoint + ' </td></tr>').appendTo(".tblData");
});

Complete Code:

<script type="text/javascript">
  function loadData(e) {
    e.preventDefault();
    $.ajax({
    type: "POST",
    contentType: "application/json; charset=utf-8",
    url: "WebForm1.aspx/GetProductsLINQ",
    data: "",
    dataType: "json",
    success: function (data) {
      $.map(data.d, function (product) {
         $('<tr> <td>' + product.Name + '</td> <td>' + product.ProductNumber + ' </td> <td>' + product.SafetyStockLevel + ' </td> <td>' + product.ReorderPoint + ' </td></tr>').appendTo(".tblData");
  });
  },
  error: function (response) {
    alert("Error" + response.responseText);
  }
});

}

</script>

ASPX Markup:

<form id="form1" runat="server">
<div>
<table class="tblData" border="1">
</table>
<asp:Button ID="btnLoadData" runat="server" Text="Load Data" OnClientClick="return loadData(event);" />
</div>
</form>
<pre>

You can use either of the below methods as AJAX method

[WebMethod]
public static List<Product> GetProducts()
{
List<Product> lstProducts = new List<Product>();
DataTable dtProducts = new DataTable();
string sqlQuery = "select top 10 Name,ProductNumber,SafetyStockLevel,ReorderPoint from Production.Product";
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksContext"].ConnectionString;

try
{
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter da = new SqlDataAdapter(sqlQuery, conn);
da.Fill(dtProducts);
foreach (DataRow row in dtProducts.Rows)
{
string name = Convert.ToString(row["Name"]);
string productNumber = Convert.ToString(row["ProductNumber"]);
int safetyStockLevel = Convert.ToInt32(row["SafetyStockLevel"]);
int reOrderPoint = Convert.ToInt32(row["ReorderPoint"]);
Product product = new Product(name, productNumber, safetyStockLevel, reOrderPoint);
lstProducts.Add(product);
}
}
catch (Exception ex)
{
throw ex;
}
return lstProducts;
}

[WebMethod]
public static List<Product> GetProductsLINQ()
{
  List<Product> lstProducts = new List<Product>();
  DataTable dtProducts = new DataTable();
  string sqlQuery = "select top 10 Name,ProductNumber,SafetyStockLevel,ReorderPoint from Production.Product";
  string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksContext"].ConnectionString;
  try
  {
     SqlConnection conn = new SqlConnection(connectionString);
     SqlDataAdapter da = new SqlDataAdapter(sqlQuery, conn);
     da.Fill(dtProducts);
     var productslinq = (from products in dtProducts.AsEnumerable()
     select new
     {
       Name = products.Field<string>("Name"),
       ProductNumber = products.Field<string>("ProductNumber"),
       SafetyStockLevel = products.Field<Int16>("SafetyStockLevel"),
       ReorderPoint = products.Field<Int16>("ReorderPoint")
     }).ToList();
     
     foreach (var product in productslinq)
     {
        lstProducts.Add(new Product(product.Name,product.ProductNumber,product.SafetyStockLevel,product.ReorderPoint));
      }
   }
   catch (Exception ex)
   {
     throw ex;
   }
   return lstProducts;
}

Thanks for reading the article. If you like this article, please subscribe.