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
"i dont use string.format to format the xml string.
i use xml schema to do that." viola
Please show me where I am doing that, to my knowledge I am only employing the string.format to format the SQL statement with the right variable in the select statement, so that part seems to be trivial, since is has nothing to do with the dataset. I am new to SOAP, and web services, so please correct me if I am wrong...
Thanks,
Joel
i use xml schema to do that." viola
Please show me where I am doing that, to my knowledge I am only employing the string.format to format the SQL statement with the right variable in the select statement, so that part seems to be trivial, since is has nothing to do with the dataset. I am new to SOAP, and web services, so please correct me if I am wrong...
Thanks,
Joel
string XMLSchemaFile: is a location of the schema file eg."http://localhost/MyWebService/example.xsd"
public void getData(string sInput)
{
string mysp = yourStoreProcedureName; ///please see the explaination below this method
oOracleConn = new OracleConnection();
oOracleConn.ConnectionStri ng = "User Id=****;Password=********; Data Source=****";
validateXMLString(sInput,v alidSchema file);
string SELECT_PROC = mysp;
StringReader objXMLRead = null;
DataSet objDSXML;
DataRow objDBRow;
SqlDataAdapter objAdapter;
SqlCommand comm;
DataSet objDSDBTable = null;
try
{
// Read XML String into Object
objXMLRead = new StringReader(sInput);
// Create a Dataset and populate with XML
objDSXML = new DataSet();
objDSXML.ReadXml(objXMLRea d, XmlReadMode.Auto);
// Create a Dataset for the Table
objDSDBTable = new DataSet("NEWDATASET");
// create a new command
comm = new SqlCommand(SELECT_PROC, oOracleConn);
comm.CommandType = CommandType.StoredProcedur e;
// Create Adapter and fill the dataset
objAdapter = new SqlDataAdapter(comm);
objAdapter.Fill(objDSDBTab le);
// loop through XML dataset
foreach (DataRow objDataRow in objDSXML.Tables[0].Rows)
{
// loop through columns and set new data
foreach (DataColumn objCol in objDSXML.Tables[0].Columns )
{
if (objDataRow[objCol.ColumnN ame] != DBNull.Value)
{
objDBRow[objCol.ColumnName ] = objDataRow[objCol.ColumnNa me];
}
}
// add the new row
objDSDBTable.Tables[0].Row s.Add(objD BRow);
}
}
catch (Exception ex)
{
........
}
}
************************** *********
create procedure yourStoreProcedureName
as
select a1, a2, ........ from Table where 1=2
go
******************
you can modify this method to suit yours.
viola
public void getData(string sInput)
{
string mysp = yourStoreProcedureName; ///please see the explaination below this method
oOracleConn = new OracleConnection();
oOracleConn.ConnectionStri
validateXMLString(sInput,v
string SELECT_PROC = mysp;
StringReader objXMLRead = null;
DataSet objDSXML;
DataRow objDBRow;
SqlDataAdapter objAdapter;
SqlCommand comm;
DataSet objDSDBTable = null;
try
{
// Read XML String into Object
objXMLRead = new StringReader(sInput);
// Create a Dataset and populate with XML
objDSXML = new DataSet();
objDSXML.ReadXml(objXMLRea
// Create a Dataset for the Table
objDSDBTable = new DataSet("NEWDATASET");
// create a new command
comm = new SqlCommand(SELECT_PROC, oOracleConn);
comm.CommandType = CommandType.StoredProcedur
// Create Adapter and fill the dataset
objAdapter = new SqlDataAdapter(comm);
objAdapter.Fill(objDSDBTab
// loop through XML dataset
foreach (DataRow objDataRow in objDSXML.Tables[0].Rows)
{
// loop through columns and set new data
foreach (DataColumn objCol in objDSXML.Tables[0].Columns
{
if (objDataRow[objCol.ColumnN
{
objDBRow[objCol.ColumnName
}
}
// add the new row
objDSDBTable.Tables[0].Row
}
}
catch (Exception ex)
{
........
}
}
**************************
create procedure yourStoreProcedureName
as
select a1, a2, ........ from Table where 1=2
go
******************
you can modify this method to suit yours.
viola
ASKER
Viola, thanks for the quick respone, however, the problem seems to be with the XML that my Web Service is returning as a dataset. 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 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);
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-ms data">
<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-ms data" xmlns:diffgr="urn:schemas- microsoft- com:xml-di ffgram-v1" >
<SampleDataSet xmlns="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0" diffgr:hasChanges="inserte d">
<ID>1</ID>
<Data>First Row</Data>
<CreateDate>2004-10-01T09: 07:03.9186 134-07:00< /CreateDat e>
</Table1>
<Table1 diffgr:id="Table12" msdata:rowOrder="1" diffgr:hasChanges="inserte d">
<ID>2</ID>
<Data>Second Row</Data>
<CreateDate>2004-10-01T09: 07:03.9186 134-07:00< /CreateDat e>
</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 ml")):
<?xml version="1.0" standalone="yes"?>
<SampleDataSet>
<Table1>
<ID>1</ID>
<Data>First Row</Data>
<CreateDate>2004-10-01T09: 15:12.1248 693-07:00< /CreateDat e>
</Table1>
<Table1>
<ID>2</ID>
<Data>Second Row</Data>
<CreateDate>2004-10-01T09: 15:12.1248 693-07:00< /CreateDat e>
</Table1>
</SampleDataSet>
Thanks,
Joel
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
ASKER
I got it figured out, I will post the code when I can.
Regards,
Joel
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
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
thank you, joel.
viola
viola
i use xml schema to do that.
eg.
validateXMLString(sInput,v
public void validateXMLString(string sInput, string XMLSchemaFile)
{
// create the XML Reader using the XML string passed
XmlValidatingReader vr = new XmlValidatingReader(sInput
// set validation type to schema
vr.ValidationType = ValidationType.Schema;
try
{
// set the schema using the schema in the application settings
vr.Schemas.Add(null, XMLSchemaFile);
}
catch (Exception ex)
{
throw new ApplicationException("Erro
}
// set the validation error handler
vr.ValidationEventHandler += new ValidationEventHandler (ValidationHandler);
// initialise class variables for validation handler
xsdValidationCount = 0;
xsdValidationErrorMessage = new StringBuilder(null);
// Ensure the XML string can be validated against the XML schema
while(vr.Read());
// check if any errors where encountered. If so, raise exception
if (xsdValidationCount != 0)
throw new ApplicationException("Erro
}
/// <summary>
/// Validation error event handler for the XML Validation
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
private void ValidationHandler(object sender, ValidationEventArgs args)
{
++xsdValidationCount; // increment counter
// build validation message
xsdValidationErrorMessage.
xsdValidationErrorMessage.
xsdValidationErrorMessage.
xsdValidationErrorMessage.
}
try this one to validate your xml string. but you have to creat a schma file for it.
viola