.Net newbie-- can you save datasets into other formats?

[Jim Bancroft]

Hi everyone,

New to the .Net party here, so please forgive the basic nature of my
questions. I have to query SQL Server and generate csv or Excel files when
all is said and done, from a VB .Net client app.

I've been reading up on DataSources, DataSets, DataGrids and ado.net
theory, but I've really only scratched the surface and wasn't sure on a good
way to approach this problem. I'd like to make the SQL Table-->Excel
transformation as painless as possible, and didn't know if DataSets had any
methods to "save into" a specified destination type?

It's not conceptually difficult, connecting to the DataSource and
running my queries, but I don't know of a way to go from a DataSet into
Excel, or if that's even an acceptable way of doing things in .Net. I'm
open to suggestions and online tutorials if you know of any, or even a code
snippet or two I can get started with. Thanks for your help.

-Jim

 

[Guest]

I'm going to say it depends on the future maintainability you are expecting to achieve. If it is a data conversation application that may have significant changes to the CSV format in the future then I think the best approach would be to write your own custom export code to iterate through the dataset and use the file/stream I/O functions that .NET provides to create the CSV file.

The dataset is stored in/can easily convert to - a text based XML file. Not sure that helps at all - perhaps a first step - you could use another utiltity to grab the XML.

Also, you will have to do some serious research into the COM objects and other InterOp routines that are available. I know from past experience that you can access the Excel API from C# and write rows to an excel file using the API. It is quite cumbersome/black magic in my opinion.

Seems silly, but why not use SQL Server DTS to do the conversion.........

Andrew S.

 

[Jim Bancroft]

Thanks for the reply, Andy--

The dataset is stored in/can easily convert to - a text based XML file.

Not sure that helps at all - perhaps a first step - you >could use another
utiltity to grab the XML.

--Looks like the route I'll take. I thought maybe ado.net had some built-in
utilities for transforming a dataset into a CSV file or Excel spreadsheet.
But, streaming to XML is a good first step. Problem is I'm lousy at walking
through XML nodes and manipulating them so I'll poke around for 3rd party
code to help convert into Excel/CSV.

Seems silly, but why not use SQL Server DTS to do the conversion.........

--You're right, I could use DTS, but I can't count on the client boxes
having the right dlls installed. DTS also seems a little overkill for the
situation I'm working in; the queries run and rowcounts returned aren't
unmanageable, so I figured it'd be quicker to "save as" directly from the
dataset.