SetColumnError Takes a Long Time to Execute

[Charles Law]

I have a data table with ~1000 rows. I go through the rows one at a time,
checking the contents. If I find a cell with an error, e.g containing null,
I use SetColumnError on the data row to flag it.

Here's my code:

For Each dr As DataRow In MyDataTable.Rows
Dim MyValue As Integer

If dr("ID").Equals(DBNull.Value) Then
dr.SetColumnError("ID", "ID cannot be null") ' this take 1 to 2
seconds each time
Else
MyValue = CType(dr("ID"), Integer)
End If

...
Next

The problem is each call to SetColumnError takes about 1-2 seconds to
execute. This is an ice-age, and if I have 100 cells to flag it takes 100
seconds to go through the data.

Does anyone know why it takes so long for this method to execute?

I am using VS2008 SP1 on XP SP3. I am targeting V3.5 of the framework,
although I have V3.5 SP1 installed. This was a problem before I installed
SP1 though. The back-end is SQL Server 2005, but the data are disconnected,
so I can't see that being an issue.

TIA

Charles

 

[Cor Ligthert[MVP]]

Hi Charles,

Takes in Brittain a second the same time as at the continent?



\\\
Module Module1

Sub Main()
Dim a As New Stopwatch
a.Start()
Dim myDataTable As New DataTable
Dim ID As New DataColumn("ID")
myDataTable.Columns.Add(ID)

For i As Integer = 0 To 100
Dim dr = myDataTable.NewRow
dr("ID") = DBNull.Value
myDataTable.Rows.Add(dr)
Next

For Each dr As DataRow In myDataTable.Rows
Dim MyValue As Integer

If dr("ID").Equals(DBNull.Value) Then
dr.SetColumnError("ID", "ID cannot be null") ' this take
1 to 2 seconds each time
Else
MyValue = CType(dr("ID"), Integer)
End If
Next
Console.WriteLine(a.ElapsedMilliseconds / 1000)
Console.ReadLine()
End Sub

End Module
///

Cor

 

[Charles Law]

Hi Cor

Good to hear from you. I tried your test, and it takes only a fraction of a
second, so there is obviously something about my data table that is causing
the problem.

I've just had another thought: I am using the data table as the data source
for a grid. I wonder if the fact that the grid is bound to the data table is
what is making it so slow. I'll have to look into that.

Cheers

Charles

 

[Andrew Morton]

Good to hear from you. I tried your test, and it takes only a
fraction of a second, so there is obviously something about my data
table that is causing the problem.

I've just had another thought: I am using the data table as the data
source for a grid. I wonder if the fact that the grid is bound to the
data table is what is making it so slow. I'll have to look into that.

Maybe .SuspendLayout and .ResumeLayout on the grid would help?

Andrew

 

[Charles Law]

Hi Andrew

I did try that and it didn't improve things. It seems that it is the fact
that the data table is the data source for the grid. I set the data source
to Nothing and the SetColumnError takes a fraction of a second. So, I create
a copy of my data table to work on and then use the copy as the data source
at the end. I couldn't really think of a better way.

Cheers

Charles

 

[Charles Law]

Hi Steve

I tried the Nothing thing before, which did work, but decided to make a copy
instead to avoid the grid going blank during the process, but I appreciate
it will take longer when the data table has many rows.

I will try the BeginDataLoad though; that sounds like it could work.

Cheers

Charles