How to Insert records from dataset to another data connection

[Phil Williams]

Hello,
I have a SQL Express dataset with 1000 records and I want to export these to
an Excel file.
The way I thought to do this was to create a connection to the Excel file
and then insert the records by executing a ExecuteNonQuery Command.

My question then is how do I insert the results from one dataset (with a SQL
connection) to another connection without looping through each record and
inserting each record one at a time!

Any feedback would be very helpful.

Thanks,
Phil

 

[Phil Williams]

FYI
The SQL bulkcopy only works for SQL Connections and I have a SQL and an
Excel Connection.

 

[Cor Ligthert [MVP]]

Phil,

Can this message from Paul Clement help you?

http://groups.google.com/group/microsoft.public.dotnet.framework.adonet/msg/4b3031f5d481b82f

I hope this helps,

Cor

 

[Hendrik]

What I have done, is using a normal StreamWriter Object, write to a
normal text file with tabs as spacing, and just save the file with the
XLS extension. I worked great for me. Here is the code:

StreamWriter sw = new StreamWriter(@"c:\EventExport.xls", false,
System.Text.Encoding.Unicode);

//Do headers
for(int i=0;i<dt.Columns.Count;i++)
sw.Write(dt.Columns.ColumnName + "\t");
sw.WriteLine(System.Environment.NewLine);

//Actual rows/data
foreach(DataRow row in dt.Rows)
{
for(int i=0;i<dt.Columns.Count;i++)
sw.Write(row.ToString() + "\t");
sw.WriteLine(System.Environment.NewLine);
}
sw.Close();

Hope this helps

 

[Phil Williams]

Thanks for that link Cor, but although I don't do C. it does look like that
loops through each record which is what I was trying to get away from doing.
Did I read the code right?
If not is there any translation to VB.
else is there a way to do this without looping through each record?

Cheers,
Phil

 

[Cor Ligthert [MVP]]

Phil,

I thought that Paul was the only one in this newgroups who had given samples
about how to enter direct from a dataset data in an Excel workbooks.

Now I cannot find that anymore beside this loop sample. Probably I have
dreamed about that and is looping the only posibility. I did not investigage
it, however the sample from Paul is nice short.

Cor