To convert a DataTable into JSON in C# is really very easy. Is there any class or method in .Net which can help us to directly do it, then answer is No. We need to write a small utility class or method which can help to convert any datatable into json easily.What we are going to do is, create a dictionary to keep column name and column values and then create the list of all the columns and values and serialize this list will give the required result. It will be clear when we will write the code, so lets start:
Create function to convert DataTable into jSon string
public String ConvertDataTableTojSonString(DataTable dataTable)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer =
new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<String, Object>> tableRows = new List<Dictionary<String, Object>>();
Dictionary<String, Object> row;
foreach (DataRow dr in dataTable.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dataTable.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
tableRows.Add(row);
}
return serializer.Serialize(tableRows);
}
This is only the code which takes DataTable as parameter and returns jSon string, Now create method to get records from database into DataTable to convert into jSon. We are going to write the code which will open the connection and read all the records into DataReader and load records by using DataTable load method. I tested it and it is really very very fast, see the code
public DataTable GetDataTable()
{
DataTable dataTable = new DataTable();
using (SqlConnection conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["dbConString"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Customer_Search";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@City", txtCity.Text);
if (conn.State != ConnectionState.Open)
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dataTable.Load(dr);
}
return dataTable;
}
Finally add a button and it's click event to call and see the result in action
protected void Button1_Click(object sender, EventArgs e)
{
DataTable dataTable = GetDataTable();
String jSonString = ConvertDataTableTojSonString(dataTable);
}
I already used this code in my project and it works for every datatable and I never face any problem of any kind. Wasn't it so easy and powerful, enjoy the coding.
Convert DataTable to List in C#
If you want to convert your DataTable to list then it is more than easy, just one line of code will do all the work
IEnumerable<DataRow> dtList = dataTable.AsEnumerable();
or
List<DataRow> dtList = dataTable.AsEnumerable().ToList();
![]() |
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|
By Ali Adravi On 09 Jun, 13 Viewed: 30,966 |
In this article we will see how to convert an object to json string or how to convert a json string to an object in C#, we will try to write a generic class with two methods, one for converting an object to json and other for converting json string to an object. I searched internet to get if... By Ali Adravi On 16 Jun 2013 Viewed: 41,182
Convert XML or JSON into a class by using visual studio is as easy as just copy and two clicks, never matter how big or how complicated is our XML or JSON. In this article we will create some dummy XML and json and will try to convert them into class without writing a single property manually. It... By Ali Adravi On 20 Dec 2014 Viewed: 2,058
In MVC, autocomplete with jquery is the best way to pull data from database and render according to our requirements, so in this article we will use jquery to show the auto complete text box without any ajax controls. We need a method to get the data from database, a controller method to handle the... By Ali Adravi On 29 Jun 2013 Viewed: 6,997
Is it possible to perform a linq query on datatable, answer is no because datatable’s row collection, DataRowCollection does not implement IEnumerable<T> so we cannot perform linq query on it. But if there is any way to convert our datatable data row collection into IEnumerable, then surely we can.... By Charles Fuller On 25 Jun 2013 Viewed: 3,899
Most of the developer use DataAdapter to fill the DataSet and then take the first table from the data set, which is quite slow, why can't we use DataReader to read the data and use datatable's Load method to load the reader into it, in this article we will see how we can load a table faster and... By Alicia Gonzalez On 09 Jun 2013 Viewed: 13,801