DataSet - adding new row

[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 )

[Jon Skeet [C# MVP]]

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

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 )

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 Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Eric Petruzzelli]

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.

Jon Skeet [C# MVP] wrote:

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

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 )

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 Skeet [C# MVP]]

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

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.

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?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Eric Petruzzelli]

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.
Jon Skeet [C# MVP] wrote:

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

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(oR Ow)
It only happens with the first row. I redid my SQL Insert, Delete stmts
in case there was a dataType change. Nothing.

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?

[Jon Skeet [C# MVP]]

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

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.
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?

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.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Eric Petruzzelli]

No. I do not get a delay if there are records.
Jon Skeet [C# MVP] wrote:

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

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.

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?

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.

[Jon Skeet [C# MVP]]

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

No. I do not get a delay if there are records.

That's very strange... any luck coming up with a test program I could
investigate?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Ron Allen]

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

"Eric Petruzzelli" <eg*@e-globalpartners.com> wrote in message
news:40**************@e-globalpartners.com...

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.
Jon Skeet [C# MVP] wrote:

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

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(oR Ow)
It only happens with the first row. I redid my SQL Insert, Delete stmts
in case there was a dataType change. Nothing.

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?

[Eric Petruzzelli]

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.

Jon Skeet [C# MVP] wrote:

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

No. I do not get a delay if there are records.

That's very strange... any luck coming up with a test program I could
investigate?

[Jon Skeet [C# MVP]]

Eric Petruzzelli <eg*@e-globalpartners.com> wrote:

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?

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

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too