12 Replies - 9487 Views - Last Post: 26 April 2011 - 05:44 AM Rate Topic: -----

#1 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 62
  • Joined: 07-April 11

Web service querying database and XML formatting

Posted 26 April 2011 - 04:23 AM

Hey everyone. My very first webservice actually works. It returns data from an Informix database. The problem is the XML isn't formatted. There are no "nodes" I think is the term. It's just one big long string of data. I think it has to do with it being a string, but I'm not sure how to fix the problem. Someone here suggested making an array and defining the nodes in my code, but I have no idea how to do that. Here's my complete code:

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.Odbc;
using System.Xml;
using System.Xml.Serialization;

namespace GetCMSData
{
    /// <summary>
    /// Summary description for GetCMSWebService
    /// </summary>
    [WebService(
        Namespace = "http://tempuri.org/",
        Description = "Returns daily data from the dsplit table.")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class GetCMSWebService : System.Web.Services.WebService
    {

        OdbcConnection con = new OdbcConnection();
        OdbcDataAdapter da;
        DataSet ds;

        string conCMS = "Dsn=CMS;Driver={OpenLink Generic ODBC Driver};Host=10.1.1.201:5000;Database=CMS;UID=root;PWD=cms500";

        [WebMethod]
        public string getSplit(string split)
        {
            ds = new DataSet();
            con.ConnectionString = conCMS;
            con.Open();

            string strSQL = "SELECT row_date, split, callsoffered " +
                            "FROM dsplit " +
                            "WHERE row_date Between '4/1/2011' AND '4/22/2011' AND split = '" + split + "'";

            da = new OdbcDataAdapter(strSQL, con);
            da.Fill(ds, "CallData");
          
            return ds.GetXml();
        }           
    }
}


And here's the returned data:

<?xml version="1.0" encoding="utf-8" ?> 
  <string xmlns="http://tempuri.org/"><NewDataSet> <CallData> <row_date>2011-04-01T00:00:00-04:00</row_date> <split>170</split> <callsoffered>578</callsoffered> </CallData> <CallData> <row_date>2011-04-02T00:00:00-04:00</row_date> <split>170</split> <callsoffered>59</callsoffered> </CallData> <CallData> <row_date>2011-04-04T00:00:00-04:00</row_date> <split>170</split> <callsoffered>697</callsoffered> </CallData> <CallData> <row_date>2011-04-05T00:00:00-04:00</row_date> <split>170</split> <callsoffered>598</callsoffered> </CallData> <CallData> <row_date>2011-04-06T00:00:00-04:00</row_date> <split>170</split> <callsoffered>598</callsoffered> </CallData> <CallData> <row_date>2011-04-07T00:00:00-04:00</row_date> <split>170</split> <callsoffered>457</callsoffered> </CallData> <CallData> <row_date>2011-04-08T00:00:00-04:00</row_date> <split>170</split> <callsoffered>519</callsoffered> </CallData> <CallData> <row_date>2011-04-09T00:00:00-04:00</row_date> <split>170</split> <callsoffered>62</callsoffered> </CallData> <CallData> <row_date>2011-04-11T00:00:00-04:00</row_date> <split>170</split> <callsoffered>669</callsoffered> </CallData> <CallData> <row_date>2011-04-12T00:00:00-04:00</row_date> <split>170</split> <callsoffered>576</callsoffered> </CallData> <CallData> <row_date>2011-04-13T00:00:00-04:00</row_date> <split>170</split> <callsoffered>511</callsoffered> </CallData> <CallData> <row_date>2011-04-14T00:00:00-04:00</row_date> <split>170</split> <callsoffered>520</callsoffered> </CallData> <CallData> <row_date>2011-04-15T00:00:00-04:00</row_date> <split>170</split> <callsoffered>628</callsoffered> </CallData> <CallData> <row_date>2011-04-16T00:00:00-04:00</row_date> <split>170</split> <callsoffered>47</callsoffered> </CallData> <CallData> <row_date>2011-04-18T00:00:00-04:00</row_date> <split>170</split> <callsoffered>664</callsoffered> </CallData> <CallData> <row_date>2011-04-19T00:00:00-04:00</row_date> <split>170</split> <callsoffered>474</callsoffered> </CallData> <CallData> <row_date>2011-04-20T00:00:00-04:00</row_date> <split>170</split> <callsoffered>456</callsoffered> </CallData> <CallData> <row_date>2011-04-21T00:00:00-04:00</row_date> <split>170</split> <callsoffered>407</callsoffered> </CallData> <CallData> <row_date>2011-04-22T00:00:00-04:00</row_date> <split>170</split> <callsoffered>406</callsoffered> </CallData> </NewDataSet></string>



Any suggestions?

Thanks!!

Dave

Is This A Good Question/Topic? 0
  • +

Replies To: Web service querying database and XML formatting

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 04:40 AM

The XML looks fine, it's just not formatted with line breaks so it looks wrong. What problems are you having with it? Can you load it into an XDocument or XmlDocument?

Here is what the data would looks like when it's formatted...

<?xml version="1.0" encoding="utf-8" ?> 
  <string xmlns="http://tempuri.org/">
	  <NewDataSet> 
		<CallData> 
			<row_date>2011-04-01T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>578</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-02T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>59</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-04T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>697</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-05T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>598</callsoffered> 
		</CallData>
		<CallData> 
			<row_date>2011-04-06T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>598</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-07T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>457</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-08T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>519</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-09T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>62</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-11T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>669</callsoffered> 
		</CallData>
		<CallData> 
			<row_date>2011-04-12T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>576</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-13T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>511</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-14T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>520</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-15T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>628</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-16T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>47</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-18T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>664</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-19T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>474</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-20T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>456</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-21T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>407</callsoffered> 
		</CallData> 
		<CallData> 
			<row_date>2011-04-22T00:00:00-04:00</row_date> 
			<split>170</split> 
			<callsoffered>406</callsoffered> 
		</CallData> 
	</NewDataSet>
</string>

Was This Post Helpful? 0
  • +
  • -

#3 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 62
  • Joined: 07-April 11

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 04:43 AM

When I call the service and put the data in a datagrid...it's one big long cell full of data...I use this to get the data:

        protected void Button1_Click(object sender, EventArgs e)
        {
            myStart = tbStartDate.Text.ToString();
            myEnd = tbStartDate.Text.ToString();
            mySplit = tbSplit.Text.ToString();

            GetCMSWebServiceSoapClient PhoneData = new GetCMSWebServiceSoapClient();

            string PhoneCalls = PhoneData.getSplit(mySplit);

            GridView1.AutoGenerateColumns = true;
            GridView1.DataSource = PhoneCalls;
            GridView1.DataBind();
        }


Thanks,

Dave
Was This Post Helpful? 0
  • +
  • -

#4 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 04:51 AM

And that should be fine. Are you getting an exception? Is it not working? Is it not showing what you think it should show? Tell us what is ACTUALLY happening that you think is wrong.
Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 05:02 AM

I loaded it into in XDocument without errors.
Was This Post Helpful? 0
  • +
  • -

#6 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 62
  • Joined: 07-April 11

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 05:06 AM

Well, I'm querying a database and returning 3 columns and a bunch of rows of data. The gridview takes all 3 columns and all the rows and shoves it into a single column single cell. So 1 cell contains:

4/1/20111705784/2/2011170594/3/20111706974/4/20111705984/5/20111705984/6/2011170457





That's supposed to be:

Date       Split   Callsoffered
4/1/2001   170     578
4/2/2011   170     94
4/3/2011   170     697
4/4/2011   170     598
4/5/2011   170     598
4/6/2011   170     457


Thanks for the help!

Dave

This post has been edited by davers: 26 April 2011 - 05:08 AM

Was This Post Helpful? 0
  • +
  • -

#7 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 05:11 AM

So you are trying to take the XML data and bind it directly to a GridView? Is this ASP.Net?
Was This Post Helpful? 0
  • +
  • -

#8 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 62
  • Joined: 07-April 11

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 05:17 AM

Hey eclipsed.. Yes it is. I have a webpage that I've added my service reference to and I'm trying to return the data to a gridview.

On a side note, I thought XML was supposed to have the little minus signs on the left to collapse the various nodes? All others I've seen have that. It's not there on mine....

This post has been edited by davers: 26 April 2011 - 05:21 AM

Was This Post Helpful? 0
  • +
  • -

#9 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 05:24 AM

Try this...

protected void Button1_Click(object sender, EventArgs e)
{
    myStart = tbStartDate.Text.ToString();
    myEnd = tbStartDate.Text.ToString();
    mySplit = tbSplit.Text.ToString();

    GetCMSWebServiceSoapClient PhoneData = new GetCMSWebServiceSoapClient();

    string PhoneCalls = PhoneData.getSplit(mySplit);

    DataSet myDataSet = new DataSet();
    myDataSet.ReadXml(new StringReader(PhoneCalls));

    GridView1.AutoGenerateColumns = true;
    GridView1.DataSource = myDataSet;
    GridView1.DataBind();
}



That is untested. See if it works.

View Postdavers, on 26 April 2011 - 08:17 AM, said:

On a side note, I thought XML was supposed to have the little minus signs on the left to collapse the various nodes? All others I've seen have that. It's not there on mine....


Those are simply there because the application you are using can determine that it's XML. I took the formatted version of your string and put it in Notepad++, and it showed up with the minuses.
Was This Post Helpful? 0
  • +
  • -

#10 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 62
  • Joined: 07-April 11

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 05:25 AM

Oh...and one other thing. I have a webservice I connect to that someone else created, and I have a class I created that puts the data into an array. i.e.
CallInfo[] PhoneCalls = CTIData.GetAllCallsMadeByEmployeeOnDate(numCID, Dt);


When I try that with my service:

CallInfo[] PhoneCalls = PhoneData.getSplit(myStart, myEnd, mySplit);
I get the error: Connot implicitly convert type 'string' to 'GetCMSData.CallInfo[]'

Thanks again!!

Dave
Was This Post Helpful? 0
  • +
  • -

#11 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 05:32 AM

Just tested and this code should work...

protected void Button1_Click(object sender, EventArgs e)
{
    myStart = tbStartDate.Text.ToString();
    myEnd = tbStartDate.Text.ToString();
    mySplit = tbSplit.Text.ToString();

    GetCMSWebServiceSoapClient PhoneData = new GetCMSWebServiceSoapClient();

    string PhoneCalls = PhoneData.getSplit(mySplit);

    DataSet myDataSet = new DataSet();
    myDataSet.ReadXml(new StringReader(PhoneCalls));

    GridView1.AutoGenerateColumns = true;
    GridView1.DataSource = myDataSet.Tables[1].DefaultView;
    GridView1.DataBind();
}



View Postdavers, on 26 April 2011 - 08:25 AM, said:

Oh...and one other thing. I have a webservice I connect to that someone else created, and I have a class I created that puts the data into an array. i.e.
CallInfo[] PhoneCalls = CTIData.GetAllCallsMadeByEmployeeOnDate(numCID, Dt);


When I try that with my service:

CallInfo[] PhoneCalls = PhoneData.getSplit(myStart, myEnd, mySplit);
I get the error: Connot implicitly convert type 'string' to 'GetCMSData.CallInfo[]'

Thanks again!!

Dave


You are returning a string, but you are trying to assign it to an array. Doesn't work like that.
Was This Post Helpful? 0
  • +
  • -

#12 davers  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 62
  • Joined: 07-April 11

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 05:39 AM

Hey again eclipsed... That's my point!! :helpsmilie: My webservice is returning the same data the other web service is returning, yet mine is returning a string! The "problem" is in my Web service. Even though it's returning data...it's not formatted correctly! When I "invoke" the other webservice, I get back XML formatted correctly. When I "invoke" my webservice, I get a big string back...I don't have access to look at the other webservice to see how they did theirs, I'm building mine as an exercise.

Thanks again for everyone's help..I appreciate it!

Dave
Was This Post Helpful? 0
  • +
  • -

#13 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1533
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Web service querying database and XML formatting

Posted 26 April 2011 - 05:44 AM

View Postdavers, on 26 April 2011 - 08:39 AM, said:

Hey again eclipsed... That's my point!! :helpsmilie: My webservice is returning the same data the other web service is returning, yet mine is returning a string! The "problem" is in my Web service. Even though it's returning data...it's not formatted correctly! When I "invoke" the other webservice, I get back XML formatted correctly. When I "invoke" my webservice, I get a big string back...I don't have access to look at the other webservice to see how they did theirs, I'm building mine as an exercise.

Thanks again for everyone's help..I appreciate it!

Dave


But the webservices don't return the same. You are returning a string of XML. Going by the return type of the other web method, looks like it's returning an array of strongly-typed objects(CallData). This array doesn't seem to be XML. Therefore, it's not the same.

There is nothing wrong with the XML. Does it look pretty? No. But that doesn't change much. It will still be parsed as XML.

The code that I posted works.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1