ASP.NET กับ JSON และการรับ-ส่งข้อมูล JSON ผ่าน Web Service (VB.NET , C#)

USE [mydatabase]
GO
/****** Object: Table [dbo].[customer] Script Date: 05/01/2012 16:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[customer](
[CustomerID] [varchar](4) NOT NULL,
[Name] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[CountryCode] [varchar](2) NULL,
[Budget] [float] NULL,
[Used] [float] NULL,
CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED 
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO customer VALUES ('C001', 'Win Weerachai', '[email protected]', 'TH', 1000000, 600000);
INSERT INTO customer VALUES ('C002', 'John Smith', '[email protected]', 'EN', 2000000, 800000);
INSERT INTO customer VALUES ('C003', 'Jame Born', '[email protected]', 'US', 3000000, 600000);
INSERT INTO customer VALUES ('C004', 'Chalee Angel', '[email protected]', 'US', 4000000, 100000);

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports Newtonsoft.Json

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class getCustomer
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function resultCustomer(ByVal strCountry As String) As String
        Dim objConn As New SqlConnection
        Dim objCmd As New SqlCommand
        Dim dtAdapter As New SqlDataAdapter

        Dim ds As New DataSet
        Dim dt As DataTable
        Dim strConnString As String
        Dim strSQL As New StringBuilder

        strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"

        strSQL.Append(" SELECT * FROM customer ")
        strSQL.Append(" WHERE CountryCode LIKE '%" & strCountry & "%' ")

        objConn.ConnectionString = strConnString
        With objCmd
            .Connection = objConn
            .CommandText = strSQL.ToString()
            .CommandType = CommandType.Text
        End With
        dtAdapter.SelectCommand = objCmd

        dtAdapter.Fill(ds)
        dt = ds.Tables(0)

        dtAdapter = Nothing
        objConn.Close()
        objConn = Nothing

        Dim json = JsonConvert.SerializeObject(dt, Formatting.Indented)
        Return json

    End Function

End Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Newtonsoft.Json;

namespace WebService_Server
{
    /// <summary>
    /// Summary description for getCustomer
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class getCustomer : System.Web.Services.WebService
    {

        [WebMethod]
        public string resultCustomer(string strCountry)
        {
            SqlConnection objConn = new SqlConnection();
            SqlCommand objCmd = new SqlCommand();
            SqlDataAdapter dtAdapter = new SqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt = null;
            string strConnString = null;
            StringBuilder strSQL = new StringBuilder();

            strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;";

            strSQL.Append(" SELECT * FROM customer ");
            strSQL.Append(" WHERE CountryCode LIKE '%" + strCountry + "%' ");

            objConn.ConnectionString = strConnString;
            var _with1 = objCmd;
            _with1.Connection = objConn;
            _with1.CommandText = strSQL.ToString();
            _with1.CommandType = CommandType.Text;
            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds);
            dt = ds.Tables[0];

            dtAdapter = null;
            objConn.Close();
            objConn = null;

            string json = JsonConvert.SerializeObject(dt, Formatting.Indented);
            return json;

        }

    }
}

Imports System.Data
Imports Newtonsoft.Json

Partial Public Class _Default
    Inherits System.Web.UI.Page

    Public Function DerializeDataTable(ByVal strJSON As String) As DataTable
        Dim dt As DataTable
        dt = JsonConvert.DeserializeObject(Of DataTable)(strJSON)
        Return dt
    End Function

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click
        Dim cls As New clsGetCustomer.getCustomer
        Dim strJSON = cls.resultCustomer(Me.txtCountry.Text)

        GridView1.DataSource = DerializeDataTable(strJSON)
        GridView1.DataBind()
    End Sub

End Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Newtonsoft.Json;

namespace WebService_Client
{
    public partial class _Default : System.Web.UI.Page
    {
	    public DataTable DerializeDataTable(string strJSON)
	    {
		    DataTable dt = null;
		    dt = JsonConvert.DeserializeObject<DataTable>(strJSON);
		    return dt;
	    }

	    protected void btnSearch_Click(object sender, EventArgs e)
	    {
		    clsGetCustomer.getCustomer cls = new clsGetCustomer.getCustomer();
		    string strJSON = cls.resultCustomer(this.txtCountry.Text).ToString();

		    GridView1.DataSource = DerializeDataTable(strJSON);
		    GridView1.DataBind();
	    }
    }
}

<html>
<head>
<title>ThaiCreate.Com</title>
</head>
<body>

<form name="frmMain" method="post" action="">
	  <h2>Search Customer</h2>
	  Search by Country Code 
	  <input type="text" name="txtCountry" value="<?=$_POST["txtCountry"];?>">
	  <input type="submit" name="Submit" value="Submit">
</form>

<?
			if($_POST["txtCountry"] != "")
			{
					include("lib/nusoap.php");
					$client = new nusoap_client("http://localhost:5377/getCustomer.asmx?wsdl",true); 
					$params = array(
							   'strCountry' => $_POST["txtCountry"]
					);
					$data = $client->call('resultCustomer', $params);
					//echo '<pre>';
					//var_dump(json_decode($data["resultCustomerResult"]));
					//echo '</pre><hr />';

					$mydata = json_decode($data["resultCustomerResult"],true); // json decode from web service


					if(count($mydata) == 0)
					{
							echo "Not found data!";
					}
					else
					{
						?>
								<table width="500" border="1">
								  <tr>
									<td>CustomerID</td>
									<td>Name</td>
									<td>Email</td>
									<td>CountryCode</td>
									<td>Budget</td>
									<td>Used</td>
								  </tr>
								<?
								foreach ($mydata as $result) {
								?>
									  <tr>
										<td><?=$result["CustomerID"];?></td>
										<td><?=$result["Name"];?></td>
										<td><?=$result["Email"];?></td>
										<td><?=$result["CountryCode"];?></td>
										<td><?=$result["Budget"];?></td>
										<td><?=$result["Used"];?></td>
									  </tr>
								<?
								}
					}

			}
?>
</body>
</html>