Help learning DataSets...

[steverbs]

Hi all.

After years of hiding behind OR/Mapping tools I'm finally being forced to re-learn SQL for a heavily report-based application. One of the key issues that has arisen is the need to output report data to XML in a structured format. I've been told time and time again that DataSets are the way to go. So, I've spent half an hour getting my head around what is going on and think I am getting there, but just need a little help. So far I have:

        compAdapter = New Odbc.OdbcDataAdapter("SELECT Company.* FROM Company", conn)
        persAdapter = New Odbc.OdbcDataAdapter("SELECT Person.* FROM Person", conn)
        comsAdapter = New Odbc.OdbcDataAdapter("SELECT Communicator.* FROM Communicator", conn)
        compAdapter.Fill(dataSet, "Company")
        persAdapter.Fill(dataSet, "Person")
        comsAdapter.Fill(dataSet, "Communicator")
        dataSet.Relations.Add("CompanyParentCompany", dataSet.Tables("Company").Columns("CompanyID"), _
                              dataSet.Tables("Company").Columns("ParentCompanyID"))
        dataSet.Relations.Add("CompanyPerson", dataSet.Tables("Company").Columns("CompanyID"), _
                              dataSet.Tables("Person").Columns("CompanyID"))
        dataSet.Relations.Add("CompanyCommunicator", dataSet.Tables("Company").Columns("CompanyID"), _
                              dataSet.Tables("Communicator").Columns("CompanyID"))
        dataSet.WriteXml(path & "gaw.xml")
        dataSet.WriteXmlSchema(path & "gaw.xsd")

Which outputs XML just fine. However, it is a bit tedious having to create three queries and redefine the database structure in code. Is there a way around having to do this? Can I write a complicated join query with filters etc and create and populate the db structure in one go?

Also, what happens if the resulting data is 1000000s or records in size? Do I have to code the paging into the SQL, or does the dataset offer a way to manage this?

One more question. Can I predefine an XSD and have the dataset output XML according to the XSD?

Any help is greatly appreciated.

Regards.

Stephen.

 

[chrissie1]

yes you can do the join in sql. is it recommended? pff, not always. And you will have problems when you will have lots of records (but for that you need to go to the sql-server forum). the sql would look something like this.

select * from company c join person p on c.ID = p.id join communicator ca on p.id = ca.id

BTW try to avoid the * it will slow down the sql and I suppose you already know about PK and FK.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting