2 questions. Partial SqlDataAdapter.Fill() and ReadXml()

  • Thread starter Thread starter Jon Brunson
  • Start date Start date
J

Jon Brunson

1. Is it possible to setup at SqlDataAdapter to only .Fill() a DataTable
with the rows that have changed? ie, if I have a table with 10,000
records in it, and I change 1 of them, can .Fill() only change that 1
row (nice & quick), rather than re-populate all 10,00 rows (which would
take some time)

2. After loading a DataTable using SqlDataAdapter.Fill(), is it possible
to do this:
a) Save that table to Xml (Load it into a DataSet, then
DataSet.WriteXml()).
b) Load it back again (DataSet.ReadXml())
c) Use another SqlDataAdapter to .Fill() the table properly, knowing
that the rows that already exist in the DataTable came from the
database, and should be updated/deleted if the database has made such
changes to them.
Currently I can only seem to make the SqlDataAdapter.Fill() method add
all the rows again (eg. if the table has 5 rows, after loading from Xml
and .Fill()ing, it contains 10). It's as if the SqlDataAdapter doesn't
know which Xml row matches each row in the database.
 
1. Not directly. You can, however, add a timestamp column to your database
and select only newer records using respective SQL statement.

2.
a) Sure. Don't forget to save schema (separately or within XML).
b) Absolutely. Don't forget to load schema prior to the data.
c) Yes if you have unique primary key in this DataSet/DataTable.
DataAdapter will update rows with matching primary keys.
Without primary key it has no way to determine if row is already in the
DataSet and would simply add it again.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
 
Thanks for the reply.

Regarding #2, is this the correct code to save to Xml with the key info?


Dim dc As New SqlCommand("SELECT * FROM SomeTable", MyConnection)

dc.CommandType = CommandType.Text

Dim da As New SqlDataAdapter(dc)

da.MissingSchemaAction = MissingSchemaAction.AddWithKey

Dim ds as New DataSet

da.Fill(ds)

ds.WriteXmlSchema("data.xsd")

ds.WriteXml("data.xml")
 
Yes, that should do it. DataSet structure description including key(s) will
be saved to schema file.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
 
Back
Top