SQL Table to DataSet to Dataview, to DataSet to XML!

27 Jan 2006 10:20 AM

matteo

Hi everybody,
i've a question for this group: i've developed a windows service that
every x minutes count records present in a sql table and if those
records are bigger than a fixed threshold start with clean operations.

Workflow is this:

1) Load records in a DataSet:
DataSet ds = new DataSet();
cmd = "SELECT * FROM " + TableName;
.........
adapter.Fill( ds );

2)Generate a DataView form table inside dataset and order it (i must
order. I tryed to order before load table in DataSet, but table is very

big and often i had timeout)

DataView dv = new DataView(ds.Tables[0]);
string order = GetTimeFieldName ( Table ) + " ASC";
dv.Sort = order;

3) Now i need convert data to XML, but not only one XML file, N XML
file of 10000 records. To do this i generate N DataTable from DataView,

add each DataTable to a New DataSet and call
WriteXML method N time.

The workflow working fine, but i think is not optimal solution (load
dataset, copy to dataview, order dataview, copy again ....... ) ,
someone have a better idea, or some suggestion to gave me?

30 Jan 2006 9:37 AM

Cor Ligthert [MVP]

Matteo,

At least should you add in my opinion in your SQL Select string the "Order
By" clause.

Than you don't need your dataview.

Just my thought,

Cor

30 Jan 2006 11:09 AM

matteo

Your solution is my first attempt, but i need to order by a field that
not is an index (i can't transform to it) and the command goes in
timeout..... I don't want to increase the timeout (i think it is
possible...) because i don't know time duration and could be exists
case where timeout will be reached anyhow...

30 Jan 2006 12:04 PM

Patrice

If this is server side only it could be also a SQL Server job. Additionaly
it could perhaps just process the n first rows you need to clean up instead
of starting the whole cleanup after the threshold is reach...

--
Patrice

Show quoteHide quote

"matteo" <matteoales***@libero.it> a écrit dans le message de
news:1138357237.507176.271810@g43g2000cwa.googlegroups.com...
> Hi everybody,
> i've a question for this group: i've developed a windows service that
> every x minutes count records present in a sql table and if those
> records are bigger than a fixed threshold start with clean operations.
>
>
> Workflow is this:
>
>
> 1) Load records in a DataSet:
> DataSet ds = new DataSet();
> cmd = "SELECT * FROM " + TableName;
> ........
> adapter.Fill( ds );
>
>
> 2)Generate a DataView form table inside dataset and order it (i must
> order. I tryed to order before load table in DataSet, but table is very
>
> big and often i had timeout)
>
>
> DataView dv = new DataView(ds.Tables[0]);
> string order = GetTimeFieldName ( Table ) + " ASC";
> dv.Sort = order;
>
>
> 3) Now i need convert data to XML, but not only one XML file, N XML
> file of 10000 records. To do this i generate N DataTable from DataView,
>
> add each DataTable to a New DataSet and call
> WriteXML method N time.
>
>
> The workflow working fine, but i think is not optimal solution (load
> dataset, copy to dataview, order dataview, copy again ....... ) ,
> someone have a better idea, or some suggestion to gave me?
>

30 Jan 2006 4:18 PM

matteo

yes, but to process the first n row i need to order the records into
the table then import into dataset, or import the entire table into the
dataset, then order, then process m blocks of n records everyone.
Remeber that i need to convert in xml format the data and SQL Server
have no functionalities to perform XML conversion then SQL Server job
is not useful for my duties...