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

[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?

 

[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

 

[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...

 

[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...

 

[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...