Clear unchanged rows in a data table

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
Thu Jan 25 13:49:02 CST 2007

If I understand you correct, you can use Remove, or you can Delete and
AcceptChanges.
"Ryan" <rchill81@gmail.com> wrote in message
news:1169746782.493428.258060@s48g2000cws.googlegroups.com...
>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.
>

Ryan
Thu Jan 25 15:39:24 CST 2007

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.

On Jan 25, 2:49 pm, "W.G. Ryan [MVP]" <WilliamR...@nospam.gmail.com>
wrote:
> If I understand you correct, you can use Remove, or you can Delete and
> AcceptChanges."Ryan" <rchil...@gmail.com> wrote in messagenews:1169746782.493428.258060@s48g2000cws.googlegroups.com...
>
>
>
> >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.- Hide quoted text -- Show quoted text -

RobinS
Thu Jan 25 18:10:52 CST 2007

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.
-------------------------------
"Ryan" <rchill81@gmail.com> wrote in message
news:1169746782.493428.258060@s48g2000cws.googlegroups.com...
>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.
>

RobinS
Thu Jan 25 18:11:46 CST 2007

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.
--------------------------------------------------
"Ryan" <rchill81@gmail.com> wrote in message
news:1169761164.211393.175170@s48g2000cws.googlegroups.com...
> 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.
>
> On Jan 25, 2:49 pm, "W.G. Ryan [MVP]" <WilliamR...@nospam.gmail.com>
> wrote:
>> If I understand you correct, you can use Remove, or you can Delete
>> and
>> AcceptChanges."Ryan" <rchil...@gmail.com> wrote in
>> messagenews:1169746782.493428.258060@s48g2000cws.googlegroups.com...
>>
>>
>>
>> >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.- Hide quoted text -- Show quoted text -
>

Petar
Fri Jan 26 01:47:24 CST 2007

RobinS wrote:
> 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
Fri Jan 26 06:21:04 CST 2007

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.

On Jan 26, 2:47 am, Petar Repac
<petar.repac@replace_with_google_mail_service.com> wrote:
> RobinS wrote:
> > 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

RobinS
Fri Jan 26 13:57:26 CST 2007

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.
-----------------------------
"Petar Repac" <petar.repac@replace_with_google_mail_service.com> wrote
in message news:ex5f54RQHHA.1200@TK2MSFTNGP02.phx.gbl...
> RobinS wrote:
>> 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

Cor
Sun Jan 28 05:08:29 CST 2007

> 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