Loading ado.net table from datatableReader - wont update table on

[Guest]

Hello,

I have to read data from an external file into a ado.net table

ds.tblExternal.ReadXml(...)

and I want to load this data into a table that resides in a sql server
(2000) DB. I can do this if I loop through ds.tblExternal and add new rows
to the ado.net table from the DB:

da.Fill(ds, "tblfromDB") '--table from sql DB

Dim dr1 As DataRow
For Each dr As DataRow in ds.tblExternal.Rows
dr1 = ds.Tables("tblFromDB").NewRow
For Each dc As DataColumn in ds.tblExternal.Columns
dr1(dc.ColumnName) = dr(dc.ColumnName)
Next
ds.Tables("tblFromDB").Rows.Add(dr1)
Next
da.Update(ds,"tblFromDB") '--table on sql server DB populates OK here

If I use a dataTableReader to load data from tblExternal to "tblFromDB" I
don't have to do Looping, and this eliminates a lot of lines of code. The
problem is that if I load "tblFromDB" using a dataTableReader, it wont
update/populate the table on the DB end.

Dim reader As DataTableReader = ds.tblExternal.CreateDataReader
ds.Tables("tblFromDB").Load(reader)
da.Update(ds,"tblFromDB") '--doesn't update table on the sql server DB

This won't populate the table on the DB like looping will, however, if I set
a datagrideview.datasource to ds.Tables("tblFromDB") after loading
"tblFromDB" using the dataTableReader, the datagridview will display data
from "tblFromDB". I am guessing that new rows were not added to "tblFromDB"
when loading data using the dataTableReader.

Does anyone know what to do to make the data from the dataTableReader able
to update/populate the table on the sql server end? The goal is to not have
to loop through tables.

Or --- is there a better way to read data from the external file into the
sql server table?

Thanks,
Rich

 

[Guest]

Well, I found the answer to my own question:

ds.Tables("tbl2").Load(reader, LoadOption.Upsert)

I have to include this optional argument:

LoadOption.Upsert

I was thinking that may have been a type like maybe the VS2005 team meant
Insert (maybe they did mean that), but maybe they meant Update/Insert.
Either way, if I add this argument to the Load Method, now the data transfers
all the way to the sql server no problem.

 

[gfergo]

Rich,

Although "upsert" sounds kinda funny, I believe it is what you need -


DataSet columns store an original and a current value. PreserveChanges
will keep the current value intact while overwriting the original
value. Upsert does the opposite of this as it keeps the original value
intact while overwriting the current value.

Here is an example of when PreserveChanges might come in handy.
Suppose a user named Peggy has opened a screen and loaded a DataGrid
with customers from a DataSet. Peggy modifies the city of the customer
with CustomerID ALFKI from Berlin to New York, but doesn't click the
Save button. She then goes off for a cup of coffee. Meanwhile,
Katherine modifies the same customer's city from Berlin to Miami.
You'll now have a data concurrency issue if Peggy comes back from her
break and saves the record. So in this situation, the original value
for Peggy's customer record was Berlin and since she changed it to New
York the current value is New York. Meanwhile, in the database the
city is now Miami. If you want to reset the original values of Peggy's
DataSet to what is in the database, you could get the data from the
database into a DataTableReader and then load it into the DataSet
using LoadOptions.PreserveChanges.