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

  • Thread starter Thread starter matteo
  • Start date Start date
M

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?
 
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
 
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...
 
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...
 
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...
 
Back
Top