DataReader - RowsAffected property

  • Thread starter Thread starter J L
  • Start date Start date
J

J L

Why and how is this property used?

Documentation says it is number of rows changed, inserted, deleted and
can only be used after the DataReader is closed. I thought the
DataReader was for forward read only access...so how can there be rows
changed/inserted/deleted?

TIA
John
 
John,

In Net1.0 and 1.1 you can only have one open connection at a time this will
change in 2.0.

I hope this helps,

Cor
 
Hi Cor,
Sorry but I dont understand what the number of connections has to do
with the RowsAffected property.

John
 
JL,
Sorry but I dont understand what the number of connections has to do
with the RowsAffected property.
Click to expand...
Maybe I don't understand you however how will you do a update when the
datatreader is still open (with its connection) and you use for the update a
connection and the command.executenonquery?

Cor
 
JL,

A DataReader is the result of a DbCommand.ExecuteReader.
Now the DbCommand might have any command text - say a batched SQL command
that might contain both inserts and updates. RecordsAffected will help you
get information out of those.

You might ask "Why in the world would I want to batch my select statement
with an insert statement"
Well say if you inserted a row, and now you wish to retreive the latest
generated identity value, you could have code that looks somewhat like this.

private const string CONN_STR = "Server=(local);Database=Test;Integrated
Security=SSPI";
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(CONN_STR))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into Animals (AnimalType) values ('Parrot');
Select * from Animals";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr.RecordsAffected);
System.Diagnostics.Debugger.Break();
}
}
}
... Obviously (before anyone corrects me), the better way to retreive
identites would be SCOPE_IDENTITY(). But hey .. I'm just tryin' to make a
point here :-)
All this is explained in my upcoming book on ADO.NET 2.0 and more !!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
I see your point. Good example.

One minor question...the doc says the RowsAffected is not available
until the reader is closed. So in your example, would you move the
writeline reference outside the loop and close the reader first?

John


JL,

A DataReader is the result of a DbCommand.ExecuteReader.
Now the DbCommand might have any command text - say a batched SQL command
that might contain both inserts and updates. RecordsAffected will help you
get information out of those.

You might ask "Why in the world would I want to batch my select statement
with an insert statement"
Well say if you inserted a row, and now you wish to retreive the latest
generated identity value, you could have code that looks somewhat like this.

private const string CONN_STR = "Server=(local);Database=Test;Integrated
Security=SSPI";
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(CONN_STR))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into Animals (AnimalType) values ('Parrot');
Select * from Animals";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr.RecordsAffected);
System.Diagnostics.Debugger.Break();
}
}
}
.. Obviously (before anyone corrects me), the better way to retreive
identites would be SCOPE_IDENTITY(). But hey .. I'm just tryin' to make a
point here :-)
All this is explained in my upcoming book on ADO.NET 2.0 and more !!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/





J L said:
Why and how is this property used?

Documentation says it is number of rows changed, inserted, deleted and
can only be used after the DataReader is closed. I thought the
DataReader was for forward read only access...so how can there be rows
changed/inserted/deleted?

TIA
John
Click to expand...
Click to expand...
 
Man ru serious? I mean .. it depends. If your batched statement looks like
this.

Insert ; Select.

Then RecordsAffected should be available before .. you can actually simply
copy paste my example and run it and find out for yourself.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



J L said:
I see your point. Good example.

One minor question...the doc says the RowsAffected is not available
until the reader is closed. So in your example, would you move the
writeline reference outside the loop and close the reader first?

John


JL,

A DataReader is the result of a DbCommand.ExecuteReader.
Now the DbCommand might have any command text - say a batched SQL command
that might contain both inserts and updates. RecordsAffected will help you
get information out of those.

You might ask "Why in the world would I want to batch my select statement
with an insert statement"
Well say if you inserted a row, and now you wish to retreive the latest
generated identity value, you could have code that looks somewhat like this.

private const string CONN_STR = "Server=(local);Database=Test;Integrated
Security=SSPI";
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(CONN_STR))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into Animals (AnimalType) values ('Parrot');
Select * from Animals";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr.RecordsAffected);
System.Diagnostics.Debugger.Break();
}
}
}
.. Obviously (before anyone corrects me), the better way to retreive
identites would be SCOPE_IDENTITY(). But hey .. I'm just tryin' to make a
point here :-)
All this is explained in my upcoming book on ADO.NET 2.0 and more !!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/





J L said:
Why and how is this property used?

Documentation says it is number of rows changed, inserted, deleted and
can only be used after the DataReader is closed. I thought the
DataReader was for forward read only access...so how can there be rows
changed/inserted/deleted?

TIA
John
Click to expand...
Click to expand...
Click to expand...
 
I did try but can not get the query to work. I get a "Characters found
after end of SQL statement" error when I try the combined SQL of
Insert and Select. Individually they work fine and I just concatenate
them with an ; between. If you have time, please advise why I get this
error.

My exact SQL is:

INSERT INTO Operators (OperatorID, OperatorName) VALUES ('12345',
'John'); SELECT * FROM Operators;

Thanks,
John
Man ru serious? I mean .. it depends. If your batched statement looks like
this.

Insert ; Select.

Then RecordsAffected should be available before .. you can actually simply
copy paste my example and run it and find out for yourself.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



J L said:
I see your point. Good example.

One minor question...the doc says the RowsAffected is not available
until the reader is closed. So in your example, would you move the
writeline reference outside the loop and close the reader first?

John


JL,

A DataReader is the result of a DbCommand.ExecuteReader.
Now the DbCommand might have any command text - say a batched SQL command
that might contain both inserts and updates. RecordsAffected will help you
get information out of those.

You might ask "Why in the world would I want to batch my select statement
with an insert statement"
Well say if you inserted a row, and now you wish to retreive the latest
generated identity value, you could have code that looks somewhat like this.

private const string CONN_STR = "Server=(local);Database=Test;Integrated
Security=SSPI";
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(CONN_STR))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into Animals (AnimalType) values ('Parrot');
Select * from Animals";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr.RecordsAffected);
System.Diagnostics.Debugger.Break();
}
}
}
.. Obviously (before anyone corrects me), the better way to retreive
identites would be SCOPE_IDENTITY(). But hey .. I'm just tryin' to make a
point here :-)
All this is explained in my upcoming book on ADO.NET 2.0 and more !!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/





Why and how is this property used?

Documentation says it is number of rows changed, inserted, deleted and
can only be used after the DataReader is closed. I thought the
DataReader was for forward read only access...so how can there be rows
changed/inserted/deleted?

TIA
John
Click to expand...
Click to expand...
Click to expand...
 
Hmm .. I tried that in .NET 2.0.

Let me try that in .NET 1.1 later tonight and get back at you?


- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



J L said:
I did try but can not get the query to work. I get a "Characters found
after end of SQL statement" error when I try the combined SQL of
Insert and Select. Individually they work fine and I just concatenate
them with an ; between. If you have time, please advise why I get this
error.

My exact SQL is:

INSERT INTO Operators (OperatorID, OperatorName) VALUES ('12345',
'John'); SELECT * FROM Operators;

Thanks,
John
Man ru serious? I mean .. it depends. If your batched statement looks like
this.

Insert ; Select.

Then RecordsAffected should be available before .. you can actually simply
copy paste my example and run it and find out for yourself.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



J L said:
I see your point. Good example.

One minor question...the doc says the RowsAffected is not available
until the reader is closed. So in your example, would you move the
writeline reference outside the loop and close the reader first?

John


On Mon, 28 Mar 2005 02:20:44 -0500, "Sahil Malik"

JL,

A DataReader is the result of a DbCommand.ExecuteReader.
Now the DbCommand might have any command text - say a batched SQL command
that might contain both inserts and updates. RecordsAffected will help you
get information out of those.

You might ask "Why in the world would I want to batch my select statement
with an insert statement"
Well say if you inserted a row, and now you wish to retreive the latest
generated identity value, you could have code that looks somewhat like this.

private const string CONN_STR = "Server=(local);Database=Test;Integrated
Security=SSPI";
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(CONN_STR))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into Animals (AnimalType) values ('Parrot');
Select * from Animals";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr.RecordsAffected);
System.Diagnostics.Debugger.Break();
}
}
}
.. Obviously (before anyone corrects me), the better way to retreive
identites would be SCOPE_IDENTITY(). But hey .. I'm just tryin' to make a
point here :-)
All this is explained in my upcoming book on ADO.NET 2.0 and more !!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/





Why and how is this property used?

Documentation says it is number of rows changed, inserted, deleted and
can only be used after the DataReader is closed. I thought the
DataReader was for forward read only access...so how can there be rows
changed/inserted/deleted?

TIA
John
Click to expand...
Click to expand...
Click to expand...
 
That would be great. Thanks.

John

Hmm .. I tried that in .NET 2.0.

Let me try that in .NET 1.1 later tonight and get back at you?


- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



J L said:
I did try but can not get the query to work. I get a "Characters found
after end of SQL statement" error when I try the combined SQL of
Insert and Select. Individually they work fine and I just concatenate
them with an ; between. If you have time, please advise why I get this
error.

My exact SQL is:

INSERT INTO Operators (OperatorID, OperatorName) VALUES ('12345',
'John'); SELECT * FROM Operators;

Thanks,
John
Man ru serious? I mean .. it depends. If your batched statement looks like
this.

Insert ; Select.

Then RecordsAffected should be available before .. you can actually simply
copy paste my example and run it and find out for yourself.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



I see your point. Good example.

One minor question...the doc says the RowsAffected is not available
until the reader is closed. So in your example, would you move the
writeline reference outside the loop and close the reader first?

John


On Mon, 28 Mar 2005 02:20:44 -0500, "Sahil Malik"

JL,

A DataReader is the result of a DbCommand.ExecuteReader.
Now the DbCommand might have any command text - say a batched SQL command
that might contain both inserts and updates. RecordsAffected will help
you
get information out of those.

You might ask "Why in the world would I want to batch my select statement
with an insert statement"
Well say if you inserted a row, and now you wish to retreive the latest
generated identity value, you could have code that looks somewhat like
this.

private const string CONN_STR = "Server=(local);Database=Test;Integrated
Security=SSPI";
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(CONN_STR))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into Animals (AnimalType) values ('Parrot');
Select * from Animals";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr.RecordsAffected);
System.Diagnostics.Debugger.Break();
}
}
}
.. Obviously (before anyone corrects me), the better way to retreive
identites would be SCOPE_IDENTITY(). But hey .. I'm just tryin' to make a
point here :-)
All this is explained in my upcoming book on ADO.NET 2.0 and more !!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/





Why and how is this property used?

Documentation says it is number of rows changed, inserted, deleted and
can only be used after the DataReader is closed. I thought the
DataReader was for forward read only access...so how can there be rows
changed/inserted/deleted?

TIA
John
Click to expand...
Click to expand...
Click to expand...
 
Confirmed. .NET 1.1 behavior is different from 2.0.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/


J L said:
That would be great. Thanks.

John

Hmm .. I tried that in .NET 2.0.

Let me try that in .NET 1.1 later tonight and get back at you?


- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



J L said:
I did try but can not get the query to work. I get a "Characters found
after end of SQL statement" error when I try the combined SQL of
Insert and Select. Individually they work fine and I just concatenate
them with an ; between. If you have time, please advise why I get this
error.

My exact SQL is:

INSERT INTO Operators (OperatorID, OperatorName) VALUES ('12345',
'John'); SELECT * FROM Operators;

Thanks,
John
On Mon, 28 Mar 2005 10:26:02 -0500, "Sahil Malik"

Man ru serious? I mean .. it depends. If your batched statement looks like
this.

Insert ; Select.

Then RecordsAffected should be available before .. you can actually simply
copy paste my example and run it and find out for yourself.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



I see your point. Good example.

One minor question...the doc says the RowsAffected is not available
until the reader is closed. So in your example, would you move the
writeline reference outside the loop and close the reader first?

John


On Mon, 28 Mar 2005 02:20:44 -0500, "Sahil Malik"

JL,

A DataReader is the result of a DbCommand.ExecuteReader.
Now the DbCommand might have any command text - say a batched SQL command
that might contain both inserts and updates. RecordsAffected will
help
you
get information out of those.

You might ask "Why in the world would I want to batch my select statement
with an insert statement"
Well say if you inserted a row, and now you wish to retreive the latest
generated identity value, you could have code that looks somewhat
like
this.

private const string CONN_STR = "Server=(local);Database=Test;Integrated
Security=SSPI";
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(CONN_STR))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into Animals (AnimalType) values
('Parrot');
Select * from Animals";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr.RecordsAffected);
System.Diagnostics.Debugger.Break();
}
}
}
.. Obviously (before anyone corrects me), the better way to retreive
identites would be SCOPE_IDENTITY(). But hey .. I'm just tryin' to make a
point here :-)
All this is explained in my upcoming book on ADO.NET 2.0 and more !!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/





Why and how is this property used?

Documentation says it is number of rows changed, inserted, deleted and
can only be used after the DataReader is closed. I thought the
DataReader was for forward read only access...so how can there be rows
changed/inserted/deleted?

TIA
John
Click to expand...
Click to expand...
Click to expand...
 
Thanks Sahil...good to know.

John

Confirmed. .NET 1.1 behavior is different from 2.0.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/


J L said:
That would be great. Thanks.

John

Hmm .. I tried that in .NET 2.0.

Let me try that in .NET 1.1 later tonight and get back at you?


- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



I did try but can not get the query to work. I get a "Characters found
after end of SQL statement" error when I try the combined SQL of
Insert and Select. Individually they work fine and I just concatenate
them with an ; between. If you have time, please advise why I get this
error.

My exact SQL is:

INSERT INTO Operators (OperatorID, OperatorName) VALUES ('12345',
'John'); SELECT * FROM Operators;

Thanks,
John
On Mon, 28 Mar 2005 10:26:02 -0500, "Sahil Malik"

Man ru serious? I mean .. it depends. If your batched statement looks
like
this.

Insert ; Select.

Then RecordsAffected should be available before .. you can actually
simply
copy paste my example and run it and find out for yourself.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



I see your point. Good example.

One minor question...the doc says the RowsAffected is not available
until the reader is closed. So in your example, would you move the
writeline reference outside the loop and close the reader first?

John


On Mon, 28 Mar 2005 02:20:44 -0500, "Sahil Malik"

JL,

A DataReader is the result of a DbCommand.ExecuteReader.
Now the DbCommand might have any command text - say a batched SQL
command
that might contain both inserts and updates. RecordsAffected will
help
you
get information out of those.

You might ask "Why in the world would I want to batch my select
statement
with an insert statement"
Well say if you inserted a row, and now you wish to retreive the
latest
generated identity value, you could have code that looks somewhat
like
this.

private const string CONN_STR =
"Server=(local);Database=Test;Integrated
Security=SSPI";
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(CONN_STR))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert into Animals (AnimalType) values
('Parrot');
Select * from Animals";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr.RecordsAffected);
System.Diagnostics.Debugger.Break();
}
}
}
.. Obviously (before anyone corrects me), the better way to retreive
identites would be SCOPE_IDENTITY(). But hey .. I'm just tryin' to
make a
point here :-)
All this is explained in my upcoming book on ADO.NET 2.0 and more !!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/





Why and how is this property used?

Documentation says it is number of rows changed, inserted, deleted
and
can only be used after the DataReader is closed. I thought the
DataReader was for forward read only access...so how can there be
rows
changed/inserted/deleted?

TIA
John
Click to expand...
Click to expand...
Click to expand...
 
Back
Top