Serialize data from database in JSON Format

Question

I am creating a web service in asp.net that serialize data in JSON Format, and access this webservice via JQuery

using System;
using System.IO;
using System.Net;
using System.Web;
using System.Linq;
using System.Web.Hosting;
using System.Web.Services;
using System.Web.Script.Services;
using System.Collections.Generic;
using System.Web.Script.Serialization;
namespace Chart_WebService
{
    /// <summary>
    /// Summary description for Service
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    [ScriptService]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service : System.Web.Services.WebService
    {
        [WebMethod]
        [ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
        public DataTable GetData()
        {
           string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
           using (SqlConnection con = new SqlConnection(constr))
           {
               using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
               {
                   using (SqlDataAdapter sda = new SqlDataAdapter())
                   {
                       cmd.Connection = con;
                       sda.SelectCommand = cmd;
                       using (DataTable dt = new DataTable())
                       {
                           dt.TableName = "Customers";
                           sda.Fill(dt);
                           return dt;
                       }
                   }
               }
           }
       }
    }
}

Please help me to complete this solution because I do not have idea to complete it Hope at you guys!

The "Please do my work" questions usually are not very welcome, if you have any doubt, ask about it, don't ask others to do your job. — Gusman, Jun 03 '15 at 08:10
What is a problem? Wrong response format? If yes, then how do you send request? — Yeldar Kurmangaliyev, Jun 03 '15 at 08:11
Agree with @Gusman here is the link that can help http://stackoverflow.com/questions/17398019/how-to-convert-datatable-to-json-in-c-sharp — Ali Umair, Jun 03 '15 at 08:18
As an aside: SQL Server 2016 is getting `FOR JSON`... http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx — Marc Gravell, Jun 03 '15 at 08:19
Consider this method to convert your `datatable` to `JSON` http://stackoverflow.com/questions/17398019/how-to-convert-datatable-to-json-in-c-sharp — Ali Umair, Jun 03 '15 at 08:22
@Ali maybe, but step 1 of any question that starts "how do I do {x} with a DataTable" is always (or at least, *should* always be) "for the love of life itself, **stop using DataTable**" — Marc Gravell, Jun 03 '15 at 08:24

score 3 · Answer 1 · edited Jun 03 '15 at 08:21

3

I would not return a DataTable. You can use raw ADO.NET and fill your result into a simple Dictionary<string, object>. With Newtonsoft.Json you can serialize the dictionary and return it.

edited Jun 03 '15 at 08:21

Marc Gravell

1,026,079
266
2,566
2,900

answered Jun 03 '15 at 08:15

BendEg

20,098
17
57
131

Yup, that would work; personally I'd probably populate a `Customer` model (via dapper or similar) just to give the serializer the extra context (and the ability to deserialize), and the ability to control the names etc a bit more flexibly, but fundamentally this would work fine. – Marc Gravell Jun 03 '15 at 08:22
Yes, a model would be nice. But i choce a Dictionary, because he used `select * from ...` in his quesiton. – BendEg Jun 03 '15 at 08:24

score 0 · Answer 2 · answered Jun 03 '15 at 08:54

Well you better use Data Reader for this solution then convert to a List , this List will be serialized to JSON, the code will look like this :

using (var conn = new SqlConnection(ConnectionString))
        {
            conn.Open();
            using (var comm = new SqlCommand(command, conn))
            {
                using (var reader = comm.ExecuteReader())
                {
                       Customers= reader.Cast<IDataRecord>().Select(x=>
                        new Customer
                        {
                            ID = (int)x["ID"],
                            name = x["name"].ToString(),

                        }).ToList();
                }
            }
        }
var json = JsonConvert.SerializeObject(Customers);

And that's it

Serialize data from database in JSON Format

2 Answers2