Exporting from a SQL server database

GuyJos

Hi.

I am writing a program where I import data into a database with a System.Web.UI.WebControls.SqlDataSource object, using its Insert method.

Like this...

Dim DataBaseConnectionString As New Web.UI.WebControls.SqlDataSource()

DataBaseConnectionString.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"

DataBaseConnectionString.InsertCommandType = Web.UI.WebControls.SqlDataSourceCommandType.Text

DataBaseConnectionString.InsertCommand = "BULK INSERT TBLMain FROM '/file.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"

DataBaseConnectionString.InsertParameters.Add("csvdatapath", OFDCSV.FileName.ToString())

Try

DataBaseConnectionString.Insert()

Catch ex As Exception

I have been able to get this working fine.

I need to export to (ideally) a .CSV or a .XLS. I have tried to use the:

Code Snippet (SQL)

COPY TO '\results.csv' CSV

Is it possible to use a simmilar method of interacting with the database through code to perform this

Thanks

ReneeC

There is a little bit of missing information such as, are these csv records fixed length without knowing that I can't give you precise answer because the question is not precise.

There are many ways to go about this programmatically. I do a lot of database programming programatical because there are so many black boxes involved and I really like to keep the black boxes down to a minimum. For one thing they end up determining how you program, ...you don't.

Let's assume you have to create a backup table for this. Is there already one like it If so, you can clone that table devoid of contents. Else you will have to create a table with a T-SQL command describing the table and its various constraints. This will be easy if those csv records are fixed length.

Let's pretend that they are and also pretend that you have created a table in the database called "Table" (is that original enough I consider calling it spot)

so here it is algorithmically:

Dim row as datarow = table.newrecord

Dim s() as string

Dim Temp as string

Dim Count as integer

While Not reader.Eof

row = table.newrecord

'read the record from a file into Temp

s() = split(temp,",") ' using a "," as an example

For each s as string in s()

row(count +1) = s

count +=1

Next

table.rows.append(row)

End While

'Update the table with an adapater

'close the file

ssta

You might consider checking http://msdn2.microsoft.com/en-us/library/cakac7e6(VS.80).aspx. This library will take care of the reading. It handles malformed lines and all the nasty stuff you don't think about until your code breaks.