DataSet and Relations? InfoPath gurus please help!

[G. Tarazi]

Hi

We had this problem in our project, in my case InfoPath is submitting the
data to a web service, and an XML is getting generated and executed on the
client computer (instead of receiving) using an ASPX page.

But anyway, the idea is similar; we end up running a converter to do the
conversion between 2 schemas, the dataset and the InfoPath schema.

The problem that you are facing is that because InfoPath is using a
hieratical schema, and the dataset that is connected to the database is
using a relational schema.

We tried multiple scenarios, here are some of them:

The easiest one:
1- You have an InfoPath schema created manually, using the BizTalk 2004
schema editor, or the normal VS.NET Schema/dataset editor (with manual
modification); not the default schema created by InfoPath "the schema there
is a bit strange ".
The modification:
<xs:element name="Dataset1" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table1">
.. . . .
Modify to:
<xs:element name="Dataset1" msdata:IsDataSet="true">
<xs:complexType>
<xs:element name="Table1">

2- You have another dataset, the database one, put the relations, and make
sure that the relation property in the dataset editor is set to "nested" (in
order of generating a hierarchy from the non hieratical relation).
3- Make sure the database dataset is using the same names as the target
schema.
4- Use this following command to get an hieratical xml from the data set:
string ixXml = myDataSet.GetXml();
5- Now load the xml to the target dataset, using something like:
MyInfoPathSchema myIPsh = new MyInfoPathSchema();
StringReader ixXmlReader = new StringReader(ixXml);
myIPsh.ReadXml(ixXmlReader);

This way is very limited, you must always make sure that target schema is
written to be exactly like the automatically generated schema, and you may
need to do some manual modifications.

The second idea is to use 2 different schemas and do the conversion with
code, after all the test that I did, I found this to be the most efficient
way.
Just run a foreach loop and do the conversion (this is the fastest
converter).

The third idea is to use an xslt, you can create an xslt fast using
something like the Map Force from www.XmlSpy.com and run it from inside
VS.NET with 4 lines of code, the problem here is the speed of the convert
itself, you are not able to debug the conversion itself (you can do this in
VS.NET 2005 beta) and if the schema changes the compiler will not trigger an
error, you code will just not work, I used this scenario for couple of days
and ignored it.


Another idea is to use Microsoft BizTalk Server 2004, it has a good maper
that can do transformation between 2 schemas, I used it for 3 months and
then uninstalled it, the amount of problem that you will face using that
product + InfoPath are incredible


There is no easy way, you will run in a lot of problems especially when
you start converting integers, data time, and Booleans between the schemas
and the dataset and the database.

By the way, that code was tested with InfoPath 2003 Service Pack 1 with C#
and VS.NET, it don't know if the normal IP 2003 works with datasets or not.

Good Luck

George

http://www.LiveTechnologies.ca

 

[astrujic]

I'm using web service to send DataSet to InfoPath. I filled data set with
for example two tables, and I created relation between those two tables.
When InfoPath receive DataSet, it don't have relation that I've
created????!!

This is possible to do if I pull tables directly from SQL Server, but I must
use DataSets.

THANK YOU ALL!!!!

 

[astrujic]

Thank you!

You've been very helpfull. When I removed <xs:choice maxOccurs="unbounded">
I received error....
Finnaly I've made it. I was doing entire thing trough designer, and I had a
problem. Relation was not preserved???. When I did everything manualy trough
the code, everything started to work. I filled my nested tables into
XmlDocument, and I sent XmlDocument to InfoPath.

Here is my sample code:
sqlConn.Open();

DataSet testDS = new DataSet("testRelation");

sqlDA1.Fill(testDS, "Table1");

sqlDA2.Fill(testDS, "Table2");

sqlConn.Close();

System.Xml.XmlDocument xmldoc = new XmlDocument();

DataRelation custRel = testDS.Relations.Add("custRel1",
testDS.Tables["Table1"].Columns["ID"],

testDS.Tables["Table2"].Columns["IDTable"]);

custRel.Nested = true;

string s;

s = testDS.GetXml();

xmldoc.LoadXml(s);

return xmldoc;


Best Regards,
Adnan