Clear unchanged rows in a data table

[Ryan]

I have a unique situation and wondering if there's a different way of
doing it. I have a strongly typed dataset for a disconnected
environment. This dataset runs on a service so I want to clear the
rows every now and then to keep memory usage to a minimum. But if
dataset can't contact for the database for some reason, I want to be
able to hold the records (because the service will still continue to
collect data) and upload them later. Is there a way to clear the rows
that are marked as unchanged.

Here's my code...

Private Function PopulateTable(ByRef dt As DataTable, ByVal sqlCmd
As SqlCommand, ByVal payload() As String)
Dim i As Integer
Dim row As DataRow = dt.NewRow()
Dim sqlDa As New SqlDataAdapter

Try
sqlDa.InsertCommand = sqlCmd
row(0) = m_machineName
For i = 1 To payload.Length - 1
If i > dt.Columns.Count - 1 Then Exit For
If i = 1 Then
row(i) = Convert.ToDateTime(payload(i - 1).Trim())
Else
row(i) = payload(i - 1).Trim()
End If
Next
Catch ex As Exception
Debug.WriteLine("PopulateTable: " + ex.Message)
Finally
dt.Rows.Add(row)
sqlDa.Update(dt) <--- Calls .AcceptChanges() method and
marks rows as UnChanged
If dt.Rows.Count > 1600 Then
dt.Rows.Clear() <--- Want to clear unchanged rows
End If
sqlDa.Dispose()
End Try
End Function

GetChanges returns a copy of the data table not a reference. If I have
to iterate through rows I will but I was hoping for a better method.

 

[W.G. Ryan [MVP]]

If I understand you correct, you can use Remove, or you can Delete and
AcceptChanges.

 

[Ryan]

Remove() only removes a single row. What I would like to do is remove
a collection of rows that has unchanged rowstate. I guess I have
iterate through the data table.

 

[RobinS]

You can extract the rows that are not unchanged into another
table. This should get you part of the way there.

Try this...

Dim dvrs as DataViewRowState
dvrs = DataViewRowState.ModifiedOriginal OR DataViewRowState.Deleted
OR DataViewRowState.Added
Dim dv as dataView = New DataView(dt)
dv.RowStateFilter = dvrs

Dim dt_New as DataTable = dv.ToTable("NoUnchangedRows")

This datatable should match the one in your original strongly typed
dataset, so could you replace your dt with this one?

dt = dt_new

You might have to cast it back into the right type:

dt = CType(dsStronglyTyped.myRealTable, dt)

Hope this helps in some way!

Robin S.

 

[RobinS]

The problem with iterating through the data table is if you remove the
rows, it will assign a rowState of Deleted to them, and not really
remove them. If you call AcceptChanges to "fix" the rowstate, it will
wipe out the rowstate on all the changed records you already have.

Robin S.
--------------------------------------------------

 

[Petar Repac]

The problem with iterating through the data table is if you remove the
rows, it will assign a rowState of Deleted to them, and not really
remove them. If you call AcceptChanges to "fix" the rowstate, it will
wipe out the rowstate on all the changed records you already have.

Robin S.

RobinS,
there is a difference between Remove and Delete.

Row.Delete() marks a row as deleted
(if row previously had RowState=Added then row is removed from table).

DataRow doesn't have Remove() method.

DataRowCollection (e.g. DataTable.Rows) has a Remove() method, but that
method doesn't mark an row as deleted, but removes the row from the
collection. It is as that row doesn't exist anymore. And if you don't
hold a reference to that row it is lost.

Ryan,
how about using a ref keyword in method declaration (in C#)

private void RemoveUnchangedRows(ref aMyDataSet) {
aMyDataSet = aMyDataSet.GetChanges();
}


Regards, Petar

 

[Ryan]

Hey Petar,

I think you put me on the right track...here's what I'm thinking will
work.

Private Function PopulateTable(ByRef dt As DataTable, ByVal sqlCmd
As SqlCommand, ByVal payload() As String)
Dim i As Integer
Dim row As DataRow = dt.NewRow()
Dim sqlDa As New SqlDataAdapter

Try
(...) <--- populate the row with the information in payload
Catch ex As Exception
Debug.WriteLine("PopulateTable: " + ex.Message)
Finally
dt.Rows.Add(row) <--- row state will be added
Try
sqlDa.Update(dt) <--- after successful call to update,
all rows will change state to "unchange"
Finally
dt = dt.GetChanges(DataRowState.Added) <--- if
something happen during the update, row states don't change and my data
table will only hold the rows thats been added
End Try
sqlDa.Dispose()
End Try
End Function

This seems right yes? Like I said, my dataset will be in a service so
I want make sure that if something happens (loose connection with
database) then I can still retain only the records that weren't updated
through the data adapter.

 

[RobinS]

Thanks for clarifying that for me. I'll save that info in a safe place
for future reference.

If this is true, the OP could just filter the table by the rowState
and remove the rows that show up as Unchanged.

Robin S.
-----------------------------

 

[Cor Ligthert [MVP]]

Remove() only removes a single row. What I would like to do is remove

a collection of rows that has unchanged rowstate. I guess I have
iterate through the data table.

Exact.

(By the way if you don't iterate, the system will do it most probably for
you, .Net exist from collections).

Cor