Figures

Figure 3 Northwind Query Results

Figure 3 Northwind Query Results

Figure 4 WSDL Output

Figure 4 WSDL Output

Figure 5 XML Results Displayed in a DataSet Grid

Figure 5 XML Results Displayed in a DataSet Grid

Figure 6 Sample Client in ASP.NET

Figure 6 Sample Client in ASP.NET

Figure 7 Parse Object Array from Web Service Calls

public static XmlElement[] GetXmlFromObjectArray(object[] oaData)
{
System.Xml.XmlElement[] oXmlResult = new System.Xml.XmlElement
  [oaData.Length];
    localhost.SqlMessage oErrorMessage; 

    for (int i = 0; i < oaData.Length; i++)
    {
        switch (oaData[i].GetType().ToString())
        {
            case "System.Xml.XmlElement":
                oXmlResult[i] = _
                  (System.Xml.XmlElement)oaData[i];
                break;
            case "WSClient.etier3.SqlMessage":
                oErrorMessage = (etier3.SqlMessage)oaData[i];
                    throw new Exception("Error - Source: " + 
                      oErrorMessage.Source + " - Message: 
                      " + oErrorMessage.Message); 
            default:
                break;
        }
    }
    return oXmlResult;
}

Figure 8 Invoke SQL Web Services and Format Data

localhost.procedures oWSProcs = null;
//int nReturnValue;
XmlElement[] oXmlResult = null; 
DataSet ds = null;
StringBuilder sOutput = new StringBuilder();

try
{
// instantiate our web service
    oWSProcs = new localhost.procedures();
                
    // call stored proc number 1
    sOutput.Append("CustOrderHist StoredProcedure Results ");
    oXmlResult = GetXmlFromObjectArray(
        oWSProcs.CustOrderHist(txtParm1.Text));        
    ds = FormatDataToBuffer("CustOrderHist", oXmlResult, sOutput);
                
    // call stored proc number 2
    sOutput.Append("OrderByCategory StoredProcedure Results ");
    oXmlResult = GetXmlFromObjectArray(
        oWSProcs.SalesByCategory(
        txtParm2.Text, txtParm3.Text));    
        ds = FormatDataToBuffer("SalesByCategory", oXmlResult, sOutput);

    // call our template
    sOutput.Append("GetAllCustomers Template Results ");
    oXmlResult = GetXmlFromObjectArray(oWSProcs.GetAllCustomers());            
    ds = FormatDataToBuffer("GetAllCustomers", oXmlResult, sOutput);
    
// show this one in the grid for fun
    dataGrid1.DataSource = ds;

    // call our UDF
    sOutput.Append("GetCustomerContactView UDF Results ");
    oXmlResult = GetXmlFromObjectArray(
        oWSProcs.GetCustomerContactView(
        txtParm1.Text));            
    ds = FormatDataToBuffer(
      "GetCustomerContactView", oXmlResult, sOutput);

    // send it to screen
txtXMLData.Text = sOutput.ToString();
}
catch (Exception err)
{
    System.Windows.Forms.MessageBox.Show(
      "Error Retrieving Results" + err.Message);
}

Figure 9 UDF for Northwind

CREATE FUNCTION GetCustomerContactView ( @CustomerID char(5) )
RETURNS @CustomerContactView TABLE
   (
        CustomerID     nchar(5),
        ContactName   nvarchar(30)
   )
AS
BEGIN
   INSERT @CustomerContactView
        SELECT CustomerID, ContactName
        FROM Customers 
        WHERE CustomerID = @CustomerID
   RETURN
END

Figure 10 Visual Basic 6.0 SOAP Client

Option Explicit
Private oSoapclient As soapclient

Private Sub cmdExecute_Click()
    Dim oReturnNodeList As IXMLDOMNodeList
    Dim oNode As IXMLDOMNode
    Dim vRetValue As Variant
    Dim sXMLOutput As String
    Dim sEndPointURL As String
    
    Set oSoapclient = New soapclient
    
    Call oSoapclient.msSOAPinit(txtWSDL.Text)
    'strip off the ?WSDL to give us our endpoint url
    sEndPointURL = Mid(txtWSDL.Text, 1, (Len(txtWSDL.Text) - 5))
    oSoapclient.ConnectorProperty("EndPointURL") = sEndPointURL
        
    Set oReturnNodeList = oSoapclient.CustOrderHist(txtParm1.Text)
    sXMLOutput = "CustOrderHist Storeprocedure "
    For Each oNode In oReturnNodeList
        sXMLOutput = sXMLOutput & oNode.xml
    Next
    
    sXMLOutput = sXMLOutput & "GetAllCustomers Template "
    Set oReturnNodeList = oSoapclient.GetAllCustomers()
    For Each oNode In oReturnNodeList
        sXMLOutput = sXMLOutput & oNode.xml
    Next
    
    sXMLOutput = sXMLOutput & "GetCustomerContactView UDF "
    Set oReturnNodeList = _
        oSoapclient.GetCustomerContactView(txtParm1.Text)
    For Each oNode In oReturnNodeList
        sXMLOutput = sXMLOutput & oNode.xml
    Next
        
    txtXML.Text = sXMLOutput
    Set oSoapclient = Nothing
End Sub

Home
Topics
Issues
Columns
Downloads
Subscribe
Contact Us

MSDN Magazine