Transfering a dataset

  • Thread starter Thread starter Ken Pinard
  • Start date Start date
K

Ken Pinard

I am reading a dataset from a CSV file and creating XML. From there I put it
into a dataset.

What I want to do now is to take that datatable and store it into a database
(through an ODBC connection).

I thought there was an easy way to assign the dataset to the new connection
and/or dataadapter and tell it to store/save/etc. the datatable to the
database.

Can someone point me in the right direction? Or am I just off my rocker
again?

Thank you for any insight you can provide,

Ken
 
See my response to Daniel Brower. The same applies to you. You're going
about the problem in about the hardest way possible.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I have done it and it isn't trivial if you want any flexibility.
But if you can force certain things to be done then it makes it a lot
simpler.

1. The text file must have all of the same fields as the table in the SQL
Server database.
2. If the file is missing a field then the datatable in memory has to create
it and fill it with a default value.
3. The datatable and the database should have the exact same field names.
4. If the text file only has new records then you can use the dataadapter
update command to add the new records to the database as long as their row
state is Added.

The real trick comes when there are records that need to be updated.
Then you have to decide how to identify them and get their row state
switched to Modified.
(The property is READ ONLY so you can't just set it even when you do know
the answer!)
It gets worse...

Are you sure you still want to go down this road?
 
What I need to do is to take the csv file and put it into a database (Access
at this time but it may change in the future). I alread have it importing
into a dataset. That is done.

I just need to know how to get it into a table in Access (via ODBC another
limitation I have to live with). I am creating the table that will receive
the data. So it will match with out any problem. It is also empty to begin
with, so that is not a problem with updating.

So I guess the problem I have is getting the dataadapter for the ODBC to
include the Dataset/Datatable I created using XML.

Is there a command to Attach the dataset to an Adapter? I guess that is what
I am looking for, that what to connect the two.

Ken
 
Boy, I wish I had thought of that. Oh, I did. I still find it confusing and
I am still looking for an answer. I was hoping that someone could point me
in the right direction.

I'm new to ADO.Net, it is just all of the ADO and DAO that is getting in the
way. So I am trying to learn as fast as I can.

I have seen so many helpful hints in the news groups, I was hoping to get
through my problem I letter faster.

Hopefully, some one can give me a hint as to what I need to read about, so I
can focus on solving my problem.

By the way, I could loan you a few books, I have a wall of them.

Ken
 
Hi Ken,

Why not just open the empty table as another datatable inside another
dataset; then loop through the table with the data and add new rows?

If you need an example of how to do this, let me know. You will need either
a commandbuilder or insert commands also.

HTH,

Bernie Yaeger
 
I was hoping there was a way to put the dataset into the newly opened
dataadapter.

I was remembering something about disconnected datasources in the original
ADO and was hoping to find a way to do that in ADO.net. Where I have a
dataset, move to an open connection/adapter and then do an update. Joe
eluded to it, but I don't know what the steps are. I keep reading stuff, but
I could be reading the totally wrong sections of the books.

Thank you for the Idea. I may have to do that just to get things moving.

Ken
 
Here is some sample code for SQL Server that might help: (watch out for line
wrapping)
Pass in a datatable variable and a SQL command like "SELECT * FROM MyTable"

Public Function UpdateServerData(ByVal dt As DataTable, ByVal selectCommand
As String) As String
Dim cnn As New SqlConnection(mConnStr)
Dim da As New SqlDataAdapter
Dim cmd As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim NumUpdated As Integer
Dim strMsg As String

Try
strMsg &= vbCrLf & "Process started at " & Now.ToLongDateString &
" " & Now.ToLongTimeString & vbCrLf
da.SelectCommand = New SqlCommand(selectCommand, cnn)
cnn.Open()

'update records in table on Server with all DataTable changes and
omit any unchanged records.
'you may be able to omit all of this and just use:
da.Update(dt)
Dim ChangedRecords() As DataRow = dt.Select(Nothing, Nothing,
DataViewRowState.Added Or DataViewRowState.Deleted Or
DataViewRowState.ModifiedCurrent)
NumUpdated = ChangedRecords.GetUpperBound(0) + 1
da.Update(ChangedRecords)

cnn.Close()

strMsg &= "The number of modified records is " &
NumUpdated.ToString & "." & vbCrLf
strMsg &= "Process finished at " & Now.ToLongDateString & " " &
Now.ToLongTimeString & vbCrLf

Return strMsg

Catch exc As Exception

End Try
End Function
 
Back
Top