j_oller
asked on
Consuming a C# Web Service’s ADO.NET Dataset in Delphi
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.ConnectionStri ng = "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
[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.ConnectionStri
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got it figured out, I will post the code when I can.
Regards,
Joel
Regards,
Joel
ASKER
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("Syste m.Int32")) ;
dt.Columns.Add("Data", System.Type.GetType("Syste m.String") );
dt.Columns.Add("CreateDate ", System.Type.GetType("Syste m.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
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("Syste
dt.Columns.Add("Data", System.Type.GetType("Syste
dt.Columns.Add("CreateDate
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
Hi, Joel.
I have used webservices in the same way (exporting data as xml).
Bogdan
I have used webservices in the same way (exporting data as xml).
Bogdan
ASKER
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("Syste
dt.Columns.Add("Data", System.Type.GetType("Syste
dt.Columns.Add("CreateDate
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-
<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-
<SampleDataSet xmlns="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0" diffgr:hasChanges="inserte
<ID>1</ID>
<Data>First Row</Data>
<CreateDate>2004-10-01T09:
</Table1>
<Table1 diffgr:id="Table12" msdata:rowOrder="1" diffgr:hasChanges="inserte
<ID>2</ID>
<Data>Second Row</Data>
<CreateDate>2004-10-01T09:
</Table1>
</SampleDataSet>
</diffgr:diffgram>
</DataSet>
The XML needs to look like this instead (I got this from C# Windows Application -> ds2.WriteXml("c:\\output.x
<?xml version="1.0" standalone="yes"?>
<SampleDataSet>
<Table1>
<ID>1</ID>
<Data>First Row</Data>
<CreateDate>2004-10-01T09:
</Table1>
<Table1>
<ID>2</ID>
<Data>Second Row</Data>
<CreateDate>2004-10-01T09:
</Table1>
</SampleDataSet>
Thanks,
Joel