DataSet - adding new row

  • Thread starter Thread starter Eric Petruzzelli
  • Start date Start date
E

Eric Petruzzelli

If I fill my dataset and there is no data. The dataset is still created
with zero rows (all columns are there). When I add my first row using
the script below, it takes over 2 seconds to add??? If I add the second
row, instant.

How can I eliminate this delay?




Dim oRow As DataRow = dsOrder.Tables("PaymentsAndCredits").NewRow

oRow("ENumber") = ENumber
oRow("CNumber") = CNumber
oRow("PType") = type
oRow("PAmount") = amount
oRow("TNumber") = 0
oRow("TAdjustment") = 0
oRow("Applied") = False

dsOrder.Tables("PaymentsAndCredits").Rows.Add(oRow)
 
Eric Petruzzelli said:
If I fill my dataset and there is no data. The dataset is still created
with zero rows (all columns are there). When I add my first row using
the script below, it takes over 2 seconds to add??? If I add the second
row, instant.

How can I eliminate this delay?

Dim oRow As DataRow = dsOrder.Tables("PaymentsAndCredits").NewRow

oRow("ENumber") = ENumber
oRow("CNumber") = CNumber
oRow("PType") = type
oRow("PAmount") = amount
oRow("TNumber") = 0
oRow("TAdjustment") = 0
oRow("Applied") = False

dsOrder.Tables("PaymentsAndCredits").Rows.Add(oRow)
Click to expand...

2 seconds sounds like a very long time. Do you know which part it is
that's taking the time? Try dumping out DateTime.Now between each line,
so you can see where the time is taken.

You could just be seeing JITting, but it sounds unlikely if it's taking
2 seconds. What kind of machine is this on?
 
Jon,

It is running on my laptop, nothing wrong there. This is the only bottle
neck. And 2 seconds is not an exaggeration.

It occurs on the line:

dsorder.Tables("PaymentsAndCredits").rows.add(oROw)


It only happens with the first row. I redid my SQL Insert, Delete stmts
in case there was a dataType change. Nothing.

Eric.
 
Eric Petruzzelli said:
It is running on my laptop, nothing wrong there. This is the only bottle
neck. And 2 seconds is not an exaggeration.

It occurs on the line:

dsorder.Tables("PaymentsAndCredits").rows.add(oROw)


It only happens with the first row. I redid my SQL Insert, Delete stmts
in case there was a dataType change. Nothing.
Click to expand...

Do you have a lot of constraints involved which check other tables? Do
you get a similar hit if you just create the dataset from scratch
without involving the database anywhere, just adding simple columns?
 
No I do not have a lot of constraints.

I created a second table with the same construction. But with this one I
did not try to FILL from my SQL Server database. When I add the row (as
before) I do not get the delay.

So now I know the delay is when I try to FILL the dataset and there are
no records. My first column is an IDENTITY column which I use so I can
use the UPDATE command from the dataset. When I am adding the row {using
command: dsorder.Tables("PaymentsAndCredits").rows.add(oRow)} to the
datatable I am not using this IDENTITY column (since I do not know its
value).

With my test datatable (the one I just created) I can use DBNULL.VALUE
or just leave off the entire column and I have no problem. The row adds.
(I guess this is because we never tried to place an identity value or
primary key value in this column.) Then after I add the row and UPDATE
the database, SQL Server will autofill the next value.

Jon, What can I do with the first column (the IDENTITY column) without
a value? Or is there a way I can FILL my datatable without using this
IDENTITY column and still UPDATE any changes?

I hope this is clear. Thanks.

Eric.
 
Eric Petruzzelli said:
No I do not have a lot of constraints.

I created a second table with the same construction. But with this one I
did not try to FILL from my SQL Server database. When I add the row (as
before) I do not get the delay.

So now I know the delay is when I try to FILL the dataset and there are
no records.
Click to expand...

Hang on - do you get the delay when you fill the dataset and there
*are* records?
My first column is an IDENTITY column which I use so I can
use the UPDATE command from the dataset. When I am adding the row {using
command: dsorder.Tables("PaymentsAndCredits").rows.add(oRow)} to the
datatable I am not using this IDENTITY column (since I do not know its
value).

With my test datatable (the one I just created) I can use DBNULL.VALUE
or just leave off the entire column and I have no problem. The row adds.
(I guess this is because we never tried to place an identity value or
primary key value in this column.) Then after I add the row and UPDATE
the database, SQL Server will autofill the next value.

Jon, What can I do with the first column (the IDENTITY column) without
a value? Or is there a way I can FILL my datatable without using this
IDENTITY column and still UPDATE any changes?
Click to expand...

Unless you're wanting to modify existing records, I'd just issue a
query which doesn't fetch that column. However, I'm intrigued by this
whole business now. Could you work out a short but complete program
which does this (and nothing else - a console app would be great) and
post the table structure, so I could investigate it a bit further
myself? If all it takes is a fill and then a row addition, don't worry
about producing the code - I can do that - just the table details/query
etc.
 
No. I do not get a delay if there are records.

Hang on - do you get the delay when you fill the dataset and there
*are* records?



Unless you're wanting to modify existing records, I'd just issue a
query which doesn't fetch that column. However, I'm intrigued by this
whole business now. Could you work out a short but complete program
which does this (and nothing else - a console app would be great) and
post the table structure, so I could investigate it a bit further
myself? If all it takes is a fill and then a row addition, don't worry
about producing the code - I can do that - just the table details/query
etc.
Click to expand...
 
Eric Petruzzelli said:
No. I do not get a delay if there are records.
Click to expand...

That's very strange... any luck coming up with a test program I could
investigate?
 
Eric, try the following.
For the identity column do a FillSchema before you do the Fill on this
table and then set
AutoIncrement = true
AutoIncrementSeed = -1
AutoIncrementStep = -1
for the identity column.
You will then have to return the Identity value using SCOPE_IDENTITY()
from the InsertCommand. I usually just RETURN SCOPE_IDENTITY(); for this
and add a parameter to the InsertCommand connected to the identity column
with a Direction of ParameterDirection.ReturnValue.

Ron Allen
 
Jon,

I created a test program for you to look at. There was no delay on the
test program. So, I then looked to see what is different?

I found that I had a dataview filtering from my dataTable. Whenever I
had at least 1 record in my dataview already, my dataview updates
without a hitch. But the problem was when I filtered the dataview and it
contained no records (not the datatable). If I then add a new record
(the first one with the particular filter criteria) the program stalls
(2-3 seconds) in order to update the new dataview with the new criteria
(which I do want it to update).

Therefore, what I did is place a count when adding the new record. If
the count of the dataview is zero I dispose of this dataview and
recreate a new one then reset it as the datasource on my usercontrol. I
don't know if this is the most efficient way, but for now it works.

Are the dataviews supposed to act in this fashion?

Jon, thank you for your help.

eric.
 
Eric Petruzzelli said:
I created a test program for you to look at. There was no delay on the
test program. So, I then looked to see what is different?

I found that I had a dataview filtering from my dataTable. Whenever I
had at least 1 record in my dataview already, my dataview updates
without a hitch. But the problem was when I filtered the dataview and it
contained no records (not the datatable). If I then add a new record
(the first one with the particular filter criteria) the program stalls
(2-3 seconds) in order to update the new dataview with the new criteria
(which I do want it to update).

Therefore, what I did is place a count when adding the new record. If
the count of the dataview is zero I dispose of this dataview and
recreate a new one then reset it as the datasource on my usercontrol. I
don't know if this is the most efficient way, but for now it works.

Are the dataviews supposed to act in this fashion?
Click to expand...

I'm pretty sure they're not - that does sound very odd (if I followed
you correctly). Anyone else had that experience?
 
Back
Top