Querying a DataSet

  • Thread starter Thread starter Arnon Axelrod
  • Start date Start date
A

Arnon Axelrod

Hi all,
I have a large and complex dataset that is retrieved from SQL Server when my
application starts. When the application runs it needs to query data from
the dataset. I need to have many different queries that most of them read
data from multiple tables, and can be quite complex (e.g. including Group
By, etc).
Does anyone knows of a way to query the in-memory dataset using SQL syntax?
maybe a 3rd party tool?

Thanks in advance,
Arnon.
 
Hi Arnon,

By default DataSet does not support any queries. What you could do is to
query database directly or to simulate query using XSL stylesheet. Here is
an example how to do this (assuming the Book3.xml is a DataSet file)

------------ VB code to run *query*

Dim loSourceXML As XPath.XPathDocument
Dim loTransform As Xsl.XslTransform
Dim loDataSet As DataSet
Dim loNewDataStream As MemoryStream
Dim loFilteredDataSet As DataSet

loDataSet = New DataSet()
loDataSet.ReadXml("Book3.xml")

loSourceXML = New XPath.XPathDocument(New
StringReader(loDataSet.GetXml()))

loTransform = New Xsl.XslTransform()
loTransform.Load("Transform.xsl")

loNewDataStream = New MemoryStream()

loTransform.Transform(loSourceXML, Nothing, loNewDataStream)
loFilteredDataSet = New DataSet()
loNewDataStream.Position = 0

loFilteredDataSet.ReadXml(loNewDataStream, XmlReadMode.Auto)
loFilteredDataSet.WriteXml("Filtered.xml")

--------------- Transform.xsl file content. It is actual *query*

<?xml version="1.0" ?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">

<xsl:template match="/">
<xsl:apply-templates />
</xsl:template>

<xsl:template match="/MY_DATASET">

<xsl:element name="MY_FIELD_NAME">

<xsl:copy-of select="DETAIL[FIELD_TO_FILTER='XXXXX']" />

</xsl:element>

</xsl:template>

</xsl:stylesheet>
 
The problem with DataView is that it performs only on a single table. In
fact, what I wish for is an SQL engine that runs on datasets. This way I can
use query builders to build complex queries and run them on my dataset.
Querying the database itself is no good because of performance and some
other reasons...
Any ideas?

Arnon.


Deepak said:
You can generate a DataView from your dataset, and run searches on DataView.

--
Regards,

Deepak
[I Code, therefore I am]
Arnon Axelrod said:
Hi all,
I have a large and complex dataset that is retrieved from SQL Server when
my
application starts. When the application runs it needs to query data from
the dataset. I need to have many different queries that most of them read
data from multiple tables, and can be quite complex (e.g. including Group
By, etc).
Does anyone knows of a way to query the in-memory dataset using SQL
syntax?
maybe a 3rd party tool?

Thanks in advance,
Arnon.
Click to expand...
Click to expand...
 
Have you checked an example, which I have posted? Since DataSet keeps XML
for all the tables in one structure, you could use XSL to query multiple
tables inside of the same DataSet

--
Val Mazur
Microsoft MVP


Arnon Axelrod said:
The problem with DataView is that it performs only on a single table. In
fact, what I wish for is an SQL engine that runs on datasets. This way I
can
use query builders to build complex queries and run them on my dataset.
Querying the database itself is no good because of performance and some
other reasons...
Any ideas?

Arnon.


Deepak said:
You can generate a DataView from your dataset, and run searches on DataView.

--
Regards,

Deepak
[I Code, therefore I am]
Arnon Axelrod said:
Hi all,
I have a large and complex dataset that is retrieved from SQL Server when
my
application starts. When the application runs it needs to query data from
the dataset. I need to have many different queries that most of them read
data from multiple tables, and can be quite complex (e.g. including Group
By, etc).
Does anyone knows of a way to query the in-memory dataset using SQL
syntax?
maybe a 3rd party tool?

Thanks in advance,
Arnon.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top