Office documentation for XML Web services
Download the sample code for this column. Microsoft Office XP and .NET Web Services have been married, are you ready? In the B2B e-commerce network world, why not combine all business processing processes to calculate everything in the calculation, bring the powerful power of the web service to end users? What are I talking about? Oh, I am talking about an Excel spreadsheet that looks like Figure 1. Figure 1: Excel spreadsheet that has enabled Web services This is not a normal spreadsheet. It uses UDDI to find the company's address and use the directory web service to find product information. When you click the "Send" button, it also performs XML conversion to the XML spreadsheet format, generates a RosettaTaN PIP 3 A4 order application format. When you type the company name you want to purchase, click the Find button, a VBA code under the spreadsheet call UDDI and complete the rest of the address. For example, type Microsoft, click Find, you will see the following in the "Seller" domain: Figure 2: "Seller" field Type quantity, such as 23, type PEAR in the explanation, then press Tab, A VBA code will query the SOAP directory web service to see if there is a matching product and list more information. In this case, I have connected the directory web service to the Northwind database, so it will return the following information: Figure 3: Details of the spreadsheet order section In this example, it also lists the description and converts it to a connection. To detail the link to this product's HTML page. If you find multiple products but don't have a condition that you typed, you will provide a list of options. For example, if you type tofu, you will see the following options: Figure 4: Example of multiple options provided when not found full match If you select one, this will be provided. Details. After completing, click the "Send" button to generate the RosettAnet PIP 3 A4 XML order format, and the order is also issued simultaneously. How to achieve? By accessing the Tools menu, select Macro, then select "Visual Basic Editor", you can browse VBA code. Some of the code under thisworkbook respond to changes in the spreadsheet, especially when you delete the instructions, the Workbook_SheetChange event will clear a project line; when you move the "Description" domain into the "SKU" domain, Workbook_sheetselectionChange event will Call FindProduct (). If FINDPRODUCT returns to XMLNode, the relevant domain will be pulled from the node to fill another details of the project line. You can check my article UDDI: an XML web service. If you find a business, AddressLines found in the UDDI response / businessinfo / contacts / contact / address / address will be used to populate the "Seller" address block. The FindProduct function in the catalog web service Catalogs module will call the directory service URL using the URL parameters containing the search term.
It will get a SOAP response and first check if it matches / envelope / body / fault, if the return is not fault, it will continue to open
Response.ContentType = "text / xml";
String Term = Request.QueryString ["Term"];
IF (term! = null) {
CatalogSearch S = New CatalogSearch (TERM);
S.Execute (OUTPUT);
} else {
Response.write ("
}
%>
Really fun will start from the Execute method. It is a very simple SQL managed provider code bundled in XMLTextWriter, which is able to return a specific domain from the SQL SELECT statement. Therefore, it is basically a while loop written to XmlTextWriter to XMLTextWriter: Public Void Execute (TextWriter STM)
{
XMLTextWriter XW = New XMLTextWriter (STM);
Xw.WriteStartElement ("envelope", "http://schemas";
Xw.WriteStartElement ("Body", "http://schemas"; ");
Try {
String const = "server = localhost; uid = sa; pwd =; database = northwind";
SqlConnection Con = New SQLCONNECTION (Constr);
C.Open ();
IDataReader Reader;
String query = "SELECT ProductName, Unitprice, QuantityPerunit,"
"SupplierID, Productid from Products Where"
"ProductName Like '%" Term "%'";
Sqlcommand cmd = new SQLCOMMAND (Query, Con);
Cmd.execute (Out Reader);
String funnamespace = "URN: Schemas-B2B-Fun: Catalogs";
Xw.WriteStartElement ("CatalogQueryResult", FunnameSpace;
While (Reader.Read ())
{
xw.writestartElement ("item"); xw.writeAttribute ("ProductName", Reader.getstring (0));
xw.writeattrDecimal ("Unitprice", Reader.getDecimal (1));
Xw.writeAttribute ("UnitOfMeasure", Reader.getstring (2));
Xw.writeAttribute ("SKU", "S" Reader.GetInt32 (3)
"-P" reader.getinT32 (4));
Xw.writeEndelement ();
}
Xw.writeEndelement ();
C. close ();
} catch (exception e) {
xw.writestartElement ("fault");
Xw.writeElementstring ("Faultcode", "500");
Xw.WriteElementstring ("Faultstring", E.TOString ());
Xw.writeEndelement ();
}
Xw.writeEndelement ();
Xw.writeEndelement ();
xw.close ();
}
URL http://localhost/catalog/search.aspx? Term = Tofu will return the following results:
UnitOfMeasure = "40 - 100 grams per package" SKU = "S6-P14" /> UnitOfMeasure = "5 kg per package" SKU = "S4-P74" /> This may be the most effective way to use the .NET framework from SQL Server to XML. Rough calculations, you can get 80 to 90 such XMLs per second on my Dell PowerEdge 2400. The "Send" button Sendorder () function loads an XML document in the XML representation of the selected range of cells in the spreadsheet. This can be achieved by the following magical VBA code line: with activesheet Set sourceXml = new msxml2.domdocument SourceXml.LoadXml .Range ("B1: N34"). Value (XLRANGEVALUEXMLSPREADSHEET) End with This will return a huge XML block, completely explaining all the information about the cells in the selected range in the spreadsheet. The following is the code snippet of the XML block:
