Push Dataset into SQL Server

[FastEddie]

I have a Class that is creating a large dataset (over 32,000 records) on the fly. I want to take the dataset and import it directly into SQL Server 2005 but I have not been able to find anything in ADO that allows me to do that all in one chunk.

Doing a search I found some people in a similar situation that have exported the dataset to an XML file and then imported into SQL Server, which seems like a long way to go just to get something done that I would think would be part of ADO.Net.

However, for lack of a better option, I went a head and created the XML file but now I am having problems getting it to import into SQL Server so before I lose another day of productivity I am goin to ask a question.

Is there a way to insert a dataset directly into SQL Server without going to a file first (even if I have to do it one row at a time)?

'        cnn.Open()
        Try
            Dim cmd As SqlCommand = New SqlCommand("TimeCards", cnn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandTimeout = 0
            cmd.Parameters.AddWithValue("@StateID", "45")
            cmd.Parameters.AddWithValue("@StoreID", "ABCDEF")
            cmd.Parameters.AddWithValue("@From", dtStartDate)
            cmd.Parameters.AddWithValue("@To", dtEndDate)
            cmd.ExecuteNonQuery()
            da.SelectCommand = cmd
            da.Fill(dsHours, "EmpHours")
 
            CheckForFile(strFilePath)
 
        Catch ex As Exception
            bolSucceed = False
        Finally
            cnn.Close()
            cnn.Dispose()
            da.Dispose()
        End Try

[stanav]

Yes, you can call the Update method of your SqlDataAdapter and pass in the dataset. Having said that, the Update method will do all 3 operations (insert, delete and update) depending on the rowstate of each datarow... I fyou want in insert only, make sure that all of your datarows have the rowstate = rowstate.added.

[techgnome]

you could also use SMO's Bulk Copy and pass it a datatable to do a single mass import. The problem with going the table adapter route is that it will make 32,000 calls to the database (one connection, but each row gets it's own call for insert/update).... the Problem with the Bulk insert is that the layout of the datatable MUST MATCH the layout of the destination table.

-tg