C# ASP.Net Web Service, JSON and Jqgrid

Posted by beakersoft | Posted in Programming | Posted on 29-07-2011

1

This week i have been writing a web service in C# that i wanted to use to send data back to a jqrid, and as its been a bit of an uphill battle i thought i’d share what i had found out.

What you Need

First head over to the jqgid page, and download the package.Include all of the base options, and the UI addons to take advantage of the UI theme roller. I would also advise installing the Firebug extension into Firefox, as this can really help you see the interaction between the grid and the web service. Once you have done this, fire up Visual Studio, create a new web project and add a web service (asmx) file, and a web form to your project.

The Web Service

This is where is starts to get interesting, trying to get your web service to return JSON instead of XML. I assume its by design, but even when you change the response format to json as below –

ScriptMethod(ResponseFormat = ResponseFormat.Json)

When you call the web service, it still wraps it in xml tags (for instance, it you return a sting then the response is in a <string> tag), so we need to use a different approach.

Instead of returning a type from your web service, we are going to have no return type, and use the Response.Write functionality to just send text (our json) back to the browser, so in your asmx file you will have a public facing web service that looks something like this –

[WebMethod]
[ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
public void GetJSONData(int IDNo){

        string JSONToReturn = GetData(intID);
        Response.Write(JSONToReturn);
        Response.End;

}

Now we just need to create our function to give us the data back, formated as a JSON string, that we can feed back into our jQgrid. Before you start creating your JSON, its a good idea to have a validitor to hand, that way you should be able to catch any issues before you try and create your grid. I used one at http://jsonlint.com/ but there are quite a few out there.

Next, we are going to create a very simple class for holding our data in ready to parse into JSON. My example is based on the information you want being in a SQL DB but it could be comming from anywhere. Anyway, here’s the class –

private class MyJSONInfo
{
public MyJSONInfo()
{
        int ID = 0;
        string UserName = string.Empty;
        string EmailAddress = string.Empty;
        string WelcomeMessage = string.Empty;
}

        public int ID { get; set; }
        public string UserName { get; set; }
        public string EmailAddress { get; set; }
        public string WelcomeMessage { get; set; }
}

Now we have a class to hold our data in, we need to popluate it. All we are going to do now is run an sql command to get our data, then put that data into our MyJSONInfo class

SqlConnection myConnection = new SqlConnection("[YOUR DB CONNECTION]");
try
{
myConnection.Open();
}
catch (Exception e)
{
Context.Response.Write("Problem opening DB - " + e.Message);
Context.Response.End();
}
SqlCommand myEntriesCmd = new SqlCommand("SELECT * FROM TABLE1", myConnection);
SqlDataReader myEntriesData = myEntriesCmd .ExecuteReader();
int IDCounter = 1;
var EntriesList = new Collection();

//got some data back
if (myEntriesData.HasRows)
{
while (myEntriesData.Read())
{
EntriesList.Add(new HubInfo {ID = IDCounter, UserName = myEntriesData["UserName"].ToString().Trim(),
        EmailAddress = myEntriesData["EmailAddress"].ToString().Trim(),
        WelcomeMessage = myEntriesData["WelcomeMessage "].ToString().Trim()});
        IDCounter++;
}
}

Almost there now, all we need is information about how rows we are returning, what page number we want to show and how many pages of information we are dealing with. These are all used by the jQgrid to aid its navigation, you will have to work these number out correctly based on your setup. Here we are just going to use dummy numbers.

So, we are going to create an object with these values, along with our MyJSONInfo ready to parse into JSON –

var JsonToReturn = new
{
        total = 5,           //total number of pages in our grid
        page = 1             //page to show in the grid,
        records = IDCounter, //how many records we are dealing with
        rows = EntriesList,  //our actual data
};

Now all that is left to do here is parse our variable into a JSON string. To do that we are going to use the Javascript Serilizer that comes with dotnet4, so make sure you include this at the top of your code –

using System.Web.Script.Serialization;

Once we have that in place, we are ready to create our JSON string. Here we do just that, and then flush it out to the browser –

//now serialize the list to json
var jsonSerializer = new JavaScriptSerializer();
string JsonString = jsonSerializer.Serialize(JsonToReturn);

//return the json
Context.Response.Write(JsonString);
Context.Response.End();

At this point when you connect to you invoke your web service you should get JSON formated data back. As I mentioned before i would run this through a validitor before you go any further, as you will probably find you have to clean up some of the text to make it work.

The jQgrid Page

Create a new web page in your project, and add the required javascript and css to your page header so you can use the jQgid, it is important to put them in this order, i’m not sure why but i have had issues in the past with the order of the scripts –

<script type="text/javascript" src="Scripts/jquery-1.5.1.min.js"></script><script type="text/javascript" src="Scripts/grid.locale-en.js"></script>
<script type="text/javascript" src="Scripts/jquery-ui-1.8.14.custom.min.js"></script><script type="text/javascript" src="Scripts/jquery.jqGrid.min.js"></script>

Now you have these, we can create the jQgrid on the page.

<script type="text/javascript">
jQuery(document).ready(function () {
        jQuery("#datalist").jqGrid({
        url: 'HubReader.asmx/GetJSONData
        datatype: 'json',
        contentType: 'application/json; charset=utf-8',
        type: 'POST',
        colNames: ['User', 'Email', 'Message'],
        colModel: [
        { name: 'UserName', index: 'UserName', width: 13, align: "left" },
        { name: 'EmailAddress', index: 'EmailAddress', width: 8, align: "left" },
        { name: 'Started', index: 'WelcomeMessage', width: 11, align: "left" }
        ],
        //Map the reader as we are using standard JSON format, not the standard jqgrid format
        jsonReader: {
        page: "page",
        total: "total",
        records: "records",
        root: "rows",
        repeatitems: false,
        id: "ID"
        },
        pager: '#pager1',
        altRows: true,
        altclass:'myAltRowClass',
        rowNum: 15,
        rowList: [15, 30, 45],
        height: 500,
        width: 1050,
        caption: "Search Results",
        scrollOffset:2,
        viewrecords: true,
        sortorder: "desc",
        });
        jQuery("#datalist").jqGrid('navGrid', '#pager1', { edit: false, add: false, del: false,search: false });
});
</script>

If you have used jQgrid before, most of this should be familiar to you, if not then you should probably have a quick read up on their site about how the grids are created and populated. Once you have built the grid, dont forget to create the elements the grid and pager will live in –

        <table id="datalist" cellpadding="0" cellspacing="0"></table>  
        <div id="pager1"></div> 

Whats happening in the code, well most of the javascript for building the grid is pretty standard, the only bit you might have not seen before is the jsonReader section. As we are not sending the json back in the default format jQgrid understands, we need to tell the grid a little bit about what elements contain what data. This needs to match the info comming out of your JSON. So for example if you have called the section containing the total number of pages TotPages (instead of total like i have) you will have to change the line to –

        total: "TotPages",

more info about the jQgrid data format options can be found at http://www.trirand.com/jqgridwiki/doku.php?id=wiki:retrieving_data

Also make sure you change your url to point to your web service asmx file.

Hopefully your should now be able to get a jQgrid populating with data from an asmx web service. If your are struggling Firebug is probably your best bet, you can tell right away by switching to its console tag what problems you are having, it will even show you the data your service is sending back.

Folder2Page Plugin

Posted by beakersoft | Posted in Programming | Posted on 11-02-2010

1

Just a quick post to point you in the direction of my latest (ok second ever!) plugin for wordpress. Its called folder2page, you can check out more info at beakersoft.co.uk/folder2page

Its a simple plugin that will allow you to use a directory of images you have on your web space as a simple gallery on page in your blog. There seemed to be a lot of plugins out there for getting images from flickr and other image hosting services, but not much if you wanted to get at the images your self.

Any comments, suggestions etc please let me know!

WordSlice – My First WordPress plugin

Posted by beakersoft | Posted in Programming | Posted on 15-06-2009

0

I while ago I wrote an artical (here)about the new Web slice feature in IE8. A few people have asked about a plugin for WordPress, and the other night I finally decided to have a go.

Head over to http://www.beakersoft.co.uk/wordslice for all the info, or to http://wordpress.org/extend/plugins/wordslice/ for the download and the install notes.

Hopefully in the not to distant future i will be add some extra features to it (its kind of limited at the moment) so stay tuned!

Creating a Check for update Routine

Posted by beakersoft | Posted in Programming | Posted on 07-10-2008

1

First off, i apologise for not blogging for a while, ive had quite a few other projects on over the summer, plus holidays so I’ve not really had time. Hopefully now I should be able to get back into it with a bit more frequency.

Anyway on to the topic of this post. Most applications nowadays will automaticly go onto the web and check to see if there is a new version, and if there is offer to download/install/visit page etc. I was looking for a simple way to do this using the dotnet framework and Visual Basic.Net, and came across the System.Net class.

This class is extremely powerful, as it contains functions for network communication.  The main one we will use here is the System.net.WebClient. We are going to use this to pull a file down of our web server that will give us information about the latest version of our application.

Information File

So first of all, create a text file, we are going to look on our web server for this file and read information. In the file we will have the following info: App name, version and download location, delimited by a pipe (|) character. You could also add extra things like new features, release date etc to this file. The example file will look something like this.

1.1.0|TestApp|www.beakersoft.co.uk/downloads/

Once you have this file, save it as something like TestApp_Ver_Control.txt, and upload it to your web server.

Downloading the Version information

Now the file containing version information is on your web server, we can write a function in vb that will open the file up, get the information contained in it and use that information to decided if our application is the latest version or not. This is where the System.net.WebClient comes in. First of declare a couple of variables. One is the WebClient, one is the url of the file you have just uploaded and a string array to hold the details of the file.

Dim myWebClient As New Net.WebClient
Dim RemoteUri As String = "www.beakersoft.co.uk/TestApp_Ver_Control.txt"
Dim strFileInfo()As String

Now, we can go and get the file of the web server, using the webclient and read the contents into a string. Then we can split the content of the string up using the pipe char, and there you have all the info you need to check the version.

All the code is wrapped up in a try/catch block to make sure we dont get any unhandled errors such as when there is no net connection, file missing etc.

	Try
	Dim file As New System.IO.StreamReader( _
                    myWebClient.OpenRead(RemoteUri))
        Dim Contents As String = file.ReadToEnd()

	'Split up the sections of the text based on the pipe (|) delimiator
	strFileInfo = Split(Contents, "|")

	'see if we need a new version
	If strFileInfo(0) > Application.ProductVersion Then
		'if we have a new version, make sure the app name matchs from the file
                If strFileInfo(1) = application.ProductName Then

			'we have a new version! So throw up a message, set a flag ect

                End If
	End If

	'close the file stream and web client
	myWebClient = Nothing
	file.Close()

        Catch ex As Exception
	If InStr(ex.Message, "(404)") Then
		'404 means file not found on webserver
                Msgbox("Problem Finding the update information file - " & vbNewLine & ex.Message & _
                        vbNewLine & "Please contact the appliaction vendor")
	Else
                Msgbox("Problem getting update information - " & vbNewLine & ex.Message & _
                        vbNewLine & "Check your internet settings and try again")
	End If
End Try

And thats pritty much it! This is a very bare bones way of doing it, and you probably want to add in support for using a proxy (you can do that using the System.Net.WebRequest.DefaultWebProxy class, and pass login credentials to myWebClient.Credentials using Net.NetworkCredential ) but as a very basic form this should work.