Sunday, May 26, 2013

JSON WEBSERVICE IN ASP.NET


Here we will see how to return json from asp.net webservice and how to use in our html or aspx page.

EXPLANATION:
Json is one of the data transfer format like xml.  JSON stands for JAVA SCRIPT OBJECT NOTATION. Now a days may of the application like Google, Facebook, Twitter etc., are using JSON as their data transfer format.  As an asp.net developer you can parse this JSON both in backend coding like C# and client script like JAVASCRIPT.  Taking help of JQuery will be added advantage in parsing the json content.

By using a jquery you can easily call a webservice without any backend technology.  In this case you can create a asp.net webservice that returns json and call you can call it in both html or aspx pages.  This makes webpages lighter & faster.

PROCEDURE :
Lets create a New asp.net empty website project.  Add webservice with your desired name.  Add the following name spaces

System.Web.Script.Serialization
&
System.Web.Script.Services

Now start coding your database function that should return json instead of dataset.  Create a function with return type string and with its arguments.  Now get fill the dataset from the database and then create a multi dimensional array with the size of datatable.  Now lopp your datarows data into the each array item.  Now the data is converted from dataset to array.  Now by using JavaScriptSerializer class convert the array to a json string and return it.

Now on your ASPX or HTML page using jquery ajax call this webservice method by passing the necessary parameters into the data property.   That’s it you will get the result in the result object.

EXAMPLE EXPLANATION:
In my example I have used “northwind” database and getting the employees.  I created a webservice with a method “GetEmployees” which takes a “searchterm” as argument and returns the employees those first name match with the search term.  Below method which I have used to return employees as json

public string GetEmployees(string SearchTerm)
    {
        try
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NSConstr"].ToString());
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "SELECT e.EmployeeID,e.FirstName,e.LastName,e.Title,e.[Address]  FROM Employees e WHERE FirstName LIKE '%" + SearchTerm + "%'";
            //cmd.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.SelectCommand.Connection = con;
            da.Fill(ds);
            con.Close();
            // Create a multidimensional array
            string[][] EmpArray = new string[ds.Tables[0].Rows.Count][];
            int i = 0;
            foreach (DataRow rs in ds.Tables[0].Rows)
            {
                EmpArray[i] = new string[] { rs["FirstName"].ToString(), rs["LastName"].ToString(), rs["Title"].ToString(), rs["Address"].ToString() };
                i = i + 1;
            }

            // Return JSON data
            JavaScriptSerializer js = new JavaScriptSerializer();
            string strJSON = js.Serialize(EmpArray);
            return strJSON;
        }
        catch (Exception ex) { return errmsg(ex); }
    }

I have used this webservice in an html page to fill the employee table using jquery and the html looks as follows.

<head>
    <title></title>
    <script src="App_Scripts/jquery-1.9.1.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        function GetEmployees() {
            try {
                $.ajax({
                    type: 'POST',
                    contentType: 'application/json; charset=utf-8',
                    url: 'NorthWindService.asmx/GetEmployees',
                    data: '{ SearchTerm: "' + $("#txtSearch").val() + '" }',
                    dataType: 'json',
                    success: function (msg) {
                        //alert($("#txtSearch").val());
                        var c = eval(msg.d);
                        $("#empTable").find("tr:gt(0)").remove();
                        for (var i in c) {                           
                            $('#empTable tr:first').after('<tr><td>' + c[i][0] + '</td><td>' + c[i][1] + '</td><td>' + c[i][2] + '</td><td>' + c[i][3] + '</td></tr>');
                        }
                    }
                });
            }
            catch (e) { alert(e); }
        }
        $(document).ready(function () {
            GetEmployees();
        });
    </script>
</head>
<body>
    <input type="text" id="txtSearch" onkeyup="GetEmployees();" />
    <table id="empTable">
        <tr>
            <th>
                FirstName
            </th>
            <th>
                LastName
            </th>
            <th>
                Title
            </th>
            <th>
                Address
            </th>
        </tr>
    </table>
</body>

I have given a link above of a sample project.  Kindly check out that for your reference with a northwind database.


Kindly, let me know if any queries.