none
XML returned from web method RRS feed

  • Question

  •  

    I have created a web service that returns data in order to create a report.  This method has a return data type of DataTable.  The data is sent from the web service method to the report as XML.

    I am using the Enterprise Library Data Access to retrieve data from the database.

     

    In Query1, I make a single query to the database.  The results are sent from the web method to Reporting Services.  A report is created. 

    Query1

        [WebMethod]
        public DataTable GetData(string ProjectID) {

            object[] paramArray = new object[1];
            paramArray[0] = Convert.ToInt32(ProjectID);

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCmd = db.GetStoredProcCommand(@"[GetReportData]", paramArray);
            DataSet ds = db.ExecuteDataSet(dbCmd);
            DataTable dt = ds.Tables[0];

            return dt;

        }

     

    These are the partial results returned from the web method.

      <?xml version="1.0" encoding="utf-8" ?>
    - <DataTable xmlns="http://tempuri.org/">
    - <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    - <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:Locale="">
    - <xs:complexType>
    - <xs:choice minOccurs="0" maxOccurs="unbounded">
    - <xs:element name="Table">
    - <xs:complexType>
    - <xs:sequence>
      <xs:element name="OrgCode" type="xs:string" minOccurs="0" />
      <xs:element name="DepartmentName" type="xs:string" minOccurs="0" />
      <xs:element name="ProjectID" type="xs:string" minOccurs="0" />
      <xs:element name="ProjectTitle" type="xs:string" minOccurs="0" />
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:choice>
      </xs:complexType>
      </xs:element>
      </xs:schema>
    - <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    - <NewDataSet xmlns="">
    - <Table diffgr:id="Table1" msdata:rowOrder="0">
      <OrgCode>1</OrgCode>
      <DepartmentName>Department1</DepartmentName>

     

    But if I make multiple queries to the database, merge the results into one table and
     and send the data back to create a report, a report is not created.

     

    In Query2, multiple queries are made to the database. The results are merged into one table and the data sent from the web method to Reporting Services.  But no report is created. 

     

    Query2

        [WebMethod]
        public DataTable GetData(string ProjectID) {

            DataTable tableOfCodes = getCodes(ProjectID);

            DataTable tableOfAllProjects = new DataTable();
            DataTableReader dataTableReader = tableOfCodes.CreateDataReader();

            DataTable dt;

            while (dataTableReader.Read()) {
                object[] paramArray = new object[1];
                paramArray[0] = Convert.ToInt32(dataTableReader[0]);

                Database db = DatabaseFactory.CreateDatabase();
                DbCommand dbCmd = db.GetStoredProcCommand(@"[GetReportData]", paramArray);
                DataSet ds = db.ExecuteDataSet(dbCmd);
                dt = ds.Tables[0];
                tableOfAllProjects.Merge(dt);
            }

            return tableOfAllProjects;

        }


    These are the partial results returned from the web method.

      <?xml version="1.0" encoding="utf-8" ?>
    - <DataTable xmlns="http://tempuri.org/">
    - <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    - <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:UseCurrentLocale="true">
    - <xs:complexType>
    - <xs:choice minOccurs="0" maxOccurs="unbounded">
    - <xs:element name="Table">
    - <xs:complexType>
    - <xs:sequence>
      <xs:element name="OrgCode" type="xs:string" minOccurs="0" />
      <xs:element name="DepartmentName" type="xs:string" minOccurs="0" />
      <xs:element name="ProjectID" type="xs:string" minOccurs="0" />
      <xs:element name="ProjectTitle" type="xs:string" minOccurs="0" />
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:choice>
      </xs:complexType>
      </xs:element>
      </xs:schema>
    - <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    - <DocumentElement xmlns="">
    - <Table diffgr:id="Table1" msdata:rowOrder="0">
      <OrgCode>1</OrgCode>
      <DepartmentName>Department1</DepartmentName>

     

     I noticed differences in the results returned by the web method between Query1 and Query2. 

    In Query1, the msdata:Locale="" whereas in Query2 msdata:UseCurrentLocale="true".

    In Query1, the first element under <diffgr:diffgram> is <NewDataSet xmlns="">.

    Whereas in Query2 the first element under <diffgr:diffgram> is <DocumentElement xmlns="">.

    Query results with the tag name  <NewDataSet xmlns=""> allow a report to be created.  How does 

    merging tables together affect the XML that is returned by the web method?

    I think that if the tag <DocumentElement xmlns=""> where named <NewDataSet xmlns=""> the
    code would run fine.  But I do not know how to change the tag name.


    Could someone help me with this?

    thanks

    I don't know if this is an XML or an ADO.NET question.

    • Moved by Xianfeng Zhang Saturday, September 25, 2010 6:34 AM It's a question about DataTable. (From:XML and the .NET Framework)
    Tuesday, September 21, 2010 9:20 PM

All replies

  • Give a name to the table "tableOfCodes"...  It should resolve your problem..!

    like: tableOfCodes.TableName = "CodesTable";


    - <BooM>
    • Edited by BoovendanM Friday, September 16, 2011 7:32 AM with sample code
    Friday, September 16, 2011 7:31 AM