Using Microsoft WebService technology to realize remote database access using Web services to share the same database between different sites

Source: Internet
Author: User
Tags soap tostring web services visual studio wsdl
Web|web Services | data | database | microsoft | Site with the release of the Microsoft Visual Studo.net Beta release, with Visual Studio.NET's strong support for XML and Web services, the use of visual Studio.NET development of Web services applications will be more and more convenient. This article takes a business-to-business E-commerce website as an example, introduces the concrete methods and steps of using Web services to share the same database between different sites. In this article, the client refers to the party that uses the Web service, which means the other side that provides the Web service.

The question of the proposed

The website is a (a) E-commerce website that engages in the business of selling mobile phone SIM cards online. Not long ago, the website with another website (referred to as B) to jointly carry out online sales unicom mobile phone SIM card business. Because all is the use of a website number resources, access is a website database, so the author uses WebService technology for another Web site developed an online card system.

Modules and key codes for each major function

1. The database uses the SQL SERVER2000, and uses the stored procedure to realize the paging display of the number browsing. The code is as follows:
CREATE PROCEDURE Fenye
(
@pagenow int,
@pagesize int,
@cityid int,
@code char (3),
@recordcount int Output
)
As
SET NOCOUNT ON

declare @allid int, @beginid int, @endid int, @pagebegin char (one), @pageend char (11)

Select @allid =count (*) from Jinan where cityid= @cityid and (code like @code + '% ')
Select @recordcount = @allid

Declare cur_fastread cursor Scroll for
SELECT code from Jinan where cityid= @cityid and (code like @code + '% ") Order by code

Open Cur_fastread
Select @beginid = (@pagenow-1) * @pagesize +1
Select @endid = @beginid + @pagesize-1

Fetch absolute @beginid from Cur_fastread to @pagebegin

If @endid > @allid
Fetch last from Cur_fastread into @pageend
Else
Fetch absolute @endid from Cur_fastread to @pageend

SET NOCOUNT OFF

Select Code,cost,status from Jinan join Xuanhaofei on Jinan.category=xuanhaofei.category and jinan.cityid= Xuanhaofei.cityid
Where code between @pagebegin and @pageend order by code

Close Cur_fastread
Deallocate Cur_fastread

Go

2. Create webservice with Visual Studio.NET. In Visual studo.net, the extension of the WebService file is. asmx. The file is placed on a Web site for other Web sites to invoke.
* Start Visual Studio.NET and select New project.
* Select Visual C # projects in the left-hand version and select ASP.net WebService in the right version.
* Click on the OK button to generate a WebService project. Create a new WebService file in your project, Webservice1.asmx. This file implements access to the database and outputs a string to the caller.

The following is the code for the file:

WebService1.asmx.cs

Using System;
Using System.Collections;
Using System.ComponentModel;
Using System.Data;
Using System.Data.SqlClient;
Using System.Configuration;
Using System.Text;
Using System.Diagnostics;
Using System.Web;
Using System.Web.Services;

Namespace WebService1
{
public class Service2:System.Web.Services.WebService
{
SqlConnection con;

Public Service2 ()
{
Codegen:this call are required by the ASP.net Web Services Designer
InitializeComponent ();
}

The [WebMethod]//[webmethod] Property declares that this method can be invoked by a remote consumer as a Web service
public string table (int pagenow,int Cityid)
{
Total number of int recordcount;//
int page=0;//Total Pages
int j=0;

SqlDataReader D=getcode (Pagenow,cityid,out RecordCount);

if (recordcount%39==0)
{
page=recordcount/39;//shows only 39 numbers per page

}
Else
{
page=recordcount/39+1;
}

StringBuilder str=new StringBuilder ("<table border= ' 1 ' width= ' 100% ' bordercolorlight= ' 00008B ' bordercolordark= ') Fffff0 ' cellspacing= ' 0 ' cellpadding= ' 0 ' height= ' ><tr> ');

for (int i=0;i<3;i++)
{
Str. Append ("<td bgcolor= ' #f0f8ff ' align= ' middle ' height= ' 0 ' valign= ' center ' >");
Str. Append ("<p style= ' margin-bottom:2px ' ><font size= ' 2 ' > Number </font></p></td>");
Str. Append ("<td bgcolor= ' #f0f8ff ' align= ' middle ' height= ' 0 ' valign= ' center ' >");
Str. Append ("<font size= ' 2 ' >" </font></td><td bgcolor= ' #f0f8ff ' align= ' Middle ' height= ', ' 0 ' valign= ') Center ' > </td> ');
}

Str. Append ("</tr><tr>");

while (D.read ())
{
Str. Append ("<td height= ' align= ' Middle ' ><font size= ' 2 ' >");
Str. Append (d["code"). ToString ());
Str. Append ("</td><td height= ' align= ' Middle ' ><font size= ' 2 ' >");
Str. Append (d["cost"). ToString ());
Str. Append ("</td>");

if (d["status"). ToString (). TrimEnd ()) = = "Booked")
{
Str. Append ("<td height= ' align= ' middle ' >");
Str. Append ("<input type= ' image ' name= ' image ' src= ' http://www.163design.net/n/e/images/hand.gif ' >");
Str. Append ("</td>");
}
Else
{
Str. Append ("<td height= ' align= ' middle ' >");
Str. Append ("<input type= ' image ' name= ' image ' src= ' http://www.163design.net/n/e/images/cart.jpg ') size=2> altogether <b > ");
Str. Append (RecordCount);
Str. Append ("</b> number <b>39</b> number/page </font>");

Return str. ToString ();
}

Private SqlDataReader getcode (int pagenow,int cityid,out int RecordCount)
{
SqlDataReader Dr=null;
Con=new SqlConnection ("server=localhost;database=yitong;uid=sa;pwd=");


SqlCommand cmd=new SqlCommand ("Fenye", con);

Cmd.commandtype=commandtype.storedprocedure;

Cmd. Parameters.Add (New SqlParameter ("@pagenow", SqlDbType.Int));
Cmd. parameters["@pagenow"]. value=pagenow;//Current Page

Cmd. Parameters.Add (New SqlParameter ("@pagesize", SqlDbType.Int));
Cmd. parameters["@pagesize"]. value=39;//number of numbers to display per page

Cmd. Parameters.Add (New SqlParameter ("@cityid", SqlDbType.Int));
Cmd. parameters["@cityid"]. value=cityid;//City Code

Cmd. Parameters.Add (New SqlParameter ("@code", sqldbtype.char,3));
Cmd. parameters["@code"]. Value= "130";/only search Unicom's mobile phone number

SqlParameter q;
Q=cmd. Parameters.Add (New SqlParameter ("@recordcount", SqlDbType.Int));
Q.direction=parameterdirection.output;

Con. Open ();

Cmd. ExecuteNonQuery ();
recordcount= (int) cmd. parameters["@recordcount"]. Total number of numbers returned by value;//

Dr=cmd. ExecuteReader ();

Return Dr;
}
}
}

3. The client page is stored on the B site. When the customer browses the website, this page can browse and order the number in the a website database. The client page uses Microsoft's WebService behavior technology to invoke Web services on a. WebService behavior is a new technology that Microsoft has added in IE5.0 to use Web services through page scripting. She uses the SOAP protocol to communicate with a Web service to dynamically update parts of the page without refreshing the entire page, which is faster and more efficient than the whole page refresh method that is typically used. To use this technology, download a WEBSERVICE.HTC component from the Microsoft Web site to the directory where the client page resides.

The code for the client page is as follows:
Client.htm

<title></title>
<script language= "JavaScript" >
<!--
function Window_onload () {
Invoke the Web service provided by a
Service.useservice ("Http://IPofA/service1.asmx?WSDL", "Myselect");
Invokes the Web Service's table method, displaying the first page
Service.myselect.callService (ShowCode, "table", 1,city.value);
}

function City_onchange () {
Service.service1.callService (ShowCode, "table", 1,city.value);
}

function Addcart (ID)
{
url = "basket.asp?code=" + ID;
Window.navigate (URL);
}

function Next (x)
{
Show Next Page
Service.myselect.callService (ShowCode, "table", X,city.value)
}

function A ()
{
Show Home
Service.myselect.callService (ShowCode, "table", 1,city.value);
}

function Previous (x)
{
Show Previous Page
Service.myselect.callService (ShowCode, "table", X,city.value);
}

function last (x)
{
Show last Page
Service.myselect.callService (ShowCode, "table", X,city.value);
}

function ShowCode (Result)
{
Result saves the results returned after invoking the Web service
Service.innerhtml=result.value;
}

-->
</script>
<body onload= "return Window_onload ()" >
<select language= "javascript" name= "City" onchange= "return City_onchange ()" >
<option value= "531" selected> Shandong Jinan </option>
<option value= "537" > Shandong jining </option> <option value= "546" > Shandong dongying </option>
</select>
<div id= "service" style= "Behavior:url (WEBSERVICE.HTC)" >
</div>
</body>
As you can see, WebService behavior enables a static page to use a Web service through a scripting program and does not have to create an agent on the client, just copy a WEBSERVICE.HTC component.
With Visual Studio.NET, you can also develop and use Web services without having to understand the underlying protocols such as HTTP, XML, SOAP, and WSDL, which is really cool.


Photo: (This is the page that I displayed when I debug the machine for reference)


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.