PDA

View Full Version : Json datastore from asp.net webservice



tblack1100
8 Jul 2010, 8:13 AM
I've just installed Ext JS Designer and am attempting the creation of a grid displaying results from Json datastore. Could someone give me a step-by-step on how to setup a json datastore, which accesses an asp.net webservice?
I tried to create a datastore by setting the url to: http://localhost/employee_listing.asmx/GetAllEmployees, which works just fine in my browser but I get a load error when I attempt the "load data". Also, if I use another webservice that requires an input parameter, where do I set that parameter?


Thanks

jarrednicholls
8 Jul 2010, 12:17 PM
Hi tblack1100,

Is your webservice returning a valid JSON string? Can you paste your JSON response here, and I can walk you through the proper configuration of your JsonStore for it.

Thanks,
Jarred

tblack1100
8 Jul 2010, 1:00 PM
Hi Jarred,
Thanks for responding to my issue so quickly!

Below is the Json content returned from my web service. I can view it in all the browsers (IE, FireFox, Opera, Chrome, Safari), but the "Load Data" function in the Ext JS Designer just gives me an error. I think this might have more to do with the datastore properties set incorrectly.


<?xml version="1.0" encoding="utf-8" ?>
<string xmlns="http://tempuri.org/">{"0":{"fullname":"Sabin, Michael","lastname":"Sabin","nickname":"Michael","firstname":"Michael","middlename":"Glenn","title":"Director Decision Support","workphone":"74411","deptname":"Decision Support"},"1":{"fullname":"Sacotte, Lauren","lastname":"Sacotte","nickname":"Lauren","firstname":"Lauren","middlename":"Elizabeth","title":"LPN Physician Office","workphone":"71000","deptname":"Clinic Operations"},"2":{"fullname":"Sage, Brittney","lastname":"Sage","nickname":"Brittney","firstname":"Brittney","middlename":"Nicole","title":"Assistant Medical","workphone":"387-3121","deptname":"Family Medicine - Newcastle"},"3":{"fullname":"Salazar, Mikie","lastname":"Salazar","nickname":"Mikie","firstname":"Mikie","middlename":"Schree","title":"Registered Nurse PRN II","workphone":"","deptname":"Moore - Emergency Department"},"4":{"fullname":"Salcido, John","lastname":"Salcido","nickname":"John","firstname":"John","middlename":"Raymond","title":"Therapist Respiratory RRT PRN Flat Rate","workphone":"71795","deptname":"Cardiopulmonary Services"},...}</string>

jarrednicholls
8 Jul 2010, 1:13 PM
Hey tblack1100,

That's precisely what I expected as the output...I am a seasoned .NET developer myself and know what the traditional asmx SOAP web services return. That output is "not" valid JSON, it is XML with a single element that happens to contain a JSON string. Have you looked at WCF services as an alternative to ASMX SOAP services? You can configure a WebHttpBinding that returns true JSON. Or, you can use standard ASP.NET [MVC] and return raw JSON that way. Your output needs to look like the following (truncated for brevity):



[{"fullname":"Sabin,Michael","lastname":"Sabin","nickname":"Michael","firstname":"Michael","middlename":"Glenn","title":"Director Decision Support","workphone":"74411","deptname":"Decision Support"},...,{"fullname":"Swing,Paula","lastname":"Swing","nickname":"Paula","firstname":"Paula","middlename":"","title":"Technologist Medical","workphone":"71100","deptname":"Laboratory"}]


Also, notice that the "array" or "IEnumerable" needs to be properly JSON encoded into a JSON array, with [], not as a JSON object {} that has each index as a key -- {"0":{...}, "1":{...}} should be [{...},{...}].

Look into .NET 3.5 or 4.0, there is a native JSON serializer: http://msdn.microsoft.com/en-us/library/system.web.script.serialization.javascriptserializer.aspx. If you have no control over the service or environment...well then that's an entirely different can of worms :-)

tblack1100
8 Jul 2010, 1:43 PM
Below is the code I use to generate the content. I was under the impression this formatted the content as needed:
<WebMethod(Description:="Gets all employees (TEST FILTER APPLIED TO SHORTEN LIST)")> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
Public Function GetAllEmployees() As String

Thanks again for your assistance.


***** BEGIN employee_listing.asmx **********

<%@ WebService Language="VB" CodeBehind="~/App_Code/employee_listing.vb" Class="employee_listing" %>

***** END employee_listing.asmx **********




***** BEGIN employee_listing.vb **********

Imports System.Web
Imports System.Collections
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Web.Script.Serialization
Imports System.Web.Script.Services
Imports System.Collections.Generic
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ScriptService()> _
Public Class employee_listing
Inherits System.Web.Services.WebService
Public Sub New()
End Sub
<WebMethod(Description:="Gets all employees with a last name beginning with the specified alpha character.")> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
Public Function GetEmployeesByAlpha(ByVal alpha As String) As String
Dim cnnTemp As System.Data.SqlClient.SqlConnection
Dim cmdTemp As System.Data.SqlClient.SqlCommand
Dim sdaTemp As System.Data.SqlClient.SqlDataAdapter
Dim objDataTable As System.Data.DataTable
Dim objRow As System.Data.DataRow
Dim objColumn As System.Data.DataColumn
Dim strSQL As String
Dim objString As New StringBuilder()
Dim objSer As New JavaScriptSerializer()
Dim objResult As Dictionary(Of String, Object)
Dim objResults As New Dictionary(Of String, Object)()
Dim strJSON As String = ""
Dim index As Integer = 0
' verify sql connection active
If cnnTemp Is Nothing Then
' create sql connection
cnnTemp = New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("intraConnectionString").ConnectionString)
' open connection
cnnTemp.Open()
Else
If cnnTemp.State <> ConnectionState.Open Then
cnnTemp.Open()
End If
End If
' create a command object and call procedure
cmdTemp = New System.Data.SqlClient.SqlCommand()
With cmdTemp
.Connection = cnnTemp
.CommandText = "sp_employee_listing_byalpha"
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 60
.Parameters.Add(New System.Data.SqlClient.SqlParameter("alpha", alpha))
End With
' create adapter from stored proc and fill the datatable
sdaTemp = New System.Data.SqlClient.SqlDataAdapter(cmdTemp)
objDataTable = New System.Data.DataTable
sdaTemp.Fill(objDataTable)
If objDataTable.Rows.Count > 0 Then
For Each objRow In objDataTable.Rows
objResult = New Dictionary(Of String, Object)()
For Each objColumn In objDataTable.Columns
objResult.Add(objColumn.ColumnName, objRow(objColumn).ToString())
Next
objResults.Add(index.ToString(), objResult)
System.Math.Max(System.Threading.Interlocked.Increment(index), index - 1)
Next
strJSON = objSer.Serialize(objResults)
End If
Return strJSON
End Function

<WebMethod(Description:="Gets all employees (TEST FILTER APPLIED TO SHORTEN LIST)")> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
Public Function GetAllEmployees() As String
Dim cnnTemp As System.Data.SqlClient.SqlConnection
Dim cmdTemp As System.Data.SqlClient.SqlCommand
Dim sdaTemp As System.Data.SqlClient.SqlDataAdapter
Dim objDataTable As System.Data.DataTable
Dim objRow As System.Data.DataRow
Dim objColumn As System.Data.DataColumn
Dim strSQL As String
Dim objString As New StringBuilder()
Dim objSer As New JavaScriptSerializer()
Dim objResult As Dictionary(Of String, Object)
Dim objResults As New Dictionary(Of String, Object)()
Dim strJSON As String = ""
Dim index As Integer = 0
' verify sql connection active
If cnnTemp Is Nothing Then
' create sql connection
cnnTemp = New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("intraConnectionString").ConnectionString)
' open connection
cnnTemp.Open()
Else
If cnnTemp.State <> ConnectionState.Open Then
cnnTemp.Open()
End If
End If
' create a command object and call procedure
cmdTemp = New System.Data.SqlClient.SqlCommand()
With cmdTemp
.Connection = cnnTemp
.CommandText = "sp_employee_listing_byalpha"
.CommandType = CommandType.StoredProcedure
.CommandTimeout = 60
.Parameters.Add(New System.Data.SqlClient.SqlParameter("alpha", "S"))
End With
' create adapter from stored proc and fill the datatable
sdaTemp = New System.Data.SqlClient.SqlDataAdapter(cmdTemp)
objDataTable = New System.Data.DataTable
sdaTemp.Fill(objDataTable)
If objDataTable.Rows.Count > 0 Then
For Each objRow In objDataTable.Rows
objResult = New Dictionary(Of String, Object)()
For Each objColumn In objDataTable.Columns
objResult.Add(objColumn.ColumnName, objRow(objColumn).ToString())
Next
objResults.Add(index.ToString(), objResult)
System.Math.Max(System.Threading.Interlocked.Increment(index), index - 1)
Next
strJSON = objSer.Serialize(objResults)
End If
Return strJSON
End Function
End Class

***** END employee_listing.vb **********

jarrednicholls
8 Jul 2010, 7:01 PM
Hi tblack1100,

First thing is first: I believe you can simply return an "object" or an "IEnumerable" from those functions, instead of a "string". If I'm not mistaken, by setting the ResponseFormat to JSON, it will automatically serialize the object that's returned into JSON. What I think happens currently is the response is actually a literal JSON string, surrounded by double quotes, which isn't what you'd want.

Second: Don't put the results in a Dictionary, just add them to a List<> or something and return the list directly. You will then get a proper JSON array when it's serialized, e.g., [{...}, {...}]

Now for the bad news: As I suspected (and confirmed http://encosia.com/2010/03/03/asmx-and-json-common-mistakes-and-misconceptions/) the Store will need to request the service using a particular Content-Type of "application/json" and the method "POST" in order to receive a raw JSON response (not JSON inside of XML).

The Designer doesn't give a way to configure the HTTP headers that are sent with the JsonStore out-of-the-box, so setting a particular Content-Type will not be possible within the Designer's UI. Likewise, the Designer currently doesn't expose the ability to deeply configure a Store's DataProxy, which is where you can explicitly set a particular HTTP method. By sending any baseParams, the method becomes POST under the hood, but it still won't help without the HTTP request's Content-Type being "application/json". We are planning to add the ability to configure a Store's DataProxy, DataReader, and DataWriter. And in the case of the HttpProxy, its Connection (where the Content-Type is set). Those features will be added in the near future, so stay tuned.

In the meantime, you can change these settings by hand in your store's subclass javascript file that is exported with your project (e.g., MyStore.js). Let me know if you'd like an example of what that would look like, I'd be happy to whip it up for you.

And of course, you could copy and paste that code to an ASP.NET endpoint (either .aspx or MVC controller action) and you can return raw JSON without needing to jump through hurdles...that is, without having to send the request as an HTTP POST with Content-Type: application/json.

tblack1100
9 Jul 2010, 5:51 AM
Yes, an example would be helpful and thanks for the link to Encosia.com. Some very useful info!!

jarrednicholls
9 Jul 2010, 7:09 AM
Here is an example of modifying the exported store code by hand. Note: when you export your project, your store files are always overwritten - so, after you modify it by hand, you should make a copy of the file.



MyStore = Ext.extend(Ext.data.JsonStore, {
constructor: function(cfg) {
cfg = cfg || {};
MyStore.superclass.constructor.call(this, Ext.apply({
storeId: 'MyStore',
idProperty: 'workphone',
autoLoad: true,
fields: [
{
name: 'fullname'
},
{
name: 'lastname'
},
{
name: 'nickname'
},
{
name: 'firstname'
},
{
name: 'middlename'
},
{
name: 'title'
},
{
name: 'workphone'
},
{
name: 'deptname'
}
],

// Below this line is added by hand, the above was generated by the Designer.
// Notice I am creating an explicit Ext.data.HttpProxy with its own Ext.data.Connection
// to meet the requirements of ASMX services
proxy: new Ext.data.HttpProxy(new Ext.data.Connection({
defaultHeaders: {
'Content-Type': 'application/json'
},
method: 'POST',
url: 'http://localhost/employee_listing.asmx/GetAllEmployees'
}))
}, cfg));
}
});
new MyStore();


In this example I make the workphone the the property that's used as the unique "id" to represent each record. You could alternatively return one more field in your JSON that's the actual unique primary key of each record instead. But the important part of the example is how we've added an explicit HttpProxy + Connection to setup the proper Content-Type and HTTP method for the JsonStore. "url" is setup on the Connection instead of the Store when you do it this way (if "url" is supplied on the Store and no "proxy" is supplied, then an HttpProxy/Connection is created automatically under-the-hood...but we are supplying a "proxy" in this case).

Hope that helps!

tblack1100
9 Jul 2010, 7:40 AM
Is this the format I should return?

<?xml version="1.0" encoding="utf-8" ?>
<string xmlns="http://tempuri.org/">[{"fullname":"Sabin, Michael","lastname":"Sabin","nickname":"Michael","firstname":"Michael","middlename":"Glenn","title":"Director Decision Support","workphone":"74411","deptname":"Decision Support"},{"fullname":"Sacotte, Lauren","lastname":"Sacotte","nickname":"Lauren","firstname":"Lauren","middlename":"Elizabeth","title":"LPN Physician Office","workphone":"71000","deptname":"Clinic Operations"},{"fullname":"Sage, Brittney","lastname":"Sage","nickname":"Brittney","firstname":"Brittney","middlename":"Nicole","title":"Assistant Medical","workphone":"387-3121","deptname":"Family Medicine - Newcastle"},{"fullname":"Salazar, Mikie","lastname":"Salazar","nickname":"Mikie","firstname":"Mikie","middlename":"Schree","title":"Registered Nurse PRN II","workphone":"","deptname":"Moore - Emergency Department"},{"fullname":"Salcido, John","lastname":"Salcido","nickname":"John","firstname":"John","middlename":"Raymond","title":"Therapist Respiratory RRT PRN Flat Rate","workphone":"71795","deptname":"Cardiopulmonary Services"},{"fullname":"Salcido, Michael","lastname":"Salcido","nickname":"Michael","firstname":"Michael","middlename":"Paul","title":"Technologist Radiologic","workphone":"71600","deptname":"Imaging Services"},{"fullname":"Saltus, Natosha","lastname":"Saltus","nickname":"Natosha","firstname":"Natosha","middlename":"Renee","title":"Clerk Physician Office","workphone":"76631","deptname":"Waterview Medical Center"},{"fullname":"Salyers, Angel","lastname":"Salyers","nickname":"Angel","firstname":"Angel","middlename":"Kaye","title":"Assistant Unit","workphone":"72400","deptname":"4-Northwest (Tele/Trauma)"},{"fullname":"Samuels, Melrose","lastname":"Samuels","nickname":"Melrose","firstname":"Melrose","middlename":"Adassa","title":"Assistant Perioperative","workphone":"71200","deptname":"HealthPlex - Surgical Services"},{"fullname":"San Juan, Chie","lastname":"San Juan","nickname":"Chie","firstname":"Carmelita","middlename":"R","title":"Coordinator Food Service","workphone":"72125","deptname":"Food & Nutrition Services"},{"fullname":"San Juan, Ferdinand","lastname":"San Juan","nickname":"Ferdinand","firstname":"Ferdinand","middlename":"R","title":"Cook","workphone":"52229","deptname":"HealthPlex - Food & Nutrition Services"}]</string>

tblack1100
15 Jul 2010, 10:26 AM
I've successfully created an .aspx page that returns JSON-formatted data. Using this instead of a web service allows me to use the "load data" feature in the designer and successfully populates a grid.

jarrednicholls
15 Jul 2010, 3:29 PM
Ok that's great! Good work. Glad you have it working now.