Data Export with ADO

Hi All,

I want to develop a small tool to export data from a database (SQL Server and Oracle). I have to export full tables or part of tables depending on an SQL statement.

My first idea is to use ADO. I open a recordset on the table (adCmdTable) and I call the Save method to save the recordset in a file. But, when I open the recordset, it takes a long time ... I suppose that ADO performs a " select * from table " to retreive all the lines ... It's too long for me because some tables have more then 10 millions of lines !!!

Do you known if it's possible to open a table without any "select" (just open and save) ? Do you kown others solutions ? I can develop with DO or ADO.Net.

Thanks in advance for your help.

Fran?ois.

Using adCmdTable causes the client to wait until the table parameters are collected, including a row count.

You should be using ADO.NET (and Visual Studio.NET).

ADO.NET is a disconnected model, so there is no traffic UNTIL you execute your query, returning only the data requested. Any 'slowness' at the start up will be due to the overhead of establishing the connection, and if you judiciously use connection pooling, that may not be much of an issue.

|||Have you looked into SSIS (SQL server integration Service) shipped as part of SQL 2005? You might not need to roll your own tool after all.|||

I try to use the WriteXml method of a DataSet to export my data. I don't want to user a "select * from table" select statement because the number of rows can be very important (more that 10 millions of lines), so I want to set the CommendType if my SqlCommand objet to TableDirect, but I have an error message : "this is not supported by SlqCLient .Net Framework" ! Is there any solution to biend a dataset on a table (without any SQL statement) and call the WriteXml method ?

Thanks.

Fran?ois.

|||SSIS is nice if you use SQL Server. But, unfortunatly, we have some customers with Oracle ... That's why I try to find a "universal" solution.|||No, 'unfortunately' using SQL Server requires the use of the SQL language. (Go figure...)