Skip to Content
avatar image
Former Member

VB.net code to create XML

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 09, 2008 at 04:07 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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.

  • avatar image
    Former Member
    Oct 06, 2008 at 09:30 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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" ?>

      - 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>

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

      <MaxTemperatureF>95</MaxTemperatureF>

      <MinTemperatureF>64</MinTemperatureF>

      <MaxTemperatureC>35</MaxTemperatureC>

      <MinTemperatureC>18</MinTemperatureC>

      </WeatherData>

  • avatar image
    Former Member
    Oct 11, 2008 at 04:08 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Oct 13, 2008 at 10:25 AM

    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

    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.

    Add comment
    10|10000 characters needed characters exceeded