LoadDataRow - issues

[VJ]

Hi..



I have 2 datasets, each with a DataTable. Each table has the same primary
key, and columns. I am trying to copy rows from one dataset to another,
based on an event from my application.



It all works fine when the row is not present in the destination DataTable.
Now when the row is present in the destination file, the .NET documentation
says use the LoadRowData method of the dataset to copy the row. When do
this, I get a unique key violation problem (right at the EndLoadData point
in the code below).. Here is my code below. Can anybody help??



Dim app As Application

Dim xmlDestFile As String = app.StartupPath & "\" & "dest.xml"

Dim xmlSourceFile As String = app.StartupPath & "\" & "source.xml"



Dim dsDest As New DataSet

Dim dsSource As New DataSet



Dim rwDest As DataRow

Dim rwSource As DataRow



dsDest.ReadXml(xmlDestFile)

dsSource.ReadXml(xmlDestFile)



Try



For Each rwSource In dsSource.Tables(0).Rows



Dim copyRowNotPersent As Boolean



For Each rwDest In dsDest.Tables(0).Rows



If rwDest.Item("Name") = rwSource.Item("Name") Then

copyRowNotPersent = True

Exit For

End If

Next



If Not copyRowNotPersent Then





Else

Dim newRow(rwSource.Table.Columns.Count - 1) As Object

Dim column As DataColumn

Dim intLoop As Int32

intLoop = 0

For Each column In rwSource.Table.Columns

newRow(intLoop) = rwSource.Item(column)

intLoop = intLoop + 1

Next

dsDest.Tables(0).BeginLoadData()

dsDest.Tables(0).LoadDataRow(newRow, False)

dsDest.Tables(0).EndLoadData()

End If



Next



dsDest.AcceptChanges()

dsDest.Write(xmlDestFile, XmlWriteMode.WriteSchema)



dsDest.Dispose()

dsSource.Dispose()



Catch myex As Exception

MsgBox(myex.ToString, MsgBoxStyle.Information +
MsgBoxStyle.OKOnly, "Test")

End Try

 

[Hussein Abuthuraya[MSFT]]

Yes, the LoadDataRow does update existing rows or add them if they don't exist.

In order for it to update existing rows, you should have PK defined in the target table. If no PK found then, it will add the rows even if they exist (create duplicates).

Here is a that you can test:

Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from Customers", "Server=hussab...")
Dim ds As New DataSet()
da.FillSchema(ds, SchemaType.Mapped, "Customers")
da.Fill(ds, "Customers")
dt = ds.Tables("Customers")

Dim dtCopy As New DataTable
dtCopy = dt.Copy
Dim dr() As DataRow = dt.Select()
Dim i As Integer
For i = 0 To dr.Length - 1
dtCopy.BeginLoadData()
dtCopy.LoadDataRow(dr(i).ItemArray, False)
dtCopy.EndLoadData()
Next

The above code will update all rows but if you comment the FillSchema Line then it will create duplicate rows.

A better way to accomplish this is to use the Merge method of the Dataset object where you don't need to write any extra code or loops to copy the rows one by one. See
the documentation for the details of this method.

I hope this helps!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

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

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.