VB.net code to create XML

[former_member537776]

I am trying to create a web service in visual studio that xcelsius can connect to. I have built the web service using visual studio and the following method:

<WebMethod()> _

Public Function GetDataFromDB() As XmlDocument

Dim errorMessage As String = ""

Dim myDatas As XmlDocument = New XmlDocument()

Dim connectionString As String = ConfigurationManager.ConnectionStrings("LocalCon").ConnectionString

Dim dbConnection As SqlConnection = Nothing

Try

dbConnection = New SqlConnection(connectionString)

dbConnection.Open()

Catch ex As Exception

errorMessage = ex.Message

End Try

If errorMessage = "" Then

Dim SQL As String = "select * From DepotMTD"

Dim GetCustomerCmd As SqlCommand = New SqlCommand(SQL, dbConnection)

Try

Dim custDA As SqlDataAdapter = New SqlDataAdapter()

custDA.SelectCommand = GetCustomerCmd

Dim custDS As Data.DataSet = New Data.DataSet("Dataset")

custDA.Fill(custDS, "Data")

myDatas.LoadXml(custDS.GetXml())

dbConnection.Close()

Catch ex As System.Exception

errorMessage = ex.Message

Finally

dbConnection.Dispose()

End Try

End If

Return myDatas

End Function

This produces the following xml:

<?xml version="1.0" encoding="utf-8" ?>

<Dataset>

<Data>

<Depot>North West</Depot>

<Turnover>21587</Turnover>

</Data>

<Data>

<Depot>North East</Depot>

<Turnover>5698</Turnover>

</Data>

<Data>

<Depot>Central</Depot>

<Turnover>16897</Turnover>

</Data>

<Data>

<Depot>South East</Depot>

<Turnover>22500</Turnover>

</Data>

<Data>

<Depot>South West</Depot>

<Turnover>23564</Turnover>

</Data>

<Data>

<Depot>Scotland</Depot>

<Turnover>25648</Turnover>

</Data>

</Dataset>

Xcelsius cannot read this what do I need to do to my code to correct this?

Any help appreciated.

Regards,

Joe

[Former Member · Accepted solution]

We experimented with web services that are built using c# and have found that Xcelcius will not consume a "dataset". Apparently the dataset type is something that only works between .net apps. Xcelcius cannot consume it or parse it into an expected table.

Similarly, you cannot send a "table" of information through the web service as an XML string. Xcelsius interprets that as one continuous sting of xml and does not parse it into a table format. (Xcelsius can accept a single value as a sting, but cannot accept a table of information passed as a sting.)

However we found that formatting your web service output as "array of sting" type works with no problems. Xcelsius can consume this xml and parses it into a table in the model as expected.

Here is an example of the web service output using the array of sting.

It is a simple table with 2 columns (one column for a company number and one column for a count of employees.)

<?xml version="1.0" encoding="utf-8" ?> 
- <ArrayOfEmployeeCount xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/">
- <EmployeeCount>
  <Company>001</Company> 
  <eCount>748</eCount> 
  </EmployeeCount>
- <EmployeeCount>
  <Company>002</Company> 
  <eCount>3296</eCount> 
  </EmployeeCount>
- <EmployeeCount>
  <Company>003</Company> 
  <eCount>13</eCount> 
  </EmployeeCount>
  </ArrayOfEmployeeCount>

This works with both the XML mapping in Excel or using the Web service connection in Xcelsius.

We've also encountered the above mentioned limitation on parameters when XML mapping in Excel. At this time, the only way i've been able to force parameters was to map to the web service with the parameter values embedded in the web service URL. (This is a problem though because there seems to be no way to dynamically change the URL as needed.)

The web service connection in Xcelsius does not suffer this limitation - however you are only allowed one instance of this connection type (ie you can only connect to one web service in your model using that connection method.)

Edited by: Blake Dodds on Oct 9, 2008 11:09 AM

Edited by: Blake Dodds on Oct 9, 2008 11:15 AM

[Former Member · Accepted solution]

Blake, would you be kind enough to post the snippet of c# code that you used to create the web service. .

I thank you in advance.

[Former Member · Accepted solution]

Below is the c# code snippet. I did not personally write the code.

A developer in my group helped set this up for me to test with xcelsius.

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{
    public Service()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    public struct EmployeeCount
    {
        public string Company;
        public string eCount;
    }

    [WebMethod]
    public EmployeeCount[] EmployeeCountByCompany()
    {

        string strSQLStatement = "SELECT  ....

(leaving out the actual sql and database connection info that would occur here)

dataAdapterPeopleSoft.Fill(dtOutput);
        connectionPeopleSoft.Close();

        EmployeeCount[] Counts = null;

        int i = 0;
        Counts = new EmployeeCount[dtOutput.Rows.Count];
        foreach (DataRow row in dtOutput.Rows)
        {
            Counts<i>.Company = row["COMPANY"].ToString();
            Counts<i>.eCount = row["EmpCount"].ToString();
            i++;
        }

Basically just getting the data from the database and filling an array with the results. Hope this is helpful.

[former_member537776]

Hi Fransisco,

Glad the code has helped, an expert in vb would probably use some other method, but this has worked for me. Depot and Turnover are declared as below, I have also declared other strings to use with other functions within my web service when I import the service into xcelsius I simply remove the strings that are not used.

Regards,

Joe

<WebService(Namespace:="http://tempuri.org/")> _

<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Public Class Service

Inherits System.Web.Services.WebService

Public Structure DepotTO

Public Depot As String

Public Turnover As String

Public Total As String

Public User As String

Public Jobs As String

End Structure

<WebMethod()> _

Public Function XDepotMTD() As DepotTO()

etc.

[former_member537776]

Hi,

I would like to thank Francisco Reyes and Blake Dodds for their help and support in providing the answer to my original question. In particular Blakeu2019s code snippet which although not in vb has helped push me in the right direction. I have reproduced the code so that anyone else who is looking for a vb method can benefit as well.

Regards,

Joe

<WebMethod()> _

Public Function XDepotMTD() As DepotTO()

Dim myDepotTO As New List(Of DepotTO)

Dim Counts() As DepotTO = Nothing

Dim conString As String = ConfigurationManager.ConnectionStrings("YourConnection").ConnectionString

Dim SQL As String = "Your SQL"

Dim data_adapter As SqlDataAdapter

data_adapter = New SqlDataAdapter(SQL, _

conString)

Dim dt As New DataTable

data_adapter.Fill(dt)

data_adapter.Dispose()

Counts = New DepotTO(dt.Rows.Count) {}

Dim i As Integer = 0

For i = 0 To dt.Rows.Count - 1

Counts(i).Depot = dt.Rows(i).Item("Depot").ToString

Counts(i).Turnover = dt.Rows(i).Item("Turnover").ToString

myDepotTO.Add(Counts(i))

Next i

Return myDepotTO.ToArray

End Function

[Former Member]

Joe thanks for the vb translation. I was using a datareader, but your way of using a datatable (thanks Blake) -- requires less code.

Edited by: Francisco Reyes on Oct 13, 2008 7:41 AM

[Former Member]

I'm stuck in the same exact spot as you. .

I read that Xcelsius will only consume single schema WS.. , so I was trying to find an example of one. . I was hoping to see an example of a Flynet single schema XML extract to see what the difference was. .

I also tried to use "web query" and have Excel consume the web service, and it works, however, I'm unable to pass any parameters and I the get stuck. .

I assume someone out there has accomplished this in .NET, otherwise the only alternative is to use Flynet or QaaWS, which we don't own...

Good luck, if I reach success I will post the results here.

Thanks.

[Former Member]

Hi,

Xcelsius looks at the WSDL to see what the return structure is so you cannot just return XML from your WS (as that is a string that would need additional XML parsing) so it expects simple structure and data types like String, Number or Array of String or Array of Number, that type of thing.

Here is a sample of a WS that returns multiple rows: http://www.webservicex.net/WeatherForecast.asmx?WSDL

I'm not sure what the code looks like for that as I have never created a WS with code.

If you can use an ASP/JSP page then you can return XML in the Xcelsius friendly XML format (via the XML Data connection), to see an example of the XML for XML Data refer to the built in help (Go into the Help, Index and search for XML Data).

Regards,

Matt

[Former Member]

I imported the WSDL, then I placed a "Spreasheet Table" to display the Output area's at run time, but but no data appears . . On the Input Values I see the name PlaceName under GetWeatherByPlaceName.

First of all how do I chose between the PlaceName and the Zip Code, since both methods should be available ?

I added a Read From: Reference of $A$1 for the PlaceName. . . and for the Output I select the WeatherData Folder and I then add the "Insert IN" Reference of $C$1 Thorugh $I$1 for the 6 columns of data it's supposed to return. . .

I set it to refresh every 5 seconds, and I see the hourglass show up when it refreshes but no data appears.

What am I doing wrong ?

If I do this directly in Excel it works.

http://www.webservicex.net/WeatherForecast.asmx?WSDL

[Former Member]

Ok, I have the GetWeatherByPlaceName Method working . . I'm still unsure why the second GetWeatherByZipCode Method does not appear as an avilable method in the Input Values window.

[Former Member]

Hi Francisco,

I can see both of the Web Service methods, they are in the (Method:) drop down box below the WSDL URL.

When you pick a Web Service method the list of Inputs and Outputs gets updated.

Regards,

Matt

[Former Member]

Thanks Matt, based on your advice I can see both methods...

Now the trick is to convert my xml from something that looks like this :

<?xml version="1.0" encoding="utf-8" ?>

- <NewDataSet>

- <FPA_Data>

<PLANT>332</PLANT>

<PROD>00001HTGML1</PROD>

<SALES>4610130.25</SALES>

<VOLUME>2404703.7230</VOLUME>

</FPA_Data>

To something that resembles the WeatherForecast output (see below) . I'm surprised that there aren't any VS.NET samples to do this . . .

<?xml version="1.0" encoding="utf-8" ?>

- <WeatherForecasts xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns="http://www.webservicex.net">

<Latitude>33.97309</Latitude>

<Longitude>118.247894</Longitude>

<AllocationFactor>0.001742</AllocationFactor>

<FipsCode>06</FipsCode>

<PlaceName>LOS ANGELES</PlaceName>

<StateCode>CA</StateCode>

- <Details>

- <WeatherData>

<Day>Wednesday, October 08, 2008</Day>

<WeatherImage>http://forecast.weather.gov/images/wtf/nskc.jpg</WeatherImage>

<MaxTemperatureF>95</MaxTemperatureF>

<MinTemperatureF>64</MinTemperatureF>

<MaxTemperatureC>35</MaxTemperatureC>

<MinTemperatureC>18</MinTemperatureC>

</WeatherData>