on 10-06-2008 9:32 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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>
User | Count |
---|---|
86 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.