Strongly Typed DataSets and "Refresh the data table" Option

  • Thread starter Thread starter Max2006
  • Start date Start date
M

Max2006

Hi,

Using Visual Studio 2005 and SQL Server 2005:

I am trying to understand the stored procedures that Strongly Typed Datasets
wizard generates for me.

When we create a new strongly typed Datasets, and choose the following path:

Choose connection-> Create new stored procedures -> Enter SQL (select * from
tbl) -> Advanced Options -> Refresh the data table

We have an advanced option to refresh the data table. When we check the
option, the wizard adds a select statement to the end of SP_INSERT and
SP_UPDATE.

I don't understand how the result of the added select statement (to the
insert and update stored procedures) are being read back by the internal
DataAdapter. What makes the table adapter reads the result of the select
statement after insert or update operations?

A link to an online article would greatly help.

Thank you,
Max
 
Hello Max,

It seems you want to know SQLDataAdapter's implementation about how to read
back the added/updated row and update the related row. If I misunderstand,
please correct me. Thanks.

For Typed Dataset Wizard, after you entered Select SQL statement, it will
use SqlCommandBuilder to generate the insert/delete/update SQL statement,
and then create the SP_INSERT/SP_UPDATE for us. If you check the REFRESH
THE DATA TABLE option in Adavanced Option, the generated stored procedure
is also included a select statement which will return the modified datarow.

PROCEDURE SP_UPDATE
....
UPDATE [dbo].[Table_1] SET [c2] = @c2, [c3] = @c3 WHERE (([c1] =
@Original_c1) AND ((@IsNull_c2 = 1 AND [c2] IS NULL) OR ([c2] =
@Original_c2)) AND ((@IsNull_c3 = 1 AND [c3] IS NULL) OR ([c3] =
@Original_c3)));
SELECT c1, c2, c3 FROM Table_1 WHERE (c1 = @c1)

After executed the SP_INSERT/SP_UPDATE, you can notice the stored procedure
not only insert/update the row in the database, but also return a record
set which identify the changes in underlying database.

For SQLDataAdapter, its update method will check the RowState property of
each row. If it is modified, The SqlDataAdapter calls the ExecuteReader
method on the SqlCommand object stored in its UPDATECOMMAND property, and
it checks the RecordsAffected property of the resulting SqlDataReader to
determine whether to apply values from the SqlDataReader to the DataRow.
Then, if the SqlDataAdapter determines that submitting the pending change
in a DataRow succeeded, the SqlDataAdapter checks the SqlCommand's
UpdatedRowSource property to determine how (or whether) it should apply
values returned by the SqlCommand to the DataRow

UpdatedRowSource:
Both Tells the SqlCommand to fetch new data for the row using both the
first returned record and output parameters. This is the default.
FirstReturnedRecord Tells the SqlCommand to fetch new data for the row
through the first returned record.
None Tells the SqlCommand not to fetch new data for the row upon execution.
OutputParameters Tells the SqlCommand to fetch new data for the row using
output parameters.

In your case, the UpdatedRowSource property is BOTH(by default). The
SQLDataAdatper will check both OutputParamets and FirstReturnedRecord.
However, due to no output parameter in this case, SQLDataAdatper use
SQLDadaReader.Read() method to retrieve the Fist Returned Record and modify
the related filed in current updated row.

This behavior has been mentioned in book <Programming Microsoft? ADO.NET
2.0 Core Reference> by David Sceppa.
Chapter 11. Advanced Updating Scenarios
- Refreshing a Row After Submitting an Update
- - Using Batch Queries to Retrieve Data After You Submit an Update

Hope this helps. If you still have anything unclear, feel free to update
here. We're glad to assist you.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thank you very much Wen Yaun for this comprehensive answer.
Do you know anywhere in MSDN that actually document this?

Thank you again,
Max



WenYuan Wang said:
Hello Max,

It seems you want to know SQLDataAdapter's implementation about how to
read
back the added/updated row and update the related row. If I misunderstand,
please correct me. Thanks.

For Typed Dataset Wizard, after you entered Select SQL statement, it will
use SqlCommandBuilder to generate the insert/delete/update SQL statement,
and then create the SP_INSERT/SP_UPDATE for us. If you check the REFRESH
THE DATA TABLE option in Adavanced Option, the generated stored procedure
is also included a select statement which will return the modified
datarow.

PROCEDURE SP_UPDATE
...
UPDATE [dbo].[Table_1] SET [c2] = @c2, [c3] = @c3 WHERE (([c1] =
@Original_c1) AND ((@IsNull_c2 = 1 AND [c2] IS NULL) OR ([c2] =
@Original_c2)) AND ((@IsNull_c3 = 1 AND [c3] IS NULL) OR ([c3] =
@Original_c3)));
SELECT c1, c2, c3 FROM Table_1 WHERE (c1 = @c1)

After executed the SP_INSERT/SP_UPDATE, you can notice the stored
procedure
not only insert/update the row in the database, but also return a record
set which identify the changes in underlying database.

For SQLDataAdapter, its update method will check the RowState property of
each row. If it is modified, The SqlDataAdapter calls the ExecuteReader
method on the SqlCommand object stored in its UPDATECOMMAND property, and
it checks the RecordsAffected property of the resulting SqlDataReader to
determine whether to apply values from the SqlDataReader to the DataRow.
Then, if the SqlDataAdapter determines that submitting the pending change
in a DataRow succeeded, the SqlDataAdapter checks the SqlCommand's
UpdatedRowSource property to determine how (or whether) it should apply
values returned by the SqlCommand to the DataRow

UpdatedRowSource:
Both Tells the SqlCommand to fetch new data for the row using both the
first returned record and output parameters. This is the default.
FirstReturnedRecord Tells the SqlCommand to fetch new data for the row
through the first returned record.
None Tells the SqlCommand not to fetch new data for the row upon
execution.
OutputParameters Tells the SqlCommand to fetch new data for the row using
output parameters.

In your case, the UpdatedRowSource property is BOTH(by default). The
SQLDataAdatper will check both OutputParamets and FirstReturnedRecord.
However, due to no output parameter in this case, SQLDataAdatper use
SQLDadaReader.Read() method to retrieve the Fist Returned Record and
modify
the related filed in current updated row.

This behavior has been mentioned in book <Programming Microsoft? ADO.NET
2.0 Core Reference> by David Sceppa.
Chapter 11. Advanced Updating Scenarios
- Refreshing a Row After Submitting an Update
- - Using Batch Queries to Retrieve Data After You Submit an Update

Hope this helps. If you still have anything unclear, feel free to update
here. We're glad to assist you.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
Click to expand...
 
Hello Max

Actually, I have searched on MSDN website before my first reply. However, I
could not found related articles so far.

Have you tried Reflector? This tool is really grateful. We usually use it.
It could disassemble the .net class for us.
http://www.aisto.com/roeder/dotnet/
[Reflector for .NET]

Hope this helps. If you have any more concern or you have anything unclear,
please feel free to update here. We are really glad to assist you.

Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
You are welcome, Max.
I'm glad to work with you.

Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Can you post some code snipplets to show us how the table adapter reads the result of the "appended select SCOPE_IDENTITY" statement after an insert or update operation?

Hi,

Using Visual Studio 2005 and SQL Server 2005:

I am trying to understand the stored procedures that Strongly Typed Datasets
wizard generates for me.

When we create a new strongly typed Datasets, and choose the following path:

Choose connection-> Create new stored procedures -> Enter SQL (select * from
tbl) -> Advanced Options -> Refresh the data table

We have an advanced option to refresh the data table. When we check the
option, the wizard adds a select statement to the end of SP_INSERT and
SP_UPDATE.

I don't understand how the result of the added select statement (to the
insert and update stored procedures) are being read back by the internal
DataAdapter. What makes the table adapter reads the result of the select
statement after insert or update operations?

A link to an online article would greatly help.

Thank you,
Max
On Monday, July 16, 2007 2:20 AM v-wywan wrote:
Hello Max,

It seems you want to know SQLDataAdapter's implementation about how to read
back the added/updated row and update the related row. If I misunderstand,
please correct me. Thanks.

For Typed Dataset Wizard, after you entered Select SQL statement, it will
use SqlCommandBuilder to generate the insert/delete/update SQL statement,
and then create the SP_INSERT/SP_UPDATE for us. If you check the REFRESH
THE DATA TABLE option in Adavanced Option, the generated stored procedure
is also included a select statement which will return the modified datarow.

PROCEDURE SP_UPDATE
...
UPDATE [dbo].[Table_1] SET [c2] = @c2, [c3] = @c3 WHERE (([c1] =
@Original_c1) AND ((@IsNull_c2 = 1 AND [c2] IS NULL) OR ([c2] =
@Original_c2)) AND ((@IsNull_c3 = 1 AND [c3] IS NULL) OR ([c3] =
@Original_c3)));
SELECT c1, c2, c3 FROM Table_1 WHERE (c1 = @c1)

After executed the SP_INSERT/SP_UPDATE, you can notice the stored procedure
not only insert/update the row in the database, but also return a record
set which identify the changes in underlying database.

For SQLDataAdapter, its update method will check the RowState property of
each row. If it is modified, The SqlDataAdapter calls the ExecuteReader
method on the SqlCommand object stored in its UPDATECOMMAND property, and
it checks the RecordsAffected property of the resulting SqlDataReader to
determine whether to apply values from the SqlDataReader to the DataRow.
Then, if the SqlDataAdapter determines that submitting the pending change
in a DataRow succeeded, the SqlDataAdapter checks the SqlCommand's
UpdatedRowSource property to determine how (or whether) it should apply
values returned by the SqlCommand to the DataRow

UpdatedRowSource:
Both Tells the SqlCommand to fetch new data for the row using both the
first returned record and output parameters. This is the default.
FirstReturnedRecord Tells the SqlCommand to fetch new data for the row
through the first returned record.
None Tells the SqlCommand not to fetch new data for the row upon execution.
OutputParameters Tells the SqlCommand to fetch new data for the row using
output parameters.

In your case, the UpdatedRowSource property is BOTH(by default). The
SQLDataAdatper will check both OutputParamets and FirstReturnedRecord.
However, due to no output parameter in this case, SQLDataAdatper use
SQLDadaReader.Read() method to retrieve the Fist Returned Record and modify
the related filed in current updated row.

This behavior has been mentioned in book <Programming Microsoft? ADO.NET
2.0 Core Reference> by David Sceppa.
Chapter 11. Advanced Updating Scenarios
- Refreshing a Row After Submitting an Update
- - Using Batch Queries to Retrieve Data After You Submit an Update

Hope this helps. If you still have anything unclear, feel free to update
here. We're glad to assist you.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Click to expand...
On Wednesday, July 18, 2007 6:33 AM v-wywan wrote:
Hello Max

Actually, I have searched on MSDN website before my first reply. However, I
could not found related articles so far.

Have you tried Reflector? This tool is really grateful. We usually use it.
It could disassemble the .net class for us.
http://www.aisto.com/roeder/dotnet/
[Reflector for .NET]

Hope this helps. If you have any more concern or you have anything unclear,
please feel free to update here. We are really glad to assist you.

Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top