Solved

Consuming a C# Web Service’s ADO.NET Dataset in Delphi

Posted on 2004-09-30
5
977 Views
Last Modified: 2010-04-05
I am trying to consume with Delphi an ADO.NET dataset provided by a C# web service. I seem to running into trouble when it comes to having Delphi’s XMLMapper take the XML from the c# web service and map the transformation out properly, it is only returning the column headers, with no data… I am posting the code to the web service.

[WebMethod]
public DataSet GetDS(string sInput)
{
      ds = new DataSet();

      oOracleConn = new OracleConnection();

      sSQL = string.format("select * from table where table.input = {0}", sInput); \\ I simplified this

      oOracleConn.ConnectionString = "User Id=****;Password=********;Data Source=****";

      oDataAdapter = new OracleDataAdapter(sSQL, oOracleConn);

      oOracleConn.Open();
      oDataAdapter.Fill(ds);
      oOracleConn.Close();

      return ds;
}

Please let me know if there are alternatives to this method, along with source code. The end result is for a C# Web Service ADO.NET dataset to be consumed by Delphi 7 and displayed in a data grid. I was not sure where to post this question, so I posted it in C# and Delphi, and whoever can answer my question first, can have points for both posts.

Regards,

Joel
0
Comment
Question by:j_oller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
bpana earned 500 total points
ID: 12198715
I think I have already answered a similar question. take a look here:
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21046672.html

Bogdan
0
 

Author Comment

by:j_oller
ID: 12201707
Thanks Bogdan,

If my C# web service pushed XML out like the XML on your post, I would not have a problem. I will paste a little more code to illustrate my problem.

C# Webservice:

            [WebMethod]
            public DataSet GetDS2()
            {
          DataTable dt = new DataTable();
            dt.Columns.Add("ID", System.Type.GetType("System.Int32"));
          dt.Columns.Add("Data", System.Type.GetType("System.String"));
            dt.Columns.Add("CreateDate", System.Type.GetType("System.DateTime"));

            DataRow dr;

            dr = dt.NewRow();
            dr["ID"] = 1;
            dr["Data"] = "First Row";
            dr["CreateDate"] = System.DateTime.Now;
          dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr["ID"] = 2;
            dr["Data"] = "Second Row";
            dr["CreateDate"] = System.DateTime.Now;
            dt.Rows.Add(dr);

            DataSet ds2 = new DataSet("SampleDataSet");
            ds2.Tables.Add(dt);
            return ds2;
            }

Pushes this XML, which Delphi's XML Mapper will not map with dataset:

<?xml version="1.0" encoding="utf-8"?>
<DataSet xmlns="http://tempuri.org/">
  <xs:schema id="SampleDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="SampleDataSet" msdata:IsDataSet="true">
      <xs:complexType>
        <xs:choice maxOccurs="unbounded">
          <xs:element name="Table1">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="ID" type="xs:int" minOccurs="0" />
                <xs:element name="Data" type="xs:string" minOccurs="0" />
                <xs:element name="CreateDate" type="xs:dateTime" 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">
    <SampleDataSet xmlns="">
      <Table1 diffgr:id="Table11" msdata:rowOrder="0" diffgr:hasChanges="inserted">
        <ID>1</ID>
        <Data>First Row</Data>
        <CreateDate>2004-10-01T09:07:03.9186134-07:00</CreateDate>
      </Table1>
      <Table1 diffgr:id="Table12" msdata:rowOrder="1" diffgr:hasChanges="inserted">
        <ID>2</ID>
        <Data>Second Row</Data>
        <CreateDate>2004-10-01T09:07:03.9186134-07:00</CreateDate>
      </Table1>
    </SampleDataSet>
  </diffgr:diffgram>
</DataSet>

The XML needs to look like this instead (I got this from C# Windows Application -> ds2.WriteXml("c:\\output.xml")):

<?xml version="1.0" standalone="yes"?>
<SampleDataSet>
  <Table1>
    <ID>1</ID>
    <Data>First Row</Data>
    <CreateDate>2004-10-01T09:15:12.1248693-07:00</CreateDate>
  </Table1>
  <Table1>
    <ID>2</ID>
    <Data>Second Row</Data>
    <CreateDate>2004-10-01T09:15:12.1248693-07:00</CreateDate>
  </Table1>
</SampleDataSet>

Thanks,

Joel
0
 

Author Comment

by:j_oller
ID: 12204985
I got it figured out, I will post the code when I can.

Regards,

Joel
0
 

Author Comment

by:j_oller
ID: 12218673
Before: I was returning a Dataset document, which most other applications dont know how to handle
Now: I have changed my code to return a plain XML Document, and then converted my dataset to xml, and served it through the webservice

There may be better ways of doing it, but this is what I have come up with.

[WebMethod]
          public System.Xml.XmlDocument getDS2()
          {
               DataTable dt = new DataTable();
               DataSet ds2 = new DataSet("SampleDataSet");

               dt.Columns.Add("ID", System.Type.GetType("System.Int32"));
               dt.Columns.Add("Data", System.Type.GetType("System.String"));
               dt.Columns.Add("CreateDate", System.Type.GetType("System.DateTime"));

               DataRow dr;

               dr = dt.NewRow();
               dr["ID"] = 1;
               dr["Data"] = "First Row";
               dr["CreateDate"] = System.DateTime.Now;
               dt.Rows.Add(dr);

               dr = dt.NewRow();
               dr["ID"] = 2;
               dr["Data"] = "Second Row";
               dr["CreateDate"] = System.DateTime.Now;
               dt.Rows.Add(dr);

               ds2.Tables.Add(dt);
         
               System.IO.StringWriter sw = new System.IO.StringWriter();
               ds2.Namespace="";
               ds2.WriteXml(sw);
               System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();
               xDoc.LoadXml(sw.ToString());
               return xDoc;
          }

Thanks,

Joel
0
 
LVL 6

Expert Comment

by:bpana
ID: 12224377
Hi, Joel.

I have used webservices in the same way (exporting data as xml).

Bogdan
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month5 days, 4 hours left to enroll

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question