ASP.NET กับ Web Service การเขียนเว็บเซอร์วิสรับ-ส่งข้อมูลจาก Database ผ่าน DataSet และ DataTable

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

' 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 getCustomerDetail
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function DetailCustomer(ByVal strCusID As String) As DataTable
        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 [CustomerID] = '" & strCusID & "' ")

        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

        Return dt

    End Function

End Class

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.ComponentModel;
using System.Data.SqlClient;

// 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 getCustomerDetail : System.Web.Services.WebService
{

	[WebMethod()]
	public DataTable DetailCustomer(string strCusID)
	{
		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 [CustomerID] = '" + strCusID + "' ");

		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;

		return dt;

	}

}

<?xml version="1.0" encoding="utf-8"?>
<DataTable xmlns="http://tempuri.org/">
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="Table">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="CustomerID" type="xs:string" minOccurs="0" />
                <xs:element name="Name" type="xs:string" minOccurs="0" />
                <xs:element name="Email" type="xs:string" minOccurs="0" />
                <xs:element name="CountryCode" type="xs:string" minOccurs="0" />
                <xs:element name="Budget" type="xs:double" minOccurs="0" />
                <xs:element name="Used" type="xs:double" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet xmlns="">
      <Table diffgr:id="Table1" msdata:rowOrder="0">
        <CustomerID>C001</CustomerID>
        <Name>Win Weerachai</Name>
        <Email>[email protected]</Email>
        <CountryCode>TH</CountryCode>
        <Budget>1000000</Budget>
        <Used>600000</Used>
      </Table>
    </NewDataSet>
  </diffgr:diffgram>
</DataTable>

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

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub


    Protected Sub btnGet_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnGet.Click

        Dim myCusDetail As New cusDetail.getCustomerDetail
        Dim dt As DataTable = myCusDetail.DetailCustomer(Me.txtCusID.Text)
        If dt.Rows.Count > 0 Then
            Me.lblCustomerID.Text = dt.Rows(0)("CustomerID").ToString()
            Me.lblName.Text = dt.Rows(0)("Name").ToString()
            Me.lblEmail.Text = dt.Rows(0)("Email").ToString()
            Me.lblCountryCode.Text = dt.Rows(0)("CountryCode").ToString()
            Me.lblBudget.Text = dt.Rows(0)("Budget").ToString()
            Me.lblUsed.Text = dt.Rows(0)("Used").ToString()
        End If

    End Sub

End Class

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
public partial class _Default : System.Web.UI.Page
{

	protected void btnGet_Click(object sender, EventArgs e)
	{
		cusDetail.getCustomerDetail myCusDetail = new cusDetail.getCustomerDetail();
		DataTable dt = myCusDetail.DetailCustomer(this.txtCusID.Text);
		if (dt.Rows.Count > 0) {
			this.lblCustomerID.Text = (string)dt.Rows[0]["CustomerID"];
			this.lblName.Text = (string)dt.Rows[0]["Name"];
			this.lblEmail.Text = (string)dt.Rows[0]["Email"];
			this.lblCountryCode.Text = (string)dt.Rows[0]["CountryCode"];
			this.lblBudget.Text = (string)dt.Rows[0]["Budget"].ToString();
			this.lblUsed.Text = (string)dt.Rows[0]["Used"].ToString();
		}

	}

}

Public Class Form1

    Private Sub btnGet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGet.Click
        Dim myCusDetail As New cusDetail.getCustomerDetailSoapClient
        Dim dt As DataTable = myCusDetail.DetailCustomer(Me.txtCusID.Text)
        If dt.Rows.Count > 0 Then
            Me.lblCustomerID.Text = dt.Rows(0)("CustomerID").ToString()
            Me.lblName.Text = dt.Rows(0)("Name").ToString()
            Me.lblEmail.Text = dt.Rows(0)("Email").ToString()
            Me.lblCountryCode.Text = dt.Rows(0)("CountryCode").ToString()
            Me.lblBudget.Text = dt.Rows(0)("Budget").ToString()
            Me.lblUsed.Text = dt.Rows(0)("Used").ToString()
        End If
    End Sub

End Class

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
public class Form1
{

	private void btnGet_Click(System.Object sender, System.EventArgs e)
	{
		cusDetail.getCustomerDetailSoapClient myCusDetail = new cusDetail.getCustomerDetailSoapClient();
		DataTable dt = myCusDetail.DetailCustomer(this.txtCusID.Text);
		if (dt.Rows.Count > 0) {
			this.lblCustomerID.Text = (string)dt.Rows[0]["CustomerID"];
			this.lblName.Text = (string)dt.Rows[0]["Name"];
			this.lblEmail.Text = (string)dt.Rows[0]["Email"];
			this.lblCountryCode.Text = (string)dt.Rows[0]["CountryCode"];
			this.lblBudget.Text = (string)dt.Rows[0]["Budget"].ToString();
			this.lblUsed.Text = (string)dt.Rows[0]["Used"].ToString();
		}
	}

}