Jquery datatable Is showing no data in table on calling a web method from a simple web service

Question

Im getting no data in table on calling a simple web service method. Im trying to put the data return by server side in a jquery datatable using datatble plugin. But its giving me no data in table with table headers. The webservice method is returning me Json data but also, a string tag is added so Im not sure if its entirely Json or not. May that might be the issue.I am fairly new to this and has always worked on server side part.In my work, I deals with C# datatables a lot so I need to seralize datatable only and most of the time Im not even sure which columns the database is going to return So I cant go with any class file which represents the table structure. Is there any way to deal with that problem?? Any help is much appreciated.

<script src="Scripts/jquery-3.1.1.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css" />
<script src="//cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $.ajax({
            url: 'UserService.asmx/UserDetails',
            contentType: "application/json; charset=utf-8",
            method: 'post',
            datatype: 'json',
            success: function (data) {
                $('#tbl').dataTable({
                    data: data,
                    columns: [
                    { 'data': 'UserId' },
                    { 'data': 'UserName' },
                    { 'data': 'UserPassword' },
                    { 'data': 'RoleId' },
                    { 'data': 'Gender' },
                    { 'data': 'EmailId' },
                    { 'data': 'DateOfBirth' },
                    { 'data': 'Address' },
                    ]
                });
            },
            error: function() {
            alert('Fail!');
        }
        });
    });
</script>

and my server side webservice code is

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string UserDetails()
    {
        DataTable dt = new DataTable();
        dt.TableName = "UserDetails";
        SqlConnection conn;
        SqlCommand cmd;
        string connection = ConfigurationManager.ConnectionStrings["Connstr"].ToString();
        string command = "SELECT * FROM tbl_User";
        using (conn = new SqlConnection(connection))
        {
            cmd = new SqlCommand(command, conn);
            cmd.CommandType = CommandType.Text;
            conn.Open();
            cmd.ExecuteNonQuery();
            SqlDataAdapter da = new SqlDataAdapter(command, conn);
            da.Fill(dt);
            conn.Close();
        }                
            var js = DataTableToJSONWithJavaScriptSerializer(dt);
        return js;
    }

    public string DataTableToJSONWithJavaScriptSerializer(DataTable dt)
        {
        JavaScriptSerializer js = new JavaScriptSerializer();
        List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
        Dictionary<string, object> childRow;
            foreach (DataRow row in dt.Rows)
            {
                childRow = new Dictionary<string, object>();
                foreach (DataColumn col in dt.Columns)
                {
                    childRow.Add(col.ColumnName, row[col]);
                }
                parentRow.Add(childRow);
            }
        return js.Serialize(parentRow);
        }

The output of webservice method is in below format.

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<string xmlns="http://tempuri.org/">
[{"UserId":"XYZ","UserName":"XYX","UserPassword":"XYX@1234","RoleId":1,"Gender":"F","EmailId":"XYX@email.com","DateOfBirth":"\/Date(623874600000)\/","Address":"ABC Str. 57"},
{"UserId":"ANATR","UserName":"ANT","UserPassword":"ABC@1234","RoleId":1,"Gender":"F","EmailId":"ANT@email.com","DateOfBirth":"\/Date(-301815000000)\/","Address":"XYZ. 2222"},
{"UserId":"WOLZA","UserName":"DIS","UserPassword":"AB@1234","RoleId":2,"Gender":"M","EmailId":"DIV@email.com","DateOfBirth":"\/Date(379362600000)\/","Address":"ul. JUMP 68"}]
</string>

Answer 1

I think

How to convert datatable to json string using json.net? can help you and after serialize, in success callback use jquery.parsejson