Humpy Humpy - 7 months ago 10
Javascript Question

Serialize List in a WebService to Json and pass to ajax on load

I am trying to pass a list that is populated by the database in a Web Service to ajax using JSON. However, I'm not sure what I'm missing and where to go from here.


Web Service


public class RetrieveWidgets : System.Web.Services.WebService
{
[WebMethod]
public static RetrieveWidgetsDAL[] GetWidgets()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dboCao"].ConnectionString);

List<RetrieveWidgetsDAL> GetWidgetList = new List<RetrieveWidgetsDAL>();

int getUserId;
string userName = HttpContext.Current.User.Identity.Name;

conn.Open();
using (SqlCommand cmdGetUserId = new SqlCommand("SELECT UserId FROM tUser WHERE UserName = @UserName", conn))
{
cmdGetUserId.Parameters.AddWithValue("@UserName", userName);
getUserId = Convert.ToInt32(cmdGetUserId.ExecuteScalar());

System.Diagnostics.Debug.Write(" --------------- " + getUserId + " --------------- " + userName + " ---------");
}

using (SqlCommand cmdGetWidgets = new SqlCommand("SELECT Title, SortNo, Collapsed, ColumnId FROM tWidgets WHERE UserId = @UserId", conn))
{
cmdGetWidgets.Parameters.AddWithValue("@UserId", getUserId);

using (SqlDataReader rdr = cmdGetWidgets.ExecuteReader())
{
while (rdr.Read())
{
RetrieveWidgetsDAL widgetDAL = new RetrieveWidgetsDAL();
widgetDAL.Title = rdr.GetString(0);
widgetDAL.SortNo = rdr.GetInt32(1);
widgetDAL.Collapsed = rdr.GetInt32(2);
widgetDAL.ColumnId = rdr.GetInt32(3);
GetWidgetList.Add(widgetDAL);
}
}

//trying to serialize GetWidgetList to JSON
var js = new JavaScriptSerializer();
var strJSON = js.Serialize(GetWidgetList);// not sure if this is the correct way?

}
conn.Close();

return GetWidgetList.ToArray();
}
}



Data Structure class


public class RetrieveWidgetsDAL
{
public string Title { get; set; }
public int SortNo { get; set; }
public int Collapsed { get; set; }
public int ColumnId { get; set; }
}



ajax


$(document).ready(function () {
if ($.cookie('modal_shown') == null) {
$.cookie('modal_shown', 'yes', { expires: 7, path: '/' });
$('#popup').dialog({
modal: true,
buttons: {
Ok: function () {
$(this).dialog("close");
}
}
});
$.ajax({
type: "Post",
contentType: "application/json charset=utf-8",
url: "Webservices/RetrieveWidgets.asmx/GetWidgets",
data: <---don't know how to get the data,
dataType: "json",
success: function (response) {
alert(data);
}
});
}
});


I'm new to the idea of JSON so I'm still struggling to learn it. Any help is greatly appreciated.

EDIT: This is what I receive now on error in the browser console..

enter image description here

EDIT 2: I've removed static from my webservice..

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public List<RetrieveWidgetsDAL> GetWidgets()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dboCao"].ConnectionString);
List<RetrieveWidgetsDAL> lstData = new List<RetrieveWidgetsDAL>();
int getUserId;
string userName = HttpContext.Current.User.Identity.Name;
conn.Open();
using (SqlCommand cmdGetUserId = new SqlCommand("SELECT UserId FROM tUser WHERE UserName = @UserName", conn))
{
cmdGetUserId.Parameters.AddWithValue("@UserName", userName);
getUserId = Convert.ToInt32(cmdGetUserId.ExecuteScalar());

System.Diagnostics.Debug.Write(" --------------- " + getUserId + " --------------- " + userName + " ---------");
}

using (SqlCommand cmdGetWidgets = new SqlCommand("SELECT Title, SortNo, Collapsed, ColumnId FROM tWidgets WHERE UserId = @UserId", conn))
{
cmdGetWidgets.Parameters.AddWithValue("@UserId", getUserId);
using (SqlDataReader rdr = cmdGetWidgets.ExecuteReader())
{
while (rdr.Read())
{
RetrieveWidgetsDAL widgetDAL = new RetrieveWidgetsDAL();
widgetDAL.Title = rdr.GetString(0);
widgetDAL.SortNo = rdr.GetInt32(1);
widgetDAL.Collapsed = rdr.GetInt32(2);
widgetDAL.ColumnId = rdr.GetInt32(3);
lstData.Add(widgetDAL);
}
}
}
conn.Close();
return lstData;
}


Now my console shows [object Object]

enter image description here

Edit 3: I used the solution that has the accepted answer, however.. had to fix the ajax..

contentType was missing a ;


contentType: "application/json; charset=utf-8"


and now it alerts on the success!

Answer

Try this

[WebMethod]
public static List<RetrieveWidgetsDAL> GetWidgets()
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dboCao"].ConnectionString);
    List<RetrieveWidgetsDAL> lstData= new List<RetrieveWidgetsDAL>();
    int getUserId;
    string userName = HttpContext.Current.User.Identity.Name;
    conn.Open();
    using (SqlCommand cmdGetUserId = new SqlCommand("SELECT UserId FROM tUser WHERE UserName = @UserName", conn))
            {
                cmdGetUserId.Parameters.AddWithValue("@UserName", userName);
                getUserId = Convert.ToInt32(cmdGetUserId.ExecuteScalar());

                System.Diagnostics.Debug.Write(" --------------- " + getUserId + " --------------- " + userName + " ---------");
            }

            using (SqlCommand cmdGetWidgets = new SqlCommand("SELECT Title, SortNo, Collapsed, ColumnId FROM tWidgets WHERE UserId = @UserId", conn))
            {
                cmdGetWidgets.Parameters.AddWithValue("@UserId", getUserId);
                using (SqlDataReader rdr = cmdGetWidgets.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        RetrieveWidgetsDAL widgetDAL = new RetrieveWidgetsDAL();
                        widgetDAL.Title = rdr.GetString(0);
                        widgetDAL.SortNo = rdr.GetInt32(1);
                        widgetDAL.Collapsed = rdr.GetInt32(2);
                        widgetDAL.ColumnId = rdr.GetInt32(3);
                        lstData.Add(widgetDAL);
                    }
                }
           }
            conn.Close();
           return lstData;
        }
    }

JS: On success use response.d

          $.ajax({
                type: "Post",
                contentType: "application/json charset=utf-8",
                url: "Webservices/RetrieveWidgets.asmx/GetWidgets",
                dataType: "json",
                success: function (response) {
                    alert(response.d);  // try using response.d
                },
                error:function (repo){
                      console.log(repo);
                }
            });

Blog Article : jQuery Ajax JSON Example in Asp.net with sql database